Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 325
  • Last Modified:

Can I use a form to enter information into various tables?

I seem to be making a mess of this new database.  I really need a form that looks a lot like an Excel spreadsheet.  The form needs to be used to both view and enter data.

I am going to try to attach the database I'm working on and the Excel spreadsheet it is replacing.

Any help would be greatly appreciated!

Oops, I forgot to mention the problem I'm having is that I cannot enter any information into the form.  Not only does it not populate the tables, but it won't even accept input.
HUB.xlsx
HUB.accdb
0
fabi2004
Asked:
fabi2004
  • 5
  • 2
  • 2
2 Solutions
 
GrahamMandenoCommented:
The message in the status bar when you try to enter data into your form is:
"Cannot add record(s); join key of table 'tblHubData' not in recordset"

You have three tables in your RecordSource query: the "main" table, tblHubData, and the two related tables, tblClients and tblPhysicians.  tblPhysicians is joined to tblHubData by the field tblHubData.Physician, but you have not included this field in you query.

Open the RecordSource query and double-click on "Physician" in tblHubData to add that field.

Then, set the ControlSource of your "Physician" combo box to "Physician" instead of "PhyLName".

Then you will be able to add records.

Best wishes,
Graham Mandeno [Access MVP 1996-2013]
0
 
fabi2004CIOAuthor Commented:
That made some of the fields work.  But now I got this message...attached and the revised db attached to.

I really appreciate your help!
error.docx
HUB.accdb
0
 
GrahamMandenoCommented:
That makes sense.  You don't have any records in tblPhysicians!

What exactly were you trying to do?
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
fabi2004CIOAuthor Commented:
I need to use the form to enter information into the underlying tables as well as view date filtered informatin.  If a physician does not exist in the tblPhysicians, I need to be able to add it.
0
 
Helen FeddemaCommented:
Generally speaking, a recordsource that includes several queries is going to be read-only.  Since you need to enter data, you would probably be better off with a main form and several linked subforms.  This will allow you to enter data.  

Forget about making it look like an Excel workbook.  Workbooks don't support normalized data, and Access does.

You might need many-to-many relationships between tblHubData and the other tables, since most likely a physician or client would be linked to multiple hub data records.  See my Access Archon #178 for a sample database illustrating forms for entering data in a many-to-many relationship.

http://www.helenfeddema.com/Files/accarch178.zip

Many-to-many form
0
 
Helen FeddemaCommented:
If only one physician and one client can be entered per hub record, you could get away with using the Physicians and Clients tables as lookup tables, with add-to combo boxes for selecting one (and adding new ones).  Here is code for an add-to combo box that opens a separate form for filling in new data:

Private Sub cbo________________NotInList(strNewData As String, intResponse As Integer)
'Created by Helen Feddema 7-May-2007
'Last modified 7-May-2007

On Error GoTo ErrorHandler
   
   Dim cbo As Access.ComboBox
   Dim dbs As DAO.Database
   Dim frm As Access.Form
   Dim intMsgDialog As Integer
   Dim intResult As Integer
   Dim rst As DAO.Recordset
   Dim strDescription As String
   Dim strEntry As String
   Dim strFieldName As String
   Dim strFilter As String
   Dim strForm As String
   Dim strMsg As String
   Dim strMsg1 As String
   Dim strMsg2 As String
   Dim strTable As String
   Dim strTitle As String
      
   'The name of the table that is the combo box's row source
   strTable = "_______________"
  
   'The name of the form
   strForm = "_______________"

   'The type of item to add to the table
   strEntry = "_______________"
  
   'The field in the lookup table in which the new entry is stored
   strFieldName = "_____________"
  
   'The add-to combo box
   Set cbo = Me.ActiveControl

   'Display a message box asking whether the user wants to add
   'a new entry.
   strTitle = strEntry & " not in list"
   intMsgDialog = vbYesNo + vbExclamation + vbDefaultButton1
   strMsg1 = "Do you want to add "
   strMsg2 = " as a new " & strEntry & " entry?"
   strMsg = strMsg1 + strNewData + strMsg2
   intResult = MsgBox(strMsg, intMsgDialog, strTitle)

   If intResult = vbNo Then
      'Cancel adding the new entry to the lookup table.
      intResponse = acDataErrContinue
      cbo.Undo
      GoTo ErrorHandlerExit
   ElseIf intResult = vbYes Then
      'Add a new record to the lookup table.
      Set dbs = CurrentDb
      Set rst = dbs.OpenRecordset(strTable)
      rst.AddNew
      rst.Fields(strFieldName) = strNewData
      rst.Update
      rst.Close
   
      cbo.Undo
      
      'Continue without displaying default error message
      intResponse = acDataErrContinue
   
      'Open form for adding more data for new item
      DoCmd.OpenForm strForm
      Set frm = Forms(strForm)
      strFilter = "[" & strFieldName & "] = " & Chr$(39) _
         & strNewData & Chr$(39)
      Debug.Print "Filter string: " & strFilter
      frm.FilterOn = True
      frm.Filter = strFilter
   End If
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub


