Message Box Problem

[dzirkelb]

I wish to have a message box pop up if I have a field that is left empty...I am able to do this through vba code with the following:

If IsNull(P3Temperature.Value) Then
MsgBox ("Enter a Value for Cold Side Item Temperature")
Cancel = True
End If

However, once I get to around 20 of those, my database errors out every time and closes. So, is there another way to have around 20-25 criteria met before an update? I tried the validation of each individual text box, frame, etc and set it to Is Not Null and customized the pop up box; however, it didn't work...it didn't try to match the validation unless something was put in. The purpose of me wanting to do this is to alert the user they forgot a box..any ideas? thanks!

 

[pono1]

Do you mean you have something like the following in the BeforeUpdate event of your form?

'Validate X
If IsNull(X.Value) Then
	MsgBox ("Enter a Value for X")
	Cancel = True
	Exit Sub
End If

'Validate Y
If IsNull(Y.Value) Then
	MsgBox ("Enter a Value for Y")
	Cancel = True
	Exit Sub
End If

'Validate Z
If IsNull(Z.Value) Then
	MsgBox ("Enter a Value for Z")
	Cancel = True
	Exit Sub
End If

Notice that I have added the Exit Sub statement to each test. This will stop the rest of the tests from running. Also, it's usually best to put the If-thens in the same order as the textboxes on the form...

Regards,
Tim

 

[dzirkelb]

Thanks for the suggestions guys!

I like having the exit sub portion to the code, that answered one of my future questions.

However, I am still getting the same problem. It is on one of the if/then statements...regardless if I have 20 or 1, this particular one always causes my database to error out and try to repair itself...I have no idea why. It is as follows:

If IsNull(S3Customers.Value) Then
MsgBox ("duh")
Cancel = True
Exit Sub
End If

It is the S3Customers.Value that is giving me problems...if I remove it, it seems to work ok. Once I add it back in, then it errors out. I placed it by itself, at beginning of code, at end, middle, etc...always, as soon as it gets to that portion of the code, it closes and tries to repair itself. There is nothing special about this field...it is a text field, not required, and nothing out of the ordinary that seperates it from other text boxes / fields in my table / form. Any ideas?

I am going to try the command button next and see what happens...

 

[dzirkelb]

If I place all the code on a command box for the on click command, it still produces the error and shuts down the database. If, however, I have a "dummy" cmd button on a seperate form with the exact same code, then it works fine...any ideas on how to fix / a workaround for this?

 

[ghudson]

I grabbed this from another post some time back and saved it for a rainy day.

'Key "Required" in the Tag property of the form object.

    Dim ctl As Control
    For Each ctl In Me
        If ctl.Tag = "Required" Then
            If IsNull(ctl) Or ctl = "" Then
                MsgBox "You must complete all required fields to continue", vbCritical, "Required Field"
                ctl.SetFocus
                Exit Sub
            End If
        End If
    Next
    Set ctl = Nothing

 

[dzirkelb]

that works awesome!!

Do you think there is a way to customize the message being sent to the user? Meaning, instead of a general one, could I somehow place a variable in there to display which one inparticular isn't filled in?

 

[ansentry]

re the code post by ghudson:

In a test db , I put the code in the After Update of the form it works in a fashion. I only have two controls on the (apart from autonumber) and if you fill in one and then try and go to a new record you get the message , and the focus goes to the empty control, with the cursor in the empty control just click the new record button at the bottom and it will let you go to a new record without entering any thing.

I may have the code in the wrong place and I this is not a criticism of ghudson just that if I am right then it could cause problems in a "real" db .

Have I done some thing wrong??

 

[ghudson]

You need to use the "Forms" BeforeUpdate event so that you do not allow the record to be saved if one of the required fields is empty. The code will move the focus to the empty field with the "ctl.SetFocus" line.

Try this to display which field is empty [not tested but you get the idea]...

    Dim ctl As Control
    For Each ctl In Me
        If ctl.Tag = "Required" Then
            If IsNull(ctl) Or ctl = "" Then
                MsgBox "You must complete the required field '" & ctl & "' before you can continue.", vbCritical, "Required Field"
                ctl.SetFocus
                Exit Sub
            End If
        End If
    Next
    Set ctl = Nothing

You should also offer the user an Undo command button so that they can clear the record if they started a new record yet decided not to continue.

 

[dzirkelb]

That is exactly what I am looking for; however, it doesn't work correctly. The msg box says "You must complete the required field '' before you can continue"

Any ideas?

 

[ghudson]

I told you it was not tested.

Try this line instead...

MsgBox "You must complete the required field '" & ctl[COLOR=Blue].Name[/COLOR] & "' before you can continue.", vbCritical, "Required Field"

The .Name is not a recoginized option by the intelisense but it works.

 

[dzirkelb]

Genius...pure genius, I love it and it works perfectly!!

You can also place it on a command button to run it, it doesn't have to be on the before update action; but, it can't be on the after update as stated earlier...my button is a submit button which runs this, cancels if there is an error (added cancel=true), or closes / opens a new form once it is entered completely. at that point, it will give a summary of the data submitted and they can then edit it if need be. Thanks a millions!!!

 

[ghudson]

Another satisfied customer!

Just ensure that you are saving the current record if you do use a command button before you fire off your code.

DoCmd.RunCommand acCmdSaveRecord

 

[dzirkelb]

I currently have the following on my command button:

Private Sub cmdSubmit_Click()

If D1EntreeOrdered.Value = 5 And IsNull(D1OtherChicken.Value) Then
MsgBox "Enter a Value for Other Chicken", vbCritical, "Required Field"
Cancel = True
Exit Sub
End If

If M6Carbonation.Value = 1 Or M6BrixLevel.Value = 1 And IsNull(M6TypeOfDrink.Value) Then
MsgBox "Enter a Value for Type of Drink", vbCritical, "Required Field"
Cancel = True
Exit Sub
End If

If P2Other.Value = 1 And IsNull(P2OtherText.Value) Then
MsgBox "Enter a Value for Other Hot Side Item", vbCritical, "Required Field"
Cancel = True
Exit Sub
End If

If P3Other.Value = 1 And IsNull(P3OtherText.Value) Then
MsgBox "Enter a Value for Other Cold Side Item", vbCritical, "Required Field"
Cancel = True
Exit Sub
End If

Dim ctl As Control
    For Each ctl In Me
        If ctl.Tag = "Required" Then
            If IsNull(ctl) Or ctl = "" Then
                MsgBox "You must complete the required field  '" & ctl.Name & "' before you can continue.", vbCritical, "Required Field"
                ctl.SetFocus
                Cancel = True
                Exit Sub
            End If
        End If
    Next
    Set ctl = Nothing

DoCmd.Close

Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "form4"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
End Sub

This updates my data when it closes..doesnt' it? It has seemed to do so, so far.

 

[ghudson]

DoCmd.Close

Yes the record will be saved when the form is closed but you should use the
DoCmd.Close acForm,Me.Name
command to close the current form from where the code is called.

A few more tips to streamline your code...

IsNull(P3OtherText)
instead of
IsNull(P3OtherText.Value)

DoCmd.OpenForm "form4"
instead of
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "form4"
DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[dzirkelb]

Thanks for all of your help on this one...its running very smoothly now

 

[ansentry]

Yes I had the code in the wrong place, after I moved it to "before update" the same think happens. When you click the OK button on the msgbox after it appears for the 2nd time it will just take you to a new record, but then maybe I am just confused.