Filter form using text box

Question

I have a form that has 7 text boxes, the first from the top is EmpID then below it are text boxes Order_No, Release_No, Sequence_No and Lot_Size which are not enabled for user input but visible. Finally at the bottom there is ReasonCode and QtyCompleted which are enabled for user input. The user enters an EmpID in the text box and the form afterupdate event should then show only the record that is associated with this id.  The form is based on a query that is filtered on the EmpID and at any given time, there is only one record per EmpID.. In the query I have the EmpID criteria set as [Forms]![frmProdStopEntry]![EmpID]

The code I have for the afterupdate event on the EmpID is:

Private Sub EmpID_AfterUpdate()

Dim strFilter As String

strFilter = "EmpID = '" & Me.EmpID & "'"

Me.Filter = strFilter

Me.FilterOn = True

End Sub

On the form load the code is:

Private Sub Form_Load()

On Error GoTo Form_Load_Err

    Me.Filter = "EmpID = ''"

Form_Load_Exit:

    Exit Sub

Form_Load_Err:

    MsgBox Error$

    Resume Form_Load_Exit

End Sub

My problem is that after the user enters the EmpID in the field, the form flashes then refreshes but nothing is displayed in the text boxes for the order_no etc. When I run the query, it displays the record correctly, the form is just not displaying it properly. What is wrong with my code?

Any help would be greatly appreciated!

Answer 1

If the field is a Text field, I think your code should work, so let's look for some other reason.

It doesn't matter where the text box is located, but for searching and filtering, the unbound filter/search text box is commonly placed in the header section and the bound data text boxes in the detail section.

After rereading your original post, I just noticed that you said the form's record source query is already filtered to the EmpID field (presumably based on a value from some other form??).  Now I'm wondering why a filtered form needs to have the same EmpID entered in an unbound text box and filtered again to the same EmpID.  It seems to me that the form's query should have no criteria on the EmpID.

I'm probably missing something in all this, so please try to clarify things for me.

Answer 2

Its actually a text field, 99% of the time the value will be a three or four digit number. The user reads that with a barcode scanner so there is really very little possibility of user entered typing mistakes.

The filter lookup is Database Default and the Format property is blank.

I tried the Me.Requery but that doesn't help, like said it already does that with the Filter and FilterOn.

Does it matter that the EmpID is located in the detail section of the form? Should it be in the header?

Answer 3

If the EmpID field in ite table is a Number type field, then lose the quotes:

   strFilter = "EmpID = " & Me.EmpID

If it is a Text field, then double check the value in the table field to make sure it is what you think it is If it's a Lookup field or has something in its Format property, what you see may not be what's really in the field..

Answer 4

Amy, forms and reports are automatically requeried whenever the Filter or FilterOn value is changed.

Answer 5

Have you tried adding Me.Requery to your AfterUpdate procedure?  I think that should work.