=============================
Code for form buttons
=============================

Private txt As Access.TextBox
Private cbo As Access.ComboBox
Private frm As Access.Form
Private prj As Object
Private strCallingForm As String
   

Private Sub cmdDiscard_Click()
'Created by Helen Feddema 20-Mar-2007
'Last modified 20-Mar-2007

On Error GoTo ErrorHandler
   
   Set prj = Application.CurrentProject
   
   'Name of the form with the add-to combo box
   strCallingForm = "_______________"
   
   DoCmd.SetWarnings False
   DoCmd.RunCommand acCmdDeleteRecord
   If prj.AllForms(strCallingForm).IsLoaded Then
      Forms(strCallingForm).Visible = True
   Else
      DoCmd.OpenForm strCallingForm
   End If
   
   Set frm = Forms(strCallingForm)
   
   'Name of add-to combo box
   Set cbo = frm![_______________]
   cbo.Requery

ErrorHandlerExit:
   DoCmd.Close acForm, Me.Name
   Exit Sub

ErrorHandler:
   If Err.Number = 2467 Then
      Resume ErrorHandlerExit
   Else
      MsgBox "Error No: " & Err.Number _
         & " in " & Me.ActiveControl.Name & " procedure; " _
         & "Description: " & Err.Description
   End If
   
End Sub

Private Sub cmdSave_Click()
'Created by Helen Feddema 20-Mar-2007
'Last modified 20-Mar-2007

On Error GoTo ErrorHandler
   
   Set prj = Application.CurrentProject
   
   'Name of the form with the add-to combo box
   strCallingForm = "_______________"
   
   'The textbox control on this form that holds the key value
   Set txt = Me![____________]
   
   If prj.AllForms(strCallingForm).IsLoaded Then
      Forms(strCallingForm).Visible = True
   Else
      DoCmd.OpenForm strCallingForm
   End If
      
   Set frm = Forms(strCallingForm)
   
   'Name of add-to combo box
   Set cbo = frm![_______________]
   cbo.Requery
   cbo.Value = Nz(txt.Value)
      
ErrorHandlerExit:
   DoCmd.Close acForm, Me.Name
   Exit Sub

ErrorHandler:
   If Err.Number = 2467 Then
      Resume ErrorHandlerExit
   Else
      MsgBox "Error No: " & Err.Number _
         & " in " & Me.ActiveControl.Name & " procedure; " _
         & "Description: " & Err.Description
      Resume ErrorHandlerExit
   End If

End Sub

Open in new window

0
 
fabi2004CIOAuthor Commented:
Helen, thanks for posting.  I had already headed in that direction, as you can see with the CR form and the HubSubform.  Making it all work is the problem.

I wish I could forget about making it look/feel like Excel.  But I know my execs, if it looks or feels too different they won't use it.  Many such past experiences with them.

Thank you so much for the link.  I'm going to go look right now.
0
 
fabi2004CIOAuthor Commented:
Just saw your second post.  That's exactly what I need.  But not only for the Physicians and Clients tables but also for the Employees table and occassionally maybe even for the Specialties table too.

All in the name of making it as user friendly and minimizing the learning curve for management who is extremely comfortable with Excel but has rarely seen Access.
0
 
fabi2004CIOAuthor Commented:
Thank you both so much.  

I am going to create a new question because I think I need to back up and start over with this form.  Maybe if I can started it correctly some of the errors will resolve themselves.

new question:  How to create a form to enter information into multiple tables  (sry, I tried to link to it but it didn't work)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 5
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now