More than three Conditional Formats VBA Access 2010
Hello Experts,
I have been made to understand that in Access 2010, one can set up to 50 Conditional Formats (I read this on some Microsoft Article on the internet).
However, how can one do this in code? Im asking because, on the Form_Load event, I have written some VBA code that calls a function which loops and adds more than three Conditional Formats in Access 2010, however, on the fourth attempt, I get an error telling me:
7966 The format condition number you specified is greater than the number of format conditions
The code is as below:
I have tried using more than three Conditional Formats on the Form's Design View, and it works, why does it not work in Code?
Thanks and Well Appreciated in Advance.
ref-IT
I have been made to understand that in Access 2010, one can set up to 50 Conditional Formats (I read this on some Microsoft Article on the internet).
However, how can one do this in code? Im asking because, on the Form_Load event, I have written some VBA code that calls a function which loops and adds more than three Conditional Formats in Access 2010, however, on the fourth attempt, I get an error telling me:
7966 The format condition number you specified is greater than the number of format conditions
The code is as below:
Sub ApplyConditionalFormatting()
'This Sub Procedure applies Conditional Formatting to the necessary controls
'based on the Fore Colour and Back Colour values in txtForeColor and txtBackColor
Dim objFrc As FormatCondition
Dim ctl As Control
Dim rstConditions As DAO.Recordset
Dim sqlString As String
Dim fcCounter As Integer
sqlString = "SELECT * FROM tblStaff"
Set rstConditions = CurrentDb.OpenRecordset(sqlString, dbReadOnly)
rstConditions.MoveFirst
For Each ctl In Me.Controls
If ctl.Tag = "Conditional" Then
With ctl
'Remove Format Conditions
.FormatConditions.Delete
'Initialise recordset Pointer to move to First Record and value of fcCounter to 0 before entering the loop
rstConditions.MoveFirst
fcCounter = 0
'Add Format Condtions to the Control Object
While Not rstConditions.EOF
Set objFrc = .FormatConditions.Add(acExpression, acEqual, "[txtStaffName] = '" & rstConditions!StaffName & "'")
.FormatConditions(fcCounter).ForeColor = rstConditions!ForeColor
.FormatConditions(fcCounter).BackColor = rstConditions!BackColor
fcCounter = fcCounter + 1
rstConditions.MoveNext
Wend
End With
End If
Next ctl
Set objFrc = Nothing
rstConditions.Close
Set rstConditions = Nothing
End SubI have tried using more than three Conditional Formats on the Form's Design View, and it works, why does it not work in Code?
Thanks and Well Appreciated in Advance.
ref-IT
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hey Macarrillo1,
Thank you for your response again.
I made the changes you suggested, but I got the very same error !!
ref-IT
PS: Its a bit late here (almost 12.30 am) - I'll upload an example file in the morning tomorrow.
Thank you for your response again.
I made the changes you suggested, but I got the very same error !!
ref-IT
PS: Its a bit late here (almost 12.30 am) - I'll upload an example file in the morning tomorrow.
ASKER
I have attached an example for what I am looking for. Attached, please find an Access accdb file, that has two forms.
In frmStaffDesignCondition I have put in some Conditional Formatting from the design view of the form. You'll see that more than three conditions have been applied to the text box control called txtStaffName. Although I have not applied the same Conditional Formatting on the other Text Box Controls on this form, ideally, I would like all the other Text Box Controls to reflect the same Conditional Formatting as the one applied on txtStaffName. Also note, the ForeColor and BackColor Properties for the Text Box Controls should be the same as the one shown on txtForeColor (for the ForeColor) and txtBackColor(for the BackColor).
I would like to emulate the above Conditional Formatting in VBA Code as Im trying to attempt to do that on the frmStaffCodeCondition.
Since the program has no way of comprehending how many staff there are going to be at any given time, it has to use a recordset to perform the Conditional Formats at run time.
This is only an example, in the actual project, this Conditional Format has to be applied to controls in another form that has groups of records that belong to different staff. Thus the staff working on the Access DB, can visually determine what set of records belong to him/her (hence allowing him/her to visually know that the records with the colour that belongs to them are the tasks that they are going to handle).
Many Thanks in Advance,
ref-IT
ConditionalFormatting.accdb
In frmStaffDesignCondition I have put in some Conditional Formatting from the design view of the form. You'll see that more than three conditions have been applied to the text box control called txtStaffName. Although I have not applied the same Conditional Formatting on the other Text Box Controls on this form, ideally, I would like all the other Text Box Controls to reflect the same Conditional Formatting as the one applied on txtStaffName. Also note, the ForeColor and BackColor Properties for the Text Box Controls should be the same as the one shown on txtForeColor (for the ForeColor) and txtBackColor(for the BackColor).
I would like to emulate the above Conditional Formatting in VBA Code as Im trying to attempt to do that on the frmStaffCodeCondition.
Since the program has no way of comprehending how many staff there are going to be at any given time, it has to use a recordset to perform the Conditional Formats at run time.
This is only an example, in the actual project, this Conditional Format has to be applied to controls in another form that has groups of records that belong to different staff. Thus the staff working on the Access DB, can visually determine what set of records belong to him/her (hence allowing him/her to visually know that the records with the colour that belongs to them are the tasks that they are going to handle).
Many Thanks in Advance,
ref-IT
ConditionalFormatting.accdb
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hey Macarrillo1,
Thank you very much for your input - I've managed to get more time from my client for this piece of work (I performed an Upgrade Installation yesterday).
I'll take a look at the piece of code you have offered, perform some experiments with it together with looking at other forums on the net. In the meantime, if you (or any other expert has anything more to offer - It'll go highly appreciated).
I'll post a feedback reply soon.
Many Many Thanks,
ref-IT
Thank you very much for your input - I've managed to get more time from my client for this piece of work (I performed an Upgrade Installation yesterday).
I'll take a look at the piece of code you have offered, perform some experiments with it together with looking at other forums on the net. In the meantime, if you (or any other expert has anything more to offer - It'll go highly appreciated).
I'll post a feedback reply soon.
Many Many Thanks,
ref-IT
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It's been quite some time to this question, however, I needed to close it.
Thanks for all your help experts, but it looks like, that as much as Microsoft has allowed a limit of up to 50 Conditional Formats (citation needed) from the designer interface, the VBA Editor will only allow up to four conditions to be satisfied at a time.
Special thanks to Macarrillo1 - but full appreciation to all the other experts as well.
ref-IT
Thanks for all your help experts, but it looks like, that as much as Microsoft has allowed a limit of up to 50 Conditional Formats (citation needed) from the designer interface, the VBA Editor will only allow up to four conditions to be satisfied at a time.
Special thanks to Macarrillo1 - but full appreciation to all the other experts as well.
ref-IT
ASKER
Many Thanks for your reply.
I can confirm that the number of controls that have the word "Conditional" in their Tag property are four and the counter fcCounter throws the error when its value is 3 (i.e in the the fourth loop, since fcCounter always starts with 0, as Format Conditions are 0 based values).
I could have written several With ctl .... End With statements, problem is, the Format Conditions are based on a Staff table, where the Program cannot comprehend how many there are going to be in that table. And for every record that belongs to a staff in a continuous form, that record should have its own colour. That's the reason for using the recordset and the counter fcCounter.
ref-IT
PS: If you'd want me to attach an example for what Im asking I could do so.