Can't get rid of error message...?!?

[jondavidf]

The TLDR version -

I want to pass master/child values (Study_ID) from one table to another through form/subform(s) operations and am having limited success. The values (Study_ID) are the primary key in my main table and linked to child tables (as Pt_ID), all in a one-to-many relationship setup with enforced referential integrity, cascade update, and delete. It's working for one of my subforms but not the other although it's set up the same. See last screenshot for error message.

The full version -

Hey all,

I'm building a database to collect specific medical info. Our team will be looking through records and gathering data like height, weight, blood pressure, medications taken, etc. Instead of collecting names (protected health information) we will use "Study_ID" in our main table and "Pt_ID" in our other child tables (labels are synonymous and I probably should have just kept them all as one or the other). Each value that could have multiple entries per patient (as Pt_ID) has it's own table - for example there's a blood pressure table with Pt_ID(s), dates and values, height and weight table with Pt_ID(s), dates and values, and medications table with Pt_ID(s), dates and values.

All info for the patient that will only have one value will be entered in the main form (named General Demographics). Things like gender and race will go here. Please see screenshot.



Once general info is entered users will select "Patient Encounters" which will open a new form. The Study ID value is passed from the General Demographics form to the Patient Encounter form, and set to a new record so values are blank/zero but inserted subform queries show past entries. This is where height, weight, blood pressure results, etc. will all be entered (inserted as subforms). Please see screenshot.





This form contains the subforms with queries tied to Study_ID/Pt_ID so it's easy to see/edit previous entries for whatever patient's records you are reviewing. Everything is mostly good to this point. It doesn't seem to automatically update the Pt_ID value from Study_ID as blood pressure, medications, etc are entered (even though they are linked fields and set to enforce referential integrity, cascade update, cascade delete in relationships and linked as Master/Child in the subform properties) so I created a macro to pass Study_ID to Pt_ID "After Update" of certain fields. For the blood pressure subform this seems to work just fine. See screenshots.


For the medications subform, though, I get an error message after I select a value from the drop-down for medications... even though it's set up identically to the blood pressure subform. I have an identical macro set up to pass Study_ID to Pt_ID after update of the "Medication" field. FYI, I also tried moving the macro to "On Click", "Before Update", and "On Change" properties of the field and got the same message. See screenshot.



After the error message is dismissed, Study_ID DOES get passed to Pt_ID but I can't seem to make it so I don't get the error message first. (FYI, Pt_ID's are in red because they will be set to visible = no in the user version of the database).

I tried to attach a couple more screenshots but evidently I'm limited to 5 .

Thanks in advance for all the help!!!

Jon

[Beetle]

Can you post a stripped down copy of the db with some dummy data? It will be difficult to offer solutions in this case without being able to see the application.

[Gicu]

If you properly set your master/child relationships you should not need macros to set the PT_ID. Try to name your control on the form differently than the field and make sure you reference the controls when you setup the master child property: http://www.fmsinc.com/microsoftacces...ink-fields.asp

Cheers,
Vlad

[NTC]

The Gicu post is correct. A bound sub form, set up via the wizard, auto creates the key link data insert into the sub table.

If for any reason one has an unbound sub form but still needs to insert a value from another form (or the need to insert a non key value if bound) - then select some action inside that sub form that is appropriate to the user's activity such as an after update event or perhaps a Dirty event - and use that as the trigger to simply write the value into the field with VBA: i.e. Me.Field = me.parent.field

[jondavidf]

I will try the VBA code. I'm not super familiar with VBA, but have some understanding of it. To set the code I assume I would need to:

1. Select the field properties I want the code attached to, e.g. Medication field -> After Update -> ... -> Code Builder.

2. Then in VBA between Private Sub Medication_After_Update (Cancel As Integer) and End Sub. I would enter:

Me.Pt_ID = me.parent.Study_ID

Is that correct or do I have to replace parent with the form name, etc? Is there anything else I need to do or have missed?

I've also attached the database as I've developed it thus far. If you have any recommendations for it I'd be more than happy to hear them.

Thanks!!

Jon

[Gicu]

Hi,

Please have a look at the attached updated file and see if that is what your after. I have changed the master fields in the two subforms to use the txtStudyIDHdr (which actually refers to the combo-box above it) instead of the empty StudyID (it is empty because you force the form to open to a new record). I have also changed the default value property of the red StudyID field on upper left (previous encounters) which is on the main form to be the same txtStudyIDHdr.

Cheers,
Vlad

[jondavidf]

Vlad,

Yes, that is what I was looking for! Thank you for your help! I noticed on the encounters form the Ht/Wt query updates when I hit the save button but the "Previous Blood Pressure Measurements" and "Previous Medications" only update if I close and reopen the form, or change to a different Study ID and then back. Do you know how to make the query subforms update when I enter new information?

Thanks,

Jon

[Gicu]

Jon,

I don't usually use macros, I like how VBA gives you a lot more control. But I suggest editing your two macros behind the Save buttons on the subforms to add a Requery action of the subform control before your error handler (just after the SaveRecord).

Cheers,
Vlad

[jondavidf]

Hey Vlad,

Just wanted to update you that everything mentioned in previous posts is now working as expected. Initially, I couldn't get the Requery to work correctly so I ended up doing a couple things:

1. Removed the subform query that displayed previous data and changed to a listbox instead.

2. Changed the macro code to VBA, then added a line to requery the listbox when the "Save" button was pressed.

Thanks again for all your help!!

Jon

[Gicu]

Nice to hear Jon, thanks for the update! That is why we all love Access here, such a great and powerful tool so easy to customize.

Cheers,
Vlad