How do I make a search form in Access 365

Question

I am currently trying to make a search form for my database. I want to place it in a navigation form (so I heard I can't use a split form). I currently have a form with 4 text boxes and one combo box for a total of 5 criteria to search with a separate button for each. Underneath, is a query based on my table. This is what I have done so far

I'm hitting a wall on how to actually perform the search though. I have never used access before this project and have no idea how to use VBA (but I do know other languages). Is it possible to perform the search using macros? If not, how would I make a search sub-procedure?

Answer 1

The way you approach this is to build the search form and develop and test the search form. Get it working? You can then drop this working form into an existing “navigation form set”.

There are a gazillion ways to do this – you are quite much only limited by your imagining. However, a common setup is to create a “main” form. This main form can have a few text boxes for you to type in your search values. And then you use the wizards to create a “results” form (a nice form in which you display the results for the user to see + pick the results).

In fact, you really don’t have to use a form + sub form, but I find this often works somewhat better then placing the “criteria” boxes in the forms heading area.

So create main form – this form is unbound (not attached to any table in the system). Have fun, lay out the form anyway you want.

Then use the wizards to create a multiple items form. Get that form laid out with nice columns. Now with main form in design mode, simply drag + drop in the “display results” form.

The result is an access form that looks like this:

In above, note how the user “typed” in some criteria.

So you place some code in the text box “after” update event. It can look like this:

dim strSql as string

strSql = "select * from tblCustomer where LastName like " & me.txtLastName & "*"

me.MySubFormname.Form.RecordSource = strSql

So we “build” the sql on the fly, and “stuff” the sql right into the sub form to display the results.

Note also in the above contines items form, we have a “view” or “edit” button when clicked can launch a detail form to the ONE record you select. In my example I used a “glasses” icon for the button. The code behind that buttion is thus:

Docmd.OpenForm "frmEditDetails",,,"id = " & me!id

edit: the sql string has to be correct, so in above, it should be:

dim strSql          as string 

 strSql = "select * from tblCustomer where LastName like '" & me.txtLastName & "*'" 
 debug.print strSql
 me.MySubFormname.Form.RecordSource = strSql

So VERY little code is required. The rest is simply whatever your fancy is in terms of how you lay out the UI.