Autofill a foreign key field

[bole]

It's a simple database with two tables. One patients table and one health reports table. It's a one to many relationship (patients - health reports).


I have a split form that's based on the patients table and displays data about the patient and there is button that opens health reports only for that patient (it filters the form). In the case that the patient doesn't have any health reports assigned, I want that button to open a new record of the health reports form with the patient ID field already filled so that it automatically assigns it to the patient. Also when I have the health reports form open, I want a button that opens a new record of a health report with the patient ID of the health report that was initially opened.


If you have a solution to the problem, didn't understand the problem or have a better way of designing this database please inform me.





I'm tight on time so I did post the same question on reddit: https://www.reddit.com/r/MSAccess/co..._fill_a_field/

[Welshgasman]

Use the PK as the Where criteria on the open form.
Also pass it in as OpenArgs to populate the FK field on BeforeInsert event.

Thank you for the notice on the cross-post.:

[June7]

Could place Reports form as a subform on Patients form and no VBA needed.

If you still want button to put user into Reports subform on new record row:

Me.Reports.SetFocus
DoCmd.GoToRecord , , acNewRec

[Micron]

I think the word "report" here does not refer to an Access report:

there is button that opens health reports only for that patient (it filters the form)

I want that button to open a new record of the health reports form

Anyway, there are decent answers at the other thread - I guess as long as it's understood that the 2nd form is based on a query or table that uses the id from the current record of the first form. A problem I think I see with having a default value setting on form2 is that errors will probably be raised whenever that form is opened and form1 is not open. Also, form2 would likely have to be modal, otherwise the current record on form1 could be changed by the user while form2 is still open.