Ideas for identifying records that have blank fields (plural)

[Xixor]

Hi, all.

I have one form setup for users to input data into a table. This form has multiple fields, some of which are always required, some of which are optional, and some of which are only required depending on the selection of an always required field. I HAVE THIS FORM SETUP WORKING PERFECTLY (yay!) using conditional formatting in VBA to highlight blank cells that the user needs to enter something into.

I have a second form setup that is a continuous form summary display of all of the records the first form creates. I can filter on this continuous form and use it to navigate to the individual record I need. This is 99% complete and working, but there is ONE more thing I would like to implement. I want to display some kind of colored tag records in the continuous form to indicate if that record has blank fields that need to be filled in on the first form.

I have attached two screenshots of what I am talking about. For example, there are two yellow-highlighted fields in form 1 thanks to my conditional formatting. In form two, I want to add some kind of tag to draw the users attention to that record showing it has fields that need to be filled in.

I could probably figure this out on my own if we were talking about only a single field, but in this case the code needs to analyze ALL fields on the form, check if any of them are blank that need to be filled in, and then display a marker on the second form. I don't know if there is some code that could analyze if some fields are "yellow" colored (from the other conditional formatting), or if the new code would have to duplicate the underlying conditional formatting code for the entire form.

*note* Making the user fill all the fields in on the form before being allowed to save is not an option in my use scenario. The forms are meant to be returned to multiple times over a period of time as new information becomes available with the end goal of all fields being complete once all information is known. I just need a visual cue on the continuous form to say "hey this form still has blank fields that need to be filled in".

Does anyone have any general ideas on how to accomplish this?

Thank you!!!

[June7]

Generally, write a custom function that does the 'check'. Function returns True or False. Call function from textbox. Set Conditional Formatting based on value returned.

[Xixor]

Thanks June7; that helps a lot.

Any thoughts on how to approach the custom function? Specifically, is there a way to evaluate each field on the form and check to see if the background color is yellow, or would the function need to basically replicate the underlying conditional formatting VBA to evaluate all the coditions for all of the fields?

[June7]

Think I've dealt with this question before and no, don't think there is any way to grab the color set by Conditional Formatting. So, yes, replicate the rules in VBA.

[Xixor]

Got it, thanks!