Access abends after DoCmd.OpenReport

Question

A report is called from VBA to receive returned records from an Access pass-through query. After the DoCmd completes the report's parameters are set in the report's appropriate label containers setting their .Caption property as required. Access fails intermittently during this process which leads me to believe that the report is not truly open to receive the parameters. Here's the VBA sub:

Private Sub Report_Open(Cancel As Integer)

    Dim strFromDate     As String
    Dim strToDate       As String
    Dim strWC           As String
    Dim intShift        As Integer
    Dim strSQL          As String

    strFromDate = InputBox("Enter From Date and Time: ")
    strToDate = InputBox("Enter To Date and Time: ")
    strWC = InputBox("Enter Work Center: ")
    intShift = InputBox("Enter Shift: ")

    strSQL = "exec dbo.uspWorkCentreReport_TEST " & "'" & strFromDate & "', " & "'" & strToDate & "', " & "'" & strWC & "', " & intShift & ";"

    CurrentDb.QueryDefs("ptq_uspWorkCentreReport").SQL = strSQL

    DoCmd.OpenReport "rpt_qry_ptq_uspWorkCentreReport", acViewReport

    Me.lblFromDate.Caption = strFromDate
    Me.lblToDate.Caption = strToDate
    Me.lblWC.Caption = strWC
    Me.lblShift.Caption = intShift

End Sub

When the failure occurrs VBA highlights the Me.lblFromDate.Caption = strFromDate. If I press Reset in VBA or End on the Run-time error '2467': dialog, Access abends without any other outward signs. Access then re-opens to save the copied *_Backupx.accdb and opens with a fresh copy of the .accdb. The error seems to be a standars MS error: As I said the report is intermittent and when it fails VB always highlights the same line in code. How do I capture what is happening or can I make VB wait a half of full second before it tries to write the parameters?

Instead of popup input boxes, have users enter criteria into controls on form. Then instead of trying to set label Caption property, have textbox reference the form control. Textbox acts as 'label'. Otherwise, have to use report Format event to set control properties. Format event triggers only for PrintPreview or direct to printer. Should have code to set QueryDefs behind a form. — June7, Jun 05 '19 at 01:18
What report are you running this code? The same one in `OpenReport`? Why are you using `inputbox` and not an Access form? This isn't Excel! — Parfait, Jun 05 '19 at 01:19
Does `Me.lblFromDate` exist? It sounds to me from that error message that either the label doesn't exist, or you are not using it correctly. In a excel userform, that's correct, but not so sure in your report. You can always comment out docmd for debugging purpose, I doubt has anything to do with that. — FAB, Jun 05 '19 at 04:42

score 0 · Answer 1 · answered Jun 05 '19 at 10:18

As I remember, captions can not be modified, when report open. Only in design mode. So this is not correct, because you have already opened report

Me.lblFromDate.Caption = strFromDate

You should use text boxes instead of captions. Also you can clear the borders, fillings and so on, that text box will appear like a caption.

Tim · Accepted Answer · 2019-06-11T15:10:39.870

Finally the correct set of code was produced. The button click creates strOpenArgs and passes it with .OpenReport. The report opens and splits the OpenArgs and populates the appropriate labels with updated Captions. Text boxes would not work! Here's the button click event:

Private Sub btnPreviewP1_Click()

    If (Me.txtToDateP1 < Me.txtFromDateP1) Then
        MsgBox ("The From Date must occurr before the To Date!")
    End If

    Dim strFromDateHMS  As String
    Dim strToDateHMS    As String
    Dim strSQLP1    As String
    Dim strOpenArgs As String

    strFromDateHMS = Format(Me.txtFromDateP1, "yyyy-mm-dd") & " " & Me.cboFromHourP1 & ":" & Me.cboFromMinuteP1 & ":" & Me.cboFromSecondP1
    strToDateHMS = Format(Me.txtToDateP1, "yyyy-mm-dd") & " " & Me.cboToHourP1 & ":" & Me.cboToMinuteP1 & ":" & Me.cboToSecondP1

    strSQLP1 = "exec dbo.uspWorkCentreReport '" & strFromDateHMS & "','" & strToDateHMS & "','" & strWCP1 & "'," & strShiftP1

    strOpenArgs = Me.RecordSource & "|" & strFromDateHMS & "|" & strToDateHMS & "|" & strWCP1 & "|" & strShiftP1

    ' This line is all that's needed to modify the PT query
    CurrentDb.QueryDefs("ptq_uspWorkCentreReport").SQL = strSQLP1

    DoCmd.OpenReport "rpt_ptq_uspWorkCentreReport", acViewReport, , , , strOpenArgs

End Sub

And here's the reports _Open:

Private Sub Report_Open(Cancel As Integer)
    Dim SplitOpenArgs() As String
    SplitOpenArgs = Split(Me.OpenArgs, "|")
    Me.lblFromDate.Caption = SplitOpenArgs(1)
    Me.lblToDate.Caption = SplitOpenArgs(2)
    Me.lblWC.Caption = SplitOpenArgs(3)
    Me.lblShift.Caption = SplitOpenArgs(4)
End Sub

This opens the report every time with new appropriate data, so long as the report is closed before the form's button is pressed again for another refresh of the report. If the report is not closed the report stays up with the original data and does not refresh with new data... But that is another question I am about to ask. Thanks All.

Access abends after DoCmd.OpenReport

2 Answers2

Linked