Conditional Formatting (revisited) (2002 SP-2)

Topic

New Reply

I would like to address this issue of ‘Conditional Formatting’ so often addressed in this forum. I have become rather frustrated with the fact that Access only allows three different formats when using their wizard. I have an application used in the transportation industry to track truck dispatching. In frmLoadBoard (continuous) the cmbOrderStatus has 15 different status’. While I believe there are at least some cases where this could be reduced (i.e. some could have a “shared” BackColor), I understand that ElseIf has no limitations (assuming that is the correct code usage).

I created a little db (to experiment with) with similar cmbOrderStatus in frmOrderDetail (normal). I then created frmLoadBoard (continuous) with txtStatus. My first brick-wall was trying to figure out where to put the code. I couldn’t see where this formatting would be an “event” for the txtBox, so I entered the following in the OnOpen event of frmLoadBoard:

If txtStatus = “ASSIGNED” Then
txtStatus.BackColor = 16777164
ElseIf txtStatus = “BILLED” Then
txtStatus.BackColor = 16765673
ElseIf txtStatus = “NEW” Then
txtStatus.BackColor = 255
End If

I then created 15 records with all of the possible status’ and opened frmLoadBoard. (Note that I only coded 3 possible formats – I didn’t want to bother with all 15 until I was certain I was on the right track). frmLoadBoard only displayed the correct BackColor for the first status (“ASSIGNED”) and ignored all the other coding (i.e. “BILLED” and “NEW”). If you click on any of the txtBoxes in the display (regardless of their status) it changes the BackColor to that for “ASSIGNED” (16777164). Somewhat discouraged, I thought perhaps the coding actually should be placed in the properties of the txtOrderStatus, but (as I previously mentioned) I cannot for the life of me determine where the code should be placed. Also, I don’t know if the code is even correct. One issue that comes to mind (and assuming the coding would go in the frmLoadBoard OnOpen event), we already are using that event in the application to display a count of new orders from frmOrderNew.

frmLoadBoard (continuous) is a visual display of orders that greatly aids the operator in seeing at a glance what status’ the orders are in. By double clicking any single order number they call the frmOrderDetail for editing – no entries/edits are actually made in frmLoadBoard. To be able to assign different BackColor to each txtStatus creates a display that instantly informs the operator if something is amiss.

As always, any and all assistance/input/comments greatly appreciated.

Reply | Quote

Replies

The problem is that in a continuous form, there really is only one set of controls, whatever the number of records displayed at once. The only things that are different from record to record, are:

• The data displayed.
• Data-dependent number formatting.
• Conditional formatting.
  [/list]If you change any other property of a control, it will be applied to all visible instances of that control.

  Fortunately, there are ways to get around this. You can make text boxes transparent and put another text box behind them, or an OLE control. By cleverly manipulating the control source and formatting of such a control, you can simulate setting a condtional background color.

  For example, take a look at post 238458 and at the thread starting at post 164260. Perhaps, you can adapt the ideas found there. Don’t hesitate to ask if you want more information, or need help with applying it.

Reply | Quote

Thank you Hans – I am researching your references.

Reply | Quote

Hans,

Lost, dazed, and confused. I have printed out all the threads and downloaded the sample db. Trying (desperately) to understand, but keep getting distracted by work. Just wanted you to know that I am trying to work this out, but it will probably take all of this evening to come up with something deserving of a reply. Nose to the grindstone!

Reply | Quote

I think I may have hurt myself. I don’t think the brain cells are functioning anymore. Couldn’t sleep, so I’m back at it. I can’t seem to make the connection between the demo (thanks Steve, re:Hans) and my application. I’m not sure I can write this out, but I’m gonna do my best.

tblMaster > qryMaster > qfltLoadBoardTenDay > frmLoadBoardTenDay > txtOrderStatus (from tblMaster Lookup re: tlkpOrderStatus)

frmLoadBoard is really for display purposes only (to get the “big picture” of all orders per qfltLoadBoardTenDay) dblclk any order and you get frmOrderDetail to make any edits or change in status (ComboOrderStatus). Because there are no edits done in frmLoadBoardTenDay, and even if the data comes from a ComboBox, it is displayed as a TxtBox (it also makes for a cleaner display in continuous forms).

I edited tblMaster to include the fields ColorKey (number) and ColorIndex (number). I don’t really understand why (how bad is that?), but those were the fields in tblData from the sample db (tblData also has Data1 and Data2), but seeing as I needed the ComboOrderStatus field from tblMaster I did not add the additional fields from the sample db.

I then created tblColors with the fields Value (number) and Color (OLE). Again, I did not include the field “Description” from the sample db because the value I need the txtOrderStatus to display is the value of ComboOrderStatus. I then went to qfltLoadBoardTenDay and added tblColors with a link between ColorIndex (qryMaster) and Value (tblColors). Clicking RUN returns no records. I haven’t even gotten to the form yet.

Reply | Quote

Hello Bryan,

Don’t worry too much, this is a rather confusing subject, since we’re trying to force Access to do something it wasn’t designed for.

This is going to be a long post; you may want to print the instructions for easier reference.

I hope you made the changes you describe in a copy of your database, otherwise you’ll need to undo them.

1. If you still have the tblColors you created, delete it (sorry).
2. Import the tblColours table from my (or Steve’s) demo database (File/Get External Data/Import…). At the moment, it contains 4 records (red, yellow, blue, green). You can add new colors later on, but we’ll stick with these four for now.
3. If you still have ColorIndex and ColorKey fields in tblMaster, delete them from the design. This is not the correct place for them.
4. Open tlkpOrderStatus in design view.
5. Add a field ColorIndex of type Numeric (Long Integer).
6. Click the Lookup tab in the properties for this field.
7. Set Display to Combo Box.
8. Set Row Source to tblColours.
9. Set Number of Columns to 2.
10. Set Column Widths to 0;1
11. Close the table and save the design changes.
12. Open the Relationships window (Tools/Relationships…)
13. Add tblColours and, if it hasn’t been added earlier, tlkpOrderStatus.
14. Create a join between tblColours and tlkpOrderStatus by dragging the Value field from tblColours and dropping it onto the ColorIndex field in tlkpOrderStatus.
15. Set referential integrity and cascading updates for this join.
16. Close the Relationships window.
17. Open tlkpOrderStatus in datasheet view.
18. Assign a ColorIndex to each status (because we have set it up as a combo box, you don’t have to know the numbers).
19. Close the table.
20. I don’t know whether tlkpOrderStatus is already part of qryMaster or qfltLoadBoardTenDay. If not, add it to qfltLoadBoardTenDay. It should be linked to tblMaster by a status field. If not, create the join now.
21. Add tblColours to the query containing tlkpOrderStatus. It should be linked automatically by Value vs ColorIndex.
22. Add the Colour field from tblColour to the query grid.
23. Close the query and save the design changes.
24. If the query you just edited is qryMaster, make sure that the Colour field is also in qfltLoadBoardTenDay.
25. Open frmLoadBoardTenDay in design view.
26. Add a bound object frame to the detail section. Exact location doesn’t matter for now.
27. Set its Control Source to Colour.
28. Set Size Mode to Stretch.
29. Close the form and save the design changes.
30. Open the form in form view.
31. The object frame should display different colors corresponding to status.
32. If this works OK, switch back to design view.
33. Now, place the object frame over the status text box.
34. Select Format/Send to Back to place it behind the text box.
35. Select the text box and make its Back Style transparent.
36. Test the result.

Phew… are you still there?

Let me know if you succeed, or what goes wrong.

Reply | Quote

Geeeesh!! Talk about “above and beyond the call of duty” – you deserve the Medal of Honor for sure. Thanks so much for all that work!!

I think we’re very close at this point, but the OLE is displaying only one color (Green) regardless of status. In step #18 “Assign a ColorIndex to each status”; tlkpOrderStatus has three columns: Status (Assigned, Billed, etc.), StatusKey (1 – 15), and ColorIndex (Red, Yellow, etc). I’m not sure what you meant by assign a color index, but I just randomly clicked on a color for each status.

Close…very close.

Reply | Quote

Bryan,

Can you post the SQL for qryMaster and for qfltLoadBoardTenDay?
– Open one of the queries in design view.
– Select View/SQL.
-Copy the text to the clipboard.
– Paste the text into your reply.
– Repeat for the other query.

Reply | Quote

Hope you don’t go blind reading this!

Reply | Quote

Whoa! Hold everything Hans. It works!! I just have to requery the frmLoadBoardTenDay every time I change the status in frmOrderDetail.

Reply | Quote

