Creating a form using multiple parameters

[Guest]

I'm trying to create a form in which users can enter multiple parameters
using drop down lists & to be able to select multiple values within a single
parameter. For example, from the Premise Type parameter, to be able to
select 'HOUSE' & 'DUPLEX'. I would like the user to be able to select all of
the parameters, then click on a button to execute the search. Also, the
table in which the data resides may contain multiple values within a single
field. For example, in the column Premise Type, data may contain
'DUPLEX/APT/HOUSE', therefore I need results to contain the parameters that
were entered on the form, no matter what order they may appear in a record.
I don't know how to set up the query to be able to grab the appropriate
records.
I'm using Access 2003. Any help is greatly appreciately. Thank you in
advance.

 

[Graham R Seach]

What you're talking about is a search/find dialog, and it's quite a common
requirement.

<<multiple values within a single field>>?????
This is a definite NO-NO! It breaks a cardinal rule of database design. Each
field must only contain one unit of information - no more! Even in the case
you cite, this data should be split into two or more different fields. Doing
so will certainly make the task of searching a lot easier, but it will
prevent a raft of other problems you're likely to experience.

The essence of a search/find dialog is to build the SQL statement in VBA.
Your code needs to check in every form field to see if it has any data in
it, and if so, append it to the SQL statement. For example, if we have 3
text boxes, called Text1 (a string value), Text2 (a string value)and Text3
(a number):
Private Sub cmdFind_Click()
Dim db As Database
Dim sSQL1 As String
Dim sSQL2 As String
Dim sSQL3 As String

On Error Resume Next

sSQL1 = "SELECT * FROM tblSomeTable "
sSQL3 = "ORDER BY somefield"

If Len(Nz(Me.Text1, "")) > 0 Then
sSQL2 = "[Field1] = """ & Me.Text1 & """ "
End If

If Len(Nz(Me.Text2, "")) > 0 Then
If Len(sSQL2) > 0 Then sSQL2 = sSQL2 & "AND "
sSQL2 = sSQL2 & "[Field1] = """ & Me.Text1 & """ "
End If

If Len(Nz(Me.Text3, "")) > 0 Then
If Len(sSQL2) > 0 Then sSQL2 = sSQL2 & "AND "
sSQL2 = sSQL2 & "[Field1] = " & Me.Text1 & " "
End If

If Len(sSQL2) > 0 Then sSQL2 = "WHERE " & sSQL2

Set db = CurrentDb
db.Execute sSQL1 & sSQL2 & sSQL3, dbFailOnError
If (Err <> 0) Then
MsgBox Err.Number & vbCrLf & Err.Description
End If

Set db = Nothing
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html