Solved: ensuring that all questions are answered on a form

[ironj32]

I have a form with 4 different tabs/pages. Each page on the form has a number of controls which either are named "txt1, txt2, txt3, etc..." or "cbo1, cbo2, cbo3, etc..." When a users clicks cmdFinish I would the form to ensure that each control has a value, and if not I would like it to show a message box saying "Please answer all questions".

I'm trying to use the code below, but it's not working. Any suggestions?

Private Sub cmdComplete_Click()
Dim Str As String, tmp As String, Ctrl As Control
For Each Ctrl In Me.Form.Controls
tmp = Left(Ctrl.Name, 3)
Select Case tmp
Case "txt"
If Ctrl = "" Then
Ctrl.SetFocus
Ctrl.BackColor = &HFFFF&
MsgBox "Please complete " & Ctrl.Name
Exit Sub
End If
Case "cbo"
If Ctrl = "" Then
Ctrl.SetFocus
Ctrl.BackColor = &HFFFF&
MsgBox "Please complete " & Ctrl.Name
Exit Sub
End If
End Select
Next Ctrl
End Sub

[mattj]

Just a suggestion - but you may want to consider giving your controls a meaningful name - it makes thigns much easier, and you know which control your wokring with.
Regarding your question, use the before update event of your form to loop through each control... You can remove the control types that you dont wish to check.

Dim ctlCurr As Control
For Each ctlCurr In WhatForm.Controls
Select Case ctlCurr.ControlType
Case acCommandButton, acOptionButton, acCheckBox, acOptionGroup, acBoundObjectFrame, acTextBox, acListBox, acComboBox, acSubform, acObjectFrame, acCustomControl, acToggleButton

'insert code to check if controls are null


End Select

[ironj32]

Thanks Matt,
I'm using the code below and it still isn't working. I don't get any errors. It's just basically doing nothing.

Private Sub cmdComplete_Click()
Dim ctlCurr As Control
For Each ctlCurr In Me.Form.Controls
Select Case ctlCurr.ControlType
Case acTextBox, acListBox
If ctlCurr = "" Then
MsgBox "Please Complete the form"
End If
End Select
Next ctlCurr
End Sub

[ironj32]

okay, i got this to work for all of my combo box's, however i would only like the message to appear once....even if 8 controls are left blank.
thanks!

Private Sub cmdComplete_Click()
Dim Str As String, tmp As String, Ctrl As Control
For Each Ctrl In Me.Form.Controls
tmp = Left(Ctrl.Name, 3)
Select Case tmp
Case "cbo"
If Ctrl = "Yes" Then
ElseIf Ctrl = "No" Then
ElseIf Ctrl = "N/A" Then
Else: MsgBox "Please complete " & Ctrl.Name
Exit Sub
End If
End Select
Next Ctrl
End Sub

[mattj]

You're exiting the sub and not continuing the code to test the remaining controls, so it look lik eit sohuld stop once it reaches the first empty control. Also "" is not the same as Null - it's an emtpty string.

I would check for both, or use the len fucntion to test how many characters are in each control.

In the code that didn't work, you haven't included combo boxes as a type of control to be tested.

[ironj32]

When I did
If Ctrl = "" Then - nothing happened.

&
If Ctrl Is Null - I get an error "Object Required"

I'm not familiar with the len function...i will look into that.

[mattj]

I believe it would be If IsNull(ctl.Name)

[DarkSprout]

Use an String array, to test all controls:


Dim strTextBoxes As String
Dim aTextBoxes() As String
Dim x As Integer
strTextBoxes = ("cboOne;cboTwo;TextOne;TextTwo")
aTextBoxes = Split(strTextBoxes, ";")
For x = LBound(aTextBoxes) To UBound(aTextBoxes)
Select Case Nz(Me.Controls(aTextBoxes(x)), "")
Case ""
MsgBox "Please complete " & aTextBoxes(x), vbInformation, "Input Error"
Exit Sub
Case Else
'// Do something if need be
End Select
Next x
DoCmd.Close acForm, "frm_FormNameToSave"

[ironj32]

Thanks for all your help! The below code is exactly what I need.

Dim Str As String, tmp As String, Ctrl As Control
For Each Ctrl In Me.Form.Controls
tmp = Left(Ctrl.Name, 3)
Select Case tmp
Case "cbo"
If IsNull(Ctrl) Then
MsgBox "Please note that you are not finished with this survey."
Exit Sub
Else
End If
Case "txt"
If IsNull(Ctrl) Then
MsgBox "Please note that you are not finished with this survey."
Exit Sub
Else
End If
End Select
Next Ctrl