Problems with sendkeys

[John Zelmer]

Hi all,

In a inhereted db, in a form, there is field A (search category) which, in its 'after update' event, has code to set focus to field B (wich is a combobox (and set to 'only in list')). After that piece of code there is code that uses 5 sendkeys commands to send 5 letters/numbers to field B as a prefix (that is used for searching purposes, people then won't have to type in the prefix everytime).

I assume the former developer did this because you can't set field B's value when it is just a part of the whole value. It won't accept the partial value as it is not in the list.

Now users are getting problems that the first sendkeys letter isn't properly send, so they miss one letter each time they search. I know sendkeys is a big No-No. I was wondering if someone has an idea for a robust solution for this problem.

I hope the problem is clear.
Thanks in advance,

John

 

[static]

Combo0.SetFocus
Combo0.Dropdown
Combo0 = "foo"

 

[John Zelmer]

Thanks Static,
I tested this and the combo drops down, but the "foo" is not entered into the field.

John

 

[static]

Replace foo with whatever 5 characters your code uses.

If the text isn't displayed by the control, you'll need to provide an example.

 

[sneuberg]

Static's code displays "foo" in the combo where I tested his code. It would probably be helpful if you posted your version of static's code along with any other code in the same procedure.

 

[John Zelmer]

Thanks. I did replace the characters but it didn't work.

However, I did some further testing and I found that the combo box consists of 3 rows of which the first isn't shown (0cm). So I assume that when you set a value to the combo it will try to set the value for column(0) and not for column(1) which is actually the field to search in. Column(0) is a field which can have duplicates, so I can't set that field's value in order to show the right value for column(2). Any suggestions with this new information?

 

[static]

Yeah it's not a good a solution really.

A good solution would probably be one that displays a pop up form and unbound list to do the search and returns the selected value to the control.

 

[John Zelmer]

Thanks, I was afraid of that. It's a complex form with lots of other controls, code and dependencies (to other forms). I'm afraid that I will unknowingly mess it up. But I will look into your solution anyway...

John

 

[static]

Can the user pick values that don't start with those characters? If not you could just filter the list.

 

[sneuberg]

How about something like:

Dim rs As DAO.Recordset
Me.Combo0.SetFocus
Me.Combo0.Dropdown
Set rs = Me.Combo0.Recordset
rs.FindFirst "[COLOR="Blue"][Last Name][/COLOR] Like '[COLOR="Blue"]Ra[/COLOR]*'"
Me.Combo0 = rs![ID]

where you would replace Ra with the five letters and [Last Name] with the field name of the second column. This code is demonstrated in the attached database

 

[static]

I think another problem is that setting the value in code doesn't give the 'not in list' error so you could end up with lots of bad records.
You would need to check the value isn't still the 'prefix' before update.

 

[sneuberg]

The code doesn't set the combo box to anything that not in it's row source. If nothing like the prefix is found it is set to the first record in the row source; first being defined by the sort order of the second or displayed column.

 

[John Zelmer]

Thanks Steve for your time. I did not know that was possible. There is one difference with my situation and that is the order of the fields in the combo:

1. Ref Number 1 (not unique) (first column (not visible))
2. Ref Number 2 (unique) first (second column (visible))
3. ID number (primary key) (third column (visible))

So they need to search on the second field and the prefix are the first 5 letters of the value to be found. When they leave the field with the code the prefix should be in the field and the cursor should be at the end of the prefix so that they immediately can type ahead to get the value they want.

Changing the order of the combo fields is something I'm trying to avoid since I can't oversee the implications for the rest of the form. I tried to change the order of the fields in your example but I doesn't work because I guess that the ID field should be the first field in the combo.

John

 

[John Zelmer]

"Can the user pick values that don't start with those characters? If not you could just filter the list."

At the moment they can, by removing the prefix characters. But I'm not completely sure if there is ever a need for that. I have to check with the users. What do you mean by filter the list? How do I do that?

 

[static]

What do you mean by filter the list? How do I do that?

sneuberg showed you how to find a record with LIKE. You can filter using the same syntax.

If the user should pick a value that starts with those 5 characters, then there is no reason to give them the option of selecting values that don't start with that string. Simply remove everything else from the list.

Const prefix As String = "foo"

Private Sub Form_Open(Cancel As Integer)
    Combo0.RowSource = "select id,txt1,txt2 from tLookup"
End Sub

Private Sub Command2_Click()
    Combo0.RowSource = "select id,txt1,txt2 from tLookup where txt1 like '" & prefix & "*'"
    Combo0.SetFocus
    Combo0.Dropdown
End Sub

 

[John Zelmer]

Thanks again Static. I see. I did some further testing and the sendkeys is used so that users don't have to type the prefix. And....it seems that normally they don't see the combo dropping down at all, they enter the remaining characters and hit tab after which the main form record gets synchronised on the basis of the first field value of the combo. If the value doesn't exist, the user gets a 'not in list' error. It seems the combo is only used to determine if the entered value actually exists. But that could be done in another way. I will check with the users if they ever dropdown the combo. If not, I think I'm able to solve it by searching the value programmatically.

John