Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 479
  • Last Modified:

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?
0
dplowman
Asked:
dplowman
  • 9
  • 5
1 Solution
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now