Create New Record using VBA code from two tables

[mburch2000]

I have a button on a form that creates a new record, based on the current record, when the user clicks the button. The original code from the MASTER table worked fine. Now I need to add additional fields to the new record from another table: Related. When I added the code for the Related table, the fields in the Related table did not populate on the new record. Can someone please help? The code is below. Thanks

Private Sub Add_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim rs2 As DAO.Recordset
        
    On Error Resume Next
    
    Set db = CurrentDb
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM [MASTER]")
    rs.AddNew
    rs![Number] = Me. Number.Value
    rs![Title] = Me.Title.Value
    rs![Year] = Me.Year.Value
    rs![Schedule] = Me.Schedule.Value
    rs![Comments] = Me.Comments.Value
    rs![Statement] = Me.Statement.Value
    rs![HistoryChanges] = Me.HistoryChanges.Value
    rs![Orig_PubDate] = Me.Orig_PubDate.Value
    rs![Last_PubDate] = Me.Last_PubDate.Value
    rs![Keywords] = Me.Keywords.Value
    rs.Update
    rs.Close
    db.Close
    Set rs = Nothing
    
    Set db = CurrentDb
    Set rs2 = CurrentDb.OpenRecordset("SELECT * FROM [Related]")
    rs2.AddNew
    rs2![Related_1] = Me.Related_1.Value
    rs2![Related_2] = Me.Related_2.Value
    rs2![Related_ 3] = Me.Related_3.Value
    rs2![Related_ 4] = Me.Related_4.Value
    rs2![Related_ 5] = Me.Related_5.Value
    rs2.Update
    rs2.Close
    Set rs2 = Nothing
    
    db.Close
    DoCmd.Close
End Sub

[zmbd]

rs2![Related_1] = Me.Related_1.Value 
    rs2![Related_2] = Me.Related_2.Value 
    rs2![Related_ 3] = Me.Related_3.Value 
    rs2![Related_ 4] = Me.Related_4.Value 
    rs2![Related_ 5] = Me.Related_5.Value

Note the spaces between the underscore and the numerics 3 thru 5? Was that intentional as there is no such space for 1 and 2.

By, simply stating that your code "doesn't work," not posting the error along with code (and omitting on which line the error occured), that appears to have had very little if any troubleshooting performed, and expecting someone to help doesn't usually result in much of an answer and may result in your thread being deleted.

[mburch2000]

rs2![Related_1] = Me.Related_1.Value 
    rs2![Related_2] = Me.Related_2.Value 
    rs2![Related_3] = Me.Related_3.Value 
    rs2![Related_4] = Me.Related_4.Value 
    rs2![Related_5] = Me.Related_5.Value

Note the spaces between the underscore and the numerics 3 thru 5? Was that intentional as there is no such space for 1 and 2.

By, simply stating that your code "doesn't work," not posting the error along with code (and omitting on which line the error occured), that appears to have had very little if any troubleshooting performed, and expecting someone to help doesn't usually result in much of an answer and may result in your thread being deleted.

Hi zmbd,

My mistake! The original code does not have the spaces. I don't get an error. I tried to create a record using this code, but only part of the new record was created. The fields in the Related table did not pull over to the new record and I don't know why. I am sorry if this is vague. Thanks

[zmbd]

You should double check that all of your Me.Related_1.Va lue type lines are actually returning what you think they are... debug.print them out and check the immedate window by pressing <ctrl><g>

Why are you duplicating data between tables, that isn't done in a > Normalized Table Structures.

[mburch2000]

I guess what I am asking is the structure of my code right, assuming I have all of the table/field names correct? If it is, then I will have to find the problem somewhere else. I'm not sure why the new record does not include the fields Related_1, Related_2, etc.

[mburch2000]

Hi zmdb,I am not duplicating data between tables. I am pulling data from both tables on a form using a query. I then need to create a new record with most of the fields on the form. The reason is so that the users don't have to re-enter the same data on the new record as only a few fields are being changed on the new record.

[zmbd]

ah... ok,

Just noticed line23 and line 26 in your original post, delete these two lines.
Insert after line38 me.requery the form normally only looks at the table once unless something happens to force it to update.

you might want to take a look at what Allen Browne has done here:

Simpler method:
Assign default values from the last record

And then the more complex situation:
Duplicate the record in form and subform

This is post in a related thread Thread955552/Post#6

[mburch2000]

Thanks zmbd. Good information. I will read through this and try and fix my code. I appreciate your help