Public variables: MSA 2003

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

--
Message posted via http://www.accessmonster.com

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"GB via AccessMonster.com" wrote in message
news:f437c74063074c338fe1e1cab44e911a@AccessMonste r.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

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?

--
Message posted via http://www.accessmonster.com

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() );"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"g B via AccessMonster.com" wrote in message
news:9627633818ac4a3ab9864ac6eb44f0ca@AccessMonste r.com...
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?

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" wrote in message
news:f437c74063074c338fe1e1cab44e911a@AccessMonste r.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

--
Message posted via http://www.accessmonster.com

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]

--
Message posted via http://www.accessmonster.com

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)


"g B via AccessMonster.com" wrote in message
news:3d6b8d83aba5454891a00997d2109a60@AccessMonste r.com...
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]

--
Message posted via http://www.accessmonster.com