Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I developed a report menu that uses an option group to determine the report type, and then based on the option selected, can result in the need for the user to further define the query criteria by selecting say an employee name from a drop-down, and even possibly further define the expected results by picking a date range using the calendar control. Works great, however I have the need to expand on the current menu. I guess without going overboard, I am wondering if there are limitations to how many of these "building blocks" can be selected, without the who stack falling over.

    For example, selecting report option 1 (in group A) could cause for the user to select an option 2B, 3B, etc. from option group B, and then based on what is selected in group B, could determine the need to selecte an [employee name' from a drop-down combo box, and then filter based on the calendar control, before pressing the "generate report" button. Whew!!! I should add then my current coding all works through one "generate report" button based on a combination of either which option and initial filter criteria is selected, or through calling out a module. As I said, it works fine, but I need (would like) to even tighten the criteria, and/or have the ability to select multiple criteria.

    I could put together an example of the code behind the current menu if needed, however the db is running at about 8-9mb and so contains some classified data that I would need to remove before posting.

    I think this is possible, but could use a little assistance.

    Thanks.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I don't see any problems with what you are planning.
    I regularly do this sort of thing, by having a single procedure that checks the status of each option group, combo box etc then builds a 'where' clause or SQL string from the active selections.

    The only risk I can think of is that the string might become too long. For some purposes (and a merge using an SQL string as the datasource is the only example that comes to mind) an SQL string cannot exceed 255 characters.
    Regards
    John



  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I agree with you John, but isn't the 255 character constraint the length of a paramemter string? I have SQL Strings that are way beyond.that - in Access 2003 the limit for a stored string is ~64K, while the limit for record sources or row sources is ~32K.
    Wendell

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I agree that in general SQL strings can be very long.

    But I know that an SQL string used as the datasource for a merge cannot be greater than 255 characters (I stopped using SQL strings for merges for this reason) so my worry is that there might be other situations where a similar limit applies.

    I just found this in Help, for DoCmd.OpenReport:
    The maximum length of the WhereCondition argument is 32,768 characters (unlike the Where Condition action argument in the Macro window, whose maximum length is 256 characters).




    I agree that you would be hard pushed to exceed 32,768 characters.


    Regards
    John



  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Sorry John, I didn't see the "merge" part of your response - and I agree - using SQL strings as a merge data source has lots of limitations.
    Wendell

  6. #6
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Most of the controls on this form are hidden, and the use the AfterUpdate event to open them, in sequential order. If I had these additional drop-down boxes to further filter the information, I am assuming those need to be done in a specific order as well, otherwise it may conflict with the query that feeds the report???

    I love a good challenge, and for me this should be good for a few 5-hour energy drinks!

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I'm not sure that you need to display the drop-down boxes in a specific sequence in order to get the right filter string for the report, but it probably makes sense to do that to control what the user can do - it is possible that a user could construct a set of constraints that would result in no data for the report. Are you trying to build the filter as each combo is clicked, or are you building it all when the "generate report" button is clicked?
    Wendell

  8. #8
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts
    The way it works now, based on using one-drop down filter, sometimes in conjuction with calendar control, is that the report option is selected, in "un-hides" the appropriate drop-down filter (and the calendar control at the same time. There is also an instruction text box that "un-hides" instructing the user to select the date range, and then the filter criteria. Once the filter criteria is set, the AfterUpdate event "un-hides" the Generate Report Button. The code behind the button focus on which original option group value was selected to determine which report is open.

    My thoughts were to use the AfterUpdate event on one filter to open the next, and then the next (if needed) however where I may have a conflict is that there are several various combinations of which filters may or may not be used, based on which option group value is selected. That is one of the reasons why I was thinking of "unhiding" all appropriate controls that are needed based on the option group value, and provide specific instructions on which order the filters should be set.

    My other challenge is to set an "All" selection in the filter drop-down, or some how may it work if not criteria is selected. I think if the filter is not set, the query will act like it is "all" anyway - is that correct?

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    In general, I think your approach makes sense, but a bit of caution is also in order. I've seen cases where a developer tried to include both menuing and filtering in a form, and that can cause the desigh to get quite convoluted. I always try to adhere to the K.I.S.S. principle when doing design work - for example I would have a separate form for each report, as it helps to restrict the complexity. On your "All" selection option, you could simply set the Filter property to Null or an empty text string and that should work fine when the filter is applied.
    Wendell

  10. #10
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Excellent advice, especially regarding the seperate forms. I work on the K.I.S.S. platform as well, and this is one of the reason why I wanted to try and develop a one stop report location. Right now the system is pretty combursome, and so I inherited some cleanup responsibilities. Thanks again!

  11. #11
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I need to open this back up and ask for a little advice. As I mentioned in the middle of the thread, I need to be able to string or look at more the value in more then one option group and in some cases, with multiple controls.

    For example, the user needs to select report XYZ from option group A, then decide in option group B if they want the detailed report (option B1) or the summary report (B2). Once they have that selected, they the need to decide in option group C if they want to filter the data by Person (C1), a work area (C2) or by specific item (C3). Based on selecting C1, C2, or C3 (after deciding if they want a "details" or "summary" report) will then "unhide" specific drop-down boxes for selecting the persons name, a work area, or a specific item. There will even be the need to even further defined the data by using calendar to set a beginning and ending date range.

    I understand I could generate one option button for each of these multiple choices, but then my form starts to look really crowded. Plus I may at some point may want to add other options.

    I use one button to currently generate the reports for a "one path" (option value, drop-down, print) format, but going into this multiple (option value, option value, option value, drop-down, calendar, print) path seems rather challenging.

    The current command button to generate the report work on either:

    If OptionGroupA.Value = 32 Then
    ...do something or calls out a module

    Else....

    But to do what I need would be something like???:

    If OptionGroupA.Value = A3 and
    If OptionGroupB.Value = B1 and
    If OptionGroupC.Value = C5 and
    Then
    cmd.PersonName.visible = true
    Date() (Calendar function visible)

    I just don't see this being how to tie this whole mess together. Uncharted terriority for me here, but very much want to figure it out...

    I appreciate any ideas or comments.

  12. #12
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    It is hard to know what anyone can suggest. What you are trying to do is complicated, and the complications are all matters of detail that we don't have access to.

    Can you hide (if there are problems of an overcrowded screen) or at least disable all the option groups at the start, and only make them visible (enabled) when they are appropriate?

    So that OptionGroupB only has a value if OptionGroupA has a certain value?

    Or divide the code into major blocks depending on the value of the early option groups?

    If OptionGroupA = 1 then
    If optionGroupB = 1 then
    etc



    elseif OptionGroupA = 2

    etc
    Regards
    John



  13. #13
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts
    The primary option group is the only thing not "hidden", and has you mentioned, this is to keep the form "clean". Control are only unhidden based on what is needed, based on the option group selected. I will work with the example you provided. I know that I need to develop a string of option A + option B + option C + control A = expected report. Like I mentioned maybe it would be easier to just have more option groups that take some of the "complication" out of the mix. I looked on-line for an example db but the only ones I could locate deal pretty much with one option group used to filter which report is selected.


  14. #14
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts
    John,

    Calling out the option groups as you mentioned in your reply, and then using an underlying to query on the drop-down filter name. Working great, and not as complicated as I orginally thought. Once I got the first one coded the rest (report options) is falling into place rather nicely.

    Thanks for pointing me in the right direction.




Posting Permissions

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