Before Update on Form

[lmnop7854]

I am wondering if there is a way to check the data that was entered on a form to make sure that some fields were filled in. Here is the deal - I have a questionnaire-type form, and there are Yes/No questions with comments. Is there a way to make sure that when the questions is answered with "No", that the comments MUST be filled in? I was thinking this should be done at the form level, under the Before Update event.

Second part of the question - there is one Yes/No question on the form that does not have comments, and so should not be subject to this event. Is there any way to code this for all but one question?

Any ideas would be welcome. Thanks.

Lisa

 

[misscrf]

I am by no means an expert, but let me share my method for handling this. You can either make a save record button or a new record button ( Access saves data entered the minute a record is started.)

Here is the evaluation part... Onclick of the button, before the action does the command of a redundant save or goes to a new record, it looks at the fields you want.

Here is code that I had done which would be similar:

Private Sub CmdSaveCand_Click()
On Error GoTo Err_CmdSaveCand_Click

Dim Msg, Style, Title, Response, Mystring

If IsNull(Me![Salutation]) Or IsNull(Me![First Name]) Then

Msg = "There are empty fields on this form. Would you like to go back and complete these?" ' Define message.

Style = vbYesNo ' Define buttons.

Title = "Required selection missed." ' Define title.

Response = MsgBox(Msg, Style, Title)

End If

If Response = vbYes Then ' User chose Yes.

Me!Salutation.SetFocus ' Perform some action.

Else ' User chose No.

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 ' Perform some action.

End If

Exit_CmdSaveCand_Click:
Exit Sub

Err_CmdSaveCand_Click:
MsgBox Err.Description
Resume Exit_CmdSaveCand_Click

-----

using the wizard to make the save or new record helps for error tags. Then you just put your evaluation with the else leading to that final action. Here, I was only testing if first name or last name were null. For you, you can just evaluate:

if field 1 = 1 (I think that is yes on a checkbox) AND field 2 is null OR
field 3 = 1 AND field 4 is null OR
Field 8 = 1 and field 10 is null Then
msg etc.

You will probably have to play with the statement, but use help to verify your syntax along the way. That's what I do. Let me know if you get your code started and your getting bugs. I will try to help.


ps. you could change this to an ok style message and stop the action if there are any nulls, else go ahead.