Filling field on underlying table of subform from unbound combobox on main form

[John V]

I have a database for tracking patient visits to medical clinics. There is a contact information table for storing the usual information about a patient. This table has a PatientID field that is used as the PK for this table, and it is the FK for all of the other tables in the database.

I have a form (Patient Encounter Sheet) for entering the information about the clinic visit. This this form has an unbound combobox for selecting the Patient ID number and various subforms for entering the visit information. The combobox (on the main form) gets its list from the contact information table.

The subforms are all bound to tables for storing the information on the subforms. These subforms all have either option groups or various checkboxes for making selections to describe the clinic visit.

I would like to have the PatientID number (chosen in the combobox) be automatically entered in the PatientID field of the underlying tables for the subforms.

I think I can probably do this with a "Private Sub PatientID_Change()" in the class module for the Patient Encounter Form (Mainform).

I would have to check if the previous value of the combobox was Null or not, because the form (and all of the subforms) open to a new record. If the previous value was Not Null then I would have to create a new record and then enter the PatientID number in the PatientID field of the underlying forms. If the previous value of the combobox was Null then I would just have to get the value into the underlying tables.

Either way, I am stumped on what VBA code to use to get the combobox value into the tables underlying the subforms. The combobox (actually the field on the contact information table that the combobox gets its list from) is a text datatype.

I have attached a screenshot of a portion of the Patient Encounter Sheet to give you some idea of what I am blathering on about.



Any help is appreciated.

John V

[June7]

Why is the combobox unbound? Don't you need to save the patient ID into record?

Need to know more about data structure. Should some tables be related to the visit record and not directly to patient?

In properly related form/subform, setting the Master/Child links properties of the subform container control will synchronize saving PK as FK into the dependent record.

[NTC]

I would say you are over-thinking, over-designing things....

A sub form is bound to the main form as part of its Data property via cross referencing key fields between the two objects. When you put the sub form into the main form the wizard asks which fields to use to cross reference and even suggests the field to use. These cross referencing fields of the form/sub form record sources should fundamentally represent the correct relationships between tables.

When you enter any data into a sub form - the cross reference value from the main form is auto entered into the sub form cross referencing field. Access makes this as part of its core functionality. There is no code needed.

I would recommend that rather than attempting a code-driven method to do it via the design you have in mind - to instead approach it by using how Access provides main/sub form functionality.

[John V]

Thank you June7 and NTC for your responses. I suspect, NTC, that you may be right that I am over thinking the whole thing. And, June7, I originally had the combobox bound to the Contact Information Table, but, when I tried to choose a value in the combobox it would give me an error. I think this is because the PatientID Number is the PK in the Contact Information Table and it would try to create a new record in that table and, therefore, try to create a duplicate PK (a definite no no, if I understand the concept correctly).

At the risk of rambling on, I would like to take a few paragraphs to explain my thinking in the design of this database. I am, by the way, not concerned if I have to start again. I am trying to teach myself Access and VBA, it's all part of the process.

So here it goes:

I have a Contact Information Table and I enter the information into this table using a form. The PatientID number is a unique number to the individual assigned by the healthcare system, so I decided to use it as the Primary Key. Each individual would only be entered into this table once and the PatientID provided (what I thought) would make an excellent PK.

Each visit the individual makes to see a healthcare professional is called an Encounter and I have several (14) other tables to store the information about their Encounter. The 14 tables are as follows:

1) Appointment Date
2) Case Management
3) Conference/Counselling
4) Dietician
5) Individual Teaching
6) Group Teaching
7) Location
8) Obstetrics/Women/Youth
9) Office Visit
10) Phone Contact
11) Pre-Visit
12) Referrals
13) Targeted Care
14) Type of Appointment

As a side note: These tables are not arbitrary. This whole thing is based on a form (the paper type) that is filled out by the attending nurse at the time of the visit, and this is exactly how the form is divided into categories.

Anyway, each of these tables generates its own PK using the AutoNumber DataType. The foreign key in each of these tables is the PatientID PK from the Contact Information Table. There is no relationship between the 14 tables that describe the Encounter. Each of these 14 tables has a form bound to it for entering the information into the table. These forms become subforms on a main form that is used to enter all of the information describing the Encounter (Healthcare Visit).

I believe the relationships between the Contact Information Table and the rest of the tables are One to Many relationships, since One Patient can have Many Encounters (Health Care Visits).

My plan for entering the information was to create a patient in the Contact Information Table (with a form). Then change to a Patient Encounter Form (that contains all of the subforms bound to the 14 tables), choose the PatientID Number of the patient who visited and enter the information into the subforms through this main form (Patient Encounter Form).

I have a feeling there is a flaw in my plan, that is not readily obvious to me, and is causing the issue we are discussing here. I am hopeful, from my explanation, that you can pinpoint the flaw in my design.

By the way: The eventual point of gathering all of this information is to be able to a data analysis of the Patient Encounters from various angles. Things like how many people visited a clinic for dietary issues in a week/month/year, or how many of these people were aboriginal, or lived in rural areas, etc.

I appreciate everyone's patience with my bumbling around with this. Any help is greatly appreciated.

John V

[June7]

Forget the form and how it is laid out. Identify your data entities and how they relate. Here is a tutorial site that might help. http://www.rogersaccesslibrary.com/

Consider for a start:

tblPatients
PatientID (PK)
FirstName
LastName
Phone
DOB
Sex
etc

tblVisits
VisitID (PK)
PatientID (FK)
VisitDate
PhysicianID
TypeOfAppointment
Reason (memo)

tblVisitDiagnosis
VisitID (FK)
DiagnosisID (FK)
MedicationID (FK) (if there can be more than one medication then maybe another related table)

Not sure you really need all 14 tables to hold patient visit info. For instance, TypeOfAppointment should probably be a field in tblVisits. You can have a table called AppointmentTypes that will be a lookup source for a combobox that lists types available for selection into the TypeOfAppointment field. But I expect each visit can be only one appointment type.

[John V]

Thanks June7. I rethought my database design a bit and now it all works great!