Select Groups within Form

[Chas]

Creating a community organization data base using Access 2003. This consists
of a table with over 200 addresses and family names and various info about
each one. Working in a single Form to Add, Delete, or Edit I need to either
select by street or by name. For example if I want to update John Smith's
record I need to see all Smith to make sure I have the correct one. Or I
may not be certain of the correct spelling of the name or of the exact
address so I want to look thru all the addresses on Main St. until I find
the right one to update.
I am asking for ideas on different ways to tackle this problem. Without
exiting the Form, how can I choose to see either of these subsets of data
before updating, adding, or deleting?

 

[Allen Browne]

One solution might be to put some unbound text boxes at the top of your
form, with a command button to filter on whatever combination of options the
user chose to use.

You will need to examine each box in turn (e.g. for the name and for the
address), and make a string from the combination. There's a downloadable
example in this article:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

If you don't know anything about VBA, it may take a little effort to follow
what's going on, but it's really worthwhile. This is a technique I use on
most forms and to interface the reports in all my databases.

 

[Klatuu]

Here is a way to use on combo box to switch between searches. In this
example, the command button caption is set for Name search and the combo's
row source is using a query to retrieve names. Then in the Click event of
the command button, you can change to address. Note the lookup actually uses
the Client ID, so how you address the value of the combo box doesn't change
unless you are using other than the bound column. Note, the combo is not a
bound control:

Private Sub cmdSearch_Click()
Const conNameSearch As String = "Search By Name"
Const conAddressSearch As String = "Search By Address:"

With Me
If .cmdSearch.Caption = conNameSearch Then
.cboSearch.RowSource = _
"SELECT tblClient.ClientID, tblClient.AddressL1 FROM
tblClient;"
.cmdSearch.Caption = conAddressSearch
Else
.cboSearch.RowSource = _
"SELECT tblClient.ClientID, tblClient.MainName FROM
tblClient;"
.cmdSearch.Caption = conNameSearch
End If
.cboSearch.Requery
End With
End Sub