Solved

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

Posted on 2013-06-18
9
309 Views
Last Modified: 2013-06-20
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
Comment
Question by:fabi2004
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
9 Comments
 
LVL 20

Assisted Solution

by:GrahamMandeno
GrahamMandeno earned 250 total points
ID: 39258046
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
 
LVL 1

Author Comment

by:fabi2004
ID: 39258110
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
 
LVL 20

Expert Comment

by:GrahamMandeno
ID: 39258309
That makes sense.  You don't have any records in tblPhysicians!

What exactly were you trying to do?
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 1

Author Comment

by:fabi2004
ID: 39259765
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
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 39259957
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
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 250 total points
ID: 39259970
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
 
LVL 1

Author Comment

by:fabi2004
ID: 39259983
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
 
LVL 1

Author Comment

by:fabi2004
ID: 39259996
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
 
LVL 1

Author Comment

by:fabi2004
ID: 39264506
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

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Out in left field or not to far off base 8 70
Access Form - Button Format Issue 13 51
Calculation in a Report 13 43
Access 2003 query, remove dupes 17 26
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question