UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Combo Box To Change Color Based On Field Value, Access 2007    
 
   
hixzen
post Jun 29 2017, 12:41 PM
Post#1



Posts: 6
Joined: 29-June 17



Hello all, thank you in advance for any assistance.
I work in law enforcement. We use Access 2007 and I am attempting to develop a CAD (Computer Aided Dispatch) substitute for our dispatchers.

Currently I am working on, what would be, a unit status window I can embed as a sub form to a call tracking log.

The idea is, a list of units would be static in the window and next to the unit number would be a combo box with various status to choose from. When the dispatcher selects a status, the combo box changes bg and font color based on the status selected.

I have limited experience with VBA but I think that is what I need to use and where I need help. I have tried conditional formatting but it seems to only allow 3 conditional entries and I need to put in more.
Additionally, the status previously selected should carry over to the next record. I am at a loss so thank you again for any help.
This post has been edited by hixzen: Jun 29 2017, 12:42 PM
Go to the top of the page
 
doctor9
post Jun 29 2017, 01:46 PM
Post#2


UtterAccess Editor
Posts: 17,333
Joined: 29-March 05
From: Wisconsin


hixzen,

welcome2UA.gif

Can you clarify a couple of things about what you're trying to do?

> unit status window I can embed as a sub form to a call tracking log
Okay, so a "unit" is a car out on patrol, or a police officer walking around, right? I'm assuming that the subform would be a Continuous form, with one record for each "unit". Is that right? On each record of the subform you want to be able to see what each "unit" is doing at the moment, correct?

> I have limited experience with VBA but I think that is what I need to use and where I need help.
> I have tried conditional formatting but it seems to only allow 3 conditional entries and I need to put in more
If this is going on a Continuous form, it won't work because any VBA you apply to one control will affect the way the control appears on ALL records. How many different statuses/colors do you need?

> Additionally, the status previously selected should carry over to the next record.
This threw me off because of my assumption that each record would be for a unique unit. If unit 5 is on patrol and unit 6 is assisting with a house fire that's been going for a half an hour, and you send unit 5 to a domestic disturbance, you wouldn't want to change unit 6's status. What does "the status previously selected should carry over to the next record" mean in real-world terms?

Dennis

EDIT: HOWEVER... having said that... I do remember a clever little workaround that might help with the coloring. I can't find the exact UA post where I originally found it, but it was originally created by user ChrisO and I apparently downloaded it in 2009. Take a look at the attachment. The coloring is done with multiple layered transparent textboxes. When the form opens, a short burst of VBA code formats the textboxes and sets up a clever little Format property that basically shows nothing when the expression is false, and a solid block of color - through the use of CHR(219), which is a solid block with no gap between consecutive characters. The only change I've made to the original file is upgrading it from Access 97 to Access 2000 format.

Dennis

EDIT 2: Found something with a similar strategy here. It doesn't use VBA to set up the multiple textboxes, they're already hard-coded. The file I provided would be a little easier to use because you just set up the textboxes with special tag text to identify them, and let the code set them up for you when the form opens.
Attached File(s)
Attached File  ConditionalFormattingA2000.zip ( 21.18K )Number of downloads: 8
 

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
hixzen
post Jun 29 2017, 03:01 PM
Post#3



Posts: 6
Joined: 29-June 17



QUOTE
Okay, so a "unit" is a car out on patrol, or a police officer walking around, right? I'm assuming that the subform would be a Continuous form, with one record for each "unit". Is that right? On each record of the subform you want to be able to see what each "unit" is doing at the moment, correct?

A "unit" could be anything from police,fire,ems,trooper, etc. The dispatcher should be able to see "at a glance" the current status of each unit.

QUOTE
If this is going on a Continuous form, it won't work because any VBA you apply to one control will affect the way the control appears on ALL records. How many different statuses/colors do you need?

I am unfamiliar with the term "Continuous Form". I am looking to use 10+ statuses, each coded with a different color which would appear the moment the dispatcher changes the status.

QUOTE
This threw me off because of my assumption that each record would be for a unique unit. If unit 5 is on patrol and unit 6 is assisting with a house fire that's been going for a half an hour, and you send unit 5 to a domestic disturbance, you wouldn't want to change unit 6's status. What does "the status previously selected should carry over to the next record" mean in real-world terms?

Ideally, access would create a new record for each time a status is changed on any unit, so I could then create reports later. I have found however, that this isn't absolutely necessary.
When a record is saved and new created, the combo box will refresh, resetting the combo boxes on all units. This can not happen. The dispatcher must be able to change 1 units' status without effecting any others.


