Results 1 to 6 of 6
  1. #1
    burrina's Avatar
    burrina is offline VIP
    Windows 7 64bit Access 2002
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383

    Open Report From List Using Combo Box

    I have a form named ReportList with a unbound combo box on it named List2. It shows all of my reports.
    I also have 2 unbound textboxes for Begin and End Dates for the criteria.
    BeginningOrderDate and EndingOrderDate

    I have a command button named Preview that needs to open the Report Selected.

    I don't want to hard code this into a query, is there another way?

    Here is my code for the OnClick Event of the Preview command button.


    Private Sub Preview_Click()
    If IsNull([BeginningOrderDate]) Or IsNull([EndingOrderDate]) Then
    MsgBox "You must enter both beginning and ending dates."
    DoCmd.GoToControl "Beginning Order Date"
    Else
    If [BeginningOrderDate] > [EndingOrderDate] Then
    MsgBox "Ending date must be greater than Beginning date."
    DoCmd.GoToControl "BeginningOrderDate"
    Else
    Me.Visible = False
    End If
    End If
    End Sub
    Attached Thumbnails Attached Thumbnails ReportList.jpg  

  2. #2
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    I don't want to hard code this into a query, is there another way?
    I doubt it - well, it depends what you mean by 'hard code.'

    I am trying to think why you are not eager to 'hard code.' Maybe it's because you have a number of complex SQL strings, one for each report, and don't fancy the complexity of constructing the SQL for each. There is a 'trick' to alleviate this somewhat that is based on the fact you can query a query.

    Design the basic extract query for each report using the easiest way possible (= design grid?). Do not include sequencing in these designs (i.e. ORDER BY) and make sure that the order date is named the same for each.

    Now you can construct a much simpler SQL string for each report along similar to the following:

    SELECT * FROM qryReport01 WHERE OrderDate >= Start AND OrderDate <= End ORDER BY aaa, bbb, ccc

    Because SQL execution may destroy the sequence of the inner query you have to place it in the outer query. Use BETWEEN if you wish. Beware of dates in Access SQL, it's much the safest to use #mm/dd/yyyy# string format.

    Does this help you at all?

    PS I forgot to say you have to poke this nested SQL sting into the report's record source.
    Last edited by Rod; 11-20-2012 at 12:50 AM. Reason: Added PS

  3. #3
    burrina's Avatar
    burrina is offline VIP
    Windows 7 64bit Access 2002
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    Wow, over my head. Can you not just simply say Me.Report.Name ? Probably dumb question?

  4. #4
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    It's not nearly as bad as it looks - and maybe my explanation is wanting. I'll code up a prototype example and post it for you. Give me a few minutes - well perhaps an hour or so.

  5. #5
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    Here you go. It's even easier than I said because I forgot about the 'Where criteria' in the DoCmd.OpenReport command.

    Database13.mdb

    Hope you can open it in 2002.

    PS 55 minutes! and I stopped for a cup of coffee.
    Last edited by Rod; 11-20-2012 at 03:14 AM. Reason: Added PS

  6. #6
    burrina's Avatar
    burrina is offline VIP
    Windows 7 64bit Access 2002
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    Hey, Thanks a lot. Helped me a lot.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Open report in accordance with combo
    By fabiobarreto10 in forum Reports
    Replies: 2
    Last Post: 05-24-2012, 09:21 AM
  2. Open Report by Combo box
    By rck3 in forum Reports
    Replies: 2
    Last Post: 05-07-2012, 05:29 PM
  3. Open a report based on a filtered list box
    By irish634 in forum Access
    Replies: 5
    Last Post: 02-07-2012, 08:10 AM
  4. Replies: 1
    Last Post: 09-21-2011, 03:21 AM
  5. Using a combo box to open a report
    By Bill Casanova in forum Forms
    Replies: 24
    Last Post: 05-24-2011, 10:42 AM

Tags for this Thread

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