Solved

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

Posted on 2013-06-18
9
304 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 31

Expert Comment

by:Helen_Feddema
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

744 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

16 Experts available now in Live!

Get 1:1 Help Now