Adding onbeforeupdate vb code to account for missing required fields (1 Viewer)

[solrac_otos]

Hi there. I am new to this forum, but not new to Access (using 2010 at the moment). That said, I am new to VB. I need code that I can incorporate with the code below, that will notify a user when required fields are left blank so that they have to go back and fill it in before updating the record. Below are the objects (shown in the order they appear on the form):

cmbModel
txtSerialNo
txtExpDate
txtPONo
cmbOfficeLoc

If any of the objects above are empty, the user should be prompted to go back and fill them in setting the focus back to the first empty object (again the fields above are in order). If conditions are not met, do not run the code below. If the conditions are met then proceed with the code below.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
Dim iResponse As Integer

' Specify the message to display.
strMsg = "Do you wish to save the changes?" & Chr(10)
strMsg = strMsg & "Click Yes to Save or No to Discard changes."

' Display the message box.
iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?")

' Check the user's response.
If iResponse = vbNo Then

' Undo the change.
DoCmd.RunCommand acCmdUndo

' Cancel the update.
Cancel = True
End If
End Sub

Any assistance would be greatly appreciated.

Kind regards.

Carlos

 

[pbaldy]

Here are the basics:

http://www.baldyweb.com/BeforeUpdate.htm

 

[solrac_otos]

Thanks Paul. How would I incorporate something like:
If Len(Me.SomeControl & vbNullString) = 0 Then
MsgBox "You need to fill out SomeControl"
Cancel = True
Me.SomeControl.SetFocus
End If

with my existing on beforeupdate code?
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
Dim iResponse As Integer

' Specify the message to display.
strMsg = "Do you wish to save the changes?" & Chr(10)
strMsg = strMsg & "Click Yes to Save or No to Discard changes."

' Display the message box.
iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?")

' Check the user's response.
If iResponse = vbNo Then

' Undo the change.
DoCmd.RunCommand acCmdUndo

' Cancel the update.
Cancel = True
End If
End Sub

 

[pbaldy]

I would add

Exit Sub

to the end of mine and the drop that whole thing before you ask the user if they want to save.

 

[solrac_otos]

Paul. Definitely in the right direction here. Here is the code you suggested geared toward one of my objects with the other code I have in place.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Len(Me.txtSerialNo & vbNullString) = 0 Then
MsgBox "You need to fill in all required fields"
Cancel = True
Me.txtSerialNo.SetFocus
End If
Exit Sub

Dim strMsg As String
Dim iResponse As Integer

' Specify the message to display.
strMsg = "Do you wish to save the changes?" & Chr(10)
strMsg = strMsg & "Click Yes to Save or No to Discard changes."

' Display the message box.
iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?")

' Check the user's response.
If iResponse = vbNo Then

' Undo the change.
DoCmd.RunCommand acCmdUndo

' Cancel the update.
Cancel = True
End If
End Sub

I saved and tested the form. Entered all information except the txtSerialNo object. I click save and then I get the MsgBox "You need to fill in all required fields". Great, I click ok and then I get another dialogue box that says No record. I close out of that box and then the set focus is on that field. How do I make it so the No record dialogue doesnt show (or skips it altogether)?

I then tried entering all of the information into the form and click save, but now the dialogue from the second part of the code doesnt show up. Any idea how I can make these two work together?

 

[pbaldy]

That Exit Sub needs to be inside the If/Then block with my code. It's getting hit no matter what now.

 

[solrac_otos]

SOrry I am not sure what you mean. If you look at the code above...isnt the exit sub right after the if then?

 

[pbaldy]

I said inside, not after:

If Len(Me.txtSerialNo & vbNullString) = 0 Then
MsgBox "You need to fill in all required fields"
Cancel = True
Me.txtSerialNo.SetFocus
Exit Sub
End If

 

[solrac_otos]

Agh. Okay. While I was waiting for your reply I started hacking at it myself. I am taking a slightly different approach. On the Save button I created an on click private sub:

Private Sub cmdSaveButton_Click()
If Len(Me.txtSerialNo & vbNullString) = 0 Then
MsgBox "You need to fill in all required fields"
Cancel = True
Me.txtSerialNo.SetFocus
Else: DoCmd.RunCommand acCmdSaveRecord
End If
End Sub

