Need a Combo Box to Select Correct Record (Duplicates)

[kucpa]

I have created a database using Access 2002. The main form in the database has a number of sub-forms available by choosing tabs. On the top of the main form I used the Combo Box Wizard to create a combo box to find a record on my form based on the values I select. The combo lookup box is based on a query using the fields Last_Name and First_Name.

Everything works fine except for a couple of people with the same last name. The combo box always selects the record of the first person with that last name, (even though I can see and select the first and last name in the combo box).

For example: I have Betty Smith, Jane Smith, and John Smith. No matter which “Smith” I choose in the lookup box, Betty Smith is always returned.

Is there a way to have the combo box select the Smith I want?

 

[DougP]

Yes if you give each person a unique ID
such as Social security number or even an autonumber field

Then you have to redo your combo box to include that Key field. If done properly you can still just see Smith or whatever and not the ID.

Put the ID out on the far rigth.

DougP, MCP

 

[willir]

kucpa

Couple of things...

First does the existing combo box use an autonumber to find a record? Example, the user sees "Smith, Betty", "Smith, Jane" etc, but the CustomerID column is hidden. If so, and I suspect this will be true since the combo and list box wizards do this approach automatically. To see if a column is hidden, look at the SQL query property. If it display CustomerID, LastName, FirstName, and you only see the LastName and FirstName, then the ID field is hidden.

To hide a field, look in the format tab under the properties for the combo box. Specifically, the column width property. Any column width of 0" will be hidden.

Second, does the query result display more than one record. At the bottom of a typical form where the forward and backward arrows are located, you will see "1 of 1", etc. For "Smith", do you see "1 of 3" ? If so, then the user just has to advance to the next record. This is a procedural thing where the user has to learn that a query can bring back more than record.

I suspect you have the first scenario and not the second. How to fix it...

One simple work around is for the user to hold down the ALT-DownArro or click on the down arro for the combo box to display the customer names. They can then select the appropriate name.

If this does not suffice, there are two other ways, either involves modifying the code just a tad where you match on the user's name instead of the numeric ID. You can create another combo box, or modify the existing one.

Regardless of using a new combo box or editing your existing one, you need to first change the properties of the SQL query so it does not include the CutomerID. You will also have to delete the 0" column width entry under the format tab.

Then, for the properties of the combo box, go to the "Event" tab. You should see "[Event Procedure]" for the After Update event. Click on the field, and then get into the VB editor by clicking on the "..." box to the right of the "[Event Procedure]".

You will see something likfe the following...

If Not IsNull(Me.LastNameQry) Then
Me.Filter = "CustomerID= " & Me.LastNameQry
Me.FilterOn = True
End If

- LastNameQry is the name of the combo box query field on the form.
- CustomerID is the name of the numeric ID field in the Customer table.

The problem is that Me.LastNameQry was storing a number. Now you want to use it to store a text string. This is what the aforementioned change to the combo box query will do -- make the first column retrieved the last name.

Consider adding / modifying to code to something like...

Dim strName as String, strQ as String

strQ = chr$(34) 'Ascii character for double quote "

If Not IsNull(Me.LastNameQry) Then
strName = me.LastNameQry
Me.Filter = "LastName = " & strQ & strName & strQ
Me.FilterOn = True
End If

Yes, some of the dynamite coders will come up with something better. But the improtant thing is that you are now referencing the LastName, not CustomerD, and you are using a text string which must be encapulsated with quuotes.


Getting fancy...
- One draw back to what I have explained so far, is that it will list all "Smiths". If you modify the SQL statement to use DISTINCT (not DISTINCTROW), it will only display "Smith" once provided your SQL query only retrieves the last name.
- If more than one record is found, you can display the different names in a small continuous form where the user can select the one they want and the filter is applied for the specific customer. A little more coding.

Richard

 

[kucpa]

Hey Doug and Richard,

Thanks so much for your useful solutions. I am using a unique number as a primary field. When I included that number and hid it to the right, I had the same results as before. When I included that field and hid it on the left, however it gave the exact result I was looking for. I really appreciate your help! Richard, you gave me some cool things to think about. If I ever need to go “fancy” I’ll certainly use your suggestions. Thanks again!

-Paul K.