Query By Form Option Group

[bryn30]

I am trying to set up a search form allowing the users to search a table using 4 different criteria.
when the form is opened I get an parameters and an error,
it asks for the user to give it parameters for
[Forms]![Search]![NPA]
[Forms]![Search]![NXX]
[Forms]![Search]![LineRange]
[Forms]![Search]![Customer]
[Forms]![Search]![OrderNumber]
[Forms]![Search]![Sequence]
then Error
"The Expression on open you entered as the event property setting produced the following error: Member already exist in an object module from which this object module derives"
Then the form closes.
Here is my code;
(please be forgiving as I am new at this)

Private Sub Form_Current()
On Error GoTo Err_Form_Current

Me.List47.Visible = False
'Me.NPA = Null
'Me.NXX = Null
'Me.LineRange = Null
'Me.Customer = Null
'Me.OrderNumber = Null
'Me.Sequence = Null


Exit_Form_Current:
Exit Sub

Err_Form_Current:
MsgBox Err.Description
Resume Exit_Form_Current
End Sub
Private Sub Form_Open(Cancel As Integer)

Me.NPA.SetFocus
'Me.NPA = 972
'Me.NXX = 578
'Me.LineRange = 4357
SearchButton_Click
End Sub
Private Sub Image54_Click()

Me.NPA.SetFocus
Me.List47.Visible = False
Me.NPA = Null
Me.NXX = Null
Me.LineRange = Null
Me.Customer = Null
Me.OrderNumber = Null
Me.Sequence = Null
End Sub
Private Sub Operator_Click()
DoCmd.Close acForm, "search", acSaveNo

End Sub

Private Sub SearchButton_Click()
' Create an SQL statement using search criteria entered by user and
' set RecordSource property of ShowSalesSubform.

Dim strSQL As String, strRestrict As String
Dim lngX As Long

lngX = Forms!Search!SearchBy.Value

strRestrict = SearchBy(lngX)

' Create SELECT statement.
strSQL = "SELECT DISTINCTROW TMSOWNER_E911_DATA.NPA & " - " & TMSOWNER_E911_DATA.NXX & " - " & TMSOWNER_E911_DATA.LINE_RANGE AS TN, "
strSQL = strSQL & "TMSOWNER_E911_DATA.CUSTOMER_NAME AS Customer , TMSOWNER_E911_DATA.ORDER_NUMBER AS [Order#], TMSOWNER_E911_DATA.SEQUENCE_NUM AS [Sequence#], TMSOWNER_E911_DATA.DATA_FILE_NAME AS File, TMSOWNER_E911_DATA.STATUS_CODE AS Status, TMSOWNER_E911_DATA.FUNCTION_CODE AS FunCode, TMSOWNER_E911_DATA.RECORD_LOAD_DATE AS LoadDate "
strSQL = strSQL & "FROM TMSOWNER_E911_DATA"
strSQL = strSQL & "WHERE strRestrict"
strSQL = strSQL & "ORDER BY TMSOWNER_E911_DATA.RECORD_LOAD_DATE DESC;"

' Set RecordSource property of ShowSalesSubform.
Me!SearchSubform.Form.RecordSource = strSQL

' If no records match criteria, reset subform's RecordSource property,
' display message, and move focus to BeginningDate text box.
If Me!SearchSubform.Form.RecordsetClone.RecordCount = 0 Then
Me!SearchSubform.Form.RecordSource = "SELECT TMSOWNER_E911_DATA.CUSTOMER_NAME AS Customer FROM TMSOWNER_E911_DATA WHERE False;"

MsgBox "No records match the criteria you entered.", 48, "No Records Found"
Me!NPA.SetFocus
End If



'ListBox

Exit_SearchButton_Click:
Exit Sub

Err_SearchButton_Click:
MsgBox Err.Description
Resume Exit_SearchButton_Click
End Sub

Private Sub ListBox()
On Error GoTo Err_ListBox

Me.List47.Visible = True
Me.List47.Requery

Exit_ListBox:
Exit Sub

Err_ListBox:
MsgBox Err.Description
Resume Exit_ListBox
End Sub
Private Function SearchBy(lngOptionGroupValue As Long) As String

' Define constants for option group values.
Const TNOption = 1
Const CustomerOption = 2
Const OrderNumOption = 3
Const SeqOption = 4

' Create restriction based on value of option group.
Select Case lngOptionGroupValue
Case TNOption:
SearchBy = TMSOWNER_E911_DATA.NPA = [Forms]![Search]![NPA] And TMSOWNER_E911_DATA.NXX = [Forms]![Search]![NXX] And TMSOWNER_E911_DATA.LINE_RANGE = [Forms]![Search]![LineRange]
Case CustomerOption:
SearchBy = TMSOWNER_E911_DATA.CUSTOMER_NAME Like "*" & [Forms]![Search]![Customer] & "*"
Case OrderNumOption:
SearchBy = TMSOWNER_E911_DATA.ORDER_NUMBER = [Forms]![Search]![OrderNumber]
Case SeqOption:
SearchBy = TMSOWNER_E911_DATA.SEQUENCE_NUM = [Forms]![Search]![Sequence]
End Select
End Function

 

[jhall156]

Try putting a .Value after your Forms!Formname.OptionGroupName expression. 'Sworth a shot. JHall

 

[bryn30]

well I got this to work,
not by the option group like I had hoped but by the way of multiple query buttons. each "option" now has a unique button that changes the rowsource of the listbox to support the user parameter.
thanks for the help