set Access report label caption from form

[perky2go]

I've created a report to use as a form letter; have a table containing the
records/fields needed in the letter; want to use a push button on a form to
set off merging the table data with the report to produce the letters. I
found a neat Replace() function that should build the contents of a label's
caption so that the basic ltr contents can be used for different occasions,
but I'm stymied in seeing if it will work because I can't get beyond Run Time
Error 438 (Object doesn't support this method or property) in recognizing my
reference to the label. It appears that the report needs to be open before I
can do anything with it, but opening the report didn't help with the error.
I'm using Access 97. Here's what I've got:

Function is on the Click event of the Form's command button
Table containing data is "LtrStaging"
Report name is "LtrRefund"
Label control is named "lblAddr"

Initial intent is to use the Replace() to string together the pieces of the
address (with one line or 2 for the street addr). The label contains
"tokens" for the address pieces, with "Provider" being the first, getting
it's data from the table field "PayToName".

Function FillOutLetter()
Dim D as database, R as recordset, C as control, Rpt as report
Set D = dbengine.workspaces(0).databases(0)
Set R = D.openrecordset("LtrStaging")
DoCmd.OpenReport "LtrRefund", acViewDesign

'Here's the line creating the error:
Set C = Reports.[LtrRefund].lblAddr
Do Until R.eof
C.Caption = Replace(C.Caption, "[Provider]", PayToName)
R.MoveNext
Loop
End Function


Here's the called function, placed in the FormModule:
Public Function Replace(Block, Token, ReplaceText)
Dim StartToken As String
Dim EndToken As String
Dim Front As String
Dim Back As String

StartToken = InStr(Block, Token) - 1
EndToken = StartToken + Len(Token) + 1
Front = Left(Block, StartToken)
Back = Mid(Block, EndToken)

Replace = Front & ReplaceText & Back

End Function

I'm probably missing some basic step/concept, but of course I don't know
what. Thanks!

 

[Marshall Barton]

I've created a report to use as a form letter; have a table containing the
records/fields needed in the letter; want to use a push button on a form to
set off merging the table data with the report to produce the letters. I
found a neat Replace() function that should build the contents of a label's
caption so that the basic ltr contents can be used for different occasions,
but I'm stymied in seeing if it will work because I can't get beyond Run Time
Error 438 (Object doesn't support this method or property) in recognizing my
reference to the label. It appears that the report needs to be open before I
can do anything with it, but opening the report didn't help with the error.
I'm using Access 97. Here's what I've got:

Function is on the Click event of the Form's command button
Table containing data is "LtrStaging"
Report name is "LtrRefund"
Label control is named "lblAddr"

Initial intent is to use the Replace() to string together the pieces of the
address (with one line or 2 for the street addr). The label contains
"tokens" for the address pieces, with "Provider" being the first, getting
it's data from the table field "PayToName".

Function FillOutLetter()
Dim D as database, R as recordset, C as control, Rpt as report
Set D = dbengine.workspaces(0).databases(0)
Set R = D.openrecordset("LtrStaging")
DoCmd.OpenReport "LtrRefund", acViewDesign

'Here's the line creating the error:
Set C = Reports.[LtrRefund].lblAddr
Do Until R.eof
C.Caption = Replace(C.Caption, "[Provider]", PayToName)
R.MoveNext
Loop
End Function


Here's the called function, placed in the FormModule:
Public Function Replace(Block, Token, ReplaceText)
Dim StartToken As String
Dim EndToken As String
Dim Front As String
Dim Back As String

StartToken = InStr(Block, Token) - 1
EndToken = StartToken + Len(Token) + 1
Front = Left(Block, StartToken)
Back = Mid(Block, EndToken)

Replace = Front & ReplaceText & Back

End Function

I'm probably missing some basic step/concept, but of course I don't know
what.

The something basic is that you need to manipulate the
contents of the report using code in the report's event
procedure. Normally this is done in the Format event of the
section that contains the control you want to manupulate.

Because the report has not done much more than initial open
processing when control is returned from the OpenReport
method, it's just too soon to do what you want that way.
Even if the OpenReport method delayed returning control to
your procedure, it would then be too late because report
formatting would already have started.

 

[Klatuu]

..sdrawkcab ti gniod era uoY

Obviously, you can't reference the report's control when it is not open;
however, as long as the form stays open while the report runs, you can do it
the other way around. That is, put a text box on your form (it can be
hiddend) and load the text you want into it. The in the report, make the
control source of the text box you want to display it in reference the form:
[Forms]![MyFormName]![MyControlName]

 

[perky2go]

Thanks, I'll try looking at if from the other way around. Seems like that
will require less data manipulation too, since the bulk of our letters
contain static required language.

And I'm thinking (correctly?) that I can then also use the Report's format
event--I'll take a look at the sequence of events with the sections & footers
& page breaks--to sometimes print a second page containing data detail?

.sdrawkcab ti gniod era uoY

Obviously, you can't reference the report's control when it is not open;
however, as long as the form stays open while the report runs, you can do it
the other way around. That is, put a text box on your form (it can be
hiddend) and load the text you want into it. The in the report, make the
control source of the text box you want to display it in reference the form:
[Forms]![MyFormName]![MyControlName]

I've created a report to use as a form letter; have a table containing the
records/fields needed in the letter; want to use a push button on a form to
set off merging the table data with the report to produce the letters. I
found a neat Replace() function that should build the contents of a label's
caption so that the basic ltr contents can be used for different occasions,
but I'm stymied in seeing if it will work because I can't get beyond Run Time
Error 438 (Object doesn't support this method or property) in recognizing my
reference to the label. It appears that the report needs to be open before I
can do anything with it, but opening the report didn't help with the error.
I'm using Access 97. Here's what I've got:

Function is on the Click event of the Form's command button
Table containing data is "LtrStaging"
Report name is "LtrRefund"
Label control is named "lblAddr"

Initial intent is to use the Replace() to string together the pieces of the
address (with one line or 2 for the street addr). The label contains
"tokens" for the address pieces, with "Provider" being the first, getting
it's data from the table field "PayToName".

Function FillOutLetter()
Dim D as database, R as recordset, C as control, Rpt as report
Set D = dbengine.workspaces(0).databases(0)
Set R = D.openrecordset("LtrStaging")
DoCmd.OpenReport "LtrRefund", acViewDesign

'Here's the line creating the error:
Set C = Reports.[LtrRefund].lblAddr
Do Until R.eof
C.Caption = Replace(C.Caption, "[Provider]", PayToName)
R.MoveNext
Loop
End Function


Here's the called function, placed in the FormModule:
Public Function Replace(Block, Token, ReplaceText)
Dim StartToken As String
Dim EndToken As String
Dim Front As String
Dim Back As String

StartToken = InStr(Block, Token) - 1
EndToken = StartToken + Len(Token) + 1
Front = Left(Block, StartToken)
Back = Mid(Block, EndToken)

Replace = Front & ReplaceText & Back

End Function

I'm probably missing some basic step/concept, but of course I don't know
what. Thanks!

 

[perky2go]

Okay--thanks for the explanation. Timing is everything! I'll reread the
section on what all is actually occuring when you open a report and the data
is read into it, with extra attention on the format event.

I've created a report to use as a form letter; have a table containing the
records/fields needed in the letter; want to use a push button on a form to
set off merging the table data with the report to produce the letters. I
found a neat Replace() function that should build the contents of a label's
caption so that the basic ltr contents can be used for different occasions,
but I'm stymied in seeing if it will work because I can't get beyond Run Time
Error 438 (Object doesn't support this method or property) in recognizing my
reference to the label. It appears that the report needs to be open before I
can do anything with it, but opening the report didn't help with the error.
I'm using Access 97. Here's what I've got:

Function is on the Click event of the Form's command button
Table containing data is "LtrStaging"
Report name is "LtrRefund"
Label control is named "lblAddr"

Initial intent is to use the Replace() to string together the pieces of the
address (with one line or 2 for the street addr). The label contains
"tokens" for the address pieces, with "Provider" being the first, getting
it's data from the table field "PayToName".

Function FillOutLetter()
Dim D as database, R as recordset, C as control, Rpt as report
Set D = dbengine.workspaces(0).databases(0)
Set R = D.openrecordset("LtrStaging")
DoCmd.OpenReport "LtrRefund", acViewDesign

'Here's the line creating the error:
Set C = Reports.[LtrRefund].lblAddr
Do Until R.eof
C.Caption = Replace(C.Caption, "[Provider]", PayToName)
R.MoveNext
Loop
End Function


Here's the called function, placed in the FormModule:
Public Function Replace(Block, Token, ReplaceText)
Dim StartToken As String
Dim EndToken As String
Dim Front As String
Dim Back As String

StartToken = InStr(Block, Token) - 1
EndToken = StartToken + Len(Token) + 1
Front = Left(Block, StartToken)
Back = Mid(Block, EndToken)

Replace = Front & ReplaceText & Back

End Function

I'm probably missing some basic step/concept, but of course I don't know
what.

The something basic is that you need to manipulate the
contents of the report using code in the report's event
procedure. Normally this is done in the Format event of the
section that contains the control you want to manupulate.

Because the report has not done much more than initial open
processing when control is returned from the OpenReport
method, it's just too soon to do what you want that way.
Even if the OpenReport method delayed returning control to
your procedure, it would then be too late because report
formatting would already have started.