Best Way to set up a searchable form 1

[tylerwork]

I am an intermediate access developer. I have a lot of experience in the past, but nothing with searchable forms.

What is the easiest way to set up a searchable and editable form? I'd prefer not to use the "Button - Search Form" function wizard, if at all possible. Anyone with any ideas? Greatly appreciated...

 

[SBendBuckeye]

Can you give a little bit more information about what you are trying to do (eg finding customers in certain states with area code changes or sales below a threshold, etc)?

 

[tylerwork]

Sure - we have an inventory database, and there are 5 different fields that we want to search from - each field search would be a different form due to management specifications. The form will display items from 5 different tables.

 

[SBendBuckeye]

I'm still a little fuzzy about what you are trying to do but here is an example of one way you could do what you are asking. Perhaps this will get you started or raise some more questions.

Lets assume you have a customer file with customer name, address, city, state, phone number with a separate area code field and you want to allow a search by some combination of city, state and area code.

First create comboboxes for the city, state and area code fields and populate them with sql like the following:

cboCity - strSQL = "Select Distinct city From tblTable " _
& "Order By city"

cboState - strSQL = "Select Distinct state From tblTable " _
& "Order By state"

cboAreaCode - strSQL = "Select Distinct areacode " _
& "From tblTable Order By area code"

Note: In all of the above cases, you will need to use square brackets [] around any fields or table names which contain embedded spaces (eg [Area Code]).

Then you use the output from the above combo boxes to generate a WHERE clause on the fly something like this:

Dim strSQL As String
Dim strWhere As String

strWhere = " Where "

If cboCity <> 0 Then 'a row was selected
strWhere = strWhere & &quot;city = '&quot; & cboCity & &quot;' And&quot;

If cboState <> 0 Then 'a row was selected
strWhere = strWhere & &quot;state = '&quot; & cboCity _
& &quot;' And&quot;

If cboAreaCode <> 0 Then 'a row was selected
strWhere = strWhere & &quot;[area code] = '&quot; _
& cboAreaCide & &quot;' And&quot;

If cboAreaCode <> 0 Then 'a row was selected
strWhere = strWhere & &quot;areacode = '&quot; _
& cboAreaCode & &quot;' And&quot;

If Len(strWhere) = 7 Then 'Nothing selected - Use all
strWhere = vbNullString
or you might want to require at least one selection
Else
'Strip the last trailing &quot; And&quot;
strWhere = Left$(strWhere, Len(strWhere) - 4)
End If

strSQL = &quot;Select fld1, fld2, etc From tbl1, tbl2, etc&quot; _
& &quot; Where clause1, clause2, etc And &quot; & strWhere _
& &quot; Order By and other SQL statements as needed&quot;

DoCmd.RunSQL strSQL

What you are doing is building a where clause on the fly with the data selected by the user from the combo boxes on your form. A couple things to be aware of: text fields need to be delimited by single quotes ' (see code above for examples of this), date fields are delimited by pound signs # instead of single quotes ' and numeric fields don't require anything.

Hope this helps get you started! Have fun and post how it turns out for you.

 

[DanEaton]

I had the similar situation. The best way to do a search form is to first create a query with every that you want to search for and everything that you want to display. There are different ways that you can search. Probably the best way from a user perspective is to set-up combo boxes from which they can choose, but not require them to fill in them all.

So set up unbound combo boxes for every field from which you would like to search. Give them distinct names so that you can recognize them later. Lets say you call them cbo1, cbo2, cbo3. In the query put this in the SQL view after the last text...

WHERE (((Field1) = [Forms]![YourSearchForm]![cbo1] OR IsNull([Forms]![YourSearchForm]![cbo1])) AND ((Field2) = ....

And So on. If done right, when you go back to design view you will see criteria under the fields for which criteria is set. Now, base the form's record source on this query. You can place the combo boxes in the header, make the form continuous (under view property), and place the results in the detail...or, you can use a sub-form to display your results. You'll need a button on your form. For its OnClick property, put Me.Requery, this will requery your underlying query.

Hopefully this will give you a start, if you have any questions, I'd be glad to re-post, like I say, I was having the same problem so it's fresh in my head.

-Dan

Dan Eaton
deaton@caemachinery.com