VBA filter retaining old value

[nigelbloomy]

In Access 2010 I am trying to make a form where the user can enter a userID and the form will show the information for that one userID.
Right now I have a form with fields for the information. At the top I have a field to enter the UserID to search for.

The VBA for the UserID search box is:

Private Sub IDLookup_Exit(Cancel As Integer)
    Me.Filter = ""
    Me.FilterOn = False
    Me.Filter = "([ID] = Forms![Entry Form]![IDLookup])"
    Me.FilterOn = True
End Sub

It works great the first time I search for a userID. The problem is that when I type in a second userID, the filter doesn't clear. It stays with the first one I typed. I thought this was a simple code to clear the filter and set it to the new userID entered. Unless I close the form and open it again, the code will never look up a new userID. What am I doing wrong?

Thank you

[ketbdnetbp]

Nigelbloomy -

Have you tried putting your code into the AfterUpdate Event of the IDLookup control?

All the best,

Jim

[nigelbloomy]

I originally had it in the after update event and it results in the same problem. 1st entry gets looked up perfectly. Every entery after that flashes a little like it is filtering again, but it keeps filtering for the first userID.

[ketbdnetbp]

Nigelbloomy -

I might try...
Me.Filter = ""
Me.FilterOn = False
Me.Filter = "([ID] = Forms![Entry Form]![IDLookup])"
Me.FilterOn = True
Me.Requery

and see if that changes anything. One other question, is this a continuous form?

Jim

[nigelbloomy]

Adding the requery worked! I haven't used continuous forms before so I don't think my form is continuous. I don't know why Access needs the requery to clear out the old filter. I was also able to delete the first two lines of code and it still works great. It does have to be in the afterupdate event though. The code goes crazy in the exit event where I had it.

Me.Filter = "([ID] = Forms![Entry Form]![IDLookup])"
    Me.FilterOn = True
    Me.Requery

Thank you for the help.

[ssanfu]

When you have an expression like "([ID] = Forms![Entry Form]![IDLookup])", everything within the quotes becomes a text string. So, Access thinks you want "WHERE [ID] equals the string "Forms![Entry Form]![IDLookup]".
You need to concatenate the value of the control (with appropriate delimiters) to the property.

If [IDLookup] is numeric, then you would use:

Me.Filter = "[ID] = " & Forms![Entry Form]![IDLookup]

If [IDLookup] is text, then you would use:

Me.Filter = "[ID] = '" & Forms![Entry Form]![IDLookup] & "'"

Expanded, the text in RED at the end looks like " ' ".

[ketbdnetbp]

Steve - Thanks, I overlooked that part. Jim