Avatar of tmckeating
tmckeating
 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?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.
Microsoft Access

Avatar of undefined
Last Comment
tmckeating

8/22/2022 - Mon
Dale Fye

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

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]


HTH

Dale

tmckeating

ASKER
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.

Tricia
Dale Fye

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.

Your help has saved me hundreds of hours of internet surfing.
fblack61
tmckeating

ASKER
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.
formdesign.JPG
Dale Fye

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

Open in new window

to read:

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

Open in new window



tmckeating

ASKER
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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Dale Fye

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
tmckeating

ASKER
Perfect ….many thanks.
Dale Fye

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


Dale

tmckeating

ASKER
Very helpful with straight forward instructions for a novice user.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck