Force data entry in fields

[Pace]

I have a form on which there are a list of questions.

A combo box gives the list of possible answers and a comment box will appear
when certain answers are given. I need to force the user to put something in
the comments boxes when they are displayed.

I have tried putting the following code in the Forms BeforeUpdate and
AfterUpdate properties and also on the Command button that takes the user to
the next form but nothing seems to work and the form simply closes.
Obviously I've got it wrong somewhere - can anyone please help?


Dim Myform As Form
Dim ctl As Object
Set Myform = Screen.ActiveForm

For Each ctl In Myform.Controls
'Only check text boxes.

Select Case ctl.ControlType
Case acTextBox

If ctl.Visible = True And Len(ctl.Value) = 0 Or Ctl.visible = True And
ctl.Value = "" Then
MsgBox "You must enter a comment", vbCritical, "Missing Data"
Cancel = True
End If
Exit For
End Select
Next ctl

 

[NoodNutt]

Private Sub YourCombo_AfterUpdate(Cancel As Integer)
Select Case CLng(YourCombo.Column(0))
Case 1
Me.YourCommentControl.Visible = True
DoCmd.GotoControl "YourCommentControl"
'set as many Case statements as you like that match those answers that
require a comment response from user.
End Select
End Sub

In the AfterUpdate of YourCommentControl
If Me.YourCommentControl = "" Then
MsgBox "You Must Enter a Comment",vbCritical, "Missing Data"
Cancel = True
Else
DoCmd.GotoControl "YourNextControl"
EndIf
End Sub

Just a thought, some people may be a little lazy, and in order to bypass
committing to a comment they may just hit a couple of full stops to satisfy
the fields.

Is your CommentControl A TextField or Memo. I'm not 100% certain if the Null
criteria will work on Memo. But if I am wrong, then all is good, otherwise
one of the many MVP's will set me straight along with the correct structure.

HTH
Mark.

 

[Pace]

Thanks for your reply, Mark - I was trying to come up with some code which,
when the user goes to close the form, would check all visible controls and
return a message saying that all comments need to be filled in if any have
been left empty.

The application is a long questionnaire with many different forms and coding
each control individually will take an age. I do take your point about users
who only enter full stops but the form is audited so we will know who entered
the data and even a full stop will indicate that the user did actually see
the question rather than just skipping a section.