Drawing a blank !

[Mike Hughes]

I have a table with three fields:
SSN
Member #
Case #
I want to create a form that I can search by SSN or Member # which will show all Case #'s known to the SSN or Member #.

A member can only have one SSN and one Member # but many Case #.

I've done this before, but can't remember how, can anyone help?

 

[crizzottzz]

Create a main form with a subform.

In the main form, use an unbound text box, and say a "search" button.

The subform should have a query as it's record source that queries from your table. The criteria for [SSN] would be Forms![MainForm]![unboundTextBoxName]

Your search button should requery the subform. i.e., the code for the button should be something like:

Forms!MainForm![Subform].Requery

This will refresh the data on the subform by re-running the query based on the SSN typed in the main form's text box.

If you want to be able to search by either the SSN or the member#, you need two unbound text boxes and your query criteria has be:

[SSN] is Null AND [Member#] = Forms!MainForm[UnboundMemberBox]
OR
[SSN] = Forms!MainForm[UnboundSSNBox] AND [Member#] is Null