new records with record sets

[Topher]

Hi, im kinda new with record sets and i hope you can help me out with this. i have a form with multiple text boxes - i want to take the info from the different text boxes and place them into different tables....

ie txtbox1 info goes to table 1 and txtbox2 info goes to table2

the problem is table1 is has a relation with table2 and when i try to update table2 i get an error saying i can't b/c a related record is needed in table2

this is my code thus far and the 1st with statement will work fine...but what about the 2nd??

Dim db As Database
Dim rstcaldata As Recordset
Dim rstcalhist As Recordset
Dim rstcalspecs As Recordset
Dim rstcalequip As Recordset
Dim criteria As String

Set db = CurrentDb()
Set rstcaldata = db.OpenRecordset("tblCalData", dbOpenTable)
Set rstcalhist = db.OpenRecordset("tblCalHistory", dbOpenTable)
Set rstcalspecs = db.OpenRecordset("tblSpecs", dbOpenTable)
Set rstcalequip = db.OpenRecordset("tblCalEquip", dbOpenTable)

With rstcalequip
.AddNew
![AssetNo] = Me.txtAssetNo
![SerialNo] = Me.txtSerialNo
![ModelNo] = Me.txtModelNo
![StatusNo] = Me.cboStatusNo
![FamilyNo] = Me.cboFamilyNo
![Description] = Me.txtDescription
.Update
End With

With rstcaldata
.AddNew
![CalCycle] = Me.txtCalCycle
.Update
End With

Thanks...
Topher

 

[Pat Hartman]

Add the "parent" record to table1 before you add the "child" record to table2.

 

[Topher]

thanks for the input, i thought i was doing that? the 1st with statement is the parent and the 2nd with is the child - i've updated my code to place the primary index # from the first table into the foreign index field of the 2nd table

Set db = CurrentDb()
Set rstcaldata = db.OpenRecordset("tblCalData", dbOpenTable)
Set rstcalequip = db.OpenRecordset("tblCalEquip", dbOpenTable)

With rstcalequip
.AddNew
![AssetNo] = Me.txtAssetNo
![SerialNo] = Me.txtSerialNo
![ModelNo] = Me.txtModelNo
![StatusNo] = Me.cboStatusNo
![FamilyNo] = Me.cboFamilyNo
![Description] = Me.txtDescription
.Update
.Move 0, .LastModified
End With

With rstcaldata
.AddNew
![EquipNo] = rstcalequip![EquipNo]
![CalCycle] = Me.txtCalCycle
.Update
End With

is this what you mean?? it seams to be working

thanks!

 

[Pat Hartman]

Yes

 

[Topher]

great! thanks