By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,500 Members | 830 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,500 IT Pros & Developers. It's quick & easy.

Using a SQL Statement To Generate A Report

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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


Nov 12 '05 #2

P: n/a
"amywolfie" <am*******@verizon.net> wrote in message
news:33**************************@posting.google.c om...
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

If you look in the help file under the OpenReport method, you will see there
is an optional parameter - WhereCondition. This is a string expression
that's a valid SQL WHERE clause without the word WHERE.
You can loop through your controls to build up various AND clauses noting if
the criteria is a text or number and adding quotes accordingly. The basic
idea is this:

strWhere = "[MyNumber] = " & Me.cboCombo
strWhere = strWhere & " AND [MyText]=""" & Me.txtText & """"

DoCmd.OpenReport "rptRestaurantsMultiFind", acViewPreview, , strWhere

Does that get you started?

Fletcher
Nov 12 '05 #3

P: n/a

Thanks very much, Fletcher! I did note the WHERE property of Open
Report, but I wasn't sure about the syntax.

I am making the transition from FileMaker Pro development to Access, so
it's the new syntax that's tripping me up.

Thanks again for the helpful advice and the civility of your reply.

Amy
==
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.