Having Issues With MsgBox Function.

[nick404]

There are a few fields in my form I want make sure a user has filled in (but I don't want to make them 'required' since that causes confusion and unnecessary questions if users click on and off a field or erase some data and click off or whatever).
Behind my submit button I have this

Private Sub btnSubmit_Click()
If Me.DateEntry.Value = Null Or Me.cboDefect.Value = Null Or Me.cboCommodity.Value = Null Or Me.txtPartNumber_PK.Value = Null Or Me.cboShift.Value = Null Or Me.txtSupplier.Value = Null Then
MsgBox "Please ensure all applicable fields are filled in.", vbExclamation + vbOKOnly, "Missing Information"
Else: DoCmd.GoToRecord , , acNewRec
End If
End Sub

I want the message box to appear if the user has the specified fields blank. But this doesn't happen and the acNewRec proceeds anyways. I tried having all the cbos and txts listed as .Value = "" but it didn't work either.


Thanks in advance.

[Perceptus]

Are you sure you are checking for the right null? These may work better
value = vbNull
or
Nz(Value,"")=""

[Bob Fitz]

1 IMHO this kind of validation would be better done in the forms Before update event.
2 Have you tried setting a breakpoint and then step through the code so that you can see the value of each control (by holding the mouse over eg: Me.DateEntry.Value ). I suspect that some of your controls are not holding Null values when the code runs.

[nick404]

Bob, why would before update be better?

[June7]

Cannot do = Null. Use IsNull(Me.DateEntry)

See error 5 in http://allenbrowne.com/casu-12.html

Data validation should be done in BeforeUpdate event if you want to cancel the update if some fields not filled in. Since you have an Else branch, I also think BeforeUpdate is more appropriate.

If you want to give the user an option to return to enter missing data, consider:

Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.DateEntry) Or ... Then
If MsgBox("Some fields are empty. Do you want to resume data entry for this record?", vbYesNo) = vbYes Then Cancel = True
End If
End Sub

Then in the AfterUpdate event:
DoCmd.GoToRecord , , acNewRec

[nick404]

Alright thank you both! What you suggested + link was helpful, thanks June

[June7]

Did some edits to post you might not have seen.

[nick404]

So if the user says no then it is considered 'updated' and the after update would proceed? But the cancel event does not count as updating?

[June7]

Right, if user elects to continue edit the update is canceled and record would not be committed. The AfterUpdate would not trigger.

[nick404]

If i take all this out of my 'submit' button how is the message box initiated (via a button)?
Would a simple Me.Refresh suffice?

[June7]

Good question. Guess I need to backtrack a bit. On reflection, I've never actually used Form BeforeUpdate or AfterUpdate (I do use those events with controls, a lot). I do use code behind buttons, similar to your Submit. So for yours:

If IsNull(Me.DateEntry) Or ... Then
    If MsgBox("Some fields are empty. Do you want to resume data entry for this record?", vbYesNo) = vbYes Then Exit Sub  
End If
DoCmd.GoToRecord , , acNewRec

[Missinglinq]

If i take all this out of my 'submit' button how is the message box initiated (via a button)?

Leave the validation code in the Form_BeforeUpdate event, and then, behind your Command Button, use either

DoCmd.RunCommand acCmdSaveRecord

or

If Me.Dirty Then Me.Dirty = False

When Access goes to save the Record, execution will move to the Form_BeforeUpdate event and the validation code will fire. It will also fire if any other thing tries to save the Record, i.e. by leaving the Record, closing the Form or closing Access, itself.

Linq ;0)>

[nick404]

Excellent. Thanks for the insights guys