473,320 Members | 1,853 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,320 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 1966
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.