Validating Fields with Save Button

[Talgo]

I have found the following code on a previous Tek-Tips thread provided by Rick39 & have incorporated it into the click event on my Save button located on my subform.

Dim currctl As Integer, numctls As Integer
Dim ctl As Control

numctls = Screen.ActiveForm.Count

For currctl = 0 To numctls - 1
Set ctl = Me(currctl)
If ctl.Tag = "Validate" Then
If IsNull(ctl) Or ctl = 0 Then
MsgBox "The field highlighted in Yellow must contain information." & _
" Please fill in the field and click the Save button again!", vbOKOnly + vbCritical + vbDefaultButton1, "FIELD(S) EMPTY"
ctl.BackColor = 65535
ctl.SetFocus
Exit Sub
ElseIf Not IsNull(ctl) Then
ctl.BackColor = Me!BackColor.BackColor
End If
End If
Next currctl

I'm trying to use this code to insure that there are no blank field on the subform. The subform contains 2 combo boxes containing text data types & 5 text boxes with either text, memo or numeric data types. Presently, the above code only works for 2 fields - 1 numeric & 1 memo data type both of which are text boxes.

I have verified by using the immediate window that all fields are set to Null when the form opens.

I can not figure out how to get the code to work in the other fields on the subform. Can anyone provide a reson why the code is not working & how to fix?

 

[KenReay]

Hi

Not sure if it is your problem, but I would prefer to see

If Len(Trim(Nz(ctl,"&quot&"&quot) = 0 Or ctl = 0 Then

that way you are checking for Null and a blank (ie space filled field

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Talgo]

Hi Ken,

I appreciate your help & I used your suggestion, but I had to get down to the Subform level for checking fields. After a few days of frustration, the code I ended up with is as follows:

Set ctl = Me.Controls
Set ctlSubForm = Forms!TimeCards!TimeCardsSubform

For Each ctlFld In ctlSubForm.Form.Controls
If ctlFld.Tag = "Validate" Then
If Len(Trim(Nz(ctlFld, "&quot & "&quot) = 0 Or ctlFld = 0 Then
ctlFld.BackColor = 65535
MsgBox "The field highlighted in Yellow must contain information." & _
" Please fill in the field and click the Save button again!", vbOKOnly + vbCritical + vbDefaultButton1, "FIELD(S) EMPTY"
ctlFld.SetFocus
Exit Sub
ElseIf Not IsNull(ctlFld) Then
ctlFld.BackColor = Me!BackColor.BackColor
End If
End If
Next

This code now works on all fields on my Subform. To get the field edited, you must put the word Validate in the Tag property of the control you want to have checked. You must also setup a txtBox named BackColor (not visable) on the Subform with a default color value of 16777215 (white) for the color change to work properly.