How do I create a search form in MS Access

[Girl Scout]

I have been trying to create a form similar to the one on the template
"Issues" from this site. I obviously needed different controls, but the
general concept should be the same. At least I thought so.

The code below is what I came up with:

Private Sub btnSearch_Click()
Dim strWhere As String

strWhere = "1=1"

' If tbxAESearch
If Not IsNull(Me.tbxAESearch) Then
'Create Predicate
strWhere = strWhere & " AND " & "frmJobs.[txtAcctExec] = " &
Me.tbxAESearch & ""
End If

' If tbxClientSearch
If Not IsNull(Me.tbxClientSearch) Then
'Create Predicate
strWhere = strWhere & " AND " & "frmJobs.[txtClientName] = " &
Me.tbxClientSearch & ""
End If

' If tbxDeptSearch
If Not IsNull(Me.tbxDeptSearch) Then
'Create Predicate
strWhere = strWhere & " AND " & "frmJobs.[txtDepartment] = " &
Me.tbxDeptSearch & ""
End If

'If tbxJobSearch
If Nz(Me.tbxJobSearch) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "frmJobs.txtJobName Like '*" &
Me.tbxJobSearch & "*'"
End If

'DoCmd.OpenForm "frmJobs", acFormDS, , strWhere, acFormEdit,
acWindowNormal
If Not Me.Form.Visible Then
Me.Form.Visible = True

Something is wrong, I haven't been able to figure out how to get it to open
the form "frmJobs" using the information filled out on the "frmSearchJobs".
I planned on having the code above connected to a button, "btnSearch".

I know very little VBA, I pretty much have played with code and am having a
problem with this concept.

Please help me. I am trying to do this for work and am running out of time.
If more information is needed please let me know.
Thanks

 

[Allen Browne]

The quotes and field names are the problem.

Assuming a field named "ClientName" that is a Text type field, you need:
strWhere = strWhere & " AND ([ClientName] = """ & Me.tbxClientSearch &
""")"

If it was a Number type field, lose the extra quotes, i.e.:
strWhere = strWhere & " AND ([ClientName] = " & Me.tbxClientSearch & ")"

Just before your OpenForm line, add:
Debug.Print strWhere
When it doesn't work, you can look in the Immediate Window (Ctrl+G) to see
what result you got. To compare that with what it should be, you can mock up
a query using any old values as criteria, and then switch it to SQL View
(View menu, in query design). The result you need is like the WHERE clause
in the query (though Access tends to add some extra brackets that you can do
without.)

 

[Pat Hartman\(MVP\)]

You can do this without any code at all if you can use Filter By Form - open
the form in form view, then
Records/Filter/Filter By Form