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.

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

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

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


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(acExpressi on, _
, "ShowControl([StyleColourCode], 1)")
With objFrc1
.ForeColor = vbBlack
.FontBold = True
.Enabled = False
End With

Set objFrc2 = Me.StyleColourDesc.FormatConditions.Add(acExpressi on, _
, "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