Help Please Conditionally Formating Duplicates

[Mike Wilson]

I am trying to conditionally format duplicated orders in my
order form

The table / form have these fields

AccountNo,OrderNo,LineNo,ProductCode,Product & QuantityOrdered

My attempt at an if statement :

If Me!LineNo and Me!ProductCode = Me!LineNo,+ 1 and Me!ProductCode then
Me!ProductName ForeColor = 255

Else:Me!ProductName Forecolor = 0

Would be grateful for any pointers

thanks

Mike

 

[Steve Schapel]

Mike,

What event are you using for this code? I would imagine your form is a
continuous view form, in which case it will not really be possible to do
what you are asking. If it is a single view form, you could use the
Current event of the form, and a Domain aggregate function, something
like this...
If Me.ProductCode = DLookup("[ProductCode]","YourTable","LineNo=" &
Me.LineNo+1) Then
Me.ProductName.ForeColor = 255
Else
Me.ProductName.Forecolor = 0
End If

In a similar vein, though, on a continuous form I think you will be able
to use Conditional Formatting of the ProductName control (via the Format
menu), with the condition entered as...
[ProductCode]=DLookup("[ProductCode]","YourTable","LineNo=" & [LineNo]+1)

Both of these approaches will result in the first of a duplicated pair
of records being shown in red. It also assumes that the LineNo will
always be strictly sequential within any given order.

 

[Guest]

Steve

This is great - the conditional format expression works a
treat. I should have pointed out that the form was
continous in my first message

Many thanks for your help

Mike

-----Original Message-----
Mike,

What event are you using for this code? I would imagine your form is a
continuous view form, in which case it will not really be possible to do
what you are asking. If it is a single view form, you could use the
Current event of the form, and a Domain aggregate function, something
like this...
If Me.ProductCode = DLookup

("[ProductCode]","YourTable","LineNo=" &

Me.LineNo+1) Then
Me.ProductName.ForeColor = 255
Else
Me.ProductName.Forecolor = 0
End If

In a similar vein, though, on a continuous form I think you will be able
to use Conditional Formatting of the ProductName control (via the Format
menu), with the condition entered as...
[ProductCode]=DLookup

("[ProductCode]","YourTable","LineNo=" & [LineNo]+1)

 

[Guest]

Steve

Just an additional question about this - if I wanted to
quantify the expression further say with a quantity or an
order, using the DLookup method should the following work

[ProductCode, OrderNo]=DLookUp
("[ProductCode,OrderNo]","YourTable","RefNo=" & [RefNo]+1)

If tried the expression varying ways but with no result

Thanks again

Mike

-----Original Message-----
Steve

This is great - the conditional format expression works a
treat. I should have pointed out that the form was
continous in my first message

Many thanks for your help

Mike

-----Original Message-----
Mike,

What event are you using for this code? I would

imagine
your form is a

continuous view form, in which case it will not really be possible to do
what you are asking. If it is a single view form, you could use the
Current event of the form, and a Domain aggregate function, something
like this...
If Me.ProductCode = DLookup

("[ProductCode]","YourTable","LineNo=" &

Me.LineNo+1) Then
Me.ProductName.ForeColor = 255
Else
Me.ProductName.Forecolor = 0
End If

In a similar vein, though, on a continuous form I think you will be able
to use Conditional Formatting of the ProductName

control
(via the Format

menu), with the condition entered as...
[ProductCode]=DLookup

("[ProductCode]","YourTable","LineNo=" & [LineNo]+1)

Both of these approaches will result in the first of a duplicated pair
of records being shown in red. It also assumes that

the
LineNo will

always be strictly sequential within any given order.

Me!
ProductCode then

.

.

 

[Steve Schapel]

Mike,

Nope, that's not valid syntax. Probably what you need is...
[ProductCode] & "," & [OrderNo]=DLookUp("[ProductCode] & ',' &
[OrderNo]","YourTable","RefNo=" & [RefNo]+1)

 

[Mike]

Steve, I've tried it for various combinations - all
working - I think I am beginning to get the hang of it

Thank you very much for your time and support

Mike

-----Original Message-----
Mike,

Nope, that's not valid syntax. Probably what you need is...
[ProductCode] & "," & [OrderNo]=DLookUp("[ProductCode] & ',' &
[OrderNo]","YourTable","RefNo=" & [RefNo]+1)

--
Steve Schapel, Microsoft Access MVP

Steve

Just an additional question about this - if I wanted to
quantify the expression further say with a quantity or an
order, using the DLookup method should the following work

[ProductCode, OrderNo]=DLookUp
("[ProductCode,OrderNo]","YourTable","RefNo=" & [RefNo] +1)

If tried the expression varying ways but with no result

Thanks again

Mike

.