Creating a Form to Search a Query and display the results in a subform with the click of a command b

[matadordevacas]

Saludos a todos. I've gone slightly bald trying to figure out how to
put together a form that will allow me to search a table / query.

Explanation:
I have a main form labled "tbl_SearchIPPLog." The form has a subform
labeled "sfrmSearch" which is based on the datasheet view of a quary
labeled "qry_searchIPPlog."

The subform has columns labeled "Update Date", "Issue Date", "IPP
Number", "Lot Number", "QAD Number", etc. etc. etc.

On the main form there are a large number of text boxes ("qUpdateDate",
"qIssueDate", "qIPPNumber", "qLotNumber", "qQADNumber", etc.) that
would correspond with the columns in the subform.

In a nutshell I want the user to be able to enter criteria in one or
many of the text boxes and click a command button ("cmdSearch") to have
the search criteria show up in the subform ("sfrmSearch").

I've been scanning the group for days and have found quite a few
solutions but as I'm just delving into Access I'm not quite sure how to
implement them. Any help or pointers would be greatly appreciated.

Gracias

 

[Albert D.Kallal]

Simply test for each text box conoctlr.

dim strWhere as string

strWhere = ""


if isnull(cboClient) = false then
strWhere = "[clientid] = " & me.cboClient
end if

if isnull(cboLocation) = false then
if strWhere <> "" then strWhere = strWhere & " and "
strWhere = strWhere & "[LocationId] = " & cboLocation
end if


if isnull(cboProject) = false then
if strWhere <> "" then strWhere = strWhere & " and "
strWhere = strWhere & "[ProjectId] = " & cboProject
end if

if isnull(txtCity) = false then
if strWhere <> "" then strWhere = strWhere & " and "
strWhere = strWhere & "[City] = '" & txtCity & "'"
end if

You can continue the above for as many contorls as you wish...

me.MySubForm.Form.RecordSource = "select * from qry_searchIPPlog where " &
strWhere