Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2001
    Thanked 0 Times in 0 Posts

    Select Case Statement within a Loop (A2K)

    I need to show all records that belongs to an employee, but at the same time, use a Select Case Statement to change a colour background for each record. The code runs with no errors, but only picks up the last record. If I stop the code, I can see that it is evaluating the records and colouring them correctly. Could someone see if there is something wrong, or maybe I need another line.


    Private Sub Employee_AfterUpdate()


    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim prm As DAO.Parameter
    Dim rst As DAO.Recordset
    Dim Image As Integer

    Set db = CurrentDb
    Set qdf = db.QueryDefs("EmployeeAbsenceYear")
    For Each prm In qdf.Parameters
    prm.Value = Eval(prm.Name)
    Next prm

    Set rst = qdf.OpenRecordset(dbOpenDynaset).Clone

    If Not ErrorCondition Then
    mbFoundFile = True
    mbFoundFile = False
    End If

    If mbFoundFile Then
    If rst.RecordCount > 0 Then

    Do While Not rst.EOF
    HoldDate = rst!Date
    Year.Date = rst!Date
    Select Case rst!Image
    Case 1
    Me.Year.SelectBackColor = RGB(0, 0, 160)
    Case 2
    Me.Year.SelectBackColor = RGB(121, 121, 255)
    Case 3
    Me.Year.SelectBackColor = RGB(185, 185, 255)
    Case 4
    Me.Year.SelectBackColor = RGB(64, 128, 128)
    Case 5
    Me.Year.SelectBackColor = RGB(106, 181, 181)
    Case 6
    Me.Year.SelectBackColor = RGB(171, 214, 214)
    Me.Year.SelectForeColor = RGB(0, 0, 0)

    Case Else
    Me.Year.SelectBackColor = RGB(0, 0, 0)

    End Select

    End If
    End If

    'MsgBox " Database not Found!"
    ErrorCondition = True

    End Sub

    Thank you. If someone can figure this out, I would be grateful and will stop pulling out my hair.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Sacramento, California, USA
    Thanked 1 Time in 1 Post

    Re: Select Case Statement within a Loop (A2K)

    Sorry, but if this is a continuous form, you can't change the backcolor of individual records like that. And what are SelectBackColor and SelectForeColor? Are those controls of some sort? There are tricks for apparently changing backcolors on individual records and you could try conditional formatting as well, but in all cases, you need to use values of some sort from within the individual records, not from another recordset.

  3. #3
    Join Date
    Oct 2001
    Thanked 0 Times in 0 Posts

    Re: Select Case Statement within a Loop (A2K)

    This information populates a calendar and these are the control names and individual records can be changed. Right now it is only picking up the last record so I know that there is something wrong with my wording or order of my coding.

    Thanks for your reply.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Select Case Statement within a Loop (A2K)

    There are two fundamental problems with your approach.
    1. <LI>There is no connection between the records in the recordset and those in the form. So as the code loops through the records of the recordset, the color assignments overwrite each other and only the last "sticks". This approach would never work, even if the second problem didn't exist.
      <LI>If you are working with a continuous form, assigning colors will act on all records, because there is in fact only one set of controls shared by all visible records. Access 2000 and higher have conditional formatting, but that is limited to 4 different formats (one standard plus three conditional).
    There are tricks to get around this; I have attached a sample Access 97 database demonstrating one of those. (I didn't invent this - see File/Database Properties)

    The basic idea is to use a table tblColours that contains small bitmaps. Normally, storing images in a database is a recipe for database bloat, but these are actually bitmaps of only one pixel wide and high, and you only need one for each color. The demo contains four colors, but you can easily add new ones.

    Each color has a unique numeric identifier, and this is used in a table or query. In the demo, the tblData table has a field ColorIndex, so colors are fixed, but you could also calculate the color index in a query.

    The query qrySeveralColours adds the bitmap image to the records of tblData. This query is used as record source of the continuous form. There is an OLE control OleColour bound to the Colour field that fills the detail section and sits behind all other controls. This control simulates a conditional background color; the maximum number of colors is the number of colors in tblColours.
    Attached Files Attached Files

Posting Permissions

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