Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    May 2010
    Location
    Glasgow, Scotland
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi, I'm looking for some help with a database I'm doing. I want to be able to run a selection of diffrent reports from a form and, depending on what report is asked for, the criteria for the query to come from a combination of 7 other drop down menus. So for example I have a list of report types in one combo box and then 7 other combo boxes that run from querys on the table I want the reports from (Start Date, End Date, Regime, Initiating Team, BF Date, Customer and Document Type are the 7 selections that can be made) For the simplest report the selection will be just Start Date and End Date but others will include all selections. Now what I need help with is getting a coimmand button to run the reports based on the report type selection and then for the reports to use the entries in any of the active combo boxes, for example the most basic one I would normally have just entered "between [Start Date] and [End Date]" in the Criteria on the query but I need it to run from the Start Date and End Date from the combo boxes on the form. Hope this makes sense to someone.

    I'm still pretty useless at VB so dummy instructions would be nice.

    Cheers

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    However you do this, it is going to involve some pretty heavy VBA coding I am afraid.

    Just to clarify:
    • Not all criteria apply to all reports. StartDate and EndDate are the only ones that apply to all.
    • Not all applicable criteria need to be used. From among those that can be used, the user will choose the ones they want to use when they run the report.
    I think it needs to be explicit to the user just what criteria are relevant to each report, so I think I would use the After Update Event of the List of reports to selectively enable/disable other combo boxes. And when you disable a combo box set its value back to null.

    Then when you compile your criteria, you don't need to base it on the selected report. Only the criteria relevant to the current report will have values in them.

    So what would the After Update Event look like

    Code:
    Select Case me.ComboReportlist
    
    Case "Report1"
     me.combo1.enabled=true
     me.combo2.enabled =false
     me.combo2=Null
     me.combo3.enabled =true
     me.combo4.enabled=false
     me.combo4=Null
    
    Case "Report2"
     me.combo1.enabled=true
     me.combo2.enabled =true
     me.combo3.enabled =true
     me.combo4.enabled=false
     me.combo4=Null
    
    Case "Report3"
    me.combo1.enabled=true
     me.combo2.enabled =true
     me.combo3.enabled =false
    me.combo3=Null
     me.combo4.enabled=true
     
    End Select
    In all this you need to decide what to enabled and disable when, and change the names.

    I would use a List Box for the list of reports, rather than a combo. With a ListBox users can see the range of options without having to click the dropdown.

    See how you go with getting this to work. We can move on the the next step when this bit is working OK.
    Regards
    John



  3. #3
    Lounger
    Join Date
    May 2010
    Location
    Glasgow, Scotland
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    John,
    Thanks for the reply,your a Star!

    I've got the After Update Event to work fine with everything so that's all good. I'm going to eventually have multiple, like 20+, different reports running from this so I think I'll have to go with drop downs instead of a List Box. Now what I'm looking to do is have a command button that will run the reports using the the combo box selections as the criteria?

    Oh and 1 other thing and this is probably very simple but I've had no success at all today, I want to be able to select all types of record from a combo box as well as specific types and blanks if you know what I mean. For example some reports of the same type will need to be for Regimes, some just for specific regimes and some for regimes that have been left blank. Hopefully this makes sense to you, I'm not that great at explaining.

    Cheers again

  4. #4
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Joliette, Quebec, Canada
    Posts
    290
    Thanks
    9
    Thanked 0 Times in 0 Posts
    have you considered creating a form with drop down boxes then have a report based on a query that referes to the dropdown items of your form, where you could also add a button to open the report. The report title could have text boxes refering to values you selected such as between date1 and date2

    I hope this helps. here is an example http://office.microsoft.com/en-us/ac...730581033.aspx

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Good that it has worked so far.

    Whether you you use a Listbox or Combo for the list of reports is up to you, and does not make any difference, exccept for appearances. I think I still prefer the Listbox, even with 20 reports.

    You need two more things. One is easy, one is more complicated.
    • A command button to open the report. You could use the wizard to build it, then edit the code so that the name of the report is not hardcoded. Instead have some code like this:
    Code:
    If Not IsNull(Me.ListReports) Then
     stDocName = Me.ListReports
     DoCmd.OpenReport stDocName, acViewPreview
    Else
    	MsgBox "Select a Report", vbInformation
    End If
    • Now you have to deal with all the various criteria, and there are two approaches to this:
    1. Use queries that refer to the various controls on your form, as suggested by r3x3
    2. Build a "Where" clause in code.
    I find the second approach the most flexible so I will explain that.
    The line of code that opens a report will accept a Where parameter as shown below. This acts as a filter, and has the same effect as adding whatever it contains as additional criteria to the query. So we don't have to change the query.

    Code:
    DoCmd.OpenReport stDocName, acViewPreview,,Strwhere
    To make all this work you need to write a general procedure that examines all the criteria combos, and turns any that have a value in them, into part of this where clause.

    Code:
    Dim strwhere as string
    Private Sub sbBuildFilter
     strWhere = "([EventDate] Between #" & Format(Me.[comboStartDate], "mm/dd/yyyy") & "# AND #" & Format(Me.[comboEnddate], "mm/dd/yyyy") & "#) and "
    
    if not isnull(me.combo1) then
     strwhere = strwhere & "([field1] =" & me.combo1 & ") and "
    end if
    
    if not isnull(me.combo2) then
     strwhere = strwhere & "([field2] =" & CHR(34) & me.combo2 & CHR(34) & ") and "
    end if
    ' now remove the trailing and
    Strwhere = left(strwhere,Len(strwhere)-4)
    
    End sub
    In the above I am assuming that the Date combos will always have something in them
    In the above the syntax for combo1 is what is used if field1 is a number. Combo2 syntax is the one to use for a text field.
    You need and "and between each part of the where clause, so you add one at the end of each line, then remove the last one at the end.
    Note that Strwhere is decalred outside this procedure, so its value can be used elsewhere. This declaration needs to be at the very top of the module, after
    Code:
    Option Compare Database
    Option Explicit
    Then modify the the report opening code by calling this procedure first.

    Code:
    If Not IsNull(Me.ListReports) Then
    sbBuildFilter
     stDocName = Me.ListReports
     DoCmd.OpenReport stDocName, acViewPreview,,Strwhere
    Else
    	MsgBox "Select a Report", vbInformation
    End If
    Regards
    John



  6. #6
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post
    Here's a solution that is simple, elegant and easy to maintain. (I've attached a sample database)

    All you need is a table to hold the names and needs of each report.

    Then a form that lists these tables and provides controls for users to enter criteria.

    The form uses the table to see what criteria is required for a report and enables the appropriate controls.

    For the developer - its a breeze.

    Just add the new report to the table and check the fields for required criteria.

    If you need a new criteria, then add a new field to the table, a new control to the form and two lines of code to the On Current event of the form.

    That's it.

    There is even more detail, like checking that the user has entered all the required criteria in the sample database I've added here.

    errata - in the code behind the Print button, the line that reads
    If IsNull(ctl) Then
    should read -
    If ctl.Enabled And IsNull(ctl) Then
    Attached Files Attached Files

  7. #7
    Lounger
    Join Date
    May 2010
    Location
    Glasgow, Scotland
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks guys for answering so fast.

    Not had much of a chance to work further on this but the command button is wroking fine and opening the reports I want, now just need to link the criteria but don't know if I'll get the chance to try this out today but I'll let you know how I get on so expect more questions soon.

    Again thanks for the excellent help I've been given so far, helped me no end.

  8. #8
    Lounger
    Join Date
    May 2010
    Location
    Glasgow, Scotland
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ok so just 1 problem now, I can't get the criteria on the form to run as the criteria on the report. For example I have

    strwhere = "([DateIssued] between #" & Format(Me.Combo0, "mm/dd/yyyy") & " AND #" & Format(Me.Combo2, "mm/dd/yyyy") & "#)"

    Where DateIssued is the field name in the query and combo0 and combo2(Yeah, put some real time into naming thins, oh well) are the start and end date.

    Cheers

  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Two things
    • Do you have strwhere at the end of the open report line?
      Code:
      DoCmd.OpenReport stDocName, acViewPreview,,Strwhere
    • Put in
      Code:
       Msgbox strwhere
      immediately before the open report line to tell what is in that variable.
    Regards
    John



  10. #10
    Lounger
    Join Date
    May 2010
    Location
    Glasgow, Scotland
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Actually just ignore that, I had the ,,strWhere after vbinformation instead of acviewpreveiw, working fine now. Only 1 problem, 1 of the fields I'm using as criteria is a check box and I can't get that to work. I've tried it using the syntax for numbers (as the value it returns is 0 or -1) and the syntax for text (as the chioce I get from the query to the combo box is yes or no) but I can't get it to work as yet.

    Thanks again for all the help guys, I'd have been lost without it.

  11. #11
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Yes/No fields are a bit of a problem. The user may want to filter for the Yes values, the No values, or not filter on this field at all. So you not to provide three options.
    For this reason, I don't use a check box on the filter form. Instead I use another combo box, with text values Yes or No. It can also be blank.

    Then I have this code :

    Code:
    If Me.comboVisitCompleted = "yes" Then
     	strwhere = strwhere & "( [PracticeVisitComplete] = true) and "
    	ElseIf Me.comboVisitCompleted = "No" Then
     	strwhere = strwhere & "([PracticeVisitComplete] = false) and "
    	End If
    In a related thread, you raise the question of how the reader of a report knows what filters were used to generate it. If the filter is just a date, the solution given there is enough, but once there are a range of variables involved you can use this solution.

    You can pass a string to a report using OpenArgs. Pass a second copy of strwhere.
    Code:
    DoCmd.OpenReport stDocName, acViewPreview,,Strwhere, , strwhere
    Then at the top of the report have a Label control (Mine is called LabelFilter) and in the OnOpen event of the REport:

    Code:
    If Not IsNull(Me.OpenArgs) Then
     Me.LabelFilter.Caption = Me.OpenArgs
    Else
     Me.LabelFilter.Caption = "All Doctors"
    End If
    Sometimes I take this a bit further. When I build the filter, I build two versions of it at once:
    • The version you have already, formatted the way Access wants it
    • A plain language version, that is more readable. (strTextFilter)
    Code:
    If Me.comboVisitCompleted = "yes" Then
     	strwhere = strwhere & "( [PracticeVisitComplete] = true) and "
     strTextFilter = strTextFilter & "(Practice Visit Completed) and "
    	ElseIf Me.comboVisitCompleted = "No" Then
     	strwhere = strwhere & "([PracticeVisitComplete] = false) and "
    	strTextFilter = strTextFilter & "(Practice Visit Not completed ) and "
    	End If
    It is the second of these that I pass to the report.
    Regards
    John



Posting Permissions

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