Same Field, previous record

ListO · Feb 3, 2000

I am trying to write some code which, when looking at a record, can compare a field in that record with the same field of the previous record in that dynaset. What's the syntax?

I want to put in something like:
Answer = Iif([Thisrecord]=[Previousrecord],"X","Y")

Please help a poor ignorant programmer.

highlo01 · Nov 22, 2005

Hi, Did you ever get the result to this problem?

I have a continuous form and I want the first field in each record to not be repeated if it is the same as the previous record - much like a group header in a report would operate!

Any help (even a point in the right direction) would be appreciated

Cheers

ListO · Nov 22, 2005

I haven't time just at the moment to reply, but I can look this up this week-end and see if I can figure out what this was about - it was posted 5 years ago, I didn't ever receive a reply, so I worked out SOMETHING to do what I was doing.

-Curt

highlo01 · Nov 23, 2005

Hi Curt,

Thanks for your offer of help, but I dug a bit deeper and have managed to solve my problem using the below code.

A big thanks goes to Stephen Lebans as I got the basis of this code from his website. http://www.lebans.com/conditionalformatting.htm

Code:

Private Sub Form_Load()

    Dim objFrc1 As New FormatCondition, objFrc2 As New FormatCondition

    Me.StyleColourCode.ForeColor = 11525607  'Detail background colour
    Me.StyleColourDesc.ForeColor = 11525607
    
    Me.StyleColourCode.FormatConditions.Delete
    Me.StyleColourDesc.FormatConditions.Delete
   
    Set objFrc1 = Me.StyleColourCode.FormatConditions.Add(acExpression, _
        , "ShowControl([StyleColourCode], 1)")
    With objFrc1
        .ForeColor = vbBlack
        .FontBold = True
        .Enabled = False
    End With

    Set objFrc2 = Me.StyleColourDesc.FormatConditions.Add(acExpression, _
        , "ShowControl([StyleColourCode], 2)")
    With objFrc2
        .ForeColor = vbBlack
        .FontBold = True
        .Enabled = False
    End With

End Sub

Function ShowControl(ctlColour As Control, controlno As Integer)

    Static prevcode As Variant, rec As Integer
    
    If ctlColour = prevcode And rec <> 1 Then
        ShowControl = False
    Else
        ShowControl = True
    End If

    If controlno = 2 Then
        prevcode = ctlColour
        rec = rec + 1
    End If
    
End Function

Note that I am regulating the first 2 controls ('StyleColourCode' & 'StyleColourDesc') based on the 1st control ('StyleColourCode') as I found that a calculated control does not seem to work (maybe someone else may have more luck with this one), but that suits my application just fine.

If you only have one control then you only need one FormatCondition object and the 'controlno' variable is not required.
If you have more than 2 controls then each FormatConditions.Add needs the second parameter of the call to ShowControl to be increased by 1. In the function ShowControl 'If controlno = 2' needs to be changed to the number of controls you are regulating.

Note also that in this form records can't be added or deleted and the first 2 controls can't be changed (hence the '.Enabled = False').
I have not checked this code for anything other than the above.

Cheers
Craig

Same Field, previous record

ListO

Señor Member

highlo01

Guest

ListO

Señor Member

highlo01

Guest

Similar threads

Users who are viewing this thread