Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2021
    Posts
    9

    Question Customize a report according to multiple options selected by the user

    Hi everyone! I am creating an application to manage information about chemical products, and I hope you can help me with this question.

    The app generates a report with the chemical safety data. For this, I created a form where the user selects for which product they want the report. I want the user to be able to customize the information that is shown in the report using multiple option buttons. Here is an example of what I want to achieve.

    This is the report template:

    Click image for larger version. 

Name:	Report example.PNG 
Views:	39 
Size:	11.8 KB 
ID:	44775



    As you can see, the section “Physical and Chemical properties” is a textbox that concatenates multiple fields, but I want the user to choose which properties will be displayed in the report. Here is an example of the form:

    Click image for larger version. 

Name:	Form example.PNG 
Views:	39 
Size:	12.7 KB 
ID:	44776

    So, if the user only selects “Appearance” and “Density”, the “Physical and Chemical properties” textbox in the report should only concatenates [FieldAppearance]&[FieldDensity].
    Does anyone know how I can accomplish this?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Could have a very long, very clumsy expression using IIf().

    Alternatively, build a custom function that builds string. Reference that function in textbox: =GetPCP()
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not entirely clear IMO. You want only the property choices to be displayed? Where does the data show up for the properties? If there is no data for the properties it seems kind of pointless, so shouldn't the property be a section header and the property values be the detail section under each header?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Join Date
    Jan 2021
    Posts
    9
    I apologize for not being clear in my initial post. The property data is already in the database, I entered it. I want the user to see which properties are available and select which are of interest to the report. The first form example I posted was poorly designed, here is another example that is closer to what i want to accomplish.

    Click image for larger version. 

Name:	Form example 2.png 
Views:	26 
Size:	26.1 KB 
ID:	44856

    The user enters to the Safety Data Sheet Generator and selects the chemical product. Then, he can see which properties are available (in this example, there is data for everything except for viscosity). Next, he decides that he wants to include only appearance, odour and boiling point in the report (discarding density). So, what I want to accomplish is that Access only concatenates the fields Appearance, Odour and BoilingPoint (the selected options) in the text box of the report corresponding to physical and chemical properties.

    I hope I have been clear this time and thank you very much for your answer.

    Quote Originally Posted by Micron View Post
    Not entirely clear IMO. You want only the property choices to be displayed? Where does the data show up for the properties? If there is no data for the properties it seems kind of pointless, so shouldn't the property be a section header and the property values be the detail section under each header?

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I would not use checkboxes for this. A user may or may not know which properties are not applicable to a product. I suspect that for instance, BP would not apply to most/all solids yet your design allows for such a choice. Suggest you research "cascading comboboxes" and employ that method. Then for each product, the subsequent properties combos can be filtered to list only those properties that are relevant. Or you could popluate a listbox with applicable properties and allow multi select on it. You could run a parameterized query (one that uses the chosen values as query critieria) and open the report based on that. One has to deal with controls that have no values chosen, however. My preference would be to build the query report sql as a string where one can deal with controls that don't have a chosen value and open the report based on that.

    I still don't understand why you want to concatenate properties in a report textbox. So much easier to list each property as a detail record for a chosen product IMO. Having been an ISO 14001 coordinator/auditor for a time, I don't see why an MSDS should be customizable in this way. That a user gets to decide what information he wants to see about safety data is very strange. As an auditor, I'd cite this capability in a compliance report for sure.
    Last edited by Micron; 03-29-2021 at 12:10 PM. Reason: spelin and gramur
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Join Date
    Jan 2021
    Posts
    9
    Thank you very much for all your suggestions. I didn't know about cascading combo boxes method and it was very useful for another part of my database.
    I understand your concerns about a customizable MSDS, the truth is that the document I need to generate with the database is not exactly an MSDS, I only used it as an example (now I think it was a bad idea, I'm not good with giving examples). In that other document, a chemist will decide what of the properties in the database are relevant or no, so I needed to give him that "freedom", but I already solved it with VBA code. Again, thank you for your help.


    Quote Originally Posted by Micron View Post
    I would not use checkboxes for this. A user may or may not know which properties are not applicable to a product. I suspect that for instance, BP would not apply to most/all solids yet your design allows for such a choice. Suggest you research "cascading comboboxes" and employ that method. Then for each product, the subsequent properties combos can be filtered to list only those properties that are relevant. Or you could popluate a listbox with applicable properties and allow multi select on it. You could run a parameterized query (one that uses the chosen values as query critieria) and open the report based on that. One has to deal with controls that have no values chosen, however. My preference would be to build the query report sql as a string where one can deal with controls that don't have a chosen value and open the report based on that.

    I still don't understand why you want to concatenate properties in a report textbox. So much easier to list each property as a detail record for a chosen product IMO. Having been an ISO 14001 coordinator/auditor for a time, I don't see why an MSDS should be customizable in this way. That a user gets to decide what information he wants to see about safety data is very strange. As an auditor, I'd cite this capability in a compliance report for sure.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Glad you have a solution. Good luck with the rest of your project!
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Multiple filter options for a report
    By bfg in forum Queries
    Replies: 5
    Last Post: 10-26-2020, 06:50 AM
  2. Allow User to Customize Columns on Report
    By Mlkobefan in forum Reports
    Replies: 19
    Last Post: 08-06-2015, 01:28 PM
  3. Replies: 11
    Last Post: 06-28-2015, 10:46 AM
  4. Creating Multiple Dependant User Options
    By s.carter in forum Database Design
    Replies: 3
    Last Post: 06-28-2015, 09:24 AM
  5. Replies: 1
    Last Post: 12-05-2014, 01:06 PM

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