Checking for missing values 50 times

  • Thread starter Thread starter DubboPete
  • Start date Start date
D

DubboPete

Hi all,

I am au fait with getting a response if a value/text is missing in a field,
but how about a BeforeUpdate event checking nearly 50 fields?

So, instead of 50 instances of If IsNull(me.text1) Then...
is there any quicker way of checking and reporting back the fields that are
missing values or text?

tia

DubboPete
 
and no... it's not always 50 instances, it could be twenty or thirty....
but any help is appreciated...

DubboPete
 
Pete, you could loop through the controls of the form, to see:
- if they have a ControlSource (some don't), and
- the Control Source is not a zero-length string (Unbound), and
- the Control Source is not an expression (starts with "="), and
- the control is Null.

To get you started:

Private Sub Form_BeforeUpate(Cancel As Integer)
Dim fld As DAO.Field
Dim strMsg As String

For Each ctl In Me.Controls
Select Case ctl.ControlType
Case acTextbox, acCombo, acListBox, acCheckbox, acOptionGroup, ...

End Select
Next
End Sub
 
Hi Allen,

"goat-herder" head on here again, how do I start the loop?
(not quite enough information there below...)
don't know how to do loops.... apart from fruit-loops....

signed
Goat-Herder (circa 1658)
1
 
You will need an understanding of VBA code to write this Pete.

Basically, you are examining each control, seeing it it has a Control Source
property, and if it does that the Control Source holds something (not
unbound) and that it does not start with "=" (bound to an expression.) You
can then test if the Value is Null, and if so, concatenate the Name of the
control into the warning string to show after the loop.
 
Back
Top