Hello. My name is***** can help you with your question.
I think I understand what you're trying to do. And it sounds like you have a table with drug information in each record. And you are trying to select all the records that are associated with a particular value for a particular field.
Where I lost you was when you said that the combo box is creating distinct records and it pulls up the first record relating to the drug but it doesn't filter just those drug results only.
This doesn't sound something like a form you would typically be doing with only one table – is a single table a requirement of what you're doing? Ideally, what we would want is for example a table with the names of drugs in them so that there would be one record for each drug name and then have other fields that tell us what other pieces of information from other tables go with that drug. That way if you pull up a drug and the drug would be a unique value in that table – then you could put pull up information and other tables associated with it whether those tables be one with side effects another with customers who take it another with prices and so on. And by doing this you can associate one drug with many other pieces of information. Does that sound like what you are trying to do?
Now you would need tables related to that one that have whatever other information you need. You should have a list of attributes that you want to record for each drug and then and then use a relationship between the tables to attach to those records to each other. You had mentioned that it's already based on a query so you also the option of using those query fields in a form as well. There are many potential variables here in terms of how to do things.
I'm still not sure what you mean about "it doesn't filter just those drug results only. Does this need to be removed from the row source?"