Public variables: MSA 2003

  • Thread starter Thread starter GB via AccessMonster.com
  • Start date Start date
G

GB via AccessMonster.com

I've created a report in MSA2000 using public variables. When i executed the report using MSA2003 it seems as the public variables are not recognized.

Public pfiller1, pitemno as string.
Option Compare Database

Private Sub Report_Open(Cancel As Integer)

pfiller1 = "LINE"
pitemno = "0010"

DoCmd.RunSQL "INSERT INTO tmp_lineitem (pfiller1 ,pitemno ) VALUES ( pfiller1 ,mitemno );"
End Sub
 
You cannot refer to variables outside of your VBA code.

If you really need to get them, you must use functions to expose them, e.g.:
Public Function GetPfiller1()
GetPfiller1 = pfiller1
End Function

BTW, the declaration:
Public pfiller1, pitemno as string
does not give you 2 string variables as you might expect. The first is
untyped, and so is a Variant. You may have intended:
Public pfiller1 as string, pitemno as string
 
I've changed the variable declaration to Public pfiller1 as string, pitemno as string

I can see that the fields pfiller1 and pitemno are filled with 'LINE' and '0010' when i'm in debug mode. When the SQL command are executed a screen pops up with "pfiller1 Enter parameter value "

DoCmd.RunSQL "INSERT INTO tmp_lineitem (pfiller1 ,pitemno ) VALUES ( pfiller1 ,pitemno );"

With the SQL statement i want to insert the values of the two fields into the table tmp_lineitem with the fieldnames pfiller1 and pitemno.

I read somewhere in the group about references in MS A2003. Do i need to look into it?
 
As per the last reply, in a SQL statement you can only get at the VBA
variables through a function call.

You will end up with something like this:
"INSERT INTO tmp_lineitem (pfiller1, pitemno ) VALUES ( Getpfiller1(),
Getpitemno() );"
 
That syntax is no longer accepted in Access (2003). Instead, you will have
to do something like this:

DoCmd.RunSQL "INSERT INTO tmp_lineitem (pfiller1 ,pitemno ) VALUES ('" &
pfiller1 & "','" & mitemno & "');"

or use a global function (in a standard module) to retrieve the values,
perhaps like this:

DoCmd.RunSQL "INSERT INTO tmp_lineitem (pfiller1 ,pitemno ) VALUES (
fn_pfiller1() ,fn_mitemno );"

Also, you can no longer use those variables/report object properties in the
data source of controls. Instead, you may be able to use something like
=reports("reportname").pfiller1

(david)


GB via AccessMonster.com said:
I've created a report in MSA2000 using public variables. When i executed
Click to expand...
the report using MSA2003 it seems as the public variables are not
recognized.
 
Worked,Thanks david.
One other issue. I want now to add pfiller1 to a field on report rpt_xyz123.
Do i need to add the following to the control source of the textbox
=reports("rpt_xyz123").pfiller1 to display the content of pfiller1?
Currently the control source is =[pfiller1]
 
I never used =[pfiller1], and I'm not currently using A2003 very often.
I understand that =[pfiller1] will not work in Access 2003.

I think that

=reports("rpt_xyz123").pfiller1

will probably work, as will

= reports!rpt_xyz123.pfiller1

(david)
 
Back
Top