By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,122 Members | 921 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,122 IT Pros & Developers. It's quick & easy.

automatically insert foreign key in subform

P: 34
Hi, I am adding records in a subform using recordsetclone. The subform has Data Entry property set to no and Add Record set to no. The main form PK is linked to the child form fk.
At the moment I am having to add the fk manually to the recordsetclone but I was wondering if that is the default behaviour? Why isn't the fk added automatically by Access? I suppose it is because the record is not written directly on the form but I just wanted to check if I am correct or I am talking nonsense. Thanks

Expand|Select|Wrap|Line Numbers
  1. Private Sub addPayment(button As Integer)
  2.  
  3.     On Error GoTo ErrorHandler
  4.  
  5.     Dim rst As DAO.Recordset
  6.  
  7.     Set rstClone = Me.PayChild.Form.RecordsetClone
  8.     Set rst = Me.PayChild.Form.Recordset
  9.  
  10.     With rstClone
  11.         .AddNew
  12.             !fkPaymentTypeID = button
  13.             !fkInvoiceID = Me!InvoiceID
  14.         .Update
  15.         rst.Bookmark = .LastModified
  16.     End With
  17.  
  18.     With Me.PayChild
  19.         .SetFocus
  20.         .Form!txtPaymentAmount.SetFocus
  21.          Debug.Print Me.Dirty
  22.         .Form.Dirty = True
  23.     End With
  24.  
  25. ExitSub:
  26.     Set rst = Nothing
  27.     Set rstClone = Nothing
  28.     Exit Sub
  29. ErrorHandler:
  30.     MsgBox "Error No: " & Err.Number & vbNewLine _
  31.          & "Error Details: " & Err.Description & vbNewLine _
  32.          & "Error in Sub: frmPay\addPayment"
  33.     Resume ExitSub
  34.  
  35. End Sub
Oct 23 '17 #1

✓ answered by NeoPa

When properly linked a subform will find the link to the main form populated for it automatically. However, that is only through the subform's interface - not when done via code directly to another recordset.

Of course, if the subform in question doesn't allow adding new records at all then adding it via the form itself will be a problem in itself. If you're already adding it to .RecordsetClone then you may find adding the FK yourself the easiest option.

Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,419
When properly linked a subform will find the link to the main form populated for it automatically. However, that is only through the subform's interface - not when done via code directly to another recordset.

Of course, if the subform in question doesn't allow adding new records at all then adding it via the form itself will be a problem in itself. If you're already adding it to .RecordsetClone then you may find adding the FK yourself the easiest option.
Oct 30 '17 #2

P: 34
Great thanks, it was bugging me!
Oct 30 '17 #3

NeoPa
Expert Mod 15k+
P: 31,419
Never a problem. I love helping people with an interest in Access.
Oct 30 '17 #4

Post your reply

Sign in to post your reply or Sign up for a free account.