Formatting based on a previous value

Crosstab queries are notoriously difficult to format.

Basically, what you will need to do is create a datasheet or continuous form that is based upon your crosstab query.  This is easy with the first time, because you already have a query that returns a set of columns.

But to do this with a query which will change over time, you have to use the Form_Load event to loop through the fields of the recordsource and reset the controlsource properties of the textbox, and the caption properties of the lables for each column.  To make this easier, name the labels and textboxes with a syntax like (lbl_0, lbl_1, txt_0, txt_1): 

Private Sub Form_Load

    Dim rs as dao.recordset
    dim fld as dao.field

    set rs = me.recordsetclone
    for each fld in rs.fields
        Me.Controls("lbl_" & fld.OrdinalPosition).Caption = Nz(fld.Name)
        Me.Controls("lbl_" & fld.OrdinalPosition).visible = true
        Me.Controls("txt_" & fld.OrdinalPosition).ControlSource = fld.Name
        Me.controls("txt_" & fld.OrdinalPosition).visible = true
    next

End Sub

Select allOpen in new window

Note: I generally add some error handling in there just in case the recordset is wider than the form has controls.  I also set the visible property of all the controls on the form to False, so that if the recordset contains less columns than the form has controls, those controls will not be visible.

Once you have done this, then you can create conditional formatting of each textbox based on the value of the previous textbox, with an expression that looks something like:

[Forms]![yourFormName]![txt_1] < [Forms]![yourFormName]![txt_2]

HTH

Dale

Well, the code I provided should result in a control name "lbl_0", not "lbl0"

lets start from scratch, can you take a screen shot of your report or form in design view.  It should have controls with label names "lbl_0", "lbl_1", "lbl_2", and "txt_0", "txt_1", "txt_2", ... in the order that they will show up in your query results.

Then copy the code that you are using so that I can determine why you received an error about "lbl0".  Paste the code here, then highlight it and click on the code snippet icon (5th from the left) on the comment editing toolbar.

do all of those controls show up in the query, in the order they are displayed on the form design?

If so, start at the top and change the label names and control names starting with "lbl_0", and going through "lbl_16".  Then, instead of using "txt_0" through "txt_16", I would give the others names of "ctrl_0" through "ctrl_16"

I understand that this may be confusing, and that the control names for the last 5 items will change over time, as you change the criteria for your query, but just do it.  Then modify:

Me.Controls("txt_" & fld.OrdinalPosition).ControlSource = fld.Name

Select allOpen in new window

to read:

Me.Controls("ctrl_" & fld.OrdinalPosition).ControlSource = fld.Name

Select allOpen in new window

glad that worked for you.  It will work in reports as well.

Dale