VBA with MS Access Reports

Question

"Me.TextZone = some value" is not allowed with a report, I want be able to use and change controls.

Answer 1

Here are some options that you can try:

1.      Use control Source expressions

Instead of setting a value directly , bind the text box to an expression

=”some value”

Or to a function

=MyCystomFunction()

 

2.      Use the Format or Print Event

If you want to dynamically set values while report is rendering, you can use VBA inside the report’s On Format event of a section:

Private Sub Detail_Format(Cancel as Integer, FormatCount as Integer)

                Me.TextZone.Caption = “some value”

End Sub

Note: for labels, use .Caption

                For unbound text boxes, you can set .Value.

 

3.      Use an Unbound Text Box

Create a text box with no control source, then in the event

Private Sub Details_Format(Cancel As Integer, FormatCount As Integer)

                Me.TextZone.Value = “Dynamic Value”

End Sub

 

4.      Pass Values from a Form to a Report

If you want to show values from a form:

Set the text box’s control source to reference the form:

=[ Forms ]![MyFormName]![MyControlName]

 

You can also refer the following URLs

·         Add a text box control to a form or report - Microsoft Support

·         Order of events for database objects - Microsoft Support

·         SetValue Macro Action - Microsoft Support

Hope this helps, Please let me know if you have any other query and if it helps please mark the answer as answered.