Full Version: Multiple criteria query based on user input into a form
UtterAccess Forums > Microsoftģ Access > Access Forms
Sandi V
I want to add functionality to an existing contacts dbase that allows contacts to be classified by organization type. (bank, law firm, university, individual, etc.). Then, when the users want to communicate with a combination of groups (law firms AND universities), Iíd like them to have a nice form where they can check boxes for all the classifications they want. The button would:
. build and run the query based on the boxes checked,
2. save it as a temporary query on which a mailing labels report is based (without prompting to overwrite the old query)
3. and then preview the mailing labels.
tblLkpClass:
IDLkpClass
Class
tblContacts:
IDContacts
Fname
Lname etc.
tblClass:
IDClass
IDContacts
Class
Whatís the best way to go about this? I donít know how to get started with the form. Query by Form methods Iíve found seem to only deal with one criterion. Thanks in advance, UA Gods.
Sandi
(I suppose in a perfect world, Iíd also like to create permanent record that the recipient recíd this mailing; but now Iím asking too much!)
JermD
You could declare a string variable in VBA in your form, like strSQL1 that could hold the query string. For instance, the user could select all of their check boxes desired, then click a command button that generates the sql string by adding the fields checked, like:
im strBank, strLawFirm, strSQL As String
If bank = true Then
strBank = "bank"
Else
strBank = ""
End If
If law_firm = true Then
strLawFirm = "law_firm"
Else
strLawFirm = ""
End If
strSQL = "SELECT " & strBank & ", " & strLawFirm & " FROM ........" (and so on)
Then open your report and set the RecordSource property to strSQL
Reports!Report1.RecordSource = strSQL
Reports!Report1.Requery
This is just an idea; I did not tweak this out as I'm sure you would have to. It is merely an avenue you could take.
Sandi V
Thanks for the suggestion...although I'm at a loss when it comes to using functions. I think I'm leaning more toward using a Yes/No field in the lkpClass table; having the user reset it with a button that runs an update query, then user rechecks the boxes...& clicks a button that runs report based on query. Does this sound plausible?
dashiellx2000
Check out this example from the code archive
TH.
Sandi V
Very nice!! Thank you!
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.