and then there is also the beforeupdate sub:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
Dim iResponse As Integer

' Specify the message to display.
strMsg = "Do you wish to save the changes?" & Chr(10)
strMsg = strMsg & "Click Yes to Save or No to Discard changes."

' Display the message box.
iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?")

' Check the user's response.
If iResponse = vbNo Then

' Undo the change.
DoCmd.RunCommand acCmdUndo

' Cancel the update.
Cancel = True
End If
End Sub

This is working exactly as I want it to. My question now is...how do I lay out the code to account for the other variables I want to check for null? Is it after the else and before the end if?
If you could just copy the existing code above right under where the next one should go I should be all set.

Paul. Thanks for your being so patient with me. It is greatly appreciated.

 

[pbaldy]

If you want one overall test, add each with OR to the test, like:

If Len(Me.txtSerialNo & vbNullString) = 0 OR Len(Me.SomeOtherTextbox & vbNullString) = 0 Then

 

[solrac_otos]

Paul. Many thanks. I have left for the day and will try this out Monday morning. I will report back when I have implemented and tested the code.

Cheers.

C.

 

[pbaldy]

No problema Carlos.

 

[solrac_otos]

Paul.

Worked like a charm!

How do I get the setfocus to apply to the the first field where the if then finds a blank field?

Here is the code as it stands.

Private Sub cmdSaveButton_Click()
If Len(Me.cmbModel & vbNullString) = 0 Or Len(Me.txtSerialNo & vbNullString) = 0 Or Len(Me.txtATExpDate & vbNullString) = 0 Or Len(Me.txtPONumber & vbNullString) = 0 Or Len(Me.cmbOfficeLoc & vbNullString) = 0 Then
MsgBox "All fields on this form are Required. Please go back and fill in the missing fields."
Cancel = True
Else: DoCmd.RunCommand acCmdSaveRecord
End If
End Sub

-C.

 

[pr2-eugin]

You have to break your If, since you need to set focus on the first control that is missing data, so something like..

Private Sub cmdSaveButton_Click()
    If Len(Me.cmbModel & vbNullString) = 0 Then
        MsgBox "All fields on this form are Required. Please go back and fill in the missing fields."
        Cancel = True
        Me.cmbModel.SetFocus
        Exit Sub
    End If
    
    If Len(Me.txtSerialNo & vbNullString) = 0 Then
        MsgBox "All fields on this form are Required. Please go back and fill in the missing fields."
        Cancel = True
        Me.txtSerialNo.SetFocus
        Exit Sub
    End If
    
    If Len(Me.txtATExpDate & vbNullString) = 0 Then
        MsgBox "All fields on this form are Required. Please go back and fill in the missing fields."
        Cancel = True
        Me.txtATExpDate.SetFocus
        Exit Sub
    End If
    
    If Len(Me.txtPONumber & vbNullString) = 0 Then
        MsgBox "All fields on this form are Required. Please go back and fill in the missing fields."
        Cancel = True
        Me.txtPONumber.SetFocus
        Exit Sub
    End If
    
    If Len(Me.cmbOfficeLoc & vbNullString) = 0 Then
        MsgBox "All fields on this form are Required. Please go back and fill in the missing fields."
        Cancel = True
        Me.cmbOfficeLoc.SetFocus
        Exit Sub
    End If
End Sub

 

[pbaldy]

Also, I often use Conditional Formatting to highlight required fields that are not yet filled out.

 

[solrac_otos]

Paul...conditional formatting? Can you elaborate...maybe give me an example on how I would apply it to my code?

 

[pr2-eugin]

This link might help you get started - http://office.microsoft.com/en-gb/a...using-conditional-formatting-HA010208133.aspx

 

[pbaldy]

I'll stay out of the way.

 

[solrac_otos]

Thanks for all the help on this thread Paul. You were invaluable!!!

I posted another thread on this same forum:
How do I capture the windows log on information of a user using a database?

If you can help with that one as well...it would be appreciated.

Cheers.

-C.