Joint Key of Table Not in Recordset

[Chillendw]

Hello everyone. First time posting here. I was hoping I could get some assistance. I've put some work into this new database, but it's been a while since I've done much with access.

I wanted to try out some of scripts that I'm trying for the first time on a couple of combo boxes in a form. But I can't test them out because when I try to add something to another field, it won't let me. I see a warning on the bottom ribbon stating that a join key of the table is not in the recordset. It sounds like there is something missing or maybe is not linked properly. I've attached a copy. It's actually pretty bare because I just started it.

The things I want to check but I'm unable to since I can't input anything are:
1. Why can't I input anything in the TransactionType field
2. I want this field to be either Received or Dispensed. I put that in the validation rule. Would that work?
3. Will adding a CustomerReferenceNumber that wasn't previously in its table pop open a form to populate the corresponding table? (I wrote some script for it but I'm not able to test it out)
4. Will adding a CustomerReferenceNumber that was previously in its table autofill FName and LName? (I wrote some script for that. Well, I tried to)
5. Will adding a LotNumber that wasn't previously in its table pop open a form to populate the corresponding table? (I wrote a different script from #2)
6. Will adding a LotNumber that was previously in its table autofill Qty, Units, Exp Date
7. Why won't it allow me to select ProductName as one of the row sources for LotNumber combo box so that I can autofill ProductName after writing in the LotNumber?

I thought I was doing it right, but I can't test it since I have this hurdle. And I also wanted to see which method of adding a record from a combo box worked better [#3 (NotinList event) or #5 (List items edit form)].
If anyone can help, it would be greatly appreciated.

Thanks in advance!

 

[sneuberg]

I don't have time at the moment to study your specific problem but I can tell you that we usually see this "Joint Key of Table Not in Recordset" problem when a query of two or more tables is made the record set of a form. There is nothing in this set up that will maintain the primary/foreign keys when a record is added in the form. It's best to stick to one table per form and put the child tables in subforms. With this set up the keys are maintained through the link fields of the subform control.

 

[Chillendw]

I don't have time at the moment to study your specific problem but I can tell you that we usually see this "Joint Key of Table Not in Recordset" problem when a query of two or more tables is made the record set of a form. There is nothing in this set up that will maintain the primary/foreign keys when a record is added in the form. It's best to stick to one table per form and put the child tables in subforms. With this set up the keys are maintained through the link fields of the subform control.

Thanks for your reply Steve.

I was definitely unaware of that. That sucks. I definitely have 3 different different tables for that 1 form. I have 1 table that pulls from the other 2.
I haven't done subforms before. But I'll look into those. I'm trying to make it as easy as possible for an employee to enter data without having to open several different forms.

Thanks again.