Compare a text box value against a query

[rilkyn]

Hi

I have a form with a subform set to continuous forms. This form reads from table [Access Paths] and displays five pieces of information for each record.

What I have been trying to do, however is highlight the text box where the value from the record is not the highest value available.

An example of the data I am working with:

Datagroup Version
CD010 1
CD010 3
CD010 5
CD020 3
CD020 5

In the above example I would want (CD010, 5) and (CD020, 5) to be highlighted.

I've already written a query which pulls out the highest version values by Datagroup, called 'Gold Datagroup query'.

I tried something like this:

Dim Backcolour As String
Dim DatagroupID As String

Backcolour = [DATA_GRP_VER]
DatagroupID = [DATA_GRP_ID]

If Backcolour = [Version]![Gold Datagroup query] Then
ElseIf DatagroupID = [Datagroup]![Gold Datagroup query] Then
[DATA_GRP_ID].BackColor = vbRed

Else

[DATA_GRP_ID].BackColor = vbGreen

The error I am getting relates to it not recognising the column on the query. I am guessing I have the syntax wrong but have had no luck in trying to find an example of what I am trying to do.

Any help would be appreciated.

 

[rilkyn]

Sorry, forgot to add that I am using Access '97

 

[ProgramError]

Have you tried using conditional formatting?
and the max() function

Ian Mayor (UK)
Program Error
Always make your words sweet and nice. Because you never know when you may have to eat them.

 

[MajP]

As ProgramError stated this can only be done using conditional formatting. Your attempted methodology would cause all records to change color. Select the apllicable textboxes and choose conditional formatting. Select "Expression is". Then do something like
[DATA_GRP_ID] = dmax("[DATA_GRP_ID]","[Acess_Paths]")
You may want to add a "me.requery" on the forms on current event, to ensure the records are colored after a delete or add.