Link to home
Start Free TrialLog in
Avatar of Tricia McKeating
Tricia McKeatingFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Formatting based on a previous value

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?User generated image
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.
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

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

Open 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]



Avatar of Tricia McKeating


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.


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.

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

Open in new window

to read:

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

Open in new window

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.
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Perfect ….many thanks.

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


Very helpful with straight forward instructions for a novice user.