Problems with Form - Conditional Formatting not behaving as expected

Wag2016

Registered User.
Local time
Today, 07:39
Joined
Mar 5, 2016
Messages
24
Please help - I do not know where I am going wrong with this. I have always just used vba to backcolor fields. This time I am using conditional formatting (new to it) and it is so simple. Just can't get it.

I have a form with 30 questions. Number field holds 3 values, yes 1, no 2, na 3. It needs to turn Green with yes, red with no, yellow with NA. Option group, radio buttons.

Is there a property to set the master order for these option groups? It seems like it almost has to go in tab order in order for the CF to behave. My users are going to pick and choose questions to answer - it can't go in sequential order. Question 10 has me stumped. Everything is there, but it will not backcolor. 11-14 work, but not ten. If I go back and work the options in sequential order, then 10 suddenly pops up and behaves.

Then there is a refresh issue. I may have too many controls on the page, but it seems to have a redraw problem. The form blanks out in spots after it calculates. This is the most complicated form I have done in a long time. I am just at a loss now.

Setup
Option group (SQ10F) 3 options. Transparent. Enabled, not locked. Underlying field is SQ10 (Number). After Update event - macro triggers save and then refresh. Tried the refresh to improve the redraw problem.

Unbound text box (tried it bound to SQ10 too, didn't change anything) SQ10C. Sits directly behind the option group. Formatting: Rule 1 Expression, [SQ10]=1 (Green), [SQ10]=2 (Red), [SQ10]=3 (Yellow).
That is all there is to it.
:banghead:
I would really appreciate any help or ideas. VBA a better idea?
 
Hi all,
I have a screenshot of the form showing the first 9 conditional format fields, and then the failure starting at 10. It is the same exact code etc, just changed the names of the two controls.

I have searched everywhere I can and I cannot find any references to a limitation of option control groups and their conditional formatting. I am grasping at straws.
 

Attachments

  • ss.PNG
    ss.PNG
    12.6 KB · Views: 154
Well here I am again with an update. I started to recreate the form (thinking it would be good to start again). I had a thought that maybe there was just too much to process on the form. I have 30 questions on there that are dlookup to a table. Calculating...calculating. So I omitted the questions. And number 10 worked.

I am disappointed about the questions. Is there an alternative to dlookup?
 
Is there an alternative to dlookup?
The best alternate would be joins so that the fields are part of the forms record set but I can't say if that is possible with the information you have provided. Could you upload the database or if that's not possible a screen shot of the relationships. Let us know which tables and fields are involved.

Another alternative would be subforms but I wouldn't expect much improvement in performance with them if they are not related.
 
Hi all,
I have a screenshot of the form showing the first 9 conditional format fields, and then the failure starting at 10.
Do questions 5 (is N/A and is green), 6 (is NO and is green) and 7 (is Yes and is red), have the right colors?
 
I had similar issues. Vba / conditional works with . (point) while my regional settings were , (comma)
Still having lots of fun with that... especially in conditional format and SQL strings

I am working with function combinations like cstr (to get a string), replace (to replace the , for .) and then another type conversion you need
https://msdn.microsoft.com/en-us/library/s2dy91zy.aspx

hope that helps
 

Users who are viewing this thread

Back
Top Bottom