469,150 Members | 1,985 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,150 developers. It's quick & easy.

Access2000: Code to auto open a Report

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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdApplyFilter_Click()
  2.     Dim varItem As Variant
  3.     Dim strAction_Types_Choices As String
  4.     Dim strReason_Choices As String
  5.     Dim strPosition As String
  6.     Dim strFilter As String
  7.     Dim StrSortOder As String
  8.  
  9.     If SysCmd(acSysCmdGetObjectState, acReport, "rpt1") <> acObjStateOpen Then
  10.         MsgBox "You must open the report first."
  11.         Exit Sub
  12.     End If
  13.  
  14.     For Each varItem In Me.LstAction_Type.ItemsSelected
  15.         strAction_Types_Choices = strAction_Types_Choices & ",'" & Me.LstAction_Type.ItemData(varItem) & "'"
  16.     Next varItem
  17.     If Len(strAction_Types_Choices) = 0 Then
  18.         strAction_Types_Choices = "Like '*'"
  19.     Else
  20.         strAction_Types_Choices = Right(strAction_Types_Choices, Len(strAction_Types_Choices) - 1)
  21.         strAction_Types_Choices = "IN(" & strAction_Types_Choices & ")"
  22.     End If
  23.  
  24.     For Each varItem In Me.LstReason.ItemsSelected
  25.         strReason_Choices = strReason_Choices & ",'" & Me.LstReason.ItemData(varItem) & "'"
  26.     Next varItem
  27.     If Len(strReason_Choices) = 0 Then
  28.         strReason_Choices = "Like '*'"
  29.     Else
  30.         strReason_Choices = Right(strReason_Choices, Len(strReason_Choices) - 1)
  31.         strReason_Choices = "IN(" & strReason_Choices & ")"
  32.     End If
  33.  
  34.     For Each varItem In Me.LstPosition.ItemsSelected
  35.         strPosition = strPosition & ",'" & Me.LstPosition.ItemData(varItem) & "'"
  36.     Next varItem
  37.     If Len(strPosition) = 0 Then
  38.         strPosition = "Like '*'"
  39.     Else
  40.         strPosition = Right(strPosition, Len(strPosition) - 1)
  41.         strPosition = "IN(" & strPosition & ")"
  42.     End If
  43.  
  44.     strFilter = "[Action_Types_Choices] " & strAction_Type_Choices & " AND [Reason_Choices] " & strReason_Choices & " AND [Position]" & strPosition
  45.  
  46. If Me.cboSortOrder1.Value <> "Not Sorted" Then
  47.     strSortOrder = "[" & Me.cboSortOrder1.Value & "]"
  48.     If Me.cmdSortDirection1.Caption = "Descending" Then
  49.         strSortOrder = strSortOrder & " DESC"
  50.     End If
  51.     If Me.cboSortOrder2.Value <> "Not Sorted" Then
  52.         strSortOrder = strSortOrder & ",[" & Me.cboSortOrder2.Value & "]"
  53.         If Me.cmdSortDirection2.Caption = "Descending" Then
  54.             strSortOrder = strSortOrder & " DESC"
  55.         End If
  56.         If Me.cboSortOrder3.Value <> "Not Sorted" Then
  57.             strSortOrder = strSortOrder & ",[" & Me.cboSortOrder3.Value & "]"
  58.             If Me.cmdSortDirection3.Caption = "Descending" Then
  59.                 strSortOrder = strSortOrder & " DESC"
  60.             End If
  61.         End If
  62.     End If
  63. End If
  64. With Reports![rpt1]
  65.     .Filter = strFilter
  66.     .FilterOn = True
  67.     .OrderBy = strSortOrder
  68.     .OrderByOn = True
  69. End With
  70. End Sub
  71.  
  72. _______________________________________________________
  73. Private Sub cmdApplyFilter_Click()
  74. On Error GoTo Err_cmdApplyFilter_Click
  75.  
  76.     Dim stDocName As String
  77.  
  78.     stDocName = "rpt1"
  79.     DoCmd.OpenReport stDocName, acPreview
  80.  
  81. Exit_cmdApplyFilter_Click:
  82.     Exit Sub
  83.  
  84. Err_cmdApplyFilter_Click:
  85.     MsgBox Err.Description
  86.     Resume Exit_cmdApplyFilter_Click
  87.  
  88. End Sub
  89.  
  90. Dim Response As VbMsgBoxResult
  91. If SysCmd(acSysCmdGetObjectState, acReport, "rpt1") <> acObjStateOpen Then
  92.     Response = MsgBox("The report is not open." _
  93.         & vbCrLf & "Do you want to open it now?" _
  94.         , vbQuestion + vbYesNoCancel)
  95.     SelectCase Response
  96.         Case vbYes
  97.             DoCmd.OpenReport "rpt1", acViewPreview
  98.         Case vbNo
  99.             Exit Sub
  100.         Case vbCancel
  101.             DoCmd.Close acForm, Me.Name
  102.             Exit Sub
  103.     End Select
  104. End If
Sep 14 '10 #1
6 1739
Stewart Ross
2,545 Expert Mod 2GB
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
Sep 15 '10 #2
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.
Sep 15 '10 #3
Stewart Ross
2,545 Expert Mod 2GB
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
Sep 15 '10 #4
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.
Sep 15 '10 #5
pod
298 100+
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

Sep 15 '10 #6
Thank you for all your help
Sep 15 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Wayfarer | last post: by
reply views Thread by charles_weaver | last post: by
reply views Thread by Greg | last post: by
reply views Thread by Andreas | last post: by
4 posts views Thread by Phil Stanton | last post: by
1 post views Thread by CARIGAR | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.