Reports!StandardInvoice!BIType="Invoice" - not executing as expect

[Guest]

In the following event procedure, I'm trying to set the value of a report
textbox named 'BIType' to the string value "Invoice" when the report is
previewed. The report previews just fine when the button btnBillToAccount is
pressed, with the exception that the textbox BIType on the report is blank.
Why doesn't textbox BIType display the string "Invoice" ?

Private Sub btnBillToAccount_Click()

On Error GoTo Err_PreviewInvoice_Click
DoCmd.OpenReport "StandardInvoice", acViewPreview, , "[JobID]=" & [JobID]
Reports!StandardInvoice!BIType = "Invoice"

Exit_PreviewInvoice_Click:
Exit Sub

Err_PreviewInvoice_Click:
MsgBox Err.Description
Resume Exit_PreviewInvoice_Click
End Sub

 

[strive4peace]

Hi Jay,

if the BIType control on your report gets its value from a form, use
this for the ControlSource in the report

=Forms!Formname!controlname

when you are assigning a controlsource, don't forget to start with an
equal sign and include delimiters, "='Invoice'"

....but I believe assingments need to be made from the design view or
code behind the report on open

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

Hi Crystal -

Thanks for the reply. Unforturnately, the value that I'd like to assign to
the report textbox control isn't on a form; it's generated programmatically
in the code of a button's Click event procedure. In the code I provided
previously, I tried unsuccessfully to assign the string value "Invoice"
directly to the report textbox (Reports!StandardInvoice!BIType).

I've therefore modified my approach to pass the value "Invoice" from the
button_Click event procedure to the report's Open event procedure (as you
suggested) using the OpenArgs arguement of the OpenReport method as:

--------------------------------------------------------------------------------------------------
Private Sub btnBillToAccount_Click()
'When this btn is pressed, set s_label to "INVOICE"
s_label = "INVOICE"
'Pass s_label to the report open event as OpenArgs arguement of OpenReport
method
DoCmd.OpenReport "StandardInvoice", acViewPreview, , "[JobID]=" & [JobID],
, s_label
End Sub
--------------------------------------------------------------------------------------------------

So far, so good. The value is passed correctly to the following Report_Open
event, but I get the error message "You can't assign a value to this object."
when the event fires:

--------------------------------------------------------------------------------------------------
Private Sub Report_Open(Cancel As Integer)
'BIType is the name of the textbox control on the report
Me.BIType = Me.OpenArgs <----------- ERROR OCCURS HERE.
End Sub
--------------------------------------------------------------------------------------------------
Note: when the error occurs, Debug shows that the value of Me.OpenArgs is
correct ("INVOICE"). So, I suspect that my syntax for referencing the BIType
textbox control (Me.BIType) is wrong or I'm trying to do something out of
context.

--
Jay

Hi Jay,

if the BIType control on your report gets its value from a form, use
this for the ControlSource in the report

=Forms!Formname!controlname

when you are assigning a controlsource, don't forget to start with an
equal sign and include delimiters, "='Invoice'"

....but I believe assingments need to be made from the design view or
code behind the report on open

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

In the following event procedure, I'm trying to set the value of a report
textbox named 'BIType' to the string value "Invoice" when the report is
previewed. The report previews just fine when the button btnBillToAccount is
pressed, with the exception that the textbox BIType on the report is blank.
Why doesn't textbox BIType display the string "Invoice" ?

Private Sub btnBillToAccount_Click()

On Error GoTo Err_PreviewInvoice_Click
DoCmd.OpenReport "StandardInvoice", acViewPreview, , "[JobID]=" & [JobID]
Reports!StandardInvoice!BIType = "Invoice"

Exit_PreviewInvoice_Click:
Exit Sub

Err_PreviewInvoice_Click:
MsgBox Err.Description
Resume Exit_PreviewInvoice_Click
End Sub

 

[strive4peace]

Hi Jay,

convert the control on your report to a Label (easier to work with this
way) instead of a Textbox (right-click in Design view, choose ChangeTo-->)

make the caption one space

then, in the report Open code

Me.BIType.caption= nz(Me.OpenArgs,"")


as a textbox, but you would need to do something like this:

Me.BIType= "='" & nz(Me.OpenArgs,"") & "'"

(but I have better success changing the label caption)


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

Ta Daahhh !! Worked like a charm, Crystal.

Ya know, I've noticed that others prefer labels instead of textboxes in
reports and I guess now I understand why. I was just too stubborn to try it.

If generating a control's value in code, is there any advantage to using a
textbox instead of a label ? I can't see any advantage; it appears that you
can programmatically manipulate the formats of both equally.

--
Thanks a bunch for the satisfying solution,
Jay