I was able to figure out a way around hitting cmdRefresh every time an edit is made on frmOrderDetail. I used the On Close event in frmOrderDetail to run (don’ be laughing now) a machro that closes and reopens frmLoadBoardTenDay. OK, so it’s a little bit funky with the screen flash and all, but I just couldn’t come up with anything else. Requery seems to be more for controls (or current focus forms). If you have some more “magic” in your hat for an issue like this, by all means give me a shout (that’s an open invitation to all BTW).

Reply | Quote

This is a bit off the original subject, but (after a nap) I began to see the “refresh” issue a bit differently and turned a mountain into a mole hill. I went back to the Timer Event for frmLoadBoardTenDay and simply ammended the code. Now the new “conditional” color display refresh’s every 60 seconds, regardless if a new order has been entered.

Private Sub Form_Timer()
Dim lngCount As Long
lngCount = DCount(“*”, “tblMaster”, “OrderStatus=’NEW'”)
If lngCount > 0 Then
Searching.Caption = “New order(s) found. Click ‘refresh’ to display: ” & lngCount
Searching.BackStyle = 1 ‘ Normal
Else
Searching.Caption = ” ”
Searching.BackStyle = 0 ‘ Transparent
End If
Forms!frmLoadBoardTenDay.Requery
End Sub

A WORD OF CAUTION: This is a single user application. I have been advised against Timer event ReQueries in multi-user environments.

Thanks again to Hans and Steve for all the code, examples, and most importantly, the learning experience. I am humbled by your generousity and hope I can (some day) return the favor to other members.

Reply | Quote

Bryan,

The reason you can do this is that (as you wrote farther up ths thread) frmLoadBoardTenDay is not used for editing. In a form intended for editing records, requerying it using a timer is disconcerting to the user, to say the least.

Note: now that you requery the form from its own OnTimer code, you can use Me.Requery instead of Forms!frmLoadBoardTenDay.Requery.

Reply | Quote

Hans,

Funny how one one revision seems to lead to another, and another,and… I am very pleased with the results of this most resent journey, but most grateful for the lessons learned along the way. While VBA results are certainly the immediate target, the learning process has the greatest long-term benefit. I became most frustrated when I tried to get Access to “think” the way I do, but really started getting somewhere when I attempted to think the way Access operates. Long way to go, but at least I’m on the way.

(Forms!frmLoadBoardTenDay versus Me.Refresh…Duh! Like I said, long way to go)

Reply | Quote

Wow…are you still out there?

This is really great. Fifteen different conditions….absolutely extraordinary!! Still have a Me.Dirty issue, but I can live with it if I have to. Strange that there isn’t a On Dirty event for OLE objects though. Probably a good reason for it, but it’s beyond me. I thought I might be able to requery frmLoadBoardTenDay with an On Close event in frmOrderDetail; it does requery the form, but the OLE isn’t affected. That doesn’t make much sense to me because the same machro (requery form) on frmLoadBoardTenDay does update the OLE object…curious.

I can’t even imagine how to go about thanking you for all your help (and certainly most of the actual work). Woody’s is the best for sure.

Reply | Quote

The attached demo uses a different approach than messing with weird characters etc.. It uses a table with the value and a bitmap image of a bit of colour. The query providing the recordsource picks up the relevant colour from this look-up table. The colour is displayed in an image control. I’ve left it in A97 format for others to look at.

HTH

Reply | Quote

Hi Steve,

That is a good alternative. It is essentially the same as the database attached to the first link in my reply (the link was to post 238458). File/Database Properties lists the same author… To miminize database size, I reduced the images to 1 by 1 pixel – you only need the color.

Reply | Quote

Fair one Hans

I answered quickly before a lesson so I didn’t look at your attachment.
I take the point about 1×1 pixel images and setting the Image control to stretch – anything to reduce the overhead!

Reply | Quote

Steve,

I only now realize that you are the author – didn’t make the connection between SteveH and shayward soon enough. I downloaded the database some time last year, studied it and adapted it a bit, but I had forgotten where it came from.

I like the technique – it’s easier to understand than the methods using block characters, and allows for an unlimited number of colors.

Please accept my apologies.

Reply | Quote

I have to admit that I read about the technique in Personal Computer World magazine many moons ago in their Databases column (Mark Whitehorn writtes it and it is well worth a read). I adapted it for an application I was writing at the time. I couldn’t find the original article to acknowledge the ‘inspirer’ of the technique when I knocked up that demo.

Anyway apologies accepted – so long as the demo proves useful to all out there.

Reply | Quote