standard report from VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
You could add columns to your query
Text1:Forms!frmYourForm!txtYourText
Then bind your report text box to [Text1]
 
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



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

--
Duane Hookom
MS Access MVP
--

JMG said:
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
Click to expand...
Click to expand...
 
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
--

JMG said:
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



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

--
Duane Hookom
MS Access MVP
--

JMG said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top