First, let's correct a misconception you seem to have about the Me keyword.
"Me" always refers to the class/instance in which Me was invoked. In this
case, it's the Form module, so Me refers to the form the button is on and has
nothing whatsoever to do with the report.
Now, the easiest way to do what you want is to simply base the report on the
tblkpCuisine table, and pass the Where expression (not including the word
"Where") to the WhereCondition argument passed to DoCmd.OpenReport. This will
filter the report's output using the expression you passed.
Oh, here's another issue. You put the variable name into the Where
expression, but the SQL that's going to execute cannot see the variable inside
the function, only code withing the function can. What you need to put into
the string is an expression of the value that was in the variable (e.g.
"tblkpCuisine.txtCuisine = """ & strCuisine & """"". This assumes that
strCuisine holds a string, and there can be no quote marks in the string.
Here's a couple of more notes.
You should not use special notation such as txt for the data types of fields
in tables as it will just make your system harder to maintain.
You should not be using text for lookup values such as Cuisine at all.
Instead, your lookup table should have an Autonumber key, and you should
reference this key from the other tables. This creates a better normalized
database such that a Cuisine name is only stored in one place, and it
eliminates things like thw quote mark issue I mentioned above.
I recommend that you read or at least skim a book on Access application
development and one on relational database design before you try to go much
farther, or you're going to make some messes that will be hard to untangle
later.
On 26 Jan 2004 14:13:37 -0800,
am*******@verizon.net (amywolfie) wrote:
Hi All:
I would like to run a report based on criteria from 3 unbound combo
boxes located on a parameter form (combo boxes are: cboCuisine,
cboLocation, and cboRestaurant)
The present code for the FIND button (using just cboCuisine for
testing) is:
Private Sub Command13_Click()
On Error GoTo Error_Handler:
Dim strCuisine As String
strCuisine = Me.cboCuisine
'Dim strLocation As String
'strLocation = Me.cboLocation
'Dim strRestaurant As String
'strRestaurant = Me.cboRestaurant
Dim strSQL As String
strSQL = "SELECT tblkpCuisine.* FROM tblkpCuisine " _
& "WHERE tblkpCuisine.txtCuisine = strCuisine"
DoCmd.OpenReport "rptRestaurantsMultiFind", acViewPreview
Me.RecordSource = strSQL
Me.Requery
====
Of course, this isn't working, and the report comes up with all
records found.
The report itself is based on a query involving several related
tables.
What is the best way to accomplish the above: i.e., have the user
enter values in the three unbound combo boxes on a parameter form and
then use this criteria to run the report?
Thanks!
amy
====
Exit Sub
Error_Handler:
MsgBox Err.Number & " " & Err.Description
End Sub
