standard report from VBA

[Guest]

Ok, I need some help on this one. I am sure it can be done, but I am not sure
where to start. I would like to create a single report template and then
depending on which query is run, output to the results to the report template.

So for example, I have a report with 4 unbound fields, how do I "connect"
the results from the query to the unbound text boxes. Likewise, for the title
of the report, how do I put a string variable into an unbound text box. The
commands I used for forms do not seem to be working (eg)

dim n1 as string
n1= "Title of the report"

Reports!Report1!text1=n1 '(where text1 is the unbound textbox in the report)

thanks in advance for any thoughts.

Rgs
JMG

 

[Duane Hookom]

You could add columns to your query
Text1:Forms!frmYourForm!txtYourText
Then bind your report text box to [Text1]

 

[Guest]

Dear Duane

Thanks for the reply. Ok, conceptually I understand what you mean but how in
code would I go about doing it. What is the VBA coding that will put a string
into an unbound textbox (eg. report title). Ideally this would happen when
the user click a button in a form and then views a report. As I want to use
one report template for all reports the title needs to be a string. likewise,
how does one, in VBA, populate an unbound textbox with a column in a query
(eg 200 records).

For example

Sub Click3()
Dim title as String
title = “Final Year End Reportâ€

NEXT REFERENCE THE REPORT......

End sub

You could add columns to your query
Text1:Forms!frmYourForm!txtYourText
Then bind your report text box to [Text1]

--
Duane Hookom
MS Access MVP
--

Ok, I need some help on this one. I am sure it can be done, but I am not
sure
where to start. I would like to create a single report template and then
depending on which query is run, output to the results to the report
template.

So for example, I have a report with 4 unbound fields, how do I "connect"
the results from the query to the unbound text boxes. Likewise, for the
title
of the report, how do I put a string variable into an unbound text box.
The
commands I used for forms do not seem to be working (eg)

dim n1 as string
n1= "Title of the report"

Reports!Report1!text1=n1 '(where text1 is the unbound textbox in the
report)

thanks in advance for any thoughts.

Rgs
JMG

 

[Duane Hookom]

You don't really need code unless you want to set the "title" value from
multiple forms. You could create a standard module with code like:

Option Compare Database
Option Explicit
Dim strTitle As String

Public Sub SetTitle(pstrTitle As String)
strTitle = pstrTitle
End Sub

Public Function GetTitle() As String
GetTitle = strTitle
End Function


Then in your form, you could run code prior to opening the report like
SetTitle Me.txtTitle
Then in your report, you could use a text box with a control source of
Control Source: =GetTitle()
or you could use code in the On Format event of the section containing an
unbound text box:

Me.txtTitle = GetTitle()

If you want a text box to display a column from your query, just set the
control source to the column name.


--
Duane Hookom
MS Access MVP
--

Dear Duane

Thanks for the reply. Ok, conceptually I understand what you mean but how
in
code would I go about doing it. What is the VBA coding that will put a
string
into an unbound textbox (eg. report title). Ideally this would happen when
the user click a button in a form and then views a report. As I want to
use
one report template for all reports the title needs to be a string.
likewise,
how does one, in VBA, populate an unbound textbox with a column in a query
(eg 200 records).

For example

Sub Click3()
Dim title as String
title = "Final Year End Report"

NEXT REFERENCE THE REPORT......

End sub

You could add columns to your query
Text1:Forms!frmYourForm!txtYourText
Then bind your report text box to [Text1]

--
Duane Hookom
MS Access MVP
--

Ok, I need some help on this one. I am sure it can be done, but I am
not
sure
where to start. I would like to create a single report template and
then
depending on which query is run, output to the results to the report
template.

So for example, I have a report with 4 unbound fields, how do I
"connect"
the results from the query to the unbound text boxes. Likewise, for the
title
of the report, how do I put a string variable into an unbound text box.
The
commands I used for forms do not seem to be working (eg)

dim n1 as string
n1= "Title of the report"

Reports!Report1!text1=n1 '(where text1 is the unbound textbox in the
report)

thanks in advance for any thoughts.

Rgs
JMG