Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    cfobare79 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Dec 2014
    Posts
    66

    Question Combo Box Selection Opens Different Reports


    I have a form with a combo box. For each selection from the drop down I would like a specific report to open when the "Open" button is selected. I'm not sure if this needs to be written in VBA or is a simple IFF statement can be created. There are 7 different selections that can be made to create 7 different reports.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I did something like that. see attached. It should give you some ideas.
    Attached Thumbnails Attached Thumbnails formtoPrintReports.jpg  

  3. #3
    cfobare79 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Dec 2014
    Posts
    66
    Thank you very much but I'm not sure what some of the terms are referring to. I'm not sure what I need to change to my titles and what stays the same. I'm also very lost at to what the -32764 does.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Copy this into your query wizard window (sql) and run it. It will list all of your reports.

    Code:
    SELECT MSysObjects.Name, 'report' AS objtyp FROM MsysObjects 
    WHERE [Name] not Like '~*'   AND  (MSysObjects.Type)= -32764;

  5. #5
    cfobare79 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Dec 2014
    Posts
    66
    Worked great

  6. #6
    cfobare79 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Dec 2014
    Posts
    66
    The code above do make the Combobox drop down with the report name. How do I get the reports to on now? I have a button but I'm not sure what the Event should look like.

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here's the code behind the button
    Code:
    Private Sub Command2_Click()
    10    On Error GoTo Err_Command2_Click
              Dim stDocName As String
              Dim iOption As Integer
    20        stDocName = Me.Combo0.Value
              'frame optPreview = 1  (Preview)
              'frame optPrint   = 2  (Normal)
    30       iOption = IIf(Me.Frame3 = 1, acViewPreview, acViewNormal)
             'Debug.Print iOption 'for debugging
             
    40       DoCmd.OpenReport stDocName, iOption
    Exit_Command2_Click:
    50        Exit Sub
    Err_Command2_Click:
    60        MsgBox Err.Description
    70        Resume Exit_Command2_Click
              
    End Sub
    Also note there i a frame (called Frame3) on the form which allows for Preview or Print
    there are 2 options
    'frame optPreview = 1 (Preview)
    'frame optPrint = 2 (Normal)

    Hope this helps

  8. #8
    cfobare79 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Dec 2014
    Posts
    66
    That helped a lot but I'm not sure how you go about keeping it from auto printing and it doesn't open the report now?

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  10. #10
    cfobare79 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Dec 2014
    Posts
    66
    I'm getting "Compile error" that highlights the "frame3".

  11. #11
    cfobare79 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Dec 2014
    Posts
    66
    I'm not sure what I have to do with the "Frame3"? Do I have to set up selection box to choice options 1 or 2?

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    If you look at post 2,Frame 3 is the control in the upper right of the form.
    Enable the form wizard.
    Put a frame on the form, then add option buttons.

    see attached
    Attached Thumbnails Attached Thumbnails Frame3.jpg  

  13. #13
    cfobare79 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Dec 2014
    Posts
    66
    I'm still coming up with the error "Object doesn't support this property or method". I have the option box "Frame3" and set the values to 1 and 2.

    Drop down box (Report to be Opened) = Combo13
    Button to Open = BillReport
    Form = Filter

    Click image for larger version. 

Name:	Capture.PNG 
Views:	16 
Size:	187.8 KB 
ID:	20016

  14. #14
    cfobare79 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Dec 2014
    Posts
    66

    Test DB

    Attached is the file and the Form "Filter" is what I'm having issues with.

  15. #15
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Try it now. You had it with Frame 3 on the label/caption, but I think it was really frame12.

    Let me know.
    Attached Files Attached Files

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 04-09-2014, 06:57 PM
  2. Replies: 3
    Last Post: 07-03-2013, 10:38 AM
  3. Combobox won't allow selection when form first opens
    By Monterey_Manzer in forum Forms
    Replies: 4
    Last Post: 11-24-2012, 12:33 PM
  4. Replies: 1
    Last Post: 10-30-2012, 10:29 AM
  5. Replies: 3
    Last Post: 10-13-2011, 01:51 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums