Checking if controls are emply (1 Viewer)

[JPR]

Hello,

I would appreciate your help with VBA code that checks if any of the multiple controls on my form are empty. Controls are combo and textboxes. My form has about 5 combos and 20 textboxes.
If any of these controls have no data, then I would like to add a msg box saying, please complete all field and stop the code running (the button simply exports data to a word template. If all controls have data, then the code will continue.

Thank you for any help.

 

[Ranman256]

sub CheckAllCtls
dim ctl as control

for each ctl in controls
  
   if typename(ctl) = "Textbox" or typename(ctl) = "Combobox" then
      if isnull(ctl) then debug.print ctl.name & " is missing"
   endif 
next

set ctl = nothing
end sub

 

[arnelgp]

here is a demo.
you need to add a Tag ("Required") to each textbox/combobox that you
want to validate. When you attempt to close the form with incomplete entry,
it will highlight that field and show a message.

 

[JPR]

Thank you both for the help.
While testing Arnelgp's code I have noticed that when trying to enter data in the empty controls, I get an error message (see file) each time I click in a textbox or cbo. (see attached screenshot).

Since I really like the idea of coloring the empty controls I was also thinking that it may be a better idea to give users the option to either close the form (reminding them that there are empty controls) or continue and enter data. This would probably require a different input box with the YES/NO buttons.

I have also noticed that there is no way to open the form in design view or simply exit the db.
Thank you and sorry for the trouble.

 

[The_Doc_Man]

Traditionally, you would write code to be executed in the FORM_BEFOREUPDATE event (which has a CANCEL argument in the call.)

You would sweep through the controls to decide which ones need filling and do a color change on them. (Last time I did this, I made the control's background color = vbYellow. Can't miss that one easily. If you made even ONE of the controls yellow, you would set CANCEL = 1 or -1 or TRUE. Any of those would be the same. You would then also pop up a message box warning that one or more critical values were missing. Use the option vbOKOnly on this particular box.

In the _BeforeUpdate context, setting CANCEL= <<non-zero value>> has the effect of preventing the update. You would find if your user tried to close the form without correcting it that it would stay open - because closing a dirty form implicitly updates the record and if the record is not complete, THAT update will ALSO be disallowed.

As to HOW you decide that the control needs a value, unfortunately the choice of method is up to you and I can't advise you. It is always a matter of "whatever best appeals to you." However, Arnel suggests placing something in the control's .TAG property and that selection method usually works well.

As an alternate, ... if you require that any bound control must have a value, you can test at least some of those controls for having a .ControlSource that isn't a constant or expression or function. If you require any combo boxes to have a value (and they don't have a declared .DefaultValue) then you can check the combo's .ListIndex, which will be -1 if nothing has been selected.

 

[JPR]

Thank you for your help. At the end I have used each of your suggestions and have come up with the code that I am sharing.

My idea is to associated the code to a button that exports data to a MS Word template. The controls that will contain data cannot be null therefore the code checks if the control is or not Null and if Null, a message box will inform user to enter the missing information and changing the cell's border color. Thank you

If IsNull(Me.LNAME) = True Then
Me.LNAME.BorderColor = vbRed
Else
Me.LNAME.BorderColor = vbGray
End If
If IsNull(Me.LNAME) Then
MsgBox "Please enter Last Name.", vbOKOnly, "Missing information!"
Me.LNAME.SetFocus
Exit Sub
End If

 

[Pat Hartman]

The validation code belongs in the form's BeforeUpdate event. If the data is required for the record to be valid, that is correct event to use so you can stop the record from being saved.

I made two important modifications. Instead of checking for just Null, I changed the code to check for BOTH null and ZLS. The second important change is --- Cancel = True -- that is how you keep Access from saving a bad record.

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Me.LNAME & "" =  "" Then
    Me.LNAME.BorderColor = vbRed
    MsgBox "Please enter Last Name.", vbOKOnly, "Missing information!"
    Me.LNAME.SetFocus
    Cancel = True
    Exit Sub
Else
    Me.LNAME.BorderColor = vbGray
End If

End Sub

 

[JPR]

Hello Pat. I have changed the code as you recommended and up to know no issues.
The only other think I would like to ask is what exactaly do you mean with BOTH null and ZLS.
Thank you and sorry but I am not an expert.

 

[Gasman]

Pat is likely asleep now: .)

A control could be Null or look like Null as it is empty, but is actually a Zero Length String (ZLS) which is ""
So by appending "" to the control/variable, even if it was Null, you have now made it a ZLS and now need only to test for that.?

 

[Pat Hartman]

Thank's Gasman. Another way is to use the Len() function
If Len(somefield) = 0 then '''' field is "empty"

I once had a chart of how expensive each type of expression was and functions are "heavy" compared to straight VBA which is why I use the concatenation method rather than the Len() method.

The reason you have to worry about ZLS is because empty controls are initially null. If a user types something and uses the esc key to remove the value, it goes back to null. If he backspaces, the control still looks "empty" but now it has a ZLS and is not null so IsNull() would return false.

Also, the default value for AllowZLS has been changed from No to Yes so now you have to be concious as you are defining your table properties. Do I want this text field to contain a ZLS and the answer is almost always NO so I strongly disagree with MS decision on this part. Fore example, if you do not understand the import of this property setting you might leave the default and set a field like CustomerName to be required. That sure makes sense. There is no customer that will have no name so you always want the customer name. However, if you left the ZLS setting at Yes, you could save "blank" as the Customer name and not get an error.