Form Filter acts like it does nothing

  • Thread starter Thread starter Guest
  • Start date Start date
G

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-
 
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.
 
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?

Douglas J. Steele said:
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!)


Pryme8 said:
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-
Click to expand...
Click to expand...
 
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...

Pryme8 said:
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?

Douglas J. Steele said:
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!)


Pryme8 said:
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-
Click to expand...
Click to expand...
Click to expand...
 
Back
Top