By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,320 Members | 2,109 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,320 IT Pros & Developers. It's quick & easy.

Change Listbox based on another field on a form

P: 16
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!!!!
Sep 24 '07 #1
Share this Question
Share on Google+
5 Replies


ADezii
Expert 5K+
P: 8,667
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:
Expand|Select|Wrap|Line Numbers
  1. Patients.[Patient ID](1) ==> Medications.[Patient ID](MANY)
Sep 24 '07 #2

P: 16
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.
Sep 25 '07 #3

ADezii
Expert 5K+
P: 8,667
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.   Me![Patient Medications].RowSourceType = "Table/Query"
  3.   Me![Patient Medications].RowSource = "Select Medication From tblMedications " _
  4.                                        & "Where [PatientID]=" & Me![PatientID]
  5. End Sub
Sep 25 '07 #4

P: 16
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!
Sep 25 '07 #5

ADezii
Expert 5K+
P: 8,667
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?
Sep 25 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.