Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2012
    Posts
    2

    Unanswered: Show/Hide fields in a Report based on a checked box in a form

    Hi All,

    Still trying to get my head around Access. (I miss Excel *sigh*).

    • I have a list of 500 stores.
    • I have an inventory of 5 different types of advertising signs that go to these stores.
    • Depending on the time of year, I may send all signs (1, 2, 3, 4 & 5) to all stores.
    • However, sometime I may only need to send signs 1 & 4 (for example).
    • I would like to be able run a report that lets me choose which fields I'd like to show up in the report.


    Can I create a form that simply shows the name of each sign type, and a yes/no check box under each name. Then, have the report make reference to that form to determine if there is a Yes or No under each sign type and whether or not it should show up on the report?

    Call me crazy, but it feels like this should be an easy solution, but I just can't get it. I'm still new at this, but learning bit by bit.

    Any help would be awesome!
    Tim )

  2. #2
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    I think you need something like "DemoDinamicReportA2003.mdb"
    (attachment, zip).
    Look at Table, Form (VBA), Report (VBA).
    Open Form and try. Adapt it as you need.
    Attached Files Attached Files

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    What you are describing here is a classic many-to-many relationship. One store can have multiple signs, and one sign can be assigned to multiple stores. You need to design a table to hold the assignment of signs to stores. This table will have a multi-part key, consisting of the primary keys from the list of stores and the list of signs. If you need to track historical data, you will need to add a couple of date fields too - date sign assigned, and date assignment ends.
    You will then be able to use this table in constructing a form to set up sign assignments.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  4. #4
    Join Date
    Sep 2012
    Posts
    2
    Hi Mstef-zg,

    Thanks for the great example
    It's very much appreciated.
    I was onto to something similar to this, but ran into the samee problem this sample file is giving me. I'm running Access 2007.

    If Forms!Form1!Check1 = 0 Then <--Acces does not like this line. I can't seem to successfully make reference to another Form or Table.


    I've tried these three code and none of them work.
    Forms!Form1!Check1 = 0 Then
    [Forms]![Form1]!Check1 = 0 Then
    [Forms!][Form1!]Check1 = 0 Then

    None of them seem to work. What am I missing?

  5. #5
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    What is the Error (error number and description) ??

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I would expect Access is explicitly requiring the value of the control not allowing the the control to be compared or doing some magic behind the background to convert your code inot your wishes
    so try
    Forms!Form1!Check1.value
    also if its a checkbox then use the intrinsic vba constanct vbtrue or vbenabled in place of 0 (or 1)

    tbh on a checkbox control it .value may not be the correct property, it could be .checked
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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