I have an Access 2007 database that has a table (Fixture Schedule), a form (Input Fixtures), and two reports (Exterior Fixture Schedule, Interior Fixture Schedule).
For this database, the users will be using the "Input Fixtures" form to add fixtures to the "Fixture Schedule" database. When they've finished adding data, the "Exterior/Interior Fixture Schedules" take the data in the table and output it in a report that can be printed or exported.
Because of the nature of the table, there will be times where the data in the table for a particular record needs to stay in place, but that record needs to be flagged as "Not Used" for the report.
I've set up the table with a column labeled "Not Used", and the form successfully uses a check box to update a fixture's current "Used/Not Used" status (i.e. when the form says "Not Used", the table says "Not Used" and vice-versa).
However, I would like to have the reports reflect the "Not Used" status in a meaningful manner.
Currently, the reports will show data from the table something like this:
F1
Information about fixture
------------------------------------------
Location: Something
Min/Max: Something
Dimensions: Something
(and so on, one text box per column in the table).
What I would like to do is have the "Not Used" status trigger the Visible status of each text box, and change the "Information about fixture" line's text. It would then look like this:
F1
NOT USED
------------------------------------------
(everything below this is hidden)
I've been trying a variety of VBA scripts. Some have had me try to assign a value to the "Tag" control and use If-Then statements, but it's not working (user error, most likely).
It seems like it should be fairly easy to have the report check the status of the "Not Used" column in the "Fixture Schedule" table and adjust its output based on that, but I'm not having any luck.
Any help would be greatly appreciated.
Thanks.
