I'm looking for some guidance on an issue that I have been struggling with for a few hours. Perhaps someone can come up with something I haven't thought about.
I have a table (call it Inspection) and a related table (call it Dates). Dates has a foreign key of InspectionID which relates to the primary key in Inspection. To summarize, each inspection can have multiple dates.
In a form which shows inspections and related dates, I have a button to open a specific "instance/date" of an inspection.
I pass the inspection ID for the selected record as well as the related primary key for the Dates table to a script (call it Open Inspection Form) which is responsible for opening the inspection form.
On the inspection form I am displaying both the PK of Inspection and dates. The PK for inspection always shows the correct inspection ID, however the PK for the dates always displays the very first date (child) entry for Inspection.
There are scripts that are called from with "Open Inspection Form" but I have reviewed these and they do not reference the Dates table.
What would cause the very first child record to always become the active record when the inspection form is opened? As a workaround, I even tried doing a "Find" of the inspection ID/date ID pair at the very end of the script, but when the script ends and the form is loaded, the first Date record ID is still shown. I do have a script trigger that runs when the form is loaded but it doesn't look like this is affecting the current row in the Dates table. I also disabled script triggers to test and the result was the same.
I know I must be missing something, but at this point I'm looking for suggestions.