Hi Jay,

convert the control on your report to a Label (easier to work with this
way) instead of a Textbox (right-click in Design view, choose ChangeTo-->)

make the caption one space

then, in the report Open code

Me.BIType.caption= nz(Me.OpenArgs,"")


as a textbox, but you would need to do something like this:

Me.BIType= "='" & nz(Me.OpenArgs,"") & "'"

(but I have better success changing the label caption)


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi Crystal -

Thanks for the reply. Unforturnately, the value that I'd like to assign to
the report textbox control isn't on a form; it's generated programmatically
in the code of a button's Click event procedure. In the code I provided
previously, I tried unsuccessfully to assign the string value "Invoice"
directly to the report textbox (Reports!StandardInvoice!BIType).

I've therefore modified my approach to pass the value "Invoice" from the
button_Click event procedure to the report's Open event procedure (as you
suggested) using the OpenArgs arguement of the OpenReport method as:

--------------------------------------------------------------------------------------------------
Private Sub btnBillToAccount_Click()
'When this btn is pressed, set s_label to "INVOICE"
s_label = "INVOICE"
'Pass s_label to the report open event as OpenArgs arguement of OpenReport
method
DoCmd.OpenReport "StandardInvoice", acViewPreview, , "[JobID]=" & [JobID],
, s_label
End Sub
--------------------------------------------------------------------------------------------------

So far, so good. The value is passed correctly to the following Report_Open
event, but I get the error message "You can't assign a value to this object."
when the event fires:

--------------------------------------------------------------------------------------------------
Private Sub Report_Open(Cancel As Integer)
'BIType is the name of the textbox control on the report
Me.BIType = Me.OpenArgs <----------- ERROR OCCURS HERE.
End Sub
--------------------------------------------------------------------------------------------------
Note: when the error occurs, Debug shows that the value of Me.OpenArgs is
correct ("INVOICE"). So, I suspect that my syntax for referencing the BIType
textbox control (Me.BIType) is wrong or I'm trying to do something out of
context.

 

[Marshall Barton]

Ta Daahhh !! Worked like a charm, Crystal.

Ya know, I've noticed that others prefer labels instead of textboxes in
reports and I guess now I understand why. I was just too stubborn to try it.

If generating a control's value in code, is there any advantage to using a
textbox instead of a label ? I can't see any advantage; it appears that you
can programmatically manipulate the formats of both equally.

The core issue here is that the Open event is too soon to
set a control's Value. You do what you had, but you needed
to move the code to the Format event procedure of the
section containing the control (or any section that's
processed before that section e.g. report header).

With that said, it's nice to have all of this kind of code
in one place in the Open event, so a label is a good
alternative.

 

[Guest]

Hi again Marshall -

Thanks for the timely input. Once again, you're right on the difinitive
bullseye.

Based on the Access error message, I suspected that I was addressing the
report control out-of-context. Your alternative worked perfectly; I'll be
adding your and Crystal's techniques to my permanent Access arsenal and will
think fondly of you and Crystal whenever I apply them.

 

[strive4peace]

Change Label Caption on Report Open
---

Hi Jay,

you're welcome, Jay

As Marsh pointed out in the next post, to change a data control, you
would have to wait until data is loaded (I opt for the Report Header
event) -- but labels can be changed on the Open event and I find it
makes the most logical sense to do it here.

If you don't need a textbox, I find it best to use labels

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

Hi Crystal -

Thanks for the follow-up. Between you and Marshall, I feel comfortable with
proceeding into the reporting component of the application I'm working on.
You don't know (or maybe you do) how much better I feel with the information
you two have provided in this single thread.

I just spent weeks slogging through the pitfalls of forms coding and was
battle weary from that; within this single thread you and Marshal have
carpet-bombed the reporting beach head for me. I'll hit the beach running
with one less daunting research task.

 

[strive4peace]

you're welcome, Jay happy to help

your appreciation is thanks enough

~~~~~~~~~~~~~~~~~~
to help you understand Access a bit better, send me an email and request
my 30-page Word document on Access Basics (for Programming) -- it
doesn't cover VBA, but prepares you for it because it covers essentials
in Access. I do also send out the first 3 chapters of a book I am
writing on VBA to all who request it.

Be sure to put "Access Basics" or "VBA chapters" in the subject line so
that I see your message...
~~~~~~~~~~~~~~~~~~

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[strive4peace]

very kind of you to say, Jay I am in concurrance on Marsh -- he is
great at being on target (thanks Marsh, you explained it so well and I
will remember too)

"Access arsenal" ... hmmm, is that like "VBA fireworks" <smile>

anyhow, nice to be part of the reason for a productive day

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*