Automatically creating new record in related table

[Remster]

Please see the input form in the attached database. If I enter "Yellow" in Field2 where Field1 contains "Lemons", it's accepted, because "Yellow" appears in Table2. But if I enter "Red" in Field2 where Field1 contains "Strawberries", it's rejected, because "Red" doesn't appear in Table2.

What's the most efficient way of getting a new record created in Table2 as soon as "Red" has been entered in Field2 of the form? Or have I structured the whole thing incorrectly?

[davegri]

FruitColor-davegri-v01.zip

See the above. Using split form (just as example) as continuous form will not work for this situation.

[Remster]

Thanks for your suggestion. However, I can't see that it helps. Unless "Red" is already in tblColors, the message "The text you entered isn't an item in the list" appears when "Red" is typed in Color (i.e. the form field). What I'm looking for is the most efficient way of getting "Red" into tblColors as soon as it has been typed in Color, if it's not already there. I assume the Not In List event is an option, but I don't know whether it's the best one.

[pbaldy]

That is the best option I think. Search here for "notinlist" and you should find two basic methods, one just adding the value and the other popping up a form to add other required info in the other table.

[Remster]

Many thanks. I'll go with the first option. I've used the Not In List event before but had forgotten it when I started the thread (largely because I wasn't using combo boxes). I was also unsure whether I'd just misconceived the issue.

[pbaldy]

No problem.