Solved

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

Posted on 2013-06-18
9
305 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
  • 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
 
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

896 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now