MsgBox development

[BrianM2]

Hi,

I'm using this code to power a form button which previews reports generated from a filtered list of instruments. The feeder query is a date order listing of instruments which are of a number of types each type requiring a different report. The code steps through the list matching the report to the instrument. The MsgBox pauses the program to enable the user to check that all the correct instruments have been selected by the filter before hitting another button to print all the selected records in one batch.

I would like the MsgBox to include an abort button to stop the code. However if I try to use the MsgBox (xxxxx,xxxxxx,xxxxx,xxxxxx,) style suggested by Access it will not compile. I get an error message like "Expression expected."

Can someone assist?

Private Sub cmdPreviewAllInstrInspFilter_Click()
On Error GoTo Err_cmdPreviewAllInstrInspFilter_Click
 
    Dim rst As Recordset
    Dim stDocName As String
    Dim rstFilter As Recordset
    Set rst = Me.RecordsetClone
    Set rstFilter = rst.OpenRecordset()
    rstFilter.MoveFirst
    
    Do
                        
        Select Case rstFilter!ExType1
            Case "d"
                stDocName = "rptInstrInspD"
            Case "n"
                stDocName = "rptInstrInspENM"
            Case "i"
                stDocName = "rptInstrInspI"
             Case "v"
                stDocName = "rptInstrInspV"
            Case Else
                stDocName = "rptInstrInspENM"
        End Select

        DoCmd.OpenReport stDocName, acPreview, , "[ID]=" & rstFilter![ID]
        MsgBox "Press Enter for next Report"
        DoCmd.Close acReport, stDocName, acSaveNo
        'Debug.Print rstFilter!ExType1
                rstFilter.MoveNext

    Loop Until rstFilter.EOF
    
Exit_cmdPreviewAllInstrInspFilter_Click:
    Exit Sub

Err_cmdPreviewAllInstrInspFilter_Click:
    MsgBox Err.Description
    Resume Exit_cmdPreviewAllInstrInspFilter_Click
End Sub

 

[Rank Am]

If you are using the MsgBox with brackets you need to declare a variable to capture and hold the response.
If you use brackets and don't have an expression this is the cause of the error.

Dim myVar as Long

myVar = MsgBox("XXXXX",vbAbortRetryIgnore+vbCritical,"XXXXX")
You can then use a select case or if Then statement based upon the result

if it doesn't matter whatt the user response is use
MsgBox "XXXXX",vbAbortRetryIgnore+vbCritical,"XXXXX" leaving out the parentheses

Regards

Jon

 

[BrianM2]

Hi Jon,

Thank you for the help. When I found that a two button OK/Abort box was not available I opted for a standard box with a keyboard abort instruction. As you suggested I used the Statement in lieu of Function syntax.

Final code is shown below.

Best regards

Brian

    Dim rst As Recordset
    Dim stDocName As String
    Dim rstFilter As Recordset
    Set rst = Me.RecordsetClone
    Set rstFilter = rst.OpenRecordset()
    rstFilter.MoveFirst
    
    Do
                        
        Select Case rstFilter!ExType1
            Case "d"
                stDocName = "rptInstrInspD"
            Case "n"
                stDocName = "rptInstrInspENM"
            Case "i"
                stDocName = "rptInstrInspI"
            Case "v"
                stDocName = "rptInstrInspV"
            Case Else
                stDocName = "rptInstrInspENM"
        End Select

        DoCmd.OpenReport stDocName, acPreview, , "[ID]=" & rstFilter![ID]
        MsgBox "Press OK (or Press <ENTER> key) for next Report. Press <ESCAPE> key to stop preview", , "RECORD STEPPING"
        DoCmd.Close acReport, stDocName, acSaveNo
        'Debug.Print rstFilter!ExType1
                rstFilter.MoveNext

    Loop Until rstFilter.EOF