Search a form using another form

[marcuscoker]

Hi
Was wondering if anybody was able to help me with this. I would like to be able to filter records on a form using another form, where the user is able to type in values in order to select the records to display on the other form.

I have a number of fields

Surname
date
Clinic
post code

If the user leaves two of the above fields blank but fills in the other two then I would like the form to display all records for the blanks.

Thanks

Marcus

 

[seth_belgium]

Create two forms, frmSearch and frmResults.

In frmSearch, place textboxes for the search-fields.

Create a query that filters your results based upon the values the user inputs in the frmSearch form. In the querycriteria below each field, place similar codes:

Like [Forms]![frmSearch]![txtPostCode]

'Like' allows users to use '*'-signs for abbreviation.
'[Forms]!...' is the reference to the searchfield on your frmSearch form.

Set this query as the recordsource for your frmResults form.

On your frmSearch form, place a button that opens the frmResults form.

That should almost do it, though you might want to include some lines of code that fill up empty search-fields with the '*'-sign. Place these in the OnClick-event of your button on the frmSearch form.

These lines should be something like this:

If IsNull(Me.txtPostCode.Value) then
Me.txtPostCode.Value = "*"
End If

Do that for each search-field and place these lines above the DoCmd.Openform line, otherwise they won't influence the results.

Good luck!

Greetz,

Seth

 

[MStef]

Look at this address

 

[marcuscoker]

Thank you

Many thanks for that, has done the trick

Marcus