Need assistance with access dashboard

[Diji Ninja]

Hello AccessForums,
I am building a simple MS access database with a dashboard and need some help. I am submitting data into a table using the click event macro in the button. Based on the criteria from either the entry form or the table it is recorded to, I would like the background color of a cell on a form (dashboard) to change red or green. I need to either utilize built in access features or write some vba to accomplish this but I am stuck at where or how I should code this action.




Here is ideally what I need to happen:

User enters info into form and submit entry form, this is working







Data is recorded into a table, this is working:





the function that I need help to complete is to change the background color of these cells based on the option selected in the drop down box "work in progress" on the entry form ( or by other method)






Any suggestions appreciated!

Thanks

[pbaldy]

It's certainly doable. What determines when to apply the format? If those are textboxes, Conditional Formatting (on the ribbon) is an option. VBA is another, if the form isn't in continuous or datasheet view.

[Diji Ninja]

Hello Paul,

I'm using the dropdown box to determine if the box is red or green. For example, the user would select "test stand 1 up" and the box would be green or " test stand 1 idle" and the box would be red. the boxes are actually just rectangular frames and do not allow conditional formatting but I could use any box or shape that would work. The conditional formatting seems to rely on a numeric value, perhaps I could link it to the primary key in the table where the drop down list was derived from. The dashboard is not continuous at this point and will just display a green or red background and some basic info and refreshed every 30 seconds. I'm open to suggestions if other layout or logic would accomplish this better. Thanks for the feedback.

[Micron]

I think I'd do away with the rectangles and make them into large text boxes. Then "Test Stand 1 (H)" can come from a table or query instead of being a label, which is what I think that is. By doing so, you can not only bind the text box so that the "label" will automatically change if you change the wording in the table, you can apply conditional formatting to these large textboxes based on the values held in the smaller ones you place on top of them (where you show =Forms!Frm...). Conditional formatting on each stand textbox would be easier than writing a long Select Case block or a bunch of IF statements to color the rectangles according to the combo choice. All you need to do is format the small textboxes according to your desired look, making borders and background transparent or whatever.

[Diji Ninja]

I've been researching and I think the boxes are gonna become textboxes and I'm going to try conditional formatting and/or expression builder on the dashboard objects. Tomorrow I will give it a go and report back. Thanks for the suggestions gentlemen.