Basic macro design validating foreign key field

[Carrbigdog]

I have a table A that contains a list of animals and a table B that contains an entry for some animals with a primary key of registration_id and a foreign key to table A. An entry in table A may have 0 to n entries in table B.

In a table A object/instance there are two fields that optionally contain foreign key links to table B for the parents of the entry. Data is entered via a form for table A. In the form for inputting the table A data, if an entry is made in each of the foreign key fields, I would like to validate that an entry exists in table B before committing the data.

I am considering creating a macro for each field that would move the key data to a hidden field on the form and then call a third macro that would query table B. My rationale is that I would have a single macro to query table B that could be used for each test.

Is this a reasonable approach to designing this?
I appreciate any comments.

[Ratslinger]

Hello,

The catch here is that in Table B you may or may not have the parents in Table A. Here are a couple of scenarios.

This, or a form of, seems to be the simplest. In Table A you have an entry for No or None. With that, the entry form for Table A can use list boxes for the two entries.

If you stick with the macro solution, you do not need multiple macros or any hidden fields. Use one macro (function) attached to the Before update event of the form. Simply check each fields data with SQL and if either or both are in error a return of False will cancel the update. Within the routine you can put a message box to notify the user.

[Carrbigdog]

Thanks for the info. You are so helpful as I try and learn how to utilize these tools.
The end result will have several thousand entries in Table B so I think the second solution will work the best. If I were to get really fancy on a False, I could create create a placeholder record in Table A and then Table B but for right now a message box seems to be the easier solution.