Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report Preview Problem (A2K / A3K)

    I have a control button whose code previews a report which displays data with respect to the current record. After previewing the report, I close the previewed report or print it, and then return to the form.

    Problem:
    In Access 2000, no problem.

    In Access 2003, when I return to the form, it is "frozen" and the only way out is to actually exit Access altogether. And that is not a good thing.

    The following references are found in both versions:

    Visual Basic for Applications
    Microsoft Access 9.0 Object Library
    OLE Automation
    Microsoft ActiveX Data Objects 2.1 Library
    Microsoft DAO 3.6 Object Library
    Microsoft Visual Basic for Applications Extensibility 5.

    The following is the code used:

    Private Sub prvReceivedReport_Click()
    Dim strCustomerID1 As String
    Dim rpt1 As Access.Report
    Dim strFilter1 As String
    strCustomerID1 = Me![Part_Mstr_No]
    strFilter1 = "[Part_Mstr_No] = " & Chr$(39) & strCustomerID1 & Chr$(39)
    Debug.Print "Filter: " & strFilter1
    DoCmd.OpenReport "rpt Inventory - Received", acViewPreview
    Set rpt1 = [Reports]![rpt Inventory - Received]
    rpt1.FilterOn = True
    rpt1.Filter = strFilter1
    rpt1.Caption = "Customer Information for Customer ID " & strCustomerID1
    ErrorHandlerExit:
    Exit Sub
    ErrorHandler:
    MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
    Resume ErrorHandlerExit
    End Sub


    I'd appreciate any suggestions,
    Cheers,
    Andy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report Preview Problem (A2K / A3K)

    I don't have Access 2003 (nor Access 3K <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>), so I have no idea about specific things that could go wrong there, but I have some remarks:
    - I hope you have a reference to the Microsoft Access 11.0 Object Library while in Access 2003, otherwise something is very wrong.
    - I hope that SP1 of Access 2003/Office 2003 has been installed, it corrects a lot of problems from the initial release.
    - You should always set object variables to Nothing at the end of the code:

    ErrorHandlerExit:
    Set rpt1 = Nothing
    Exit Sub
    ErrorHandler:

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Preview Problem (A2K / A3K)

    Hans,

    I should have looked at my references more closely. Yes, I'm using Microsoft Access 11.0 Object Library, I do have SP1 installed, and I added your line of code.
    That's the good news. Still getting the "frozen" screen. Thank you for your suggestions, I'll be away from my system for a few hours, so maybe Access's answer to the Tooth Fairy will come along and leave a solution under my keyboard,
    Cheers,
    Andy

  4. #4
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Preview Problem (A2K / A3K)

    You are attempting to set a filter on a report whilst not in design view. I do not think you can do this. You should be opening your report and passing in a Where clause (without the Where)
    e.g.
    DoCmd.OpenReport "rpt Inventory - Received", acViewPreview, strFilter1

    Remove the lines after that referring to the FilterOn property etc.

    HTH
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Preview Problem (A2K / A3K)

    Steve,

    Thanks most kindly for the dead on suggestion. The changes worked. Strangely, though, the original code worked in A2000, but threw up electroncially in A2003. Go figure. Anyway, thanks again,
    Cheers,
    Andy

  6. #6
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Preview Problem (A2K / A3K)

    Steve,

    We need to revisit this code as I ran into a slight anomaly when I started doing more testing.

    The original premise is that I locate a part number on the Main Form, ie. A200 and then press the command button to preview the report that is to show all instances of this part number being received that exist.
    Like I said further testing ended up displaying all the received records for all the part numbers, and displaying the first Part Number in the file, A100. This is not what I had in mind. Again, it's weird, the original coding worked in A2000, but not A2003.

    So to reiterate: the original code was:

    Private Sub prvReceivedReport_Click()
    Dim strCustomerID1 As String
    Dim rpt1 As Access.Report
    Dim strFilter1 As String
    strCustomerID1 = Me![Part_Mstr_No]
    strFilter1 = "[Part_Mstr_No] = " & Chr$(39) & strCustomerID1 & Chr$(39)
    Debug.Print "Filter: " & strFilter1

    DoCmd.OpenReport "rpt Inventory - Received", acViewPreview
    Set rpt1 = [Reports]![rpt Inventory - Received]
    rpt1.FilterOn = True
    rpt1.Filter = strFilter1

    rpt1.Caption = "Customer Information for Customer ID " & strCustomerID1
    ErrorHandlerExit:
    Exit Sub
    ErrorHandler:
    MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
    Resume ErrorHandlerExit
    End Sub

    The current code which is not working is:

    Private Sub prvReceivedReport_Click()
    Dim strCustomerID1 As String
    Dim rpt1 As Access.Report
    Dim strFilter1 As String
    strCustomerID1 = Me![Part_Mstr_No]
    strFilter1 = "[Part_Mstr_No] = " & Chr$(39) & strCustomerID1 & Chr$(39)
    Debug.Print "Filter: " & strFilter1

    DoCmd.OpenReport "rpt Inventory - Received", acViewPreview, strFilter1

    Set rpt1 = [Reports]![rpt Inventory - Received]
    rpt1.Caption = "Customer Information for Customer ID " & strCustomerID1
    ErrorHandlerExit:
    Set rpt1 = Nothing ' added this line as suggested by Hans
    Exit Sub
    ErrorHandler:
    MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
    Resume ErrorHandlerExit
    End Sub


    Would appreciate any help on this,

    =================================

    Steve,

    Ignore the above, unless you're really masochistic. I found the following code that had been posted by Hans. I changed the names to suit the current situation and it looks like this:

    DoCmd.OpenReport "rpt Inventory - Received", acViewPreview, , "Part_Mstr_No = " & Chr(34) & Me.Part_Mstr_No & Chr(34)

    To add injury to insult, after I found this snippet AGAIN, I realized that I had not only seen it before but had used it elsewhere in my system.

    The mind is the second thing to go.....

    Appreciated your previous help,
    Cheers,
    Andy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •