Print VBA declared variable from Dlookup on form load on Report

[lacasa]

I have a report calculate total wages based on hours * hourly rate. The rate is kept in a table. I grab the rate using Dlookup. The same rate is used throughout all the calculations - only the hours change. So I figured it would be wise to assign the rate to a variable on form load to save on muliple Dlookup calculations (over 200).

Option Compare Database
'Declare the bill rate
Dim RespiteRate1 As Currency

Private Sub Report_Load()

'Assign values from Rate table
RespiteRate1 = DLookup("[Rate]", "[Rates]", "[RateCategory]= 'Respite'")

MsgBox RespiteRate1

End Sub


I added the message box just to see if it really is grabbing the rate and it is. I just can't seem to get it to print on the same report that loaded. I assign a value of =[RespiteRate1] to a text box on the form and I get nothing.

Probably is a scope problem, but I have played with various locations for the variable declaration.

I know this should be simple, but I guess not for me.

 

[lbigk]

If all you are trying to do is view on the report, change the field from text to lablel and set .Caption = RespiteRate1.

lbigk

 

[lacasa]

Thanks. I should have been more clear.

I also want to use this in the calcuation hours * RespiteRate1. I pull hours directly from the Report query.

 

[lbigk]

OK, I think the simplest way would be to declare RespiteRate1 as a Global value and in the report query have a field with calculation Wages:[hours] * RespiteRate1

 

[lacasa]

I put a label on the report and set the caption value as =RespiteRate1 but did not get teh value only "=RespiteRate1". I tried variations without success.

I tried to declare

Global RespiteRate1 As Currency

but it still did not work. I tried it in several locations. Where do you declare a Global variable to be used on a form?

 

[lacasa]

I think I figured this out. I used the VBA code to assign the variable value to a text box on the form and then used this text box in my form calculations. So I guess that although the VBA interface is opened through my Access Project they don't communicate unless you code them to.