export variable from a form to a report using VBA

Thierry Schmitt

Hello all,
I m quite sure that s a common problem and even that the answer to it
has been given somewhere, forgive my lack of perspicacity but here is
my problem:

In the vba code of a button on a form, leading to the preview of a
report, I ve got a variable (string) which has been filled with some
information from a SQL querry. I want to export this variable to the
report to be previewed.
The report has been built before with the assistant and the button on
the form has a command looking like DoCmd.Open "form"....

What would be the best way then to add a textbox or a label on the
report with the string resulting from the SQL querry?

What I ve been trying to do so far is to add a textbox on the report
and add in vba code something like
Reports!Invoice_Report.TextBoxAdress= var, but even if the debugger
doesnt complain, the textbox is empty (even though I m sure I ve got
the good value in var).

Any hints or pointer to a web page would be appreciated.

Nov 12 '05 #1

Pieter Linden

sc******@cf.ac.uk (Thierry Schmitt) wrote in message news:<42**************************@posting.google. com>...

Hello all,
I m quite sure that s a common problem and even that the answer to it
has been given somewhere, forgive my lack of perspicacity but here is
my problem:

In the vba code of a button on a form, leading to the preview of a
report, I ve got a variable (string) which has been filled with some
information from a SQL querry. I want to export this variable to the
report to be previewed.
The report has been built before with the assistant and the button on
the form has a command looking like DoCmd.Open "form"....

What would be the best way then to add a textbox or a label on the
report with the string resulting from the SQL querry?

What I ve been trying to do so far is to add a textbox on the report
and add in vba code something like
Reports!Invoice_Report.TextBoxAdress= var, but even if the debugger
doesnt complain, the textbox is empty (even though I m sure I ve got
the good value in var).

Any hints or pointer to a web page would be appreciated.

What if you create a wrapper function that assigns the resulting
variable to the function name, and then put that on your report?

Nov 12 '05 #2

Pieter Linden

Thierry,
This is how I got mine to work... although my function is really
simple and stupid, but there's no reason you can't show the result of
a function on a report.

Okay, my function:

Public Function MyVariable() As String
MyVariable = "Jimi Hendrix"

End Function

Yes, simple and stupid, I know...

On my report, I have a textbox with its control source set to:
=MyVariable()

And I get the value in the control source on my report. Or is your
value supposed to change for each record? If so, just put it in the
Detail section of your report, and pass a fieldname from your
rowsource to it.

e.g.
Public Function TypeBackwards(ByVal strValue As String) As String
TypeBackwards = StrReverse(strValue)
End Function

Text13.Rowsource==TypeBackwards([Service])
(where Service is a field on my report.)

HTH,
Pieter

Nov 12 '05 #3

Mike Storr

If the tag property of the button is not being used, assign the variable to
it and have the textbox on the report reference the form control in it's
OnLoad event.

Example: In Button Code
Me!Button.Tag = Var

In Report OnOpen (Maybe OnLoad) Event
Me!TextFromButton = Forms!FormName!Button.Tag)

Of course the form will need to remain open until the report is finished
opening.

Mike Storr
www.veraccess.com
"Pieter Linden" <pi********@hotmail.com> wrote in message
news:bf**************************@posting.google.c om...

sc******@cf.ac.uk (Thierry Schmitt) wrote in message

news:<42**************************@posting.google. com>...

Hello all,
I m quite sure that s a common problem and even that the answer to it
has been given somewhere, forgive my lack of perspicacity but here is
my problem:

In the vba code of a button on a form, leading to the preview of a
report, I ve got a variable (string) which has been filled with some
information from a SQL querry. I want to export this variable to the
report to be previewed.
The report has been built before with the assistant and the button on
the form has a command looking like DoCmd.Open "form"....

What would be the best way then to add a textbox or a label on the
report with the string resulting from the SQL querry?

What I ve been trying to do so far is to add a textbox on the report
and add in vba code something like
Reports!Invoice_Report.TextBoxAdress= var, but even if the debugger
doesnt complain, the textbox is empty (even though I m sure I ve got
the good value in var).

Any hints or pointer to a web page would be appreciated.

What if you create a wrapper function that assigns the resulting
variable to the function name, and then put that on your report?

Nov 12 '05 #4