Subform Question 5

[Minuet]

I work in an outpatient clinic and have an Access form in which I enter extensive patient information. All patients have a hospital chart # which is my primary key. Ideally I would like to have a button on my form which when pressed, would pull up a subform containing all of the appointments the individual patient has had in the past. I need to be able to enter an unlimited amount of dates for each patient. I am familiar with command buttons and have a basic understanding of making subforms. Could anyone give me some direction on how to go about making my appointment form?

 

[Terpsfan]

I would create a table called tblPatients and another table at least called tblAppointments. I would create a PatientID autonumber as a primary key (long integer data type) in the tblPatients table and a PatientID as same data type as a foreign key in tblAppointments. Then go to Tools...relationships and add both tables. Then drag the primary key from tblPatient to tblAppointments and click all 3 check boxes. Then I'd go back and create a form-subform via the form wizard. First add all the fields from tblPatients and then all the fields from tblAppointments...the wiz will automatically link your forms together and synchronize them so that you can display a patient history of appointments in the subform. Here are some initial fields to put in the tables.

tblPatients tblAppointments tblDoctors
PatientID AppointmentID DoctorID
LastName PatientID DoctorLname
FirstName AppointmentDate DoctorFname
Init AppointmentTime
Social DoctorID
Address Symptomology

 

[Minuet]

Thank you so much for your reply! As a beginner with no formal instruction in Access, I'm trying to work my way through your reply. I'm having a bit of trouble with the foreign key. How do I mark the PatientID autonumber field in tblAppointments as the foreign key?

 

[Minuet]

After working some more with my forms, I have figured that a foreign key doesn't need to be marked in any way (?). I have followed your instructions, and when I try to create my relationship between the two tables (tblPatients and tblAppointments) I get the error message "Invalid field definition 'Patient ID'in definition of index or relationship." What does this mean?

 

[Terpsfan]

You have a PatientID in both tables with the same datatype? And you dragged the PatientID from tblPatients to tblAppointments?

 

[Minuet]

Yes to both questions.

 

[SheetsERR]

Minuet,

Omega's post was great! Just thought I'd reply real quick to your latest question....

PatientID in tblAppointments should actually be of the Number data type - not autonumber. PatientID in the other table will be your autonumber primary key.

The autonumber primary key in a main table links to a number foreign key in the subsequent table. Set the data type for each table's column in Design View. Finally, set the relationship for each by clicking Tools > Relationships. Tables won't be linked until you set their relationships.

Sheetserr

 

[Terpsfan]

Change the PatientID's data type in tblAppointments from Autonumber to Number, then Long Integer if the field properties. Then go back and set your relationships.

 

[Minuet]

Thanks to both of you! I was able to create the relationship!

 

[Minuet]

Although I'm making progress, my forms don't work quite the way I want them to. Like I said earlier, my main form is used to enter and save patient information such as name, address, tel, health numbers, diagnoses, etc. I have two combo boxes which I use to look up a particular patient. I can look a patient up using their name or their hospital chart number. If John Doe was coming in for an appointment today, I would type in "Doe John" into my combo box, and his information would come up. I then want to be able to click on an "Appointment" button on the form and enter today's date date so we have record of him coming in. How do I connect my main form to the new form I created with your help?

 

[rohosub]

Hi.
This question and the answers have helped me a lot too.
But I have a similar situation where I'd like all the 'linked' records in the Appointments table to appear in a list-box. It works fine with single fields, but I can't get it to work with the list box.

If I apply a list box to the subform I'm only able to list all the records in the table and not only the ones related to the PatientID.

Could someone please help?

roho

 

[Terpsfan]

Minuet, if you have created the two tables and set up a relationship between them, you can use the form wizard to automatically create a subform that is linked to the record in the main form. When you bring up the form wizard, first add the tblPatients table and the fields, then add the tblAppointments table and fields...then it will ask you whether you want a form/subform layout or linked forms. A form/subform layout is going to present both forms together, with the main one on top (tblPatients). The linked forms is going to present the main form and then a button to click for the related records in tblAppointments. To add a new appointment, all you'd do is click on the asterisk in the appointment subform or linked form to insert a new record. Hope this helps.

 

[rohosub]

Hi.
I solved my problem myself by using a datasheet in the subform instead of a list box.

Thx anyway.

roho

 

[Minuet]

I have a question which relates to my original question above. I keep track of patients' past appointment dates using my subform (in which I can enter an unlimited amount of dates). Is there any way I can run a query to show me the last entered appointment date for each patient in my database? If so, how do I do this?