MS Access If Null Message box

I have a form with around 10 fields that all need to be entered with data except for two of them. How can I have a message box pop up letting the user know that data needs to be entered and in which field. Currently I get a run time error 3146, stating cannot insert the value null in to the column. Would this be something that I need to include on the form, before update?
dplowmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
Yes, the Form_BeforeUpdate is the appropriate place to do it.  With code similar to:

Private Sub Form_BeforeUpdate (cancel as integer)

    if me.txt_Field1 & "" = "" Then
        msgbox "Please enter data in Field1"
        Cancel = true
        me.txt_Field1.SetFocus
    elseif me.txt_Field2 & "" = "" then
        msgbox "Please enter data in Field2"
        Cancel = true
        me.txt_Field2.SetFocus
    elseif me.txt_Field3 & "" = "" then
        msgbox "Please enter data in Field3"
        Cancel = true
        me.txt_Field3.SetFocus
    End if

End Sub




0
dplowmanAuthor Commented:
Thanks. The error message pops up, but when I click ok i get the following error.

Run-Time Error 3021, no current record.
0
dplowmanAuthor Commented:
When I click debug, this is the code that is being highlighted.

Private Sub cmdSaveandNew_Click()

DoCmd.RunCommand acCmdSaveRecord
cmdEmail_Click
DoCmd.Close acForm, Me.Name
DoCmd.OpenForm "QV"

End Sub
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Dale FyeCommented:

If you have code that prevents the user from saving the record unless they click cmdSaveAndNew, then I would actually put the following code in that click event, instead of the BeforeUpdate event.

Private Sub cmdSaveandNew_Click()

    if me.txt_Field1 & "" = "" Then
        msgbox "Please enter data in Field1"
        me.txt_Field1.SetFocus
        Exit Sub
    elseif me.txt_Field2 & "" = "" then
        msgbox "Please enter data in Field2"
        me.txt_Field2.SetFocus
        Exit Sub
    elseif me.txt_Field3 & "" = "" then
        msgbox "Please enter data in Field3"
        me.txt_Field3.SetFocus
        Exit Sub
    End if

    me.dirty = false 'does the same thing as the RunCommand acCmdSaveRecord

    Call cmdEmail_Click

    DoCmd.Close acForm, Me.Name
    DoCmd.OpenForm "QV"

End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeCommented:
I try to avoid giving command buttons multiple missions, is this button on Form "QV"?

0
dplowmanAuthor Commented:
Yes, I have one button on the qv form that saves, emails, and opens new form.
0
dplowmanAuthor Commented:
I am now getting an error of:

run-time error 3146

cannot insert value null into column associate

This happens after I click ok on the message box.
0
dplowmanAuthor Commented:
Here is the code I am using.
Private Sub cmdSaveandNew_Click()

If Me.txtQVDate & "" = "" Then
        MsgBox "Please enter date issued."
        cancel = True
        Me.txtQVDate.SetFocus
    ElseIf Me.cboAssociate & "" = "" Then
        MsgBox "Please enter the associate's name."
        cancel = True
        Me.cboAssociate.SetFocus
    ElseIf Me.txtReviewer & "" = "" Then
        MsgBox "Please enter who you are."
        cancel = True
        Me.txtReviewer.SetFocus
    ElseIf Me.txtSource & "" = "" Then
        MsgBox "Please enter the source of the qv."
        cancel = True
        Me.txtSource.SetFocus
    ElseIf Me.txtLoanCode & "" = "" Then
        MsgBox "Please enter the loan number."
        cancel = True
        Me.txtLoanCode.SetFocus
    ElseIf Me.cboSection & "" = "" Then
        MsgBox "Please chose the section."
        cancel = True
        Me.cboSection.SetFocus
    ElseIf Me.cboCategory & "" = "" Then
        MsgBox "Please select the category."
        cancel = True
        Me.cboCategory.SetFocus
        Exit Sub
    End If
    
RunCommand acCmdSaveRecord

cmdEmail_Click

DoCmd.Close acForm, Me.Name

DoCmd.OpenForm "QV"

End Sub

Open in new window

0
dplowmanAuthor Commented:
I am also getting an error on the first criteria. The first section is a date and after hitting ok on the message box, i get:

Type mismatch, cannot coerce parameter value. Outlook cannot translate your string.
0
Dale FyeCommented:

You failed to notice that when I moved the code into cmdSaveAndNew I got rid of the Cancel line (only means something in the BeforeUpdate event), and replaced that with Exit Sub as the last line in each If / Then section.  

Because of that, when the error is encountered, it drops through the IF / Then and to the acCmdSaveRecord line.

1. Remove the "Cancel" line in each If-Then
2. Add the Exit Sub line (as you did with cboCategory) as the last line in each If / Then section.
0
dplowmanAuthor Commented:
Sorry, I figured it out. I was leaving the exit sub after every section.
0
dplowmanAuthor Commented:
Works perfectly! Thank you. The only other issue is if the user decides to close the form after starting to fill it out, i get an error odbc- call failed, cannot insert null values. How can I create a button to close and not to save the form?
0
Dale FyeCommented:
I usually include code that prevents the user from closing the form unless they hit the Save or Cancel button.  Don't have time to go into that now, but if you search the solutions on something like "prevent form close" you should find an explanation of the technique.

Then add a Cancel button that looks like:

Private Sub cmd_Cancel_Click

    me.undo
    docmd.close acform, me.name

End Sub
0
dplowmanAuthor Commented:
Awesome work! Have a great day!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.