Compare a text box value with a query value

[rilkyn]

This has also been posted on Microsoft: Access Forms as I was not sure where best to put this

Hi

I am using Access '97

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 as they have the highest version numbers

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.

 

[dhookom]

Any code that modifies the properties of controls on a continuous form will apply the modification to every instance of the control. Version of Access > 97 have Conditional Formatting which resolves this.

The first sample at

http://www.access.hookom.net/Samples.htm

has a continuous form with varying background colors but doesn't use conditional formatting.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]