I have attached a sample image of the form I am working with to give an idea. Thus far I have coded in VBA, that when the status box is changed, it saves the current record so any change overwrites the previous record, which is acceptable. I think now my major concern is the limit on the conditional formatting.
This post has been edited by hixzen: Jun 29 2017, 03:01 PM
Attached File(s)
Attached File  Sample.jpg ( 32.11K )Number of downloads: 1
 
Go to the top of the page
 
doctor9
post Jun 29 2017, 03:19 PM
Post#4


UtterAccess Editor
Posts: 17,333
Joined: 29-March 05
From: Wisconsin


hixzen,

> I am unfamiliar with the term "Continuous Form".

Ooh, okay. Well, picture a grocery receipt. You could create one with two forms: A main form, which shows one record at a time. This form would display things like the date of the visit to the grocery store, the name of the customer, the credit card info used. Then, inside this main form, we'd have a Continuous subform that lists the quantity, the description of each item, the cost per item, and the final cost based on how many of those items you bought. A continuous form holds as many records as you need. When you look at it in Design View, there's just one textbox for quantity, one for description, one for unit cost, and one for the extended cost. When you look at it in Form View (when you can enter data), you can see rows of the same control being repeated, and you can add as many records as you like.

> Ideally, access would create a new record for each time a status is changed on any unit, so I could then create reports later.
> I have found however, that this isn't absolutely necessary.

Based on your description, I'd say this is a very good idea. In this specific case, the form would only display the MOST RECENT status for each unit, I think. When you change the combobox, you'd write a record to the table with the new status and the date/time that you changed the status, then requery the form to show the most recent statuses for each unit. That way you can see a historical record of a unit's status changes for a given day/week/whatever, for example.

Looking at your image, I see 331, 331a, 331b, 331c... is the unit "331" and these are different statuses for the same unit?

Based on the fact that you don't know what a Continuous Form is... you should really slow down for a bit. The first, most important aspect of a database design is getting the tables set up properly. This should be done BEFORE you create any forms or reports, because the tables are like the foundation to a house. I'd strongly recommend you look at some of the articles in our Newcomers Reading List to familiarize yourself with proper Data Normalization. I can't stress this enough: Setting up properly Normalized tables first will make everything else much easier and less stressful.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
doctor9
post Jun 29 2017, 05:06 PM
Post#5


UtterAccess Editor
Posts: 17,333
Joined: 29-March 05
From: Wisconsin


hixzen,

I'm taking a long weekend, but I thought it could be fun to take a stab at getting at least a form of your illustration up and running. Here are the basics of the attachment:

1. tblStatuses holds the list of statuses. I assumed that they all have a 10- code, so just put the digits after the dash in intCode. Put the color you want for the background of the combobox in lngBackColor, and if you want white text instead of black, un-check the checkbox for boolBlackText.
2. The code in the form's Open event can handle as many different statuses as you can dream up now.
3. tblUnits is pretty self-explanatory. List your units here. I've added a few dummy values to demo the form.
4. tblUnitStatuses is the workhorse. It's a Junction Table that creates the many-to-many relationship between units and statuses (one unit has many statuses over time, and one status is used by many units). The notes are stored here as well as the date/time that you changed the status for a unit.
5. The form itself only displays the most recent status for each unit. When you select a new status with the combobox, a new record is added to the tblUnitStatuses table, and the form is requeried to show the updated list. It sorts the form by the 10- code, so 10-2 would be listed before 10-3, 10-4, etc. If you want a custom sort to put "off duty" always at the top for instance, you can add a sort field to the tblStatuses table and put unique values in each record, then change the form's query.
6. There are TWO textboxes actually displaying the 10- code status; one with black text, and one with white text. The white text is in front, but it only displays a value if the checkbox for boolBlackText is unchecked.
7. The combobox is actually placed BEHIND the multiple colored textboxes, and it's not actually bound to a field. It just runs code when you change it's value.
8. The code is set up to have a blank "notes" field for each new status. Check out the VBA for the form to see how to change that to copy the existing notes into the new status if you like. I just commented out the line of code. Un-comment it, and comment out the existing code.

Hope this helps,

Dennis
Attached File(s)
Attached File  DispatchDemo.zip ( 23.75K )Number of downloads: 10
 

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
hixzen
post Jul 15 2017, 11:50 AM
Post#6



Posts: 6
Joined: 29-June 17



Doctor9,
I apologize for the incredibly long delay. Been busy around here but I greatly appreciate your assistance.

I have been experimenting with the dispatch demo. Developmentally, I find much of it to be beyond my skill level but, I continue to prob and decode it to understand it's workings.

