run event as a user types (ie: not on change)

[Core]

I did some research on google and apparently the "On Change" event does not fire while the contents of a text box is being changed but when the users commits the changes.

Scenario:

I have a list box that contains all the courses we offer (a lot!). I have a text box under this not bound to anything, for user to type a filter term. I would like the results in the list box to change as the user types, ie:

results displayed:

• Home and Office Level 1
• Home and Office Level 2
• Health and Safety
• English Level 1
• Numeracy Level 2
• etc etc etc

The user clicks the text box and types "H" and the list changes as soon as the keyboard is hit and released the list box becomes:

• Home and Office Level 1
• Home and Office Level 2
• Health and Safety
• English Level 1
• etc etc etc

Then the user follows with "O" and it becomes:

• Home and Office Level 1
• Home and Office Level 2
• etc etc

I tried On Key Press and that does not work using a requery macro, After Update works however.

The list box is bound to a query which has the text box in its criteria: Like "*" & [textbox] & "*"

Any help appreciated, regards.

 

[boblarson]

Actually, the On Change DOES fire with every keystroke. The key is knowing what to look for. You need the TEXT property and not the VALUE property in that case.

 

[Core]

Actually, the On Change DOES fire with every keystroke. The key is knowing what to look for. You need the TEXT property and not the VALUE property in that case.

Thank you!!!!

 

[alexriverajr]

I'm trying to do the same but where would I be able to change these features. So far I have the OnChange but it only allow me to type in one letter and no more.

 

[boblarson]

I'm trying to do the same but where would I be able to change these features. So far I have the OnChange but it only allow me to type in one letter and no more.

What code do you have in the event?

 

[alexriverajr]

This what I have in the OnChange event pro:

Private Sub SearchName_Change()
Me.Refresh
End Sub

And this what I have in the query which the text box filters:

Like "*" & [Forms]![frmMainFormSearchGuest]![SearchName] & "*"

I'm just trying to do a filter as you type textbox. Thanks in advance.

 

[Core]

This what I have in the OnChange event pro:

Private Sub SearchName_Change()
Me.Refresh
End Sub

And this what I have in the query which the text box filters:

Like "*" & [Forms]![frmMainFormSearchGuest]![SearchName] & "*"

I'm just trying to do a filter as you type textbox. Thanks in advance.

It should be xxxx.requery

 

[boblarson]

You need to do it another way. The text box does not change its value in the On Change event. It changes its TEXT so you would need to take out the criteria from the query and then change your On Change to something like this:

Me.Filter = ""
Me.FilterOn = False
Me.Filter = "[SearchName]=" & Chr(34) & Me.SearchName.TEXT & Chr(34)
Me.FilterOn = True

But I would start after they type at least 3 letters (saves things backing up on you and slowing down):

If Len(Me.SearchName & "") >=3 Then
   Me.Filter = ""
   Me.FilterOn = False
   Me.Filter = "[SearchName]=" & Chr(34) & Me.SearchName.TEXT & Chr(34)
   Me.FilterOn = True
End If

 

[alexriverajr]

I used the re-query command and it updates only when i'm out of the field. I would like to able to remain in the field. For example, with re-query I have to click on the tab key and then it updates vice when I was using refresh it would filter the items containing the type letter but it wouldn't let me type in more than on letter. How can I fix that.

Thanks.

 

[boblarson]

You need to do it another way. The text box does not change its value in the On Change event. It changes its TEXT so you would need to take out the criteria from the query and then change your On Change to something like this:

Me.Filter = ""
Me.FilterOn = False
Me.Filter = "[SearchName]=" & Chr(34) & Me.SearchName.TEXT & Chr(34)
Me.FilterOn = True

But I would start after they type at least 3 letters (saves things backing up on you and slowing down):

If Len(Me.SearchName & "") >=3 Then
   Me.Filter = ""
   Me.FilterOn = False
   Me.Filter = "[SearchName]=" & Chr(34) & Me.SearchName.TEXT & Chr(34)
   Me.FilterOn = True
End If

Sorry, change this
"[SearchName]=" & Chr(34) & Me.SearchName.TEXT & Chr(34)

to this

"[SearchName] Like *" & Chr(34) & Me.SearchName.TEXT & Chr(34) & "*"

 

[alexriverajr]

Whats the end if as the bottom. Do I need to put in some type of parameter or argument?

Thanks

 

[boblarson]

Whats the end if as the bottom. Do I need to put in some type of parameter or argument?

Thanks

The End If at the bottom is the closing for the IF.

If Len(Me.SearchName & "") >=3 Then
   Me.Filter = ""
   Me.FilterOn = False
   Me.Filter = "[SearchName]=*" & Chr(34) & Me.SearchName.TEXT & Chr(34) & "*"
   Me.FilterOn = True
End If

 

[alexriverajr]

This is what I have now and I removed the filter from the query but nothing works now:

Private Sub SearchName_Change()
If Len(Me.SearchName & "") >= 3 Then
   Me.Filter = ""
   Me.FilterOn = False
   Me.Filter = "[SearchName] Like *" & Chr(34) & Me.SearchName.Text & Chr(34) & "*"
   Me.FilterOn = True
   End If

Not sure what I'm screwing up.

 

[boblarson]

1. Is the field in the form, actually named SearchName or is it something else?

2. Any chance of posting it here?

 

[alexriverajr]

The field is indeed called SearchName, below is the DB. The form in question is frmMainFormSearchGuest. In included is the file, warning this is work in progress as i'm just learning. Thanks

 

[boblarson]

I'll try to take a look when I get home. I have Access 2007 there but not here at work.

 

[alexriverajr]

Thanks. I appreciate you help!

 

[boblarson]

Okay, first of all you have a subform, with a main form (important detail).

Second, your field in the subform is not SearchName but GuestName (important detail)

See the attached, revised version.

 

[alexriverajr]

Thanks for the help. I saw what you did and that helped me learn.