Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Conditional Formatting (revisited) (2002 SP-2)

    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.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Formatting (revisited) (2002 SP-2)

    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:
    <UL><LI>The data displayed.
    <LI>Data-dependent number formatting.
    <LI>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>post 238458</post#> and at the thread starting at <post#=164260>post 164260</post#>. Perhaps, you can adapt the ideas found there. Don't hesitate to ask if you want more information, or need help with applying it.

  3. #3
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (revisited) (2002 SP-2)

    Thank you Hans - I am researching your references.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  4. #4
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (revisited) (2002 SP-2)

    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! <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15> <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23> <img src=/S/heavy.gif border=0 alt=heavy width=40 height=34>
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  5. #5
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (revisited) (2002 SP-2)

    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
    Attached Files Attached Files
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Formatting (revisited) (2002 SP-2)

    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>post 238458</post#>). 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.

  7. #7
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (revisited) (2002 SP-2)

    Fair one Hans

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

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Formatting (revisited) (2002 SP-2)

    Steve,

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> 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.

  9. #9
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (revisited) (2002 SP-2)

    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.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  10. #10
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (revisited) (2002 SP-2)

    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. <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Formatting (revisited) (2002 SP-2)

    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. <LI>If you still have the tblColors you created, delete it (sorry).
      <LI>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.
      <LI>If you still have ColorIndex and ColorKey fields in tblMaster, delete them from the design. This is not the correct place for them.
      <LI>Open tlkpOrderStatus in design view.
      <LI>Add a field ColorIndex of type Numeric (Long Integer).
      <LI>Click the Lookup tab in the properties for this field.
      <LI>Set Display to Combo Box.
      <LI>Set Row Source to tblColours.
      <LI>Set Number of Columns to 2.
      <LI>Set Column Widths to 0;1
      <LI>Close the table and save the design changes.
      <LI>Open the Relationships window (Tools/Relationships...)
      <LI>Add tblColours and, if it hasn't been added earlier, tlkpOrderStatus.
      <LI>Create a join between tblColours and tlkpOrderStatus by dragging the Value field from tblColours and dropping it onto the ColorIndex field in tlkpOrderStatus.
      <LI>Set referential integrity and cascading updates for this join.
      <LI>Close the Relationships window.
      <LI>Open tlkpOrderStatus in datasheet view.
      <LI>Assign a ColorIndex to each status (because we have set it up as a combo box, you don't have to know the numbers).
      <LI>Close the table.
      <LI>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.
      <LI>Add tblColours to the query containing tlkpOrderStatus. It should be linked automatically by Value vs ColorIndex.
      <LI>Add the Colour field from tblColour to the query grid.
      <LI>Close the query and save the design changes.
      <LI>If the query you just edited is qryMaster, make sure that the Colour field is also in qfltLoadBoardTenDay.
      <LI>Open frmLoadBoardTenDay in design view.
      <LI>Add a bound object frame to the detail section. Exact location doesn't matter for now.
      <LI>Set its Control Source to Colour.
      <LI> Set Size Mode to Stretch.
      <LI>Close the form and save the design changes.
      <LI>Open the form in form view.
      <LI>The object frame should display different colors corresponding to status.
      <LI>If this works OK, switch back to design view.
      <LI>Now, place the object frame over the status text box.
      <LI>Select Format/Send to Back to place it behind the text box.
      <LI>Select the text box and make its Back Style transparent.
      <LI>Test the result.
    Phew... are you still there?

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

  12. #12
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (revisited) (2002 SP-2)

    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.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Formatting (revisited) (2002 SP-2)

    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.

  14. #14
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (revisited) (2002 SP-2)

    Hope you don't go blind reading this!
    Attached Files Attached Files
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  15. #15
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (revisited) (2002 SP-2)

    Whoa! Hold everything Hans. It works!! I just have to requery the frmLoadBoardTenDay every time I change the status in frmOrderDetail.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

Page 1 of 2 12 LastLast

Posting Permissions

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