I'm sure that this is very simple but it's driving me mad, please help. I have a database with many related tables and would like for a new record to be created in all related tables when a new record is added to a central table. For example one table is a list of plant species abbreviations, when a new abbreviation record is added I would like all of the related tables to have a record added for this abbreviation with the rest of their fields left blank ready for input. Does this make any sense to anybody?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Automatically add record to linked tables
- Thread starter SciBoy
- Start date
Hi,
Lets say your main table has a field PlantID. This contains the name of the plant, and a description.
Your second table is, lets say, a notes table. Where for each plant you can attach a note.
Personally, I would put the ID (or abbriviation in your case) in the second table when I needed to enter the record, rather than jsut create it for a just in case population. However, its possible.
Create a form for your main table and add all necessary fields to this form. Create a command button on this form with the name cmdUpdate with the following code:
Private Sub cmdUpdate_Click()
docmd.openform "frmUpdateTables"
End Sub
Now create a second form called frmUpdateTables (for example). Select the record source as your second table. Create an OnOpen event with the following code.
Private Sub Form_Open(Cancel As Integer)
Dim strIDNumber as String
'frmMainTable will be whatever your form is called with the
'main table as record source
strIDNumber = Forms![frmMainTable]![PlantID]
Docmd.gotorecord ,, acnew
PlantID = strIDNumber
Docmd.gotorecord ,, acnew
msgbox "PlantID " & strIDNumber & " has been added."
Docmd.close
End Sub
What will happen is that each tmie you create a record, you can click the Update button to create this entry in what table you like.
HTH's
Lets say your main table has a field PlantID. This contains the name of the plant, and a description.
Your second table is, lets say, a notes table. Where for each plant you can attach a note.
Personally, I would put the ID (or abbriviation in your case) in the second table when I needed to enter the record, rather than jsut create it for a just in case population. However, its possible.
Create a form for your main table and add all necessary fields to this form. Create a command button on this form with the name cmdUpdate with the following code:
Private Sub cmdUpdate_Click()
docmd.openform "frmUpdateTables"
End Sub
Now create a second form called frmUpdateTables (for example). Select the record source as your second table. Create an OnOpen event with the following code.
Private Sub Form_Open(Cancel As Integer)
Dim strIDNumber as String
'frmMainTable will be whatever your form is called with the
'main table as record source
strIDNumber = Forms![frmMainTable]![PlantID]
Docmd.gotorecord ,, acnew
PlantID = strIDNumber
Docmd.gotorecord ,, acnew
msgbox "PlantID " & strIDNumber & " has been added."
Docmd.close
End Sub
What will happen is that each tmie you create a record, you can click the Update button to create this entry in what table you like.
HTH's
- Status
Similar threads
- Locked
- Question
- Question
- Locked
- Question