locked
Reference controls record by record RRS feed

  • Question

  • I am using a Continuous Form that has a control on it called "Status".  I want to change the background color of this control depending on the value of today's date versus a scheduled date.  I successfully change the background color but it changes the color of the control on every record in the recordset instead of by each record's status.

     

     

    How do I reference the “Status” control individually by record?  I would like to reference it like this:

    Me.Status(intRecordNo).BackColor = vbRed

    using an index of some sort.

     

     

    Friday, September 23, 2011 3:07 PM

Answers

  • GordonGecko wrote:

    OK, I created three format rules in the order you gave me.  It seems to
    work if DateRevisedOrComplete exists but not if it is NULL.  I changed
    your code to be DateRevisedOrComplete ="" but it still didn't work.

    You have to use Nz or IsNull to check against NULL. What should happen, if
    DateRevisedOrComplete is Null? I don't see that in your original code.


    Peter Doering [MVP Access]

    • Marked as answer by Bruce Song Monday, October 3, 2011 9:09 AM
    Wednesday, September 28, 2011 1:00 PM

All replies

  • GordonGecko wrote:

    I am using a Continuous Form that has a control on it called "Status".  I
    want to change the background color of this control depending on the
    value of today's date versus a scheduled date. 

    Conditional Formatting will do the job for you. Form design, select the
    control, ribbon Format - Control Formatting - Conditional Formatting, New
    Rule ...


    Peter Doering [MVP Access]

    Friday, September 23, 2011 3:13 PM
  • Hi thanks.  I think my condition checking might be too complex for this.  It would require several nested IIF statements.  Can you have nested IIF statements like Excel?
    Friday, September 23, 2011 3:30 PM
  • Here is what I'm trying to do but I need to refer to each control by it's record number index:

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset
    Dim frm As Form
    Dim intRecNo As Integer
    Dim intDays As Integer
    Dim varRecCount As Variant

    Set db = CurrentDb
    Set rst = Me.Recordset

    'On Error GoTo StatusAI_Exit

    'get count of active Action Items

    If rst.EOF Then
          varRecCount = 0
          GoTo StatusAI_Exit
       Else
          rst.MoveLast
          varRecCount = rst.RecordCount
    End If

    'Loop through each record to determine due dates and status

    rst.MoveFirst
    varRecCount = 1

    While Not rst.EOF 'Me.CurrentRecord < varRecCount


        If Not Forms!frmActionItems!DateRevisedOrComplete Then
            intDays = Forms!frmActionItems!DateRevisedOrComplete - Forms!frmActionItems!txtToday
        Else
            intDays = Forms!frmActionItems!DateOriginalDue - Forms!frmActionItems!txtToday
        End If

        Select Case intDays
            Case Is < 1
                Forms!frmActionItems!Status.BackColor = vbRed
            Case 1
                Forms!frmActionItems!Status.BackColor = vbYellow
            Case Is > 1
                Forms!frmActionItems!Status.BackColor = vbGreen
        End Select

        rst.MoveNext
        varRecCount = varRecCount + 1
       
    Wend


    StatusAI_Exit:
        Exit Sub

    End Sub

    Friday, September 23, 2011 3:44 PM
  • Is your Status control unbound? That would cause all records to have the same formatting on a continuous form. If it is unbound can you add that field to your query so the control can be bound?
    Bill Mosca
    http://www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals
    Friday, September 23, 2011 4:16 PM
  • It is bound to a field in the table tblActionItems called Status
    Friday, September 23, 2011 4:18 PM
  • GordonGecko wrote:

    Here is what I'm trying to do but I need to refer to each control by it's
    record number index:

    I dind't study all your code, but from following statements ...

        If Not Forms!frmActionItems!DateRevisedOrComplete Then
            intDays = Forms!frmActionItems!DateRevisedOrComplete - Forms!frmActionItems!txtToday
        Else
            intDays = Forms!frmActionItems!DateOriginalDue - Forms!frmActionItems!txtToday
        End If

        Select Case intDays
            Case Is < 1
                Forms!frmActionItems!Status.BackColor = vbRed
            Case 1
                Forms!frmActionItems!Status.BackColor = vbYellow
            Case Is > 1
                Forms!frmActionItems!Status.BackColor = vbGreen
        End Select

    ... I understand you require 3 conditional formats for control [Status],
    because of 3 back colors. Expressions would be ...

    for red:
    ([DateRevisedOrComplete]=0 And ([DateRevisedOrComplete]-[txtToday])< 1) OR
    ([DateRevisedOrComplete]<>0 And ([DateOriginalDue]-[txtToday])< 1)

    for yellow:
    ([DateRevisedOrComplete]=0 And ([DateRevisedOrComplete]-[txtToday])= 1) OR
    ([DateRevisedOrComplete]<>0 And ([DateOriginalDue]-[txtToday])= 1)

    for green:
    ([DateRevisedOrComplete]=0 And ([DateRevisedOrComplete]-[txtToday])> 1) OR
    ([DateRevisedOrComplete]<>0 And ([DateOriginalDue]-[txtToday])> 1)


    Peter Doering [MVP Access]

    Friday, September 23, 2011 9:56 PM
  • OK, I created three format rules in the order you gave me.  It seems to work if DateRevisedOrComplete exists but not if it is NULL.  I changed your code to be DateRevisedOrComplete ="" but it still didn't work.

    Monday, September 26, 2011 3:29 PM
  • I am back to my original question.  I am using several Select queries when I add or edit records in this form.  The problem is that the event fires for all controls of the same name instead of just the control on the active record.  I need to just fire the event on the control for the active record.  Therefore, I need to be able to reference a control with some sort of index or by using the ActiveRecord property.
    Monday, September 26, 2011 8:21 PM
  • GordonGecko wrote:

    OK, I created three format rules in the order you gave me.  It seems to
    work if DateRevisedOrComplete exists but not if it is NULL.  I changed
    your code to be DateRevisedOrComplete ="" but it still didn't work.

    You have to use Nz or IsNull to check against NULL. What should happen, if
    DateRevisedOrComplete is Null? I don't see that in your original code.


    Peter Doering [MVP Access]

    • Marked as answer by Bruce Song Monday, October 3, 2011 9:09 AM
    Wednesday, September 28, 2011 1:00 PM
  • GordonGecko wrote:

    I am back to my original question.  I am using several Select queries
    when I add or edit records in this form.  The problem is that the event
    fires for all controls of the same name instead of just the control on
    the active record.

    What event are you using? The event firing on the current row is the
    Form_Current event.


    Peter Doering [MVP Access]

    Wednesday, September 28, 2011 1:04 PM
  • Hi GordonGecko,

    How about the problem on your side? Do Peter's suggestion help you? If you still show any concern on the problem, just feel free to let us know.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, September 30, 2011 5:50 AM