473,224 Members | 1,540 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,224 software developers and data experts.

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 1963
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

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

Similar topics

2
by: Wayfarer | last post by:
I am using Microsoft Access 2000. I am designing a report using an aggregate query parameterized on a date range. I am using a form for users to select the range, allowing "First Quarter 2005"...
0
by: charles_weaver | last post by:
I create a Code Comment Web Report for a C# ( or C++) project. All of the files are there, one for each method. But when I run the meain page it doesn't seem to hook together. I get to the page...
0
by: Greg | last post by:
Hello, I'm about to write a database application and I'm wondering how to approach it. Is it worth to use the database code auto-generation (VC# 2005) in the long run? Or at some point I'll be...
2
by: hb | last post by:
Hi, The auto-formatting of HTML code in Visual Studio.Net 2003 is very annoying! I already unchecked the 2 checkboxes in "Apply Automatic Formatting" section located at: Tools-->Options-->Text...
0
by: Andreas | last post by:
Hi there! The splendid Code Comment Web Report is only available in C#. I request it in VB.net. The documentation states: "Currently, only C# supports the code comment syntax required for...
2
by: Miguel | last post by:
I have reviewed the many postings on this topic and understand the principle, but am not sure where in my case to apply trapping an error. I have created a form to supply parameters to a report. I...
1
by: Scott | last post by:
Hello- I have a contol button on a form that I want to code to open a report (rptReviews), then display the print dialogue box so that users can select a printer. This report needs color, but...
7
by: martin DH | last post by:
Hello, I have a report that I open that pull its data from a form that builds a where string. Opening the report first opens the form, where I enter criteria, and then pulls matching records from a...
4
by: Phil Stanton | last post by:
I am opening a report (in Preview) from a menu system I use the following code if there is no data in a report Private Sub Report_NoData(Cancel As Integer) MsgBox "There are no errors in...
4
by: pacarv | last post by:
I've created a database in Access 2003 with multiple reports that are accessable from the switchboard. The switchboard also has a print button. The user selects the report from the menu which then...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.