Check for empty/null fields in subform datasheet recordset

[ccondran08]

I have just watched this youtube video clip https://www.youtube.com/watch?v=fe5D4iK0Pvk& in the search for the answer for my problem. This video clip shows you how to loop through all the controls (text boxes) on a form and find the blank control fields and highlight them red and alert with a message box.

For Each ctrl In Me.Controls
If ctrl.Tag = "FILL" Then
If IsNull (ctrl) Or Len (ctrl) = 0 Then
ctrl.BackColour = vbRed
CheckForEmpty = False
End If
Next

However, what I am looking for is a solution that will loop through a certain field in a subform datasheet recordset (query) and highlight in red where there is a blank field and also prompt a message box to alert the user that there are blank fields. I appreciate any help.

 

[arnelgp]

you can use conditional formatting.

 

[ccondran08]

Thanks arnelgp, that was second choice.

 

[rcerda]

I tried the same thing but when you are using a tabular sub form it does not work because it includes previous records that are listed in the sub form for one client ID. I really do not know if it can be done any other way ??

 

[isladogs]

Hi rcerda

Firstly welcome to the forum. Perhaps you could do an introductory post about yourself as well

Anyway as you probably realised, this is an old thread.
I've not tried it in a tabbed subform if that's what you meant (or with a datasheet) but attached is a screenshot of how I do this in a single form layout.

In this example there are two required fields that haven't been completed.
These are highlighted in a deliberately 'loud' colour to make them stand out & a message box explains the issue to users.
I'm not using conditional formatting to do any of this

If its any use, I can post the code tomorrow

 

[Pat Hartman]

@rcerda,
Welcome. Data should be validated BEFORE a record is saved. Use the BeforeUpdate event of the subform and cancel the save to prevent bad data from being added. The problem with attempting to do this from the main form is that only ONE record of the subform can have the focus no matter how many you can see and you may not be aware of what record that is.

If you want to validate OLD data, do it with a query and create a form specifically to fix the problems. Then fix your subform to not allow bad data to begin with.