Unbound combo box selecting records w/same name but different Lot Number

[tayl429]

I am fairly new to Access and may not ask this question appropriately but here goes.....

I am creating a medication database for a physician's office. I have it setup in a form and it works just like I want it too until I have two or more drugs with the same lot number.

Here goes:

I have an unbound combo box in the header of my form. I click on the arrow to show the records in the table. If I have a drug named advil with a lot number of 1234 and another drug named zinc with a lot number of 1234 it always goes to the Advil, even though I clicked on zinc. How can I get the selected item to show up in the detail area of my form?

[nico5038]

A combobox will use the "bound field" (see the properties window for the combobox under the datatab).
As you use the lot number that's not unique, you'll always get the first.
One option is to set the bound field to the description, or add the unique productID and use that.

When you want to show all medicins for a lot, you'll need to define a frmLot form bound to the lot and a subform for the medicins. The Access subform wizard will propose a link field and when you accept the proposal, then only the medicins of that lot will show.

Getting the idea ?

Nic;o)

[missinglinq]

In Design View of the query underlying your form, create a new, calculated field named LotAndDrug, by entering this in a blank Field box:

LotAndDrug: [YourLotNumberFieldName] & " " & [YourDrugFieldName]

Now, delete the combobox you have and use the wizard to create a new one. Select the third option "Find a record based on the value I selected in my combobox."

Hit Next

Now, from the same query the form is based on, click on the field you've just created, LotAndDrug, to move it into the right hand box. This is the field that will appear in your combobox.

Click thru the wizard to finish the process.

Now, when you go into your combobox you'll see

1234 Advil
1234 Zinc

and if you click on 1234 Zinc that's the record that will be retrieved, not 1234 Advil.

If you wanted to retrieve records by the drug name and lot number, instead of lot number and drug name, simply replace

LotAndDrug: [YourLotNumberFieldName] & " " & [YourDrugFieldName]

with LotAndDrug: [YourDrugFieldName] & " " & [YourLotNumberFieldName]

In this kind of situation, nico, there's no relationship between lot numbers, so there probably wouldn't be any reason to retrieve or group records by lot numbers.

Welcome to TheScripts!

Linq ;0)>