The demo itself is exactly what I was trying to achieve. I can not however seem to figure out how to change the sorting.
QUOTE
If you want a custom sort to put "off duty" always at the top for instance, you can add a sort field to the tblStatuses table and put unique values in each record, then change the form's query.


In this type of feature you wouldn't want the unit order to change. As you stated, the form will place the lowest numbered and most recent 10 code at the top. Is there a way I can remove this? So that it all remains static?
Go to the top of the page
 
hixzen
post Jul 15 2017, 11:59 AM
Post#7



Posts: 6
Joined: 29-June 17



Can I achieve the units remaining static in the form display by editing this VBA code from the form?

QUOTE
strSQL = "INSERT INTO tblUnitStatuses ( dteStatusChangeAsOf, lngUnitID, lngStatusID, strNotes ) " & _
"VALUES ( #" & Now() & "#, " & Me.lngUnitID & ", " & Me.cboStatusOption & ", Null);"

This post has been edited by hixzen: Jul 15 2017, 12:00 PM
Go to the top of the page
 
doctor9
post Jul 17 2017, 08:13 AM
Post#8


UtterAccess Editor
Posts: 17,333
Joined: 29-March 05
From: Wisconsin


hixzen,

The INSERT INTO code just adds a new status change to the table, that doesn't affect form sorting. If you want to remove sorting from the form entirely (I think that's what you mean by "remains static"), open the form in Design View, then view the form's Record Source in Design View. It will display the query in a basic grid design, with the listed fields at the bottom. One of the columns will be the field intCode. Below the table name of this column is the word "Ascending" which indicates that the data is sorting by the intCode field, from lowest to highest. You can change this to "Not Sorted".

With this sort of form, I'd personally recommend that you sort by SOMETHING so you can easily find what you're looking for, but you know best what you need.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
hixzen
post Jul 17 2017, 12:59 PM
Post#9



Posts: 6
Joined: 29-June 17



Thanks very much for your help.

I am able to add new units and sort them in the order I desire. However when I try to add a new status in tblStatuses , I did as you instructed but when the new status is selected in the form, it seems to display it as transparent. You now only see the black and white but no actual color. (See attached) What am I missing? I was able to edit the colors for ones you created, just can not seem to make new status' function...
Attached File  Capture1c.jpg ( 77.72K )Number of downloads: 1

Attached File  Capture2c.jpg ( 77.9K )Number of downloads: 2

Attached File  Capture3c.jpg ( 51.17K )Number of downloads: 1
Go to the top of the page
 
doctor9
post Jul 17 2017, 01:06 PM
Post#10


UtterAccess Editor
Posts: 17,333
Joined: 29-March 05
From: Wisconsin


hixzen,

Remember, my demo is set up for only 5 statuses as I posted it. If you have six or more, you'll need to make new textboxes, one for each new status. If you un-stack the controls for the status, you'll see:

1. Combobox for data entry
2. Black status textbox
3. White status textbox
4. Five unbound textboxes with the .Tag properties "Status_1", "Status_2", "Status_3", "Status_4" and "Status_5".

For each new status, you'll need to copy one of these unbound textboxes, and give it the appropriate tag. The sixth status will be tagged "Status_6", for example. If you look at the original demo file that I based mine off of, you can see how they work more clearly, because it includes both stacked and slightly-overlapping versions so you can see how the coloring works.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
hixzen
post Jul 18 2017, 05:01 PM
Post#11



Posts: 6
Joined: 29-June 17



Thank you, I tried working with it. I copied and pasted a new unbound box , added the .Tag's I.E Status_6 and added status 6 to tblStatuses but I am still missing a step I think. When the newly created status is selected it does display the correct color I wanted but now fills the entire box and the black/white text does not show through. I don't think it has anything to do with the re-stacking as all other statuses still function normally.

Should I of not copied and pasted the unbound txt box? I wouldn't think that would make a difference ... confused.gif
Go to the top of the page
 
doctor9
post Jul 19 2017, 08:11 AM
Post#12


UtterAccess Editor
Posts: 17,333
Joined: 29-March 05
From: Wisconsin


hixzen,

The white text and black text status textboxes need to be on top, and the colored unbound textboxes need to be behind them. Use the Arrange tab when the form is in Design view to change which controls are in front.

EDIT: Just to clarify, here's the order you should have, from Front to Back:

1. White status text (strWhiteStatus)
2. Black status text (txtStatus)
3. The unbound textboxes that provide the coloring
4. The combobox used to select the status (cboStatusOption)

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    28th July 2017 - 06:05 AM