Form Filter acts like it does nothing

[Guest]

I am working on a search field that pulls a form up depending on the search
type the user chooses, The form pulls up but the filter never takes effect,
Can someone please explain why this may be?

-START-
Private Sub searchbutton_Click()

If Me.picksearchtype = "Employee First Name" Then
DoCmd.OpenForm "Employee_Page", acNormal, , "[First Name] Like
Forms![Index]![S_Search] & '*'"
Form_Employee_Page.Pick_employee = Form_Employee_Page.S_ID

ElseIf Me.picksearchtype = "Employee Last Name" Then
DoCmd.OpenForm "Employee_Page", acNormal, , "[Last Name] Like
Forms![Index]![S_Search]& '*'"
Form_Employee_Page.Pick_employee = Form_Employee_Page.S_ID

ElseIf Me.picksearchtype = "Employee ID" Then
DoCmd.OpenForm "Employee_Page", acNormal, , "[ID] = Forms![Index]![S_Search]"
Form_Employee_Page.Pick_employee = Form_Employee_Page.S_ID

ElseIf Me.picksearchtype = "SOP Name" Then
MsgBox "Value 3 returned"
ElseIf Me.picksearchtype = "SOP ID" Then
MsgBox "Value 4 returned"
Else
MsgBox "Pick search type!"
End If
DoCmd.Close acForm, "Index", acSaveNo
End Sub
-END-

 

[Douglas J. Steele]

The reference to the control needs to be outside of the quotes:

DoCmd.OpenForm "Employee_Page", acNormal, , _
"[First Name] Like """ & Forms![Index]![S_Search] & "*"""

That's 3 double quotes after Like, and 3 double quotes after the asterisk.
While you might be able to get away with

DoCmd.OpenForm "Employee_Page", acNormal, , _
"[First Name] Like '" & Forms![Index]![S_Search] & "*'"

you'd run into problems with names that contain apostrophes.

 

[Guest]

That Did not work, still the same results... Prehaps I need If statment that
flags if the Employee Page was opened by the Index... and if so set a filter?

The reference to the control needs to be outside of the quotes:

DoCmd.OpenForm "Employee_Page", acNormal, , _
"[First Name] Like """ & Forms![Index]![S_Search] & "*"""

That's 3 double quotes after Like, and 3 double quotes after the asterisk.
While you might be able to get away with

DoCmd.OpenForm "Employee_Page", acNormal, , _
"[First Name] Like '" & Forms![Index]![S_Search] & "*'"

you'd run into problems with names that contain apostrophes.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am working on a search field that pulls a form up depending on the search
type the user chooses, The form pulls up but the filter never takes
effect,
Can someone please explain why this may be?

-START-
Private Sub searchbutton_Click()

If Me.picksearchtype = "Employee First Name" Then
DoCmd.OpenForm "Employee_Page", acNormal, , "[First Name] Like
Forms![Index]![S_Search] & '*'"
Form_Employee_Page.Pick_employee = Form_Employee_Page.S_ID

ElseIf Me.picksearchtype = "Employee Last Name" Then
DoCmd.OpenForm "Employee_Page", acNormal, , "[Last Name] Like
Forms![Index]![S_Search]& '*'"
Form_Employee_Page.Pick_employee = Form_Employee_Page.S_ID

ElseIf Me.picksearchtype = "Employee ID" Then
DoCmd.OpenForm "Employee_Page", acNormal, , "[ID] =
Forms![Index]![S_Search]"
Form_Employee_Page.Pick_employee = Form_Employee_Page.S_ID

ElseIf Me.picksearchtype = "SOP Name" Then
MsgBox "Value 3 returned"
ElseIf Me.picksearchtype = "SOP ID" Then
MsgBox "Value 4 returned"
Else
MsgBox "Pick search type!"
End If
DoCmd.Close acForm, "Index", acSaveNo
End Sub
-END-

 

[Guest]

I have now Figured out that It works the very first time I load up my program
and try the search box... but after that it acts as if the S_Search box is
blank...

That Did not work, still the same results... Prehaps I need If statment that
flags if the Employee Page was opened by the Index... and if so set a filter?

The reference to the control needs to be outside of the quotes:

DoCmd.OpenForm "Employee_Page", acNormal, , _
"[First Name] Like """ & Forms![Index]![S_Search] & "*"""

That's 3 double quotes after Like, and 3 double quotes after the asterisk.
While you might be able to get away with

DoCmd.OpenForm "Employee_Page", acNormal, , _
"[First Name] Like '" & Forms![Index]![S_Search] & "*'"

you'd run into problems with names that contain apostrophes.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am working on a search field that pulls a form up depending on the search
type the user chooses, The form pulls up but the filter never takes
effect,
Can someone please explain why this may be?

-START-
Private Sub searchbutton_Click()

If Me.picksearchtype = "Employee First Name" Then
DoCmd.OpenForm "Employee_Page", acNormal, , "[First Name] Like
Forms![Index]![S_Search] & '*'"
Form_Employee_Page.Pick_employee = Form_Employee_Page.S_ID

ElseIf Me.picksearchtype = "Employee Last Name" Then
DoCmd.OpenForm "Employee_Page", acNormal, , "[Last Name] Like
Forms![Index]![S_Search]& '*'"
Form_Employee_Page.Pick_employee = Form_Employee_Page.S_ID

ElseIf Me.picksearchtype = "Employee ID" Then
DoCmd.OpenForm "Employee_Page", acNormal, , "[ID] =
Forms![Index]![S_Search]"
Form_Employee_Page.Pick_employee = Form_Employee_Page.S_ID

ElseIf Me.picksearchtype = "SOP Name" Then
MsgBox "Value 3 returned"
ElseIf Me.picksearchtype = "SOP ID" Then
MsgBox "Value 4 returned"
Else
MsgBox "Pick search type!"
End If
DoCmd.Close acForm, "Index", acSaveNo
End Sub
-END-