passing a value to a report

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

PeterK - 25 May 2007 09:53 GMT

Form1 is a prompt form that sets filter criteria for Report1. Clicking OK
opens the report and closes Form1. With help from my friends I have worked
out the code to do this.

This is what I can't do - If Form1 has a textbox with a value 'abc', how do
I write the code that sets the value 'abc' in a textbox in Report1?

Cheers.

Signature

PeterK

Reply to this Message

Scott McDaniel - 25 May 2007 12:51 GMT

>Form1 is a prompt form that sets filter criteria for Report1. Clicking OK
>opens the report and closes Form1. With help from my friends I have worked
>out the code to do this.
>
>This is what I can't do - If Form1 has a textbox with a value 'abc', how do
>I write the code that sets the value 'abc' in a textbox in Report1?

Is the textbox bound? If so, then if the Recordsource for the Report is the same as the Form, then just add the field to
the report, then add a textbox to your report with that field as the .ControlSource.

If not, then perhaps you could use the OpenArgs argument of the Docmd.OpoenReport (the last argument) ... then in the
Report's Open event:

Me.YourTextbox = Me.OpenArgs

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

Reply to this Message

Ofer Cohen - 25 May 2007 12:56 GMT

Is this text box used as a filter for the report?

If it does, then set a text box in the report to be bound to the field the
filter is on.

If not then can you explain what this text box in the form used for.

************************

Signature

Good Luck
BS"D

> Form1 is a prompt form that sets filter criteria for Report1. Clicking OK
> opens the report and closes Form1. With help from my friends I have worked
[quoted text clipped - 4 lines]
>
> Cheers.

Reply to this Message

PeterK - 28 May 2007 03:45 GMT

The overall context...
A few weeks ago I posted a question about hiding the prompt form when a
report based on it is launched (since the query referred to the prompt form
fields, the form had to stay open). Albert Kallal replied in very full and
helpful terms suggesting I switch to WHERE clauses, so the report is filtered
but still complete after the prompt form is closed. I've tried it out, and
understand reasonably well this whole new coding world.

But what I can't do is set up the report or page headers. Here's a trial eg:

Report = rptStudentHistory
Form = frmStudentHistory_Prompt
Form filters = cboStudentID, dtStartDate, dtFinishDate

The final report will thus show the history of a selected student between 2
selected dates.

The filters work and the report detail is correct. But how do I say
something like "Student summary for [strStudentname] from [dtStartDate] to
[dtFinishDate]"? I can get the student name by adding a new calculated field
to the query (=[strFirstName] & " " & [strSurname]). But the dates have got
me completely lost.

Any tips you can give will be gratefully received.

Signature

PeterK

> Is this text box used as a filter for the report?
>
[quoted text clipped - 13 lines]
> >
> > Cheers.

Reply to this Message

Ofer Cohen - 28 May 2007 12:32 GMT

As Scott Suggested use the OpenArgs of the OpenReport command line

Something like:

Dim MyStr as String
MyStr = "Student summary for " & Me.[strStudentname] & " from " &
[dtStartDate] & " to " & [dtFinishDate]"

Docmd.OpenReport "rptStudentHistory", , , Condition , , MyStr

*************************************
On the OnPrint event of the section where the text box located write the code

Me.[TextBoxName]= Me.OpenArgs
*************************************

Signature

Good Luck
BS"D

> The overall context...
> A few weeks ago I posted a question about hiding the prompt form when a
[quoted text clipped - 38 lines]
> > >
> > > Cheers.

Reply to this Message

PeterK - 29 May 2007 07:35 GMT

I need help with the technical bits of the code.

I have the following lines throughout my Sub:

Dim strReportHeader As String
strReportHeader = "Student summary for " & Me.strName
DoCmd.OpenReport strReportName, acViewPreview, , strWhere, , strReportHeader

When I click the preview button I get this message: "Compile error: method
or data member not found."

If I cut the code back to...
strReportHeader = "Student summary for "
... then the report runs correctly with that string appearing in the report
header.

What am I doing wrong?

Signature

PeterK

> As Scott Suggested use the OpenArgs of the OpenReport command line
>
[quoted text clipped - 54 lines]
> > > >
> > > > Cheers.

Reply to this Message

Ofer Cohen - 29 May 2007 09:44 GMT

Can you post the criteria you apply to the strWhere?

Is the "strName" is the name of the text box in the form?

You can get this message if the code doesn't find this as an object in the
form.

Signature

Good Luck
BS"D

> I need help with the technical bits of the code.
>
[quoted text clipped - 72 lines]
> > > > >
> > > > > Cheers.

Reply to this Message

PeterK - 30 May 2007 03:09 GMT

strName is the field in the report's underlying query. The form has a combo
box named cboStudent, and I have changed to that name in the code. Progress
of sorts. The report header now reads "Student history for 74" - ie, the
value of cboStudent is correctly passed to OpenArgs, but that value is the
StudentID rather than the Student name.

cboStudent has the following fields in its Row Source:
intStudentID (the bound field)
strSurname
strFirstName

So I presume the next question is, how do you reference the unbound fields
in a combo box?

I am very much still a learner, and appreciate the help and patience I get
from you and others.

Signature

PeterK

> Can you post the criteria you apply to the strWhere?
>
[quoted text clipped - 79 lines]
> > > > > >
> > > > > > Cheers.

Reply to this Message

Ofer Cohen - 30 May 2007 07:58 GMT

To get different columns in the combo you can use

For Surname
Me.cboStudent.Column(1)

For FirstName
Me.cboStudent.Column(2)

Note: the column number start with 0, so the Id will be 0 or nothing
To combine both values to get the full name
Me.cboStudent.Column(1) & " " & Me.cboStudent.Column(2)

Signature

Good Luck
BS"D

> strName is the field in the report's underlying query. The form has a combo
> box named cboStudent, and I have changed to that name in the code. Progress
[quoted text clipped - 96 lines]
> > > > > > >
> > > > > > > Cheers.

Reply to this Message

PeterK - 31 May 2007 06:03 GMT

We have liftoff!

If you have time, here are some questions to help me understand principles
(I still feel like my coding is at the 'paint by numbers' level - change the
context slightly and I'm stuffed).

1. What is the point of the Me. prefix?
2. We have created the report from the values on the prompt form rather than
the report's query. Is that right? and is it the right way to do it?
3. It looks to me like OpenArgs can only ever feed one string to a report.
So for example you couldn't set the values for more than one text box in the
report header.
4. I printed my trial report and got the same data as in preview. This
despite not yet adding the OnPrint code for the report header. Do I still
need to do this?

Once again, muchas gracias.

Signature

PeterK

> To get different columns in the combo you can use
>
[quoted text clipped - 108 lines]
> > > > > > > >
> > > > > > > > Cheers.

Reply to this Message

Ofer Cohen - 31 May 2007 07:59 GMT

1. What is the point of the Me. prefix?

Me. Refer to the object (Form or Report) where the code is in.
Me.[TextBoxName] refer to the value of the text box within the current form.

You'll write
Forms![FormName].[FieldName]
if you want to refer to a text box in a form from a different object (form,
report, module, query)
******************************************
2. We have created the report from the values on the prompt form rather than
the report's query. Is that right? and is it the right way to do it?

If I understand the question, you mean that the report criteria is passing
using the WhereCondition of the OpenReport command line, rather then using
the query in the Report RecordSource.
If that the question, then Yes, it is the proper way of doing that for some
reasons.

* You can open the Report from different forms, if needed
* You can validate and change the criteria before openning the report, again
if needed

**********************************
3. It looks to me like OpenArgs can only ever feed one string to a report.
So for example you couldn't set the values for more than one text box in the
report header.

You can pass only one string, but you can pass few field values with a
seperator, and then split it in the report

Docmd.OpenReport "ReportName",,,,, Me.TExt1 & "-" & Me.Text2 & "-" & Me.Text3

Then, on the OnPrint event split the value by "-" (the seperator need to be
a value that won't apear in the text

Me.[TextBoxName1] = Split(Me.OpenArgs,"-")(0)
Me.[TextBoxName2] = Split(Me.OpenArgs,"-")(1)
Me.[TextBoxName3] = Split(Me.OpenArgs,"-")(2)
*********************************************
4. I printed my trial report and got the same data as in preview.

Note sure I understand the question.

Ofer

Reply to this Message

PeterK - 01 Jun 2007 04:21 GMT

As always, you ask a question and get more than you bargained for. That
Split function is a completely new trick for me. More study...

Q4: I thought I typed more than appears on my post; anyway, the point I was
making is that earlier in our thread you guided me about passing openArgs to
the report. then you said this:

"On the OnPrint event of the section where the text box located write the code

Me.[TextBoxName]= Me.OpenArgs"

My assumption at the time was that without this code the header would appear
in print preview but not print off. I haven't yet entered this code, but the
report printed ok, header and all. So I'm not sure what the code is for.

I have one more related problem (I appreciate your time and help - if I'm
taking up too much of it let me know and I'll post as a new thread)

Some of my existing reports require filtering for a particular month. I'm
testing a copy of one and can't get the WHERE clause to filter properly (no
error messages, just wrong data).

My idea was to define strDate1 and strDate2 as the first and last days of
the month and use these to set the filter string. The result seems to be a
filter on one side and not the other (eg for a student who started in January
I get January to March when I choose March, and January to april when I
hcoose April.)

Code follows:

Private Sub cmdPreview_Click()

'Opens the named report with selected filters.
'Closes the prompt form.

Dim strReportName As String 'The report to be opened
Dim strPromptForm As String 'The name of the prompt form.
Dim strWhere As String 'The filter string
Dim strDate, strDate1, strDate2 As String 'The date filters -
'to be added to strWhere
Dim intMonth, intYear As Integer 'The month and year filters
Dim strReportHeader, strDateHeader As String
'The strings used to build the report
header (OpenArgs)

strReportName = "rptTrial1"
strPromptForm = "frmTrial2_Prompt"

intMonth = Me.cboMonth
intYear = Me.txtYear
strDate1 = DateSerial(intYear, intMonth, 1) '(first day of the month)
strDate2 = DateSerial(intYear, intMonth + 1, 1) - 1 '(last day of the month)

'Set the date filter and date header string
If IsNull(cboMonth) = False Then
strDate = "tblLessons.dtTransactionDate >= #" & strDate1 & _
"# And tblLessons.dtTransactionDate <= #" & strDate2 & "#"
strDateHeader = Me.cboMonth.Column(1) & " " & Me.txtYear

End If

'Set the student and date filters
If IsNull(cboStudentName) = False Then
strWhere = "tblStudents.intStudentID = " & cboStudent
End If

If strDate <> "" Then
strWhere = strWhere & " and " & strDate
End If

'Create the report header
strReportHeader = "Student summary for " & Me.cboStudent.Column(2) _
& " " & Me.cboStudent.Column(1) & " for " & strDateHeader

'Run the report
DoCmd.OpenReport strReportName, acViewPreview, , strWhere, , strReportHeader
DoCmd.Close acForm, strPromptForm

End Sub

Cheers

Signature

PeterK

> 1. What is the point of the Me. prefix?
>
[quoted text clipped - 41 lines]
>
> Ofer

Reply to this Message