Solved: Check content upon leaving a form

[paddy69]

How to check if all fields are filled upon leaving a form? If one or more fields are left blank when clicking OK I want to display a warning and set the focus to the first blank field on the form.

[fumei]

This is purely a logic issue and can be solved only one way. You check them. Literally.

It depends on how fussy you want to be. Here is SIMPLE pseudo-code, and it only deals with textboxes on the userform:
Sub ExitButton_Click()
Dim oCtl As Control
Dim sControlName As String
dim bSetName As Boolean
For Each oCtl In Me.Controls
If TypeOf oCtl = "Textbox" AND
oCtl.Text = "" Then
If bSetName <> True Then
sControlName = oCtl.Name
bSetName = True
End If
End If
Next
If bSetName = True Then
Msgbox "A field is blank. Please enter data " & _
"into all fields."
Me.Controls(sControlName).SetFocus
Exit Sub
Else
' whatever you do if all fields ARE completed
End If
End SubThis does not keep any record of which ones may be blank (this could be done), but it DOES get the name of the first textbox it finds is blank. Focus will be returned to that one.

[paddy69]

Whatever I try I keep getting run-time errors.
Here's the code I used:

Sub CmdOK_Click()
Dim oCtl As Control
Dim sControlName As String
Dim bSetName As Boolean

For Each oCtl In Me.Controls
If TypeOf oCtl Is TextBox And oCtl.Text = "" Then
If bSetName <> True Then
sControlName = oCtl.Name
bSetName = True
End If
End If
Next
If bSetName = True Then
MsgBox "A field is blank. Please enter data " & _
"into all fields."
Me.Controls(sControlName).SetFocus
Exit Sub
Else
Unload Me
frmDetail.Show
End If
End Sub


Row 7 (If TypeOf oCtl Is...) returns a Run-time error 438 (Object doesn't support this property or method).
Am I doing something wrong?

[fumei]

Try changing the following: For Each oCtl In Me.Controls
If TypeOf oCtl Is TextBox And oCtl.Text = "" Then
If bSetName <> True Then
sControlName = oCtl.Name
bSetName = True
End If
End If
Next
to:
For Each oCtl In Me.Controls
If TypeOf oCtl Is TextBox Then
If Me.Controls(oCtl.Name).Text = "" Then
If bSetName <> True Then
sControlName = oCtl.Name
bSetName = True
End If
End If
End If
Next
I forgot that VBA evaluates the entire AND. I thought it would hit the first parameter (If TypeOf oCtl Is TextBox) and if it was NOT a textbox (it is a commandbutton) it would go...well one of the AND is False, so forget it. This is not the case. It evaluates the entire AND, and then returns True or False.

And since (if the control is a commandbutton) oCtl does not have a property oCtl.Text...it fails.

Replace the code chunk as above. It should work now.

[paddy69]

And it does work! Thanks!