Search code examples
vbams-accessreportms-access-2013

How do I assign a value to an unbound textbox when opening report in VBA?

I am using Access 2013 and have a report that I am trying to set the values of eight unbound textboxes when it gets opened. In the code I have written so far, I have opened a recordset and used it to calculate the value of eight items. This part of code works fine. Now I want to get these eight values into the report where they will be used in a calculation.

The first unbound textbox name in the report is "CivPistonUnder"

Here is the code:

Dim strDocName As String
Dim lngPistonUnder As Long

strDocName = "rptAWMP"
DoCmd.OpenReport strDocName, acViewPreview, , , acWindowNormal
'this is where I want to set the value of the unbound textbox
'I have tried
Me.CivPistonUnder = lngPistonUnder
'I get a compilation error: Method or data member not found
'I tried
Set CivPistonUnder = lngPistonUnder
'I get Compile error: Object required
'I tried
Set Report.rptAWMP!CivPistonUnder = lngPistonUnder
'I get Object Required

Is there a way to pass this info to the report?

Solution

  • You can't manipulate data on a report after it has been opened in print preview. It simulates printing, you can't change anything after it has been sent to the printer too. :)

    You must do this in a report event, e.g. Report_Load.

    Private Sub Report_Load()
    ' This will work
    Me.txtUnbound.Value = "Hello World!"
End Sub

    To pass the values to the event procedure, you can e.g. use TempVars, or a Public array / collection.