"related record is required in table . . ."

[AccessIdiot]

Argh! Just when I think everything is working and I am doing one final test before showing it to the guys I built the db for, Access throws out a weird message and won't let me add a record. But only SOMETIMES.

I am getting "You cannot add or change a record because a related record is required in table "tbl_Entrianment". The only thing that relates the two tables in question is the Entrainment_ID WHICH I CAN SEE ON MY FORM.

Let me back up.

I have a form/table Entrainment. An Entrainment can have lots of Specimens (one to many with Entrainment_ID; autonumber/primary key in tbl_Entrainment and number/foreign key in tbl_Specimen_Entrainment). On my Entrainment form is a button to view/add Specimens. It launches a filtered Specimen_Entrainment form (where Entrainment_ID of the Entrainment form = the Entrainment_ID of the Specimen form). I can add new records to the Specimen form no problem.

However, I have a "not in list" function on the Species control of the Specimen_Entrainment form. After I add a new Species and return to the Specimen_Entrainment form I get the error message. Here is the code for the not in list:

 Private Sub Species_ID_NotInList(NewData As String, Response As Integer) 
Dim Result
Dim Msg As String
Dim CR As String
 
    CR = vbCr
 
    ' 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 Species form in data entry
       ' mode as a dialog form, passing the new species code name in
       ' NewData to the OpenForm method's OpenArgs argument. The
       ' OpenArgs argument is used in Species form's Form_Load event
       ' procedure.
       DoCmd.OpenForm "frm_Species_Freshwater", , , , acFormAdd, acDialog, NewData
    End If
 
    ' Look for the species code name the user created in the Specimen form.
    Result = DLookup("[Species_Code]", "tbl_Species_Freshwater", _
             "[Species_Code]='" & NewData & "'")
    If IsNull(Result) Then
       ' If the species code 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 species code was created, set the Response argument to
       ' indicate that new data is being added.
       Response = acDataErrAdded
    End If
 
End Sub

The thing is, I can add the new species, but when I close the frm_Species_Freshwater form to continue entering data on the Specimen_Entrainment form that's when I get the error message. I can see the correct Entrainment_ID on the Specimen_Entrainment form so I'm not sure why it's giving me the error message?

[AccessIdiot]

I should also add that if I go ahead and change the species to something else (it is a combo box on the Specimen_Entrainment form), something that already existed before, I still get the error message. It's like, once I add something to the Species table through the not in list function it doesn't want to continue in any way.

[Rabbit]

Toss in a save command before opening the form. I've come across this where the record with the primary key hasn't been updated to the table before trying to add a foreign key record.

[AccessIdiot]

Hey Rabbit which form do you mean? Do you mean after a new species is added to the list before returning to the Specimen_Entrainment form?

[AccessIdiot]

You are a freaking genius, thank you!

[Rabbit]

You are a freaking genius, thank you!

If only. Experience more than anything.