Show multiple records in a medical form

[AbeA]

I work at a small mental health clinic and am trying to accomplish the following. Access skills a little rusty, it's been a while.

What I have:

• I have one table that lists all of the patients, their phone number, address, etc.
• In another table, it lists the medications that each patient takes, as well as the strength, dosage, etc.
• The two tables have a relationship via the "Patient ID" column in each table.
• Most patients have multiple entries in the second table, as most patients take multiple medications.

What I am attempting to accomplish:

• The main form in my database simply lists all the patients names in datasheet view. I want to be able to click a Patient's name, then go to a form that lists all of that patients medications. I've attached a screenshot that encapsulates precisely what I am looking to accomplish.
• Since most patients take multiple medications, I need to be able to list multiple records in a single form, grouped by medication name, just like in the attached image.

I am uncertain as to how to accomplish this. I've tried using subforms but it doesn't list the data at all? Also, it looks like an Access report can create precisely what I need, but I need to have a live form that I can use to update data real-time.

 

[pr2-eugin]

Hello AbeA, Create the second form as a Continuous Form, then add this as a SubForm to the Main details Form.. Then later you can change the clause to GroupBy.. Makes sense?

 

[Simon_MT]

Try building your form from the Medications linking to the Patients with the Patients information in the Header. Use a query behind the Form to sort the Medications.

Simon

 

[AbeA]

OK, I attempted to do what you said, and I'm getting close here. I added the patient list form as a tabular form. I then I created a Query that filters by patient name and by medication, then added this query as a subform on my patient form.

But unfortunately, what I am getting now is a single user name appearing in the form, then all the medications with the same name listed in the subform below.

I need to be able to view all the medications for a single patient, not just one at a time. There are multiple records with the same data in the "last name" and "first name" columns in my query, again because many patients have multiple medications.

Thanks for any help you may provide.

 

[JHB]

You should link the main and the sub form together with the "PatientID" in the "Link Master Fields" and the "Link Child Fields".
I've made a small example for you in the attached database.
Open the form "Patient_datasheet", click on the name and the "Patient" form with the sub form opens.
The attached picture shows the 2 tables to the left and the 2 forms to the right.

 

[AbeA]

Awesome JHB, Thank you. I was able to get it listed in the Form the way I wanted, with help from you guys, again, thanks.

One more problem:

I cannot add a new prescription for a patient on my main form (screenshot below). I can input data in the "Drug" column, but when I attempt to type anything in the "Rx Date" column, I receive the following error in the status bar: "Cannot add record(s): join key of table 'Prescriptions' not in record set" Any idea what's causing this?

 

[AbeA]

OK, I've figured out the reason why it wasn't sorting by two columns in my query. I had to add "rx date" in twice, uncheck the visibility, then sort by "descending" and it displayed correctly.

The two issues remain:

1:"Cannot add record(s): join key of table 'Prescriptions' not in record set" error in subform when attempting to add data to the "Rx Date" column.
2: Date Picker not appearing for Last Appointment/Next Appointment fields. Date Picker appears just fine when I create a NEW form and add these fields in, but won't appear for my existing form.

Thanks for any help you may be able to provide.

 

[JHB]

1. Which field(s) is the key field in the table 'Prescriptions', (again it is more or less the same problem as you had before, link the main form and sub form together with the "Link Master Fields" and the "Link Child Fields".)?

2. Set the property for the date fields to show the datepicker, (see the attach picture).

 

[AbeA]

Thanks again JHB. I got it working. I had to add/ click and drag the "*",( the first entry) for each table in Design View of my Main Query.