Change Listbox based on another field on a form

[datasec23]

Hi...I am trying to get my listbox data to change based on a field on a form. Each time I click on the next record indicator at the bottom of the form, I would like the Listbox to change information. The control field is "Patient ID" and the listbox is "Patient Medications". When I go to the next Patient ID record I would like the Listbox to show the medications for that patient. Any ideas or direction on this would be really appreciated. It sounds like it should be easy to do, but for some reason the filters aren't working. I can get it to work on a subform, but that won't work for me in the long run.

Thanks!!!!

[ADezii]

Hi...I am trying to get my listbox data to change based on a field on a form. Each time I click on the next record indicator at the bottom of the form, I would like the Listbox to change information. The control field is "Patient ID" and the listbox is "Patient Medications". When I go to the next Patient ID record I would like the Listbox to show the medications for that patient. Any ideas or direction on this would be really appreciated. It sounds like it should be easy to do, but for some reason the filters aren't working. I can get it to work on a subform, but that won't work for me in the long run.

Thanks!!!!

Are there Patients and Medications Tables linked by Patient ID, namely:

Patients.[Patient ID](1) ==> Medications.[Patient ID](MANY)

[datasec23]

Yes they are linked as you show them. I have been able to get the listbox to show only the medications in the list for the first record now, but when I change to the next patient record the listbox stays the same with the information for only the first patient record.

[ADezii]

Yes they are linked as you show them. I have been able to get the listbox to show only the medications in the list for the first record now, but when I change to the next patient record the listbox stays the same with the information for only the first patient record.

Copy and Paste the following code to your Form's Current() Event and make any necessary Name substitutions:

Private Sub Form_Current()
  Me![Patient Medications].RowSourceType = "Table/Query"
  Me![Patient Medications].RowSource = "Select Medication From tblMedications " _
                                       & "Where [PatientID]=" & Me![PatientID]
End Sub

[datasec23]

Thank you for the help, but it did not seem to work.

Basically, I have a query that pulls all of the information I need from multiple tables, and it contains the medication list for all patients. When I point to this query through a listbox from a form, I want to show only the medication list for the current patient id on the form. IE if I am looking at Patient 15's record, I only want to see the medications for patient 15 in the listbox. I have tried code and queries and joins and relationships and I just can't get the filter to work

I was up until 3:00am trying to figure this out...Please help, I know I am missing something little.

Thanks!

[ADezii]

Thank you for the help, but it did not seem to work.

Basically, I have a query that pulls all of the information I need from multiple tables, and it contains the medication list for all patients. When I point to this query through a listbox from a form, I want to show only the medication list for the current patient id on the form. IE if I am looking at Patient 15's record, I only want to see the medications for patient 15 in the listbox. I have tried code and queries and joins and relationships and I just can't get the filter to work

I was up until 3:00am trying to figure this out...Please help, I know I am missing something little.

Thanks!

Is [PatientID] a Text or Numeric Field?