pass through parent's foreign ID to child table

[donnavz1]

Good day. I am an auditor. I go out to sites to audit files (an audit instance). At each audit, I audit multiple files called certifications. Each certification has multiple findings. I have built the database, but am struggling with the forms.

I have a parent AuditInstance table. It has a primary ID, date of audit, name of auditor, and purpose of audit.

I have a child CertAudit table. It has a primary ID; it has a foreign ID (AuditInstanceI D), and other data pertaining to the certification.

All of this works beautifully within the tables themselves if I begin to enter data within AuditInstance
table. I create that record; then click on the plus; then create a CertAudit record, and click on that plus, and so forth create all my other records in all my other tables.

Now, I need to create Forms. All of my forms are based on the tables directly.

I have an AuditInstance form that creates an AuditInstance record.

I then want to use a button on the AuditInstance form to open a CertAudit form, and have the CertAudit form
populate with the AuditInstanceID I just created in the AuditInstance form. This will always be the last AuditInstanceID in the AuditInstance table. I want it to populate automatically instead of having to manually select the AuditInstanceID from within the CertAudit form.

How do I pass that last AuditInstanceID from the parent table into the child table, so that I can perhaps use
GoToRecord and Last.

I have considered a query that pulls the last AuditInstanceID , but I don't know how to make it work in
practice.

Thank you.

[NeoPa]

I generally find myself requiring two things in this sort of situation :

1. That all records displayed of a child table match the selected record of the parent. This is done via filtering the form. Filtering is generally handled by using the WhereCondition parameter of the DoCmd.OpenForm procedure.
2. That each new record entered defaults to match the selected parent record. This is done by setting the DefaultValue property of the control on the child form used to hold the parent ID to the value of the selected parent record ID. This code can be put in the parent form after the call to DoCmd.OpenForm, or alternatively, the OpenArgs parameter of the call can pass the data through to the child form and the code can be put in the OnOpen event procedure.

[donnavz1]

The OpenForm + WhereCondition worked beautifully; thank you. I was nowhere near this solution because I was focused on telling Access which record is the last record. I'm left wondering how it worked? How does Access know which record I want? Is Last effectively a system assumption? Or, is it simply because I still had the parent form open, and that was the record selected?

[NeoPa]

I'm sorry Donna, but your thinking is so far away from my understanding that I have no idea what you're asking here. Possibly because I have no idea why the last record concept has anything to do with this situation.

[donnavz1]

Ha! Amusing how oblivious I am. In my mind, I was trying to pass the primary ID of the record I had just created in the parent table (so, the last ID in that table, where AuditInstanceID =77, of a table that has AuditInstanceID s numbering from 1 to 77) to the child table. I was thinking that Access wouldn't know which record I wanted to pass unless I pointed to the record, and the only way I thought of to identify the record is to point out that it's the last record created. Clearly that's incredibly flawed thinking! I will figure it out eventually. Thank you for your time.

[NeoPa]

If you are wanting child records (IE the many side of a one-to-many relationship) on a form then, as there are many potential records, instead of selecting only one record, you should be thinking of filtering for all the matching child records. Do I understand you well enough? I'm not sure.

If, for any reason, you really want to select only a single record, that can also be done using a filter, but an alternative workable approach might be to pass the ID across to the new form within the OpenArgs parameter and let the form navigate automatically to the matching record. I would have thought the first approach made more sense in most circumstances though, if I'm honest.