VBA, Global Variable passed to Parameter

Global Variable passed to Parameter

Author	Message
Tom Walsh #1 / 4	Global Variable passed to Parameter I have a report, which is based on a query. The query has an open Parameter [papernum] which requires inputting on opening of the report. (i.e. Enter Parameter Value box) The report needs to be printed many times, for many different values of [papernum], so I have put a loop in a vb module to pass an incrementing number to [papernum] as a global variable. But it doesn't work :-( When the openreport runs, it still asks me to 'Enter Parameter Value' for papernum, and I have not got a clue why. Could someone please point out my (almost certainly trivial) error. Thanks Code is as follows: Option Compare Database Global papernum As Integer _________________________ Function PrintReps() For I = 1 To 200 papernum = I DoCmd.OpenReport "ReportForPrinting", acViewPreview ' note, acViewPreview will be changed to acViewNormal when code works MsgBox papernum ' this is to check the variable is looping correctly...it is. Next I End Function
Mon, 18 Oct 2004 18:45:42 GMT

p #2 / 4	Global Variable passed to Parameter when u want to use a variable in a query it's best to use a function which returns the value off that variable. function retVar() retVar=papernum end function select field1,field2, retVar() as papernum from table 'papernum does not need to be this alias. should be the query for your report then Quote: > I have a report, which is based on a query. The query has an open > Parameter [papernum] which requires inputting on opening of the > report. (i.e. Enter Parameter Value box) > The report needs to be printed many times, for many different values > of [papernum], so I have put a loop in a vb module to pass an > incrementing number to [papernum] as a global variable. > But it doesn't work :-( > When the openreport runs, it still asks me to 'Enter Parameter Value' > for papernum, and I have not got a clue why. Could someone please > point out my (almost certainly trivial) error. Thanks > Code is as follows: > Option Compare Database > Global papernum As Integer > _________________________ > Function PrintReps() > For I = 1 To 200 > papernum = I > DoCmd.OpenReport "ReportForPrinting", acViewPreview > ' note, acViewPreview will be changed to acViewNormal when code works > MsgBox papernum > ' this is to check the variable is looping correctly...it is. > Next I > End Function
Mon, 18 Oct 2004 20:38:40 GMT

Tom Walsh #3 / 4	Global Variable passed to Parameter Quote: >when u want to use a variable in a query it's best to use a function which >returns the value off that variable. Thanks for your reply; Perhaps 'it's best', but I was planning on leaving my query unchanged; I was basically wondering whether it can work the other way? Is it possible? <snip>
Mon, 18 Oct 2004 21:25:24 GMT

Dirk Goldga #4 / 4	Global Variable passed to Parameter No, you can't supply the value of a query parameter from a global variable. You must change your query, either to call a function for the value, or to refer to a control on a form where the value is stored. -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) Quote: > >when u want to use a variable in a query it's best to use a function which > >returns the value off that variable. > Thanks for your reply; > Perhaps 'it's best', but I was planning on leaving my query unchanged; > I was basically wondering whether it can work the other way? Is it > possible? > <snip>
Wed, 20 Oct 2004 00:28:49 GMT

Page 1 of 1

[ 4 post ]