How to refer to a VBA module variable within a report select state

[FSHOTT]

I am trying to understand how to reference (identify) a variable from a
recordset within a VBA module to a Report Select Statement Where clause. The
VBA module is to loop through an EmailInfo table (Recordset), establish an
Outlook message and attach a Supplier Report for each individual Supplier ID.
The Supplier Report is based on a supplier performance query (tables of
suppliers & their performance for all recorded years). In the Supplier Report
the Select Where clause uses variables cboYear, cboMonth & SupplierID to
filter the query data for the report. My problem is that in my tests I assign
the cboYear , cboMonth to a specific year, month and the supplier ID I get
from the recordset (eMailInfo table) but am not able to correctly identify
these variables in the Select Statement. If there a syntax such as
ModuleName!VariableName I should be using?

 

[Dorian]

You are opening a series of reports from within a loop of a recordset?
This sounds like a strange design.
The recordset fields can be referred to with rs.fields("column name").value
where rs is the recordset name.
You could pass these values to a report via the openargs parameter (look up
'openargs' in Access help).
Hope this is some help.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[FSHOTT]

Dorian & Ken Thank You for the fishing lesson! I think I need more lessons as
I still cannot get my database to work properly. First Dorian I looked up the
Access help for 'openags' and read the help but I have 3 variables to
establish in the where clause. I do I do this? Is there a way to refer to 3
variables with the 'openargs' property?
Ken I implemented the code you recommended but get a compile error message
indicating that the lngSupplierID variable is not defined. The variable is
defined and established (lngSupplierID = rst.Fields("SupplierID")) in my
SendMessages() sub procedure but incures the error in the Function
GetSupplier() procedure. The help message for this indicates there may be an
issue with the Options Explicit statement in the module. I obviously am doing
something wrong. You help is greatly appreciated.

 

[FSHOTT]

Ken Thank You very much. I followed you instructions and it now works. I can
now tackle the next issue.
--
frank-a

The variable must be declared at module level, not locally in the procedure.
Open the module and select General from the top left combo box. Then select
(Declarations) from the top right combo box and declare the variable below
the lines which you'll already see there. Make sure you remove the
declaration from the procedure. This exposes the variable to code anywhere
in the module, so you can assign a value to it in the SendMessages()
procedure, and get the value in the GetSupplier() function.

BTW you can pass multiple values via the OpenArgs mechanism by using a module
which Stuart McCall and I once developed. You'll find a demo of it at:

http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=24091&webtag=ws-msdevapps


Its not the approach I'd choose in this case, though.

Ken Sheridan
Stafford, England

Dorian & Ken Thank You for the fishing lesson! I think I need more lessons as
I still cannot get my database to work properly. First Dorian I looked up the
Access help for 'openags' and read the help but I have 3 variables to
establish in the where clause. I do I do this? Is there a way to refer to 3
variables with the 'openargs' property?
Ken I implemented the code you recommended but get a compile error message
indicating that the lngSupplierID variable is not defined. The variable is
defined and established (lngSupplierID = rst.Fields("SupplierID")) in my
SendMessages() sub procedure but incures the error in the Function
GetSupplier() procedure. The help message for this indicates there may be an
issue with the Options Explicit statement in the module. I obviously am doing
something wrong. You help is greatly appreciated.

I am trying to understand how to reference (identify) a variable from a
recordset within a VBA module to a Report Select Statement Where clause. The

[quoted text clipped - 8 lines]

these variables in the Select Statement. If there a syntax such as
ModuleName!VariableName I should be using?

--



.