By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 896 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

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

P: 26
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub TxtProxyUser_Change()
  2.  
  3. OnlyNumbers
  4.  
  5. If IsNull(Me.TxtProxyUser) Then
  6.     Me.TxtProxyName = Null
  7. Else
  8. End If
  9.  
  10. If Len(Me.TxtProxyUser.Text) > 0 Then
  11. Me.TxtProxyName.Value = Nz(DLookup("Consultant", "TblStaffList", "[Personnel Number]=" & Me.TxtProxyUser), "Not Found")
  12. Else
  13. End If
  14.  
  15. End Sub
Jun 26 '12 #1

✓ answered by 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:


Expand|Select|Wrap|Line Numbers
  1. Private Sub TxtProxyUser_Change()
  2.   Debug.print ".text: " & me.TxtProxyUser.Text
  3.   Debug.print ".Value: " & me.TxtProxyUser.Text
  4. End Sub
would yield in your immediate pane:
Expand|Select|Wrap|Line Numbers
  1. .text: t
  2. .Value:
If you now press h, the result would be:
Expand|Select|Wrap|Line Numbers
  1. .text: th
  2. .Value:
Now in your code:
Expand|Select|Wrap|Line Numbers
  1. If Len(Me.TxtProxyUser.Text) > 0 Then
  2. Me.TxtProxyName.Value = Nz(DLookup("Consultant", "TblStaffList", "[Personnel Number]=" & Me.TxtProxyUser), "Not Found")
  3. Else
  4. 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:
Expand|Select|Wrap|Line Numbers
  1. If Len(Me.TxtProxyUser.Text) > 0 Then
  2. Me.TxtProxyName.Value = Nz(DLookup("Consultant", "TblStaffList", "[Personnel Number]=" & Me.TxtProxyUser.Text), "Not Found")
  3. Else
  4. End If

Share this Question
Share on Google+
12 Replies


Rabbit
Expert Mod 10K+
P: 12,315
You said you get the above error but you never posted any error code.
Jun 26 '12 #2

P: 26
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.
Jun 26 '12 #3

Rabbit
Expert Mod 10K+
P: 12,315
What does it show as the value in the debugger?
Jun 26 '12 #4

P: 26
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.
Jun 27 '12 #5

P: 26
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?
Jun 27 '12 #6

100+
P: 759
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]
Jun 27 '12 #7

P: 26
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.
Jun 27 '12 #8

100+
P: 759
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):

Expand|Select|Wrap|Line Numbers
  1. Private Sub Control_Change
  2.     Update record set
  3.     ......... other code
  4. End Sub
Jun 27 '12 #9

P: 26
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.
Jun 28 '12 #10

100+
P: 759
Take a look to post #8 in this thread.
Maybe it is what you are looking for.
Jun 29 '12 #11

TheSmileyCoder
Expert Mod 100+
P: 2,321
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:


Expand|Select|Wrap|Line Numbers
  1. Private Sub TxtProxyUser_Change()
  2.   Debug.print ".text: " & me.TxtProxyUser.Text
  3.   Debug.print ".Value: " & me.TxtProxyUser.Text
  4. End Sub
would yield in your immediate pane:
Expand|Select|Wrap|Line Numbers
  1. .text: t
  2. .Value:
If you now press h, the result would be:
Expand|Select|Wrap|Line Numbers
  1. .text: th
  2. .Value:
Now in your code:
Expand|Select|Wrap|Line Numbers
  1. If Len(Me.TxtProxyUser.Text) > 0 Then
  2. Me.TxtProxyName.Value = Nz(DLookup("Consultant", "TblStaffList", "[Personnel Number]=" & Me.TxtProxyUser), "Not Found")
  3. Else
  4. 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:
Expand|Select|Wrap|Line Numbers
  1. If Len(Me.TxtProxyUser.Text) > 0 Then
  2. Me.TxtProxyName.Value = Nz(DLookup("Consultant", "TblStaffList", "[Personnel Number]=" & Me.TxtProxyUser.Text), "Not Found")
  3. Else
  4. End If
Jun 29 '12 #12

P: 26
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.
Jul 4 '12 #13

Post your reply

Sign in to post your reply or Sign up for a free account.