Add Records to Multiple Tables From One Form

[utdel]

I have a form where I am adding a record to a table. This works fine but I also need to add some of the form's data to another table. At what point(event) should I add this new record? Also would I need to open a recordset for the other table and manually add the record via code? (short example of code would be nice )

Any help or guidanced would be appreciated. Thanks in advance.

 

[Jack Cowley]

It sounds like you are saving the data twice and that means redundant data, a no-no. You should only save the data in one place not two.

 

[utdel]

No this will not be redundant data. I need to create two different work order records per customer request. The new records will contain customer ids and other data(default start and end dates based on the main forms entry date) from the main form. All three tables are linked. I don't want the clerks entering the customer's request to have to create the two new work order records.

Thanks for you advice.

 

[mrssevans]

Sounds like you need to either use subforms on your main form or you need to use a query to make the form and make the query from the tables you need to enter data into.

 

[BukHix]

I think this may be what you are looking for. I needed to add data to a table that did not have any relation to the tables I was working one at the time. So I needed a way to dynamicaly create the recordset and put data into a table.

Dim rsCOD As DAO.Recordset
Dim strJobName, strJobNumb, strLocation As String

strJobName = "Talismanic"
strJobNumb = "91269"
strLocation = "Access World Forum"

Set rsCOD = CurrentDb.OpenRecordset("Jobs")

rsCOD.AddNew
rsCOD!JobName = strJobName
rsCOD!JobNumber = strJobNumb
rsCOD!Location = strLocation
rsCOD.Update

Set rsCOD = Nothing

This is a simplified example of the code I used in my app but I thought this would help you get started. Here is the thread with the complete code and more information about this topic.

Topic: Using RecordSet to update table?

Post back if you have more questions or if I missed your point entirely.

 

[utdel]

Thanks to everyone who replied!!!

I think I got enough info from BukHix's code to do what I need to do. Special thanks to you BukHix, looks like its going to work for me.

Later guys...