Strange Combo Box behavior when using keyboard and arrow keys 1

[keun]

I have a Client form with thousands of clients and a combobox to get you to the record you want. Let's say I have twenty people with the last name Smith. Hitting the arrow on the combobox and then typing SMI gets you to the Smiths, if you use the arrow keys to arrow down to the Smith that you want, and then hit ENTER, gets you to the first Smith, not the Smith that you had selected.

I have never seen that before! Any ideas?

Private Sub cboFind_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ClientFamily] = '" & Me![cboFind] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Me.cboFind.Value = ""
End Sub

 

[MajP]

Looks to me like it is doing exactly what was coded

rs.FindFirst "[ClientFamily] = '" & Me![cboFind] & "'"

or

rs.FindFirst "[ClientFamily] = '" Smith "'"

How about binding it to a person id

rs.FindFirst "[PersonID] = " & me.cboFind

 

[MajP]

This may be of interest
faq702-6304

 

[keun]

MajP: Thanks, I will change the combobox to bind to ClientID. I had considered that.

I am still confused, however, why selecting with the keyboard returns a different result than selecting with the mouse. That is, if a user scrolls through the list and clicks on the fifth Smith, the user it taken to that record. But if the arrow keys and enter key are used to select the fifth Smith, the user is taken to the first Smith.

Thanks for the link also; I will look at that!

 

[MajP]

Wierd, The way your code is written, you should always find the first occurence. You should always try to use the "Find" methods with an indexed field.

The only way the click is working is that some other event is firing. Any chance you have a click event with the correct code in it. The Two methods produc slightly different events,(i.e keypress and click), but both fire the after update.

 

[keun]

That's what I get for listening to the users. Both methods return the same result (after I tested). Sorry!

I made the change, so that the combobox now stores the ClientID, I just sort the list by Last Name.

So, is there no way for users to type the Last Name but have Access select the ClientID (the primary key)?

 

[MajP]

Yes. Actually that is the most common design. Here is an example

Rowsource:

SELECT datTblPersonnel.autoPersonnelID, datTblPersonnel.strLastName, datTblPersonnel.strFirstName FROM datTblPersonnel;

Number of columns: 3
Bound Column:1
Column widths: 0",1",1"

there are three columns in the recordset but because of the column widths property the first column is invisible because it has a width of zero

The first visible column shows up in the combo

when I type in smith or click, the value is then the value of the ID since autoPersonnelID is the bound field.
The bound column does

 

[keun]

NICE! After some tweaking I had it set up JUST LIKE THAT, so all I had to do was go in, and according to your instructions, change my first column to 0". It is now doing exactly what I want it to!

Thanks a bunch!

 

[bdm1]

Real strange problem...I have a form based on a table. A combo box retrieves the matching record when selected. All worked fine until now. Records show in the combo list but when selected, I get the error message that the text entered isn't an item in the list. This does not happen with every record but just some. I have deleted the combo box and rebuilt it (with the wizard) and still the problem. I have also created a new form with combo based on the same table and still have the same error selecting certain records. This would indicate that the problem lies within the table but I am at a loss to figure out what went wrong. No changes were made to the table. Am using Access 2000 in Win XP. Does anyone have a clue as to what is going on? Any help is more than appreciated.
bdm

 

[keun]

I think you will have better luck by starting a new thread!