Add New Record To Subform (Related Table) via VBA

[calvinle]

Hi,

If I am using my access to add new record manually, then it does not have a problem. However, if I want to add data via VBA, then I have error.

Here is the code that I am using to add new record to the subform that has table related to another table, but I always get the error message:
"You cannot add or change a record because a related record is required in table 'tblMember'"

   Dim rs  As DAO.Recordset
  Dim x As Long
  Set rs = Me.sfProduct.Form.RecordsetClone
  Me.member_id = "New Member"
  Me.first_name = "New First"
  Me.last_name = "New Last"
  x = 1
  Do
  If x = 4 Then Exit Do
  rs.AddNew
  rs![member_id] = Me.member_id
  rs![product] = "New Product"
  rs.Update
  rs.Bookmark = rs.LastModified
  x = x + 1
  Loop

I want to be able to add data not by manually entered by the user, but thru VBA.

Please help.

See attached sample.
View attachment Add VB DAta.accdb

 

[Pyro]

You need to save the parent record before adding sub-records.

Dim rs  As DAO.Recordset
Dim x As Long

Set rs = Me.sfProduct.Form.RecordsetClone

Me.member_id = "New Member"
Me.first_name = "New First"
Me.last_name = "New Last"

x = 1

'Save here
[COLOR="DarkOrchid"]DoCmd.RunCommand acCmdSaveRecord[/COLOR]

Do
    If x = 4 Then Exit Do
    
    With rs
        .AddNew
        ![member_id] = Me.member_id
        ![product] = "New Product"
        .Update
        .Bookmark = rs.LastModified
        
    End With
    
    x = x + 1
    
Loop

 

[calvinle]

Thank you! I knew u had to save the record first, but I used : docmd.save which didnt work..

Thanks!