asked on

Access 2016 Report Page Header VBA code - How do I pull a value from a field from the Record Source that is binded to the report?

I usually code in VB.net. I am working on an existing application that is in Access 2016. I need to add a report to it. I am struggling with the correct syntax to reference a field from the table that is binded to the report.

The Reports name is Workflow_prt and its binded Record Source is an Access table named rpt_wrk_flw_prt_out. I am trying to get the value of fields in that table named "rec_tm" and "status". I want to set the text boxes in the Page Header to formatted version of the field values (rec_tm) or a literal value based on the those values (status).

Here is the code and some of the things I have tried:

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

' Record time
'txtRecTm = CStr(Format(rpt_wrk_flw_prt_out.Fields("rec_tm"), "hh:mm tt"))
txtRecTm = CStr(Format(Me!rec_tm, "hh:mm tt"))

' Status
If rpt_wrk_flw_prt_out.Fields("status") = "1" Then
   txtStatus = "OPEN"
ElseIf rpt_wrk_flw_prt_out.Fields("status") = "2" Then
   txtStatus = "CLOSED"
ElseIf rpt_wrk_flw_prt_out.Fields("status") = "3" Then
   txtStatus = "CANCELED"
Else
   txtStatus = " "
End If

End Sub

Open in new window

Thanks

John Tsioumpris

better put it in the onprint event...and hit print preview

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PatHartman

sqdperu,
If you found my answer helpful, please select it as the answer and close the question so it doesn't become abandoned.

sqdperu

ASKER

Pat,

It's unfornate that you cannot directly access the all the fields in a record source without a control on the form. I took your advice and put an invisible control on the form (and colored it yellow). I am now able to run the select case statement as long as the record set contains data. If it is empty I get error "You entered an expression that has no value.".
What code do I put in the Page Header to detect an empty recordset/table so I can "Exit Sub"? See the commented code on some of the things I tried:

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

'If DCount(action_id, rpt_wrk_flw_prt_out) < 1 Then
'If rpt_wrk_flw_prt_out Is Empty Then
'If Me.action_id Is Null Then
'If Me.RecordSource Is Empty Then
'   Exit Sub
'End If

' Status
Select Case Me.txtTblStatus
    Case 1
        Me.txtStatus = "OPEN"
    Case 2
        Me.txtStatus = "CLOSED"
    Case 3
        Me.txtStatus = "CANCELED"
    Case Else
        Me.txtStatus = " "
End Select

' Time Elapsed
Me.txtTmElp = elapsed_time_ext(Me.txtTblTask_rec_dt, Now)

End Subcode

Open in new window

Thanks

SOLUTION

John Tsioumpris

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PatHartman

Having to make the bound control doesn't have anything to do with the no data problem.

Here is the typical way to handle a report with no data:

Private Sub Report_NoData(Cancel As Integer)
    MsgBox "No data was found for this report.", vbOKOnly
    Cancel = True
End Sub

Open in new window

SOLUTION

John Tsioumpris

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sqdperu

ASKER

It does not seem practical to have to put on your report an invisible text box bound to a field in your result set/table to be able to use it in code, but apparently that is what is required in Access. Like Pat says, make the background yellow so you know that it is not on the form for display purposes. The code John provided for the Pager Header is what I wanted and worked.

Thanks guys