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

  • Thread starter Thread starter matadordevacas
  • Start date Start date
M

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
 
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
 
Back
Top