adding conditionial sum for a field

[stumbler]

Hello,
i need to total up all sales for 1 supplier and compare to the total.
I was thinking on adding a condition in a calculated field. The condition refers to a textbox on the report header.

=Sum(IIf([supplier]=[txtSupplier];totalqty;0))

but each time i get a prompt to supply the value: it's not reading from the header.
for testing purposes, i supply the value for txtsupplier control in the onload event

Private Sub Report_Load()
Me.txtSupplier = "LANNEA"
End Sub

Any suggestions?

Thx

 

[llkhoutx]

Store the value before the report is opened. On Load is too late.

 

[stumbler]

Private Sub Report_Open(Cancel As Integer)

Reports("rptSalesPerMonth").txtSupplier = "LANNEA"
Me.txtSupplier = "LANNEA"

End Sub

gives me the error: cannot assign value to thisd control

 

[stumbler]

Found this: http://www.pcreview.co.uk/forums/modify-control-value-access-report-through-vba-t2924379.html
So it seems not to be possible. I solved as discribed: link the control to the form where the users open the report. and that works