Help!!!

[Ray1127]

I have a report that In the On Open Event of the report a section of code runs that sets 4 variables. I then need to use these variables in a formula in 4 text boxes. the problem is that when the report runs Access 2000 sees the variables in the formula as Parameters and prompts me for the values instead of getting them from the variables.

How do I get around this.

A. Can I have the formula use a variable instead of a parameter and how do I do that. Or
B. Assign the value in the variable to the parameter without prompting me for it?

 

[dhookom]

Share your code.
What is the scope of the variables? Where are they declared?

Are you attempting to use variables in the Control Sources of text boxes? If so, don't waste your time.

You might be able to use a function that returns the value of the variable. Another option is to use unbound text boxes and set their value in the ON FORMAT event of the section of the report containing the text boxes.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Like Duane was suggesting. If you want to use variables in a calculated control or sql string, you need a function that returns the value of the variable. Ex in a standard module.

dim someVariable as long

Public function getSomeVariable() as long
getSomeVariable = someVariable
end function



then somewhere in your db

someVariable = 123

and in a calculated control

control source: = getSomeVariable() * 60

or in a query
select field1 from someTable where field1 = getSomeVariable()

 

[Ray1127]

The code is in a module as follows:

Option Compare Database
Option Explicit
Public strfirstqtr As String
Public strsecondqtr As String
Public strthirdqtr As String
Public strfourthqtr As String

Public Sub getquarters()
Dim rs As ADODB.Recordset
Dim strSQL As String
Set rs = New ADODB.Recordset
strSQL = "Select qtr from query2 order by qtr"
rs.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
'Me.txt1stqtr.Caption = rs!QTR
strfirstqtr = rs!QTR
rs.MoveNext
'Me.txt2ndQtr.Caption = rs!QTR
strsecondqtr = rs!QTR
rs.MoveNext
'Me.txt3rdqtr.Caption = rs!QTR
strthirdqtr = rs!QTR
rs.MoveNext
'Me.txt4thqtr.Caption = rs!QTR
strfourthqtr = rs!QTR
rs.Close
Set rs = Nothing

End Sub

The subroutine is called from the Open Event of the report.

I have verified that the variables are set.

There is a text box in the report with the following controlsource:
I enter it this way.
=Sum(IIf([QTR]=strfirstqtr,[CT],0))

As soon as I'm done it changes it to this:
=Sum(IIf([QTR]=[strfirstqtr],[CT],0))

Then when I run the report I get prompted to enter strfirstqtr. I have set a breakpoint in the Report open event after the subroutine is run and type in ?strfirstqtr into the Immediate window and get the appropriate string which is 2008-Q2
but I still get prompted to enter strfirstqtr

 

[dhookom]

You have gotten two good replies which you seem to have ignored. Was there something that wasn't clear?

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Yes Ray, like I said what you are trying to do is not possible. We did not have to see your code to tell you that, we already told you that. So what exactly do you want?

 

[MajP]

If it makes you feel better here it is one more time.
"You can not use a variable in a calculated control or a query."

but something like this would work
Public module
public function getFirstQtr() as string
getFirstQtr = strFirstQtr
end function

control
=Sum(IIf([QTR]=getFirstQtr(),[CT],0))

 

[Ray1127]

Sorry guess I didn't get it. I thought you were asking for clarification on what I did. So that is what I provided. My mistake.

 

[dhookom]

Ray1127,
Do you need more assistance on this? Some of us have issues viewing needs from a less experienced perspective. We have a tendency to throw around code samples and other stuff and make assumptions that everyone understands.

Duane
Hook'D on Access
MS Access MVP

 

[Ray1127]

No I don't. Simply I broke up the Subroutine into 4 functions each function uses the same query but sets the function = to the different records first = first record, second = second record etc. I'm sure there's a better way but this works and when I get more time I'll take another look at it.