Access2000: Code to auto open a Report

[SusanK4305]

Here are my two code for the Report that will be opened.
This Report is in Access and is name rpt1, It isn't opening like it should. Please Help... Thank you.

Private Sub cmdApplyFilter_Click()
    Dim varItem As Variant
    Dim strAction_Types_Choices As String
    Dim strReason_Choices As String
    Dim strPosition As String
    Dim strFilter As String
    Dim StrSortOder As String
 
    If SysCmd(acSysCmdGetObjectState, acReport, "rpt1") <> acObjStateOpen Then
        MsgBox "You must open the report first."
        Exit Sub
    End If
 
    For Each varItem In Me.LstAction_Type.ItemsSelected
        strAction_Types_Choices = strAction_Types_Choices & ",'" & Me.LstAction_Type.ItemData(varItem) & "'"
    Next varItem
    If Len(strAction_Types_Choices) = 0 Then
        strAction_Types_Choices = "Like '*'"
    Else
        strAction_Types_Choices = Right(strAction_Types_Choices, Len(strAction_Types_Choices) - 1)
        strAction_Types_Choices = "IN(" & strAction_Types_Choices & ")"
    End If
 
    For Each varItem In Me.LstReason.ItemsSelected
        strReason_Choices = strReason_Choices & ",'" & Me.LstReason.ItemData(varItem) & "'"
    Next varItem
    If Len(strReason_Choices) = 0 Then
        strReason_Choices = "Like '*'"
    Else
        strReason_Choices = Right(strReason_Choices, Len(strReason_Choices) - 1)
        strReason_Choices = "IN(" & strReason_Choices & ")"
    End If
 
    For Each varItem In Me.LstPosition.ItemsSelected
        strPosition = strPosition & ",'" & Me.LstPosition.ItemData(varItem) & "'"
    Next varItem
    If Len(strPosition) = 0 Then
        strPosition = "Like '*'"
    Else
        strPosition = Right(strPosition, Len(strPosition) - 1)
        strPosition = "IN(" & strPosition & ")"
    End If
 
    strFilter = "[Action_Types_Choices] " & strAction_Type_Choices & " AND [Reason_Choices] " & strReason_Choices & " AND [Position]" & strPosition
 
If Me.cboSortOrder1.Value <> "Not Sorted" Then
    strSortOrder = "[" & Me.cboSortOrder1.Value & "]"
    If Me.cmdSortDirection1.Caption = "Descending" Then
        strSortOrder = strSortOrder & " DESC"
    End If
    If Me.cboSortOrder2.Value <> "Not Sorted" Then
        strSortOrder = strSortOrder & ",[" & Me.cboSortOrder2.Value & "]"
        If Me.cmdSortDirection2.Caption = "Descending" Then
            strSortOrder = strSortOrder & " DESC"
        End If
        If Me.cboSortOrder3.Value <> "Not Sorted" Then
            strSortOrder = strSortOrder & ",[" & Me.cboSortOrder3.Value & "]"
            If Me.cmdSortDirection3.Caption = "Descending" Then
                strSortOrder = strSortOrder & " DESC"
            End If
        End If
    End If
End If
With Reports![rpt1]
    .Filter = strFilter
    .FilterOn = True
    .OrderBy = strSortOrder
    .OrderByOn = True
End With
End Sub
 
_______________________________________________________
Private Sub cmdApplyFilter_Click()
On Error GoTo Err_cmdApplyFilter_Click
 
    Dim stDocName As String
 
    stDocName = "rpt1"
    DoCmd.OpenReport stDocName, acPreview
 
Exit_cmdApplyFilter_Click:
    Exit Sub
 
Err_cmdApplyFilter_Click:
    MsgBox Err.Description
    Resume Exit_cmdApplyFilter_Click
 
End Sub
 
Dim Response As VbMsgBoxResult
If SysCmd(acSysCmdGetObjectState, acReport, "rpt1") <> acObjStateOpen Then
    Response = MsgBox("The report is not open." _
        & vbCrLf & "Do you want to open it now?" _
        , vbQuestion + vbYesNoCancel)
    SelectCase Response
        Case vbYes
            DoCmd.OpenReport "rpt1", acViewPreview
        Case vbNo
            Exit Sub
        Case vbCancel
            DoCmd.Close acForm, Me.Name
            Exit Sub
    End Select
End If

[Stewart Ross]

Please let us know what exactly is occurring and what tests you have done so far - 'It isn't opening like it should' does not help us to help you at all.

-Stewart

[SusanK4305]

The error message (by MS Access Assistant)reads as follows:

*The expression may not result in the name of a macro, the name of a user-defined funtion, or [Event Procedure].
*There may have been an error evaluating the funtion,event,or macro


What should happen: When you click Apply Filter it should check for the open report. If a report isn't open it should ask it the person would like to open it. If yes then report should open. and the run filter.

What happens: I click Apply Filter and receive the error message.

What I have done: As far as "test" go nothing. What test can I do? I have however read over the codes many times and as far as I can see it should work. I have also checked the reoprt name. See I am still fig. out about coding so I used templates to do this. The first have works but when I added the secound half it gave me the error message.

[Stewart Ross]

What you need to do is to set a breakpoint at the start of the Apply_Filter routine (around line 9 above, say), then single-step through each line of code until you come to the line which causes the failure you mention.

Until we know which one is failing we'd just be guessing at present.

We have an Insights article on debugging in VBA which you may find useful. The overview is linked here for you.

-Stewart

[SusanK4305]

I don't know what a breakpoint is. however I think I fig. it out. I just had it open the report auto w/o checking or asking.
But for more knowlege I would still love to know how to set a breakpoint. Can you show me what it would look like? Thank you.

[pod]

See link and image below for Breakpoint in VBA

but you can also use msgbox to troubleshoot, by placing it ... let's say in the middle of your script and see if the problem occurs before or after the message box pops up

http://www.learnaccessnow.com/chap20d.html

[SusanK4305]

Thank you for all your help