find data using field in form

[Cirrostratus]

I have a form that is bound to a query. The user inputs their id in a text box and the form should find the corresponding record with 5 different pieces of data displayed in separate text boxes. There is at any given time only one record set per id. The query has in the EmpID the criteria: [Forms]![frmProdStopEntryNew]![EmpID] to reference back the EmpID as the filter.

On the form, after the employee enters their ID, the afterupdate should pull-up the record that corresponds to that id. The code for the afterupate is:

Private Sub Emp_ID_AfterUpdate()
Me.Filter = "EmpID = '" & Me.Emp_ID & "'"
 DoCmd.RunCommand acCmdApplyFilterSort
Me.StopTime = Now()
ReasonID.SetFocus
End Sub

I also have a form load to go to a new record just so that the form is blank when the user enters it.

Private Sub Form_Load()
On Error GoTo Form_Load_Err
 
    DoCmd.SetWarnings False
    DoCmd.GoToRecord , "", acNewRec
Form_Load_Exit:
    Exit Sub
Form_Load_Err:
    MsgBox Error$
    Resume Form_Load_Exit
End Sub

The problem I'm experience is that the query to find the record has a 50/50 hit and miss success rate. I think this is because the form load gets activated each time the query is refreshed afterupdate. And the form load has the new record syntax so it sort of nullifies it each time. How do I fix that?

Any kind of help would be great!

Cheers!

 

[Cronk]

Is EmpID a text field? The quotes should not be used for a numeric field.
ie
Private Sub Emp_ID_AfterUpdate() Me.Filter = "EmpID = " & Me.Emp_ID me.FilterOn= true
End SubYou don't need to go to a new record on opening the form. You could put a non existent filter record in the Form Load event.

 

[Cirrostratus]

Yes, EmpID is a text field (don't ask why)..

I'm not sure what you mean by a non existent filter record? Can you help on that a little more?

 

[Cronk]

Use Me.FilterOn = True instead of
DoCmd.RunCommand acCmdApplyFilterSort

Also, in the Form Load, instead of going to a new record to display a blank form, add your filtering code to go to a non existent record
Me.Filter="EmpID = ''"

 

[Cirrostratus]

For some reason, that still doesn't work. I've now got:

Private Sub Emp_ID_AfterUpdate()
Me.Filter = "EmpID = '" & Me.Emp_ID & "'"
Me.FilterOn = True
DoCmd.Requery
End Sub

When the user enters the EmpID, it just basically reloads the form but nothing populates in the fields. Its really weird.. :banghead:

The form load is now like you suggested.

?!?

 

[Cronk]

What happens if you take out the line
DoCmd.Requery

Um, why did you add it? It wasn't in your original post.

 

[Cirrostratus]

I've been trying different things so that was one of them. It makes no difference if its there or not. After you enter the EmpID in the form, the form "flashes" and then it clears out the EmpID and you get a blank form. The query itself works great, just for some reason the form is not populating what's in the query. I'm up for trying anything right now..

Thanks again for all the help so far!

 

[Cronk]

Time to post your database.

 

[Cirrostratus]

Ok, I managed to solve the issue.

The problem was that my query had the reference to the Form in the criteria for EmpID field. Apparently the code referencing to the query and the query referencing to the form caused some strange circular effect where it sort of nullified itself. When I took that out, the issue went away and it started working.

So my form load looks like this:

Private Sub Form_Load()
On Error GoTo Form_Load_Err
 
    Me.Filter = "EmpID = ''"
    Emp_ID.SetFocus
 
Form_Load_Exit:
    Exit Sub
Form_Load_Err:
    MsgBox Error$
    Resume Form_Load_Exit
End Sub

And my afterupdate on the EmpID fields looks like this:

Private Sub Emp_ID_AfterUpdate()
Dim strFilter As String
strFilter = "EmpID = '" & Me.Emp_ID & "'"
Me.FilterOn = True
Me.Filter = strFilter
Me.StopTime = Now()
ReasonID.SetFocus
End Sub

And it works.. thanks Cronk for all the input, I appreciate it!