Messagebox popping up twice?

[82280zx]

I will post the code I have in it, and I'll fix the form and re upload it June, sorry about that.

[82280zx]

What I'm trying to do is prompt the user if he/she forgot to enter date in a required Field. I think my problem is in my save and close button at the moment, what I'd like to do is something like this.

If IsNull(Me.U_Location) Then
MsgBox "Please enter the U Location of the Asset", vbCritical, "Canceling Update"
Me.U_Location.BackColor = vbRed
Me.U_Location.SetFocus
Cancel = True
End If

If they forgot to enter the data, pop a error up, highlight the box, set the focus to the box and have the user enter the data into it before allowing the record to be added/saved. Now the next question is how can I make the save and close button check to make sure none of the required data is null and save the data if it's correct?

[June7]

Options:

1. set the Required property for field in table and let Access nag the users

2. code in form BeforeUpdate event to check value of each control where data is required

[82280zx]

I think I figured out the problem, I'll give it some tests but thank you all for your help

[82280zx]

Options:

1. set the Required property for field in table and let Access nag the users

2. code in form BeforeUpdate event to check value of each control where data is required

I'm actually nagging the user when they click the save and close button on my form, it checks to see if any of the fields is null before it can save and close the record.

[Missinglinq]

...how can I make the save and close button check to make sure none of the required data is null...

You don't! You use the Form_BeforeUpdate event to do this type of validation, because it can be canceled, if the validation fails, but the OnClick event of a Command Button cannot be canceled. Only use the 'save and close' button to save and close the Form; leave the rest up to Form_BeforeUpdate!

You're missing one Command in your validation routine:

Exit Sub

Your routine would work, if you were only validating a single Control, but when you're validating multiple Controls you need to break out of the Form_BeforeUpdate sub once a validation fails, otherwise Access simply continues to drop down to the next validation, essentially ignoring the SetFocus command, and pops the next error message! So what you need is something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
 
 If IsNull(Me.txtModel) Then
  MsgBox "Please enter the Model Information", vbCritical, "Canceling Update"
  Me.txtModel.SetFocus
  Cancel = True
  Exit Sub
 End If

 If IsNull(Me.U_Location) Then
  MsgBox "Please enter the U Location of the Asset", vbCritical, "Canceling Update"
  Me.U_Location.BackColor = vbRed
  Me.U_Location.SetFocus
  Cancel = True
  Exit Sub
 End If

End Sub

Linq ;0)>

[82280zx]

It would really help if you would post your entire code for your Form_BeforeUpdate event, as I requested earlier, as not everyone can run an Access 2013 file.

Linq ;0)>

I've moved it to my save and close command button. But I'm not sure that's where it needs to be. Heres the code I have in it.

Private Sub cmdSaveClose_Click()
If Form.Dirty Then
If IsNull(Me.txtModel) Then
MsgBox "Please enter the Model Information", vbCritical, "Canceling Update"
Me.txtModel.BackColor = vbRed
Me.txtModel.SetFocus
Cancel = True
Exit Sub
End If

If IsNull(Me.txtSerialNumberorDellServiceTag) Then
MsgBox "Please enter the Serial Number or the Service Tag", vbCritical, "Canceling Update"
Me.txtSerialNumberorDellServiceTag.BackColor = vbRed
Me.txtSerialNumberorDellServiceTag.SetFocus
Cancel = True
Exit Sub
End If

If IsNull(Me.txtAllocation) Then
MsgBox "Please select an Allocation of the Asset", vbCritical, "Canceling Update"
Me.txtAllocation.BackColor = vbRed
Me.txtAllocation.SetFocus
Cancel = True
Exit Sub
End If

If IsNull(Me.cboProductTypeIDFK) Then
MsgBox "Please select the Product Type", vbCritical, "Canceling Update"
Me.cboProductTypeIDFK.BackColor = vbRed
Me.cboProductTypeIDFK.SetFocus
Cancel = True
Exit Sub
End If

If IsNull(Me.cboManufacturer) Then
MsgBox "Please select the Manufacturer", vbCritical, "Canceling Update"
Me.cboManufacturer.BackColor = vbRed
Me.cboManufacturer.SetFocus
Cancel = True
Exit Sub
End If

If IsNull(Me.cboLocation) Then
MsgBox "Please enter the Location of the Asset", vbCritical, "Canceling Update"
Me.cboLocation.BackColor = vbRed
Me.cboManufacturer.SetFocus
Cancel = True
Exit Sub
End If

If IsNull(Me.cboRoom) Then
MsgBox "Please enter the Room the Asset is located in", vbCritical, "Canceling Update"
Me.cboRoom.BackColor = vbRed
Me.cboRoom.SetFocus
Cancel = True
Exit Sub
End If

If IsNull(Me.cboGridIDFK) Then
MsgBox "Please enter the Grid Location of the Asset", vbCritical, "Canceling Update"
Me.cboGridIDFK.BackColor = vbRed
Me.cboGridIDFK.SetFocus
Cancel = True
Exit Sub
End If

If IsNull(Me.U_Location) Then
MsgBox "Please enter the U Location of the Asset", vbCritical, "Canceling Update"
Me.U_Location.BackColor = vbRed
Me.U_Location.SetFocus
Cancel = True
Exit Sub
End If

If IsNull(Me.txtHostName) Then
MsgBox "Please enter the U Location of the Asset", vbCritical, "Canceling Update"
Me.txtHostName.BackColor = vbRed
Me.txtHostName.SetFocus
Cancel = True
Exit Sub
End If

If IsNull(Me.Campus) Then
MsgBox "Please enter the Campus information", vbCritical, "Canceling Update"
Me.Campus.BackColor = vbRed
Me.Campus.SetFocus
Cancel = True
Exit Sub
End If

If IsNull(Me.cboDepartmentIDFK) Then
MsgBox "Please enter the Department information", vbCritical, "Canceling Update"
Me.cboDepartmentIDFK.BackColor = vbRed
Me.cboDepartmentIDFK.SetFocus
Cancel = True
Exit Sub
End If

If IsNull(Me.cboAgencyIDFK) Then
MsgBox "Please enter the Agency Information", vbCritical, "Canceling Update"
Me.cboAgencyIDFK.BackColor = vbRed
Me.cboAgencyIDFK.SetFocus
Cancel = True
Exit Sub
End If

If IsNull(Me.ServerUsedBy) Then
MsgBox "Please enter which Agency uses this server", vbCritical, "Canceling Update"
Me.ServerUsedBy.BackColor = vbRed
Me.ServerUsedBy.SetFocus
Cancel = True
Exit Sub
End If

DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close

Else

Dim Answer As Integer
If Me.Dirty = True Then
Dim Response As Integer
' Displays a message box with the yes and no options.
Response = MsgBox(Prompt:="Do you wish to discard data?", Buttons:=vbYesNo)
' If statement to check if the yes button was selected.
If Response = vbYes Then
DoCmd.RunCommand acCmdUndo
DoCmd.Close
Else
Me.txtModel.SetFocus
End If
Else
' The no button was selected.
DoCmd.Close
End If

End If
End Sub

What I would like to do is make sure a new asset cannot be created unless data is entered into the fields in my code. I would also like to make a way to cancel the form so no data was entered if they cancel or back out of the form. I'm still thinking the BeforeUpdate would be the best but how do I go about saving the asset after all the fields clear? Maybe a Boolean?

Thanks again everyone!

[Missinglinq]

The validation code, i.e.

If IsNull(Me.txtModel) Then
  MsgBox "Please enter the Model Information", vbCritical, "Canceling Update"
  Me.txtModel.BackColor = vbRed
  Me.txtModel.SetFocus
  Cancel = True
  Exit Sub
End If

and so forth, needs to be in the Form_BeforeUpdate event, which I explained in Post #21!

Once all of the required fields are populated, the Form_BeforeUpdate event will execute and the Record will be saved.

The only code you need in your cmdSaveClose_Click event is

DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close

Execution will then go to the Form_BeforeUpdate event and the validation will be taken care of! If all required fields are populated, the Record saves...if not, the Record doesn't save and the error messages will pop up!

Linq ;0)>

[82280zx]

The validation code, i.e.

If IsNull(Me.txtModel) Then
  MsgBox "Please enter the Model Information", vbCritical, "Canceling Update"
  Me.txtModel.BackColor = vbRed
  Me.txtModel.SetFocus
  Cancel = True
  Exit Sub
End If

and so forth, needs to be in the Form_BeforeUpdate event, which I explained in Post #21!

Once all of the required fields are populated, the Form_BeforeUpdate event will execute and the Record will be saved.

The only code you need in your cmdSaveClose_Click event is

DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close

Execution will then go to the Form_BeforeUpdate event and the validation will be taken care of! If all required fields are populated, the Record saves...if not, the Record doesn't save and the error messages will pop up!

Linq ;0)>

Is there a way the user can back out or cancel the form if he decides to come back later? Without saving the partial data?

Thanks again! I'll give this a try.

[Missinglinq]

A New Record can be dumped, before it's saved, by using the <Esc> key. To do it in code would simply be

Me.Undo

Linq ;0)>

[82280zx]

It seems to work but I'm getting a Microsoft Visual Basic Run-Time error '2501' The RunCommand action was canceled. Is there a way to stop that from showing up?

This is related to the Save & Close btw. If all the data isn't entered in correctly that runtime error pops up.

[June7]

Maybe with error handler code. http://allenbrowne.com/ser-23a.html