Linking Primary Key fields in a subform to the main form

[mmedrano]

I am basically a beginner when it comes to designing forms in Access, but have recently developed a patient tracking database. It all works well, except for one key piece.

I have a main form, where new patients are entered and get an autoID key that follows them throughout all visits. Then, each patient also gets a visit ID, which applies to a series of visits with different dates. Therefore, when filling out forms on a patient, I need the auto ID, visit ID and date fields to all be primary keys. I have set this part up correctly.

My problem is that I have a subform in the main form that allows the user to click on a buttom that opens up a form that they have to fill out periodically for each patient. I want the autoID, visit ID and date fields to automatically be entered into the form that the user clicks on when it opens. I have yet to be able to do this. This is the key piece of the database. Any help would be greatly appreciated!

Thanks, Melissa

 

[David R]

Utilize the wizard for Insert subform/subreport if possible. It's pretty good about linking up the proper fields, as long as they exist.
What that means is your Visits table should have a Long Integer field where you can store the (Autonumber) [PatientID] from the main table. This is what tells Access that this Visit belongs to this Patient, and is the strength of relational databases; you only have to enter data once, and link it to other relevant data. [VisitID] should actually be the primary key on your Visits table, and will not even exist on your Patients table. Your date field doesn't have to be a unique field unless I'm misunderstanding your design.

When you use the Insert Subform wizard, it will prompt you for what fields you want to link. Select [PatientID] on the Patients table (your main one), and the corresponding [PatientID] Long Integer field on the Visits table.

Depending on whether your popup subform (the one you said needed to be filled out periodically) is linked to the patient or the specific visit will determine how you want to pull that information over. Post back with more information if you're still stuck.

HTH,
David R


[This message has been edited by David R (edited 01-24-2002).]

 

[mmedrano]

Thanks for the information, David. However, I do need to use the date field as one of the primary keys, though, b/c a patient can come in with the same visit ID, but on a different date and the date is an important field for this specific database. So, basically, each patient will have only 1 autoID, but can have multiple visit IDs and within each visit ID have multiple dates. I did link the 2 ID fields when creating the "open form" control, but you can't like more than one field...just the one field. And what I really need is to link all 3 fields to each other. And again, I will appreciate any information!
melissa

 

[David R]

Ok, I think I understand. Your Visit is more like a "Reason" table, and within it you may have multiple events. The same reasoning applies as before, you can nest subforms if you like. Just use VisitID as the 'foreign key' field in your VisitDetail/Incident table, just like you did with PatientID on the Visits table. You can use a date field as the primary key of this new table but I'd recommend making an autonumber field your primary key and just index your date field.

Hope that helps,
David R

 

[mmedrano]

Thanks again! I'll try and work with that now.

melissa