I'm using the code below to add a record to my Organizations table when the name typed in the Organization box on the Master form is Not in List.
The Organization table only has two fields, the ContactID autonumber field and the the Organizations text field. The code opens frmOrganization, and it does insert a record into tblOrganizations with an ID and the correct new organization name. But it also inserts a a second record into the table with a new ID and the ID of the previous record as the organization name, and it it this record that is linked to the main table:
ContactID Organization
70 The X Company
71 70
The master table ends up with 71 in the organization link field, when it should be 70 and the second row shouldn't even exist. Below is the code, lifted from Microsoft:
Dim Result
Dim Msg As String
Dim CR As String
CR = Chr$(13)
' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub
' Ask the user if he or she wishes to add the new customer.
Msg = "'" & NewData & "' is not in the list." & CR & CR
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
' If the user chose Yes, start the Customers form in data entry
' mode as a dialog form, passing the new company name in
' NewData to the OpenForm method's OpenArgs argument. The
' OpenArgs argument is used in Customer form's Form_Load event
' procedure.
DoCmd.OpenForm "frmOrganizations", , , , acAdd, acDialog, NewData
End If
' Look for the customer the user created in the Customers form.
'Result = DLookup("[Organization]='" & NewData & "'")
Me.Organization = DLookup("[ContactID]", "tblOrganizations", _
"[Organization]='" & NewData & "'")
If IsNull(Result) Then
' If the customer was not created, set the Response argument
' to suppress an error message and undo changes.
Response = acDataErrContinue
' Display a customized message.
MsgBox "Please try again!"
Else
' If the customer was created, set the Response argument to
' indicate that new data is being added.
Response = acDataErrAdded
End If