KeyPress event

[spalmateer]

Hi,
I have a search form where I enter criteria in a textbox and it limits the criteria of a query. The results are shown in the same form. I'm currently using the the following code to requery the the query after update on the textbox:

Private Sub txtLastName_AfterUpdate()
Me.frmSubSearch.Requery
End Sub

This code works fine, but only after I exit the box, thus after it updates. I'd like it to requery after every keypress in the textbox. Is this possible? I've tried entering the code on the key press event but its not working. I've also tried it with keypreview on and off. I don't know if its because its not updating after every keypress. The criteria of the query references the value in the textbox, not a table, so I would think it wouldn't have to update to work. The textbox is unbound. Can I force update after every keypress? Thanks in advance for any advice!
Scott

 

[boblarson]

If you want it to update with every keystroke, use the ON CHANGE event.

BL
hth

 

[spalmateer]

Hi,
Thanks for the reply. I was using it on the wrong event. Do you know if there is a way to force an update after every keystoke. I've tried using DoCmd.RunCommand acCmdSaveRecord to save the record but then it sets the cursor back at the beginning of the field so the user can't continue typing. The reason I'm asking is because I have two textboxes on my form. One the user types something in the other is hidden and takes what was entered into the firstbox and adds an asterisk. Then the query references the hidden textbox for its criteria. Here is the code:

Private Sub cboLastName_Change()
If IsNull(Me![cboLastName]) Then
Me![txtLastName] = Null
Else: Me![txtLastName] = Me!cboLastName.Value & "*"
End If
DoCmd.RunCommand acCmdSaveRecord 'This doesnt work- This is where I want it to update the field but keep the cursor at its current position.
Me.frmSubSearch.Requery
Me.txtRecord.Requery
End Sub

Any ideas? Thanks
Scott

 

[spalmateer]

Basically I just need to know if there is a way to update after every keypress while leaving the cursor in the same place. If I use DoCmd.RunCommand acCmdSaveRecord then the cursor moves to the beginning of the field after saving. Please, does anybody have any ideas? Thanks!
Scott

[This message has been edited by spalmateer (edited 04-23-2002).]

 

[David R]

Why not just use a combobox? You're introducing a LOT of processing time asking Access to requery the entire recordset everytime your user presses a key.

 

[spalmateer]

I'm mainly trying to avoid comboboxes because I've heard they are not good with big recordsets. If I'm trying to filter the query based on last name, there is going to be probably like 15000 different last names in our database. The processing time now is minimal but its just a pain in the butt to click out of the textbox (so it updates) just to see the results. It would be nice if they just showed based on your keystrokes.

 

[David R]

If you think a combobox is going to be inefficient with 15000 records, what makes you think requerying the entire recordset on every keystroke is going to be any better?

If you are committed to doing this, boblarson gave you the event you should use.

 

[spalmateer]

Point well taken. I will try both. I understand from Bob's suggestion that I should use the onchange event but the question remains unanswered if it is possible to force update after every key stroke (without saving because it moves the cursor). I can get it to requery after every keystroke using the onchange event but until it updates, it value supplied to the query wont change. Thanks for all the advice so far. I understand it may not be efficient but I'd like to try it out first, then make the decision.
Scott

[This message has been edited by spalmateer (edited 04-24-2002).]