Sub works when on "lost focus" but not on "change"

[scrapcode]

Hi everyone, I have a sub that works fine when used with the lost focus event, but when I try and use the same sub with the change event I get the above error.

I've searched the net and found lots of information but none of it that I can use.

I'm sure I'm missing something basic so apologies if it's a schoolboy error!

This all applies to an Acess 2003 form. I'm looking to create an instant search that checks a number entered into a text box against a table to return a name. It works fine when the lost focus event is used but this means the user has to click off to see the outcome of the search. As the Dlookup I'm using is instant it won't slow down the form to run the sub every time there is a key press on this text box.

I'm missing something basic though because just changing the event to change is throwing out syntax errors when previously there weren't any.

Here's the code as it is, thanks for any help:

Private Sub TxtProxyUser_Change()
 
OnlyNumbers
 
If IsNull(Me.TxtProxyUser) Then
    Me.TxtProxyName = Null
Else
End If
 
If Len(Me.TxtProxyUser.Text) > 0 Then
Me.TxtProxyName.Value = Nz(DLookup("Consultant", "TblStaffList", "[Personnel Number]=" & Me.TxtProxyUser), "Not Found")
Else
End If
 
End Sub

[Rabbit]

You said you get the above error but you never posted any error code.

[scrapcode]

Sorry, my browser crashed and I was pasting this back in. I must have missed the error part.

Its runtime 3075. It says there is a syntax error in the query expression '[Personnel Number]='.

It highlights the Dlookup when it debugs.

[Rabbit]

What does it show as the value in the debugger?

[scrapcode]

Sorry Rabbit, I'm not following. What do you mean the value? With the above sub headed with an "change" event, when a value is entered into the text box it debugs with the error message I posted then the code window highlights line 11.

I don't know if it'll help but the "only numbers" part of the code calls a sub that ensures it's only a numerical value that can be entered into the text box.

When I'm testing it even if I put a letter into the textbox it debugs in the same way. So it's like the sub crashes before it even calls out, but crashes on a later line of code.

I always compile my forms after any changes to check for errors but that doesn't find this, its only when the form is running it happens.

[scrapcode]

Actually, after running the code in break mode the only number sub seems to have stopped working entirely. It's just skipping over values that aren't numbers so that's a different issue I need to work through. As it's a different sub then I don't see the issues being connected, unless because the event is "change" it affects the values for items in the textbox?

[Mihail]

Just an idea:
Before the control lost focus, the update event is raised.
On the other hand, when the _Change event is fired, the update event is not (yet) fired.
It is why (I think) Access ask you for [Personnel Number]

[scrapcode]

Yes, that makes sense. It seems to fit with what I'm seeing. This just comes from my lack of knowledge in some areas (being self taught).

So if this is the way Acess treats things in the change event, how would you ever get any sub to work in that event? It would always run before it's had a chance for the change in question to take effect.

[Mihail]

From my experience: do not use the Change event with bound controls.
Use the After Update event.

Or, if you still wish to use the Change event, force the update event.
Try something like this (but I have doubt about that):

Private Sub Control_Change
    Update record set
    ......... other code
End Sub

[scrapcode]

cheers Mihail, I'll have a try at that. Using after update crossed my mind briefly, but again I've never used it so was wary. Will that act in the 'instant search' way I'm looking for? That's the main outcome I want. Every key press results in another search being done.

[Mihail]

Take a look to post #8 in this thread.
Maybe it is what you are looking for.

[TheSmileyCoder]

The reason your code fails when you move it to another event is because:
Not all properties are equally available within the 2 events.

In BeforeUpdate or AfterUpdate you can use ControlName.Value just fine. You can also use ControlName.Value to reference any other control besides teh currently active one. The .Value is also the default property of most controls, and therefore you can often omit the .Value, and the code will still run fine.

Now .Text on the otherhand will sometimes LOOK similar to .Value but they are NOT the same (well duh)

For example you can only call the .Text on a control that has focus. The value stored in .Text is the UNSAVED, currently IN EDIT version of the text.

In your case, your control is empty when you start, so both .Value and .Text will be empty strings.

Now as you start typing your first alphanumeric charecter (For example "T")into the textbox the change event fires. If you want to try it out, use the following code to better understand what happens:

Private Sub TxtProxyUser_Change()
  Debug.print ".text: " & me.TxtProxyUser.Text
  Debug.print ".Value: " & me.TxtProxyUser.Text
End Sub

would yield in your immediate pane:

.text: t
.Value:

If you now press h, the result would be:

.text: th
.Value:

Now in your code:

If Len(Me.TxtProxyUser.Text) > 0 Then
Me.TxtProxyName.Value = Nz(DLookup("Consultant", "TblStaffList", "[Personnel Number]=" & Me.TxtProxyUser), "Not Found")
Else
End If

you are using Me.TxtProxyUser (Which as previously stated will force access to use the default property .Value, which as shown above will be an empty string, when used from the CHANGE event, (while it will be NON-Empty if used from the beforeUpdate or AfterUpdate)

If you want to use it in the change event (Which, if you want the search to update EACH time you press a key, as opposed to when you are done entering, and press enter, is the correct event to use) then you simply need to change your code to use the .Text as shown below:

If Len(Me.TxtProxyUser.Text) > 0 Then
Me.TxtProxyName.Value = Nz(DLookup("Consultant", "TblStaffList", "[Personnel Number]=" & Me.TxtProxyUser.Text), "Not Found")
Else
End If

[scrapcode]

Thank you so, so much Smiley. I'm sorry it's taken me so long to reply but this is the first of me being back at it from days off.

It worked exactly as I needed it to. Thank you also for not just fixing the problem, but explaining it in a way that means I understand it for future projects.

You rock dude.