Adding new record to a recordset

[TryTryAgain]

I have two tables - parent and child - trying to add a new record to the child table. I create a parent record using addnew and update but when I try to add child record I get the message that it can't be added because there isn't a related parent record. When I look at the parent table the record is there....

 

[VBslammer]

If you're using DAO there shouldn't be anything special that needs to be done if the relationship is setup right. See if your code is similar to this:

Sub AddCustomerJob()
On Error GoTo ErrHandler
  Dim db      As DAO.Database
  Dim rst     As DAO.Recordset
  Dim lngID   As Long

  Set db = CurrentDb()
  Set rst = db.OpenRecordset("Customers", DAO.RecordsetTypeEnum.dbOpenTable)

  With rst
    .AddNew
     lngID = .Fields("CustID")  [green]'capture autonumber for new record[/green]
    .Fields("CustName") = "Arnold Schwarznegger"
    .Fields("Gender") = "Male"
    .Fields("Age") = "49"
    .Update
  End With
  
  rst.Close
  Set rst = db.OpenRecordset("Jobs", DAO.RecordsetTypeEnum.dbOpenTable)
  
  With rst
    .AddNew
    .Fields("CustID") = lngID  [green]'foreign key link to parent table[/green]
    .Fields("StartDate") = "1/1/04"
    .Fields("CompleteDate") = "12/31/04"
    .Fields("JobName") = "World Gym"
    .Fields("Cost") = "650000"
    .Update
  End With

ExitHere:
  On Error Resume Next
  rst.Close
  Set rst = Nothing
  Set db = Nothing
  Exit Sub
ErrHandler:
  Debug.Print Err, Err.Description
  Resume ExitHere
End Sub

VBSlammer

"You just have to know which screws to turn." - Professor Bob