We help IT Professionals succeed at work.

Formatting based on a previous value

tmckeating asked
I have a simple crosstab query in access 2016 which uses a field called WeekEnd as the column heading which is a date field. So each pupil in my list has an attendance rate for end of every week. I have used conditional formatting to highlight in red those pupils whose rate is below 85%.  What I would like to do is format in red if the attendance rate is below the previous value or green if is above the previous value based on the date. Is this possible?attendance rate
Also noticed that my form does not dynamically update when I add in a new week's data. Its in the crosstab query but does not automatically transfer to the form.

Any help appreciated, many thanks in advance.
Watch Question

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

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

End Sub

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]



I get an error #2465 Microsoft Access cant find the field label "lbl0" referred to in your expression.  I just copied and pasted what you gave me am I supposed to edit in some way to meet my needs. Problem is I am not familiar with VBA so not sure what the lines mean.

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

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.

Sorry my fault its lbl_0 it cant find...apologies. I did try labelling each lbl_0 and txt_0 , then 1 but I cant label the fields that don't exist yet. You can see from my form design it has dates that will be added to as new data is appended each week.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

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
Me.controls("txt_" & fld.OrdinalPosition).visible = true

to read:

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

I think I see now I have to just create as many fields that I think I will need. So one for every week 50+ in anticipation of them being populated. There are only 5 date fields just now as that's how much data I had when I created the form from the crosstab query.
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

if you need to display 50 weeks, then yes, you will need to create a control for each week.  But will you really be running the query and generating 50 weeks worth of columns, or will you be doing that a month or a quarter at a time?  You only need to create enough controls for the number of potential columns in your crosstab query.

The advantage of this technique is that the column names in the query will change, but the Form_Load event will set the ControlSource property of those controls based on whatever is there, so if the query returns:

1/1/2020, 2/1/2020, 3/1/2020


one month and:

2/1/2020, 3/1/2020, 4/1/2020

the next month, you don't need to have 4 controls, but only three.

Perfect ….many thanks.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

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


Very helpful with straight forward instructions for a novice user.