2 issues with Combo Box searches...

[RickDB]

Hello, here goes...


#1

I have a form with a combo box to be used for navigating to records (created using the wizard), based on a query with these fields:
1 - Last Name (Sorted Ascending)
2 - First name (Sorted Ascending)
3 - Company
4 - CustomerID (PK)

The combo box only partially works, it pulls up a list, but here is the problem:

If the name comes up 'Hartman, BillyBob', and there is only one Hartman, then all is well.

But if you have several Hartmans, say:

Hartman, Ace
Hartman, Bob
Hartman, Stan

and say you try to select Bob, it always goes to the first Hartman in the list (in this case Ace).

It is a pain in the ace, what am I doing wrong? Thanks for any help...

(I will ask #2 after this is resolved)

 

[ejstefl]

It is searching based on last name. Change it to search for the customer ID.

 

[RickDB]

But people will not know the customer number. They will be on the phone with George Smith, they'll know his name, but have no idea what his customer number is, so it seems a search box for customer number would be useless.

 

[ejstefl]

Right.... I guess I'm picturing a combo box that has:

Hartman, Ace
Hartman, Bob
Hartman, Stan

And no matter which the user selects, the form displays Ace. This would be because Access is using the last name to search, not the CustomerID. The CustomerID will always be unique, the name won't.

Just modify the code to use CustomerID as the search criteria instead of name. The user will still select the name, but the database will search for Customer ID. Make sense?

 

[RickF]

You need to do a search on the last name try something like this to find the name. Put the code in your OnUpdate event

max = Me.Names.ListCount

For i = 1 To max
Me.cboName = Me.cboName.ItemData(i)

If Me.cboName.Column(1, i) = Me.txtLastName.Value Then
Exit For
End If

Next i

 

[RickDB]

Right.... I guess I'm picturing a combo box that has:

Hartman, Ace
Hartman, Bob
Hartman, Stan

And no matter which the user selects, the form displays Ace. This would be because Access is using the last name to search, not the CustomerID. The CustomerID will always be unique, the name won't.

Just modify the code to use CustomerID as the search criteria instead of name. The user will still select the name, but the database will search for Customer ID. Make sense?

Exactly, I just wanted to clarify... I figured the wizard would have done this automatically.

Thank you ejstefl! I will give this a try shortly.

 

[ejstefl]

I'm not sure the code the wizard uses, but I like to use code like this:

me.recordsetclone.findfirst "[CustomerID] = " & me.cboSearchCustomer
me.recordset.bookmark = me.recordsetcone.bookmark

Just make sure that the combo box is bound to Customer ID.

 

[gemma-the-husky]

what you do from your first example is put the id key as the FIRST item in the combo box. your user is then selecting that particular record, and you can use the combo box value directly in references. Make the size of the first column 0, and the id key will be HIDDEN from the users, who will see the next column in the selection - in your example Hartman.

As this is still a problem, as there are duplicated "Hartman" values, you may actually need to do something else and include yet another column. The wizard is not sufficiently flexible for this, so instead of using the wizard, design the combo box source as a stored query. Make sure the PK is the first column in the query. Make the second column something like
[last name] & "," & [first name]. or
[firstname] & " " & [lastname]) whatever you need

Set the sort orders of the remaining column as you want. You can add additional columns without displaying them to get the sort order you want.

So now you have 5 visible columns in your query, not 4, ordered in a different way. So make sure the combo box properties show 5 columns.

Now in the combo box set the widths to be 0, 0.1, 4, 4, 4
The 0.1 for the second column is very important.

What happens is that the combobox returns the value of the first column, the pk. If you are using the text value in your code you may need to modify the code.

However, you will see that the combo box actually DISPLAYS the contents of the first non-zero column, ie column 2, but this is hidden in the drop down, because it is only 0.1 wide.

Hope this helps

 

[gemma-the-husky]

now whats number #2

 

[ejstefl]

There is no reason to set it to .1 - you can set it to 0.

 

[gemma-the-husky]

no, if you set a column length to 0 it won't display in the combo box. a combo box displays the first non-zero length column in its results. so if you set a column to a micro lengthm, you wont see it in the drop down, but it will display in the closed combo box.

 

[RickDB]

OK, now I need to figure this out:

Against my better judgement (from what I read about normalization), and for making forms easier to set up, I have three phone number fields:

BusinessPhone
MobilePhone
EveningPhone

How do I string those fields together into a similar Combo Box search in the top of my form navigation?

Using the wizard, you can only search the first field entered in the search (i.e. BusinessPhone).

Thanks!

 

[boblarson]

To quote gemma-the-husky

...combo box displays the first non-zero length column in its results.

Just as a further expansion of what gemma had said it will also display any number of columns (subject to the limitation, which I don't know as I don't display more than about 3 columns at a time for user simplicity) you wish as long as you specify the number of columns and set the size of the columns to a value greater than zero.

So, to have a query underlying a combo box that pulls this:
CustomerID
CustomerLastName
CustomerFirstName

and have it only display the first and last name, would be to set the number of columns to 3 and then size it 0";1",1"

If you had the query with the ID last like this:
CustomerLastName
CustomerFirstName
CustomerID

you can reference the combo box in your query that pulls the data based on the combo box selection like:
[Forms!].[YourFormNameHere].[YourComboBoxNameHere].Column(2)
and it would be the same as using
[Forms!].[YourFormNameHere].[YourComboBoxNameHere]
where you had the CustomerID as the first field in the underlying query. And to display in the combo box you would set the columns to 3 and the sizes to 1";1";0"

 

[boblarson]

Now, on to using the search to search in many fields.

I had created a simple search to let the program search in whatever field I had my cursor in when I clicked the search button on my form. So, if I was in business phone, it would search in business phone and if I was in city, it would find the first instance of that city.

There are more complex ways to do this, but here is the code I used for that simplistic search.

    Dim strInput As String
    On Error GoTo Err_cmdFindGrantee_Click

    strInput = InputBox("Find what?", "Find Record")
    Screen.PreviousControl.SetFocus
    If strInput <> "" Then
        DoCmd.FindRecord strInput, acAnywhere, False, acSearchAll, , acCurrent
    End If
    Exit Sub

Err_cmdFindGrantee_Click:
    MsgBox Err.Description
    Resume Next

 

[gemma-the-husky]

But to clarify what i generally do, with eg bobs example of a query with columns

CustomerID
CustomerLastName
CustomerFirstName

I add a further column in the query if necessary

CustomerID
CustomerLastName & " , " & customerFirstName & " (ID: " & customerid & " )" 'additional column
CustomerLastName
CustomerFirstName

and set the widths to 0,0.1,3,3 say.

Then when you drop down the combo box, it shows

Bloggs Fred
Hartman Pat
Smith John

but when you make your selection, it shows
Hartman, Pat (ID: 1234)

which may be important to see if you have duplicate surnames.

 

[gemma-the-husky]

But to clarify wat i generally do, with eg bobs example of a query with columns

CustomerID
CustomerLastName
CustomerFirstName

I add a further column in the query if necessary

CustomerID
CustomerLastName & " , " & customerFirstName & " (ID: " & customerid & " )" 'additional column
CustomerLastName
CustomerFirstName

and set the widths to 0,0.1,3,3 say.

Then when you drop down the combo box, it shows

Bloggs Fred
Hartman Pat
Smith John

but when you make your selection, it shows
Hartman, Pat (ID: 1234)

which may be important to see if you have duplicate surnames.