filter on form

[slimjen]

All, I am having trouble with this code to filter the SSN in an unbound field on a form. I’ve done it two ways. See code.

Private Sub SSN_AfterUpdate()
' Find the record that matches the control.
'    Dim rs As Object
'    Set rs = Me.Recordset.Clone
'    rs.FindFirst "[SSN] = '" & Me![ssn] & "'"
'    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.Filter = "[SSN] = " & Chr(34) & Me.[SSN] & Chr(34)
Me.FilterOn = True
Me.SSN = ""
End Sub

One is comment out to try the other code. When I first enter a number; it finds the related record. But if I enter another number; it auto fills a number and just beeps without giving an error and it doesn’t find the record. It’s not until I hit a reset button to clear all fields on the form does it accepts another number to find a new record. I have an input mask for the SSN in the unbound field because the SSN in the table all have hyphens. I don’t know a way of not using it without the input mask as the users prefer in order to find the records with hyphens. I don’t understand why this happens when I use the same code for a last name search or customer number search and I can perform another search without resetting the form. See code.

Private Sub cboLname_AfterUpdate()
' Find the record that matches the control.
Me.Filter = "[LastName] = " & Chr(34) & Me.cboLname & Chr(34)
Me.FilterOn = True
Me.cboLname = ""

All the fields are text fields. The only thing different I can see is the input mask on the SSN. Please help.

[ItsMe]

Maybe something like this....

Me.FilterOn = false
me.filter = ""
Me.Filter = "[SSN] = '" & Me.SSN & "'"
Me.FilterOn = True

[slimjen]

Not working. Still gives the same result. thanks

[NTC]

I don't have a fix but this phrase you post seems like it may be key: ...."if I enter another number; it auto fills a number...."

auto fills what? .... is there some way of understanding what is being auto filled ?...

for sanity I would think you should temporarily remove the mask and see if you can make it work by manually entering the dashes just to get everything down to basics.....

[slimjen]

thanks for replying. It autofils with another ssn. I removed the input mask and it gives me the results I need but the users want an input mask because they don't want to be held up trying to input the hyphens Any suggestions??

[slimjen]

Ok. Right now; when the user does the first search in the combo box; it filters the results fine. But the cursor remains in the combobox. When the user is done; they just start typing a new search which fails. I discovered if I put the cursor in the box at the beginning; the input mask shows and it works. So the problem is afterupdate. I have the box reset to clear the current search but it doesn’t show the input mask. Should I have a “Setfocus” to another field to force the user to click the combo box each time they want to search?? I can’t think of anything else that will show the input mask after update for the next search. thanks

[ItsMe]

I am not sure I follow but..

you can try
Me.cmbCombo = ""
Me.setfocus
Me.cmbCombo.Setfocus

[slimjen]

no joy. I'll find a way around it. Will work on it tonight