Prevent save if all fields not answered

[Kurt]

I have a main form with one subform, used to enter survey
responses. The subform has two visible controls, QstnText
and cboRspns, which are populated when the user enters a
survey date on the main form and clicks an "Enter Survey"
button. Then the user proceeds to enter a response for
each question populated (there's a total of 22
questions).

When the user is done, I would like to ensure that every
question has been answered (i.e., cboRspns Is Not Null
for every question). If this condition isn't satisfied,
the user would be prompted with a message when he tries
to add another record or leave the form. The record would
not be saved.

It seems I need to have code in the Before Update event
of *both* the main form and the subform. The code in the
main form would prevent data from being saved if the user
enters a survey date but doesn't enter any of the subform
data (e.g., if he didn't click on the "Enter Survey"
button which populates the subform) and instead just
closed the form. Second, the code in the subform would
check for any null values in cboRspns and prevent the
save if needed.

I just can't seem to figure out the code for this or if
this is even the best way to go. Any ideas? Thanks.

- Kurt (some details below)

The RecordSource of the main form:
-------------------------------------------
SELECT DISTINCTROW tblSrvRspns.RspnsID,
tblSrvRspns.SurveyDate
FROM tblSrvRspns
ORDER BY tblSrvRspns.RspnsID;

The RecordSource of the subform (abbreviated):
---------------------------------------------------------
SELECT DISTINCTROW tblResponses.*, tblQuestions.QstnText,
tblQuestions.QstnNum
FROM tblResponses
RIGHT JOIN tblQuestions ON
tblResponses.QstnID=tblQuestions.QstnID
ORDER BY tblQuestions.QstnNum;

The Row Source for cboRspns is:

 

[Jeff Boyce]

Kurt

Conceptually, the main form HAS been saved when you click into the subform.
Otherwise, how would the subform "know" which main-form key it is related
to? Besides, forcing the user to enter all questions/responses before being
able to save the survey seems, well, heavy handed. What if someone needs to
take a break, or there's a fire alarm, or ...?

Would it be sufficient to have a "flag" control on your main form that is,
say, an annoying color of red until all subform (questions) have been
entered? Your main form could have code that runs when each record is
loaded (On Current) that inspects the question/response table and confirms
(or not) the completed set of questions, setting the flag. Your subform
could have code that inspects the same, setting the [parent]! form's flag if
not...

JOPO (just one person's opinion)

Good luck

Jeff Boyce
<Access MVP>

 

[Kurt]

Conceptually, the main form HAS been saved when you

click into the subform. Otherwise, how would the
subform "know" which main-form key it is related
to?

I see. Perhaps then, if there is missing data in the
subform, I could undo the main form save which occurred
earlier in the process.

I put this code in the main form's Before Update event.
It prevents the 1st save of interest but the form closes
instead of returning focus to a control. How can I keep
the form open?
------------------------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
' Undo save and keep form open if date is missing
If IsNull(Me.SurveyDate) Or Me.SurveyDate = "" Then
MsgBox "The survey date is missing. & _
Chr(13) & Chr(13) & "Please enter a survey date
or delete the record.", 48, "Missing Survey Date"

DoCmd.RunCommand acCmdUndo
Me.SurveyDate.SetFocus
Else
'do nothing
End If
End Sub
------------------------------------------------------

Besides, forcing the user to enter all
questions/responses before being able to save the
survey seems, well, heavy handed. What if someone
needs to take a break, or there's a fire alarm, or ...?

If the survey were much longer than 22 simple questions,
I would agree. But for data quality issues, I'd still
like to force this requirement. So I tried adding this
code to the subform:
--------------------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
' Prevent record from being saved if missing data
If IsNull(Me.Rspns) Or Me.Rspns = "" Then
MsgBox "You cannot save the data until you fill
in all of the questions. " & _
"Use the missing value (-9) if needed.",
vbCritical, "Missing Data"

DoCmd.RunCommand acCmdUndo

Else
'do nothing
End If
End Sub
---------------------------------------------------

The problem with this code is that it executes only if
the user selects an answer in cboRspns and then deletes
it. Additionally, it executes for *each* cboRspns which
is populated, instead of executing at the subform level
after evaluating all of the cboRspns values. Perhaps the
BeforeUpdate event is not the place to put the code?

Kurt

 

[Jeff Boyce]

Kurt

Again, why would you make a user re-enter mainform (and subform) data simply
because they hadn't entered EVERYTHING related to the survey/response?

Good luck

Jeff Boyce
<Access MVP>

 

[Kurt]

For those interested, here's what I ended up with which
seems to work. This code is behind the Close button and
makes use of some query results to determine if the
record meet certain conditions. (I couldn't get this to
work with the BeforeUpdate event, so the user can
circumvent this code by moving to another record then
closing on a record which meets the conditions. So, this
code will help reduce - but not eliminate - the chance of
missing values.)

------------------------------------------------
If IsNull(Me.RspnsID) Then

' A record hasn't been started. Okay to close.
DoCmd.Close

ElseIf IsNull(Me.SurveyDate) Or Me.SurveyDate = "" Then

' Survey date is missing. Cancel & inform user.
DoCmd.CancelEvent
MsgBox "The survey date is missing. This is required
information." & _
Chr(13) & Chr(13) & "Please enter a survey date or
delete the record.", 48, "Missing Survey Date"

Me.SurveyDate.SetFocus

ElseIf DCount("*", "qrySrvRspns") = 0 Then

' A survey has been started but no questions have
' been answered.
DoCmd.CancelEvent
MsgBox "You've begun a survey but haven't answered
any questions. Please click the 'Enter Survey'
button to enter the survey " & _
"or delete the record. ", vbCritical, "Missing Data"

ElseIf DCount("*", "qryNullResponses") > 0 Then

' A survey has been started but some questions
' haven't been answered.
DoCmd.CancelEvent
MsgBox "You cannot save the data until you fill in all
of the questions. " & _
"Use the missing value (-9) if needed.",
vbCritical, "Missing Data"
Else
DoCmd.Close
End If