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

"related record is required in table . . ."

AccessIdiot
100+
P: 493
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:
Expand|Select|Wrap|Line Numbers
  1.  Private Sub Species_ID_NotInList(NewData As String, Response As Integer) 
  2. Dim Result
  3. Dim Msg As String
  4. Dim CR As String
  5.  
  6.     CR = vbCr
  7.  
  8.     ' Exit this subroutine if the combo box was cleared.
  9.     If NewData = "" Then Exit Sub
  10.  
  11.     ' Ask the user if he or she wishes to add the new customer.
  12.     Msg = "'" & NewData & "' is not in the list." & CR & CR
  13.     Msg = Msg & "Do you want to add it?"
  14.     If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
  15.        ' If the user chose Yes, start the Species form in data entry
  16.        ' mode as a dialog form, passing the new species code name in
  17.        ' NewData to the OpenForm method's OpenArgs argument. The
  18.        ' OpenArgs argument is used in Species form's Form_Load event
  19.        ' procedure.
  20.        DoCmd.OpenForm "frm_Species_Freshwater", , , , acFormAdd, acDialog, NewData
  21.     End If
  22.  
  23.     ' Look for the species code name the user created in the Specimen form.
  24.     Result = DLookup("[Species_Code]", "tbl_Species_Freshwater", _
  25.              "[Species_Code]='" & NewData & "'")
  26.     If IsNull(Result) Then
  27.        ' If the species code was not created, set the Response argument
  28.        ' to suppress an error message and undo changes.
  29.        Response = acDataErrContinue
  30.        ' Display a customized message.
  31.        MsgBox "Please try again!"
  32.     Else
  33.        ' If the species code was created, set the Response argument to
  34.        ' indicate that new data is being added.
  35.        Response = acDataErrAdded
  36.     End If
  37.  
  38. 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?
Apr 30 '07 #1
Share this Question
Share on Google+
5 Replies


AccessIdiot
100+
P: 493
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.
Apr 30 '07 #2

Rabbit
Expert Mod 10K+
P: 12,327
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.
Apr 30 '07 #3

AccessIdiot
100+
P: 493
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?
Apr 30 '07 #4

AccessIdiot
100+
P: 493
You are a freaking genius, thank you!
Apr 30 '07 #5

Rabbit
Expert Mod 10K+
P: 12,327
You are a freaking genius, thank you!
If only. Experience more than anything.
Apr 30 '07 #6

Post your reply

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