Checking for missing values 50 times

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DubboPete - 02 May 2005 08:25 GMT

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

Reply to this Message

DubboPete - 02 May 2005 11:36 GMT

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

DubboPete

> Hi all,
>
[quoted text clipped - 8 lines]
>
> DubboPete

Reply to this Message

Allen Browne - 02 May 2005 11:46 GMT

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

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> and no... it's not always 50 instances, it could be twenty or thirty....
> but any help is appreciated...
[quoted text clipped - 13 lines]
>>
>> DubboPete

Reply to this Message

DubboPete - 04 May 2005 12:04 GMT

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

> Pete, you could loop through the controls of the form, to see:
> - if they have a ControlSource (some don't), and
[quoted text clipped - 33 lines]
>>>
>>> DubboPete

Reply to this Message

Allen Browne - 04 May 2005 13:46 GMT

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.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Hi Allen,
>
[quoted text clipped - 42 lines]
>>>>
>>>> DubboPete

Reply to this Message