[barrbe]

This is a continuation of a couple threads from AllExperts because the followups were limited there...
pt1: http://www.allexperts.com/user.cgi?m=6&catID=1440&expID=47239&qID=4846172
pt2: http://www.allexperts.com/user.cgi?m=6&catID=1440&expID=47239&qID=4846439

My original question was: I have a form that basically filters the table based on the information input into unbound text boxes. I would like to refine this filtered table by using an option group that filters based on the status of the standard.
I've now got a code doesn't give me an error message but isn't filtering the results either. I've been told that I need to create a look up table that has a description for each status, but a number that is actually stored. I'm not sure how to set up this look up table and what needs to be where in it.

The code for my form is:

Private Sub cmdFilter_Click()
    'Purpose: Allow one to search db by either name, lot number, or gc/ms number. This also allows one to search by partials of any of these fields.
    Dim strWhere As String
    
    If Not IsNull(Me.txtFilterStandardName) Then
        strWhere = strWhere & "([StandardName] Like ""*" & Me.txtFilterStandardName & "*"")"
    End If
    
    If Not IsNull(Me.txtFilterLotNumber) Then
        strWhere = strWhere & "([LotNumber] Like ""*" & Me.txtFilterLotNumber & "*"")"
    End If
    
    If Not IsNull(Me.txtFilterGCMSNumber) Then
        strWhere = strWhere & "([GCMSNumber] Like ""*" & Me.txtFilterGCMSNumber & "*"")"
    End If
    
    
    'Apply Filter
    Me.Filter = strWhere
    Me.FilterOn = True
End Sub


Private Sub cmdReset_Click()
    'Purpose:   Clear all the search boxes in the Form Header, and show all records again.
    Dim ctl As Control
    
    'Clear all the controls in the Form Header section.
    For Each ctl In Me.Section(acHeader).Controls
        Select Case ctl.ControlType
        Case acTextBox
            ctl.Value = Null
        Case acOptionGroup
            ctl.Value = Null
        End Select
    Next
    
    'Remove the form's filter.
    Me.FilterOn = False
    Me.Frame70 = False
End Sub
Private Sub Frame70_AfterUpdate()
        
    'Purpose: to be able to refine the search results based on the status of the standard.
    DoCmd.ApplyFilter "[Status] = " & Me.Frame70 & " AND [StandardName] = '*" & Me.txtFilterStandardName & "*'"
 
End Sub

Any help is greatly appreciated.

[ScottGem]

Ok, lets start with what level of statuses you have. What is now stored in the Status field in the table you are filtering? Also what data type is the Status field?

[barrbe]

The status field currently has "Active" , "Inactive" , and "Archived" as the options based on if the chemical is opened, not opened or old.

[ScottGem]

Ok, but what is stored in the field? What datatype is it?

[barrbe]

It is a text field with a value box

[ScottGem]

OK, that's why it hasn't been working. You had said that the value returned by the option group corresponded to what is stored, but its not.

So you have three choices. You can convert the value stored in the option group to text, you can use a combobox instead of an option group. Or you can can store a numerical code rather than the text. If you use the 1st or 3rd choices then you should create a lookup table for status. Like so:

tluStatus
StatusD (PK Autonumber)
Status

So, in the 1st option you would convert the number selected in the option group to the text. Then use the text in your Where clause. In the third option you store number and then use joins to pull the text. In the 2nd option, you use a combo to pick the text.