I have a form set up as follows:
Main Form: Customers: AccountNumber, FirstName, LastName
Subform: Reference: Date, Reference
Subform within Reference: Model, Serial
The flow goes from entering customer data, then related reference data, then related item data. One customer can have many references and one reference can have many items. The account number is indexed with no duplicates allowed.
I set up the account number control as a combo box that lists all previously imputed parent records and prefill the first and last name controls with the following:
Code:
Private Sub AccountNumber_AfterUpdate()
Dim db As DAO.Database, rst As DAO.Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("Customers", dbOpenDynaset)
If Not rst.BOF Then
rst.FindFirst "[AccountNumber]='" & Me!AccountNumber & "'"
If Not rst.NoMatch Then
With rst
Me!FirstName = !FirstName
Me!LastName = !LastName
End With
End If
End If
Set rst = Nothing
Set db = Nothing
End Sub
This works well at pulling up the customer information, but when I enter the subform I'm informed the record cannot be created as it will cause a duplicate account number. My intention is to pull up an existing parent record to add additional child records. If the account number does not exist in any records then a new parent record will be created. I am a novice and most of what I've learned has been from searching through forums so please keep that in mind when drafting a reply. Any help is appreciated!