Pass criteria from form to VBA module

J

JoeA2006

I am trying to run the following query in module that exports the data to an
Excel sheet. The module works correctly when I use the first statement with
the literal for the MONYR

sSQL = " SELECT qryConsolidatedPerp.*, qryConsolidatedPerp.LOC FROM
qryConsolidatedPerp WHERE (((qryConsolidatedPerp.EndBalMo)='AUG06'));"

When I try to pass the value from a control using this
Mnyr = Me.Text8.Value
sSQL = " SELECT qryConsolidatedPerp.*, qryConsolidatedPerp.LOC FROM
qryConsolidatedPerp WHERE qryConsolidatedPerp.EndBalMo = Mnyr"

I receive the message
"Runtime error 3061 too few parameters expected 1"
I can Debug.Print the value of Mnyr but it does not seem to get passed to
the query.
Is a syntax problem?
 
J

Jeff L

You need to have a parameter established in your module to receive the
value. That would look like
Public Sub MyModule(Monyr as string)

Now when you want to run your module you do it like this
Call MyModule(Me.Text8)

Your SQL statement would be
sSQL = " SELECT qryConsolidatedPerp.*, qryConsolidatedPerp.LOC FROM
qryConsolidatedPerp WHERE qryConsolidatedPerp.EndBalMo = '" & Mnyr &
"'"

Hope that helps!
 
Top