From Form, Search for Data in Query

[zSplash]

This is a rank beginner's question, and I apologize for being so dumb. I am
fairly familiar with VBA, don't really understand it as it applies to
Access. (It can't be as difficult as I'm trying to make it!)

Query1 contains Lastname, Firstname, and various address fields. Form1 has
these same fields, but I want to be able to "ask" what first and last name
to search Query1 for. (The query is sorted alphabetically by last/first).
[I have tried an unbound textbox, a bound combo box (for lastname), and an
input box unsuccessfully -- I get nothing but errors!] After user input of
the search criteria, I want the form to show the records that meet the
search criteria.

Would somebody please head me in the right direction to accomplish such a
seemingly simple goal? Maybe I'm trying to include too much in a single
form (it does take a long time to download the huge query).....

TIA

 

[Howard Brody]

Start by putting two ComboBoxes on your form (cboLastname
and cboFirstName). Have cboLastName populate with all the
individual last names in your main table. When a last
name is selected, requery cboFirstName to select only the
first names where the last name matches.

Then use this code to build and apply a filter for your
form:

Dim strFName as String
Dim strLName as String
Dim strFilter as String

strFName = [cboFirstName]
strLName = [cboLastName]
strFilter = "[FirstName]='" & strFName & "' AND [LastName]
='" & strLName & "'"

DoCmd.ApplyFilter , strFilter

Hope this helps!

Howard Brody

 

[zSplash]

Thanks for the help, Howard.

I have started a completely new form with 2 combo boxes, as you suggest. I
don't know what you mean by "requery cboFirstName to select only the first
names...". How do you do that? I have copied your code to folder
"db1.Form_new1.thiscode", but I don't know how to "call it".

Maybe I'm so out of my league it'd be better to be directed to an on-line
tutorial for this sort of thing. Can you direct me to such?

st.

Start by putting two ComboBoxes on your form (cboLastname
and cboFirstName). Have cboLastName populate with all the
individual last names in your main table. When a last
name is selected, requery cboFirstName to select only the
first names where the last name matches.

Then use this code to build and apply a filter for your
form:

Dim strFName as String
Dim strLName as String
Dim strFilter as String

strFName = [cboFirstName]
strLName = [cboLastName]
strFilter = "[FirstName]='" & strFName & "' AND [LastName]
='" & strLName & "'"

DoCmd.ApplyFilter , strFilter

Hope this helps!

Howard Brody

-----Original Message-----
This is a rank beginner's question, and I apologize for being so dumb. I am
fairly familiar with VBA, don't really understand it as it applies to
Access. (It can't be as difficult as I'm trying to make it!)

Query1 contains Lastname, Firstname, and various address fields. Form1 has
these same fields, but I want to be able to "ask" what first and last name
to search Query1 for. (The query is sorted alphabetically by last/first).
[I have tried an unbound textbox, a bound combo box (for lastname), and an
input box unsuccessfully -- I get nothing but errors!] After user input of
the search criteria, I want the form to show the records that meet the
search criteria.

Would somebody please head me in the right direction to accomplish such a
seemingly simple goal? Maybe I'm trying to include too much in a single
form (it does take a long time to download the huge query).....

TIA


.