If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Outlook 2003 link to public calendar from folder list? | Peter | Installation & Setup | 1 | September 8th, 2004 03:33 PM |
Office 2003 / Office XP Shortcut Bar | Marc Bressman | Setup, Installing & Configuration | 6 | June 26th, 2004 08:42 AM |
Continual Error 1321 Trying to Install Office 2003 | Chad Harris | General Discussions | 9 | June 11th, 2004 08:19 AM |
SBS 2003 Outlook 2003 Contacts Public folder address book | Jordi Pocurull | Contacts | 1 | June 1st, 2004 11:46 PM |