On Click Event No Longer Working

[ITwannabe]

Hi,

I have a form with the intent that users will enter/select data from various text & combo boxes to update a specific table.

I created a button with an "On Click" event that is intended to update the table when clicked and then clear the form.

It was working fine, until I added code to create custom message boxes when certain fields were left blank. All other functionality of the form is working fine (message boxes, etc.) but it no longer updates the table.

I need some help with the coding to update the table and clear the form once the "Enter" button is clicked. Again, everything else is working fine.

Here's the code as it exits currently:

Option Compare Database
Private Sub cboEmpID_Change()
Me.txtLegalName.Value = Me.cboEmpID.Column(1)
Me.txtDOH.Value = Me.cboEmpID.Column(2)
Me.txtLocation.Value = Me.cboEmpID.Column(3)
Me.txtMgr.Value = Me.cboEmpID.Column(4)
Me.txtHRBP.Value = Me.cboEmpID.Column(5)
End Sub

Private Sub cbo_EmpID_Change()
End Sub

Private Sub NextRecord_Click()
If Nz(Me.txtDateIssued, "") = "" Then
MsgBox "Date Issued is a required field", vbInformation, ""
Exit Sub
End If
If Nz(Me.Combo45, "") = "" Then
MsgBox "CA/PIP Level is a required field", vbInformation, ""
Exit Sub
End If
If Nz(Me.Combo33, "") = "" Then
MsgBox "Reason is a required field", vbInformation, ""
Exit Sub
End If

If Nz(Me.Combo37, "") = "" Then
MsgBox "HR Advisor is a required field", vbInformation, ""
Exit Sub
End If
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
End Sub

 

[Minty]

What you have shown there makes no sense. The only commands are associated with a "NextRecord" button and none of the code there would do anything other than pop a message box up.

It isn't going to the next (new?) record even if none of the message boxes fire.

Assuming you are on a bound form, then that validation code really belongs in the Before Update event, and your next button code should simply be

docmd.gotorecord , , acNewRec

 

[ITwannabe]

Thanks Minty,

I apologize for not making sense. I'm a novice. I moved the validation code to the Before Update event and added the code you noted to the next button on click event.

It will now update the table as long as all of the required fields are completed, which is great. The problem I'm having is when a message box opens due to one of the required fields being left blank, and you click "Ok", it clears all of the other fields from the form. So if there are 7 non required fields already filled in, and you leave the 8th field blank which is required and click ok to the pop up, it clears the previous 7 fields. the only time I want any fields cleared is after the form is entered once all the necessary fields are completed.

Here's the code as it stands right now, again I'm a novice so I'm aware and apologize that my code and explanations aren't the best...that's why I'm reaching out to the experts:

Option Compare Database
Private Sub cboEmpID_Change()
Me.txtLegalName.Value = Me.cboEmpID.Column(1)
Me.txtDOH.Value = Me.cboEmpID.Column(2)
Me.txtLocation.Value = Me.cboEmpID.Column(3)
Me.txtMgr.Value = Me.cboEmpID.Column(4)
Me.txtHRBP.Value = Me.cboEmpID.Column(5)
End Sub
Private Sub cbo_EmpID_Change()
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Nz(Me.txtDateIssued, "") = "" Then
MsgBox "Date Issued is a required field", vbInformation, ""
Exit Sub
End If
If Nz(Me.Combo45, "") = "" Then
MsgBox "CA/PIP Level is a required field", vbInformation, ""
Exit Sub
End If
If Nz(Me.Combo33, "") = "" Then
MsgBox "Reason is a required field", vbInformation, ""
Exit Sub
End If

If Nz(Me.Combo37, "") = "" Then
MsgBox "HR Advisor is a required field", vbInformation, ""
Exit Sub
End If
End Sub
Private Sub NextRecord_Click()
DoCmd.GoToRecord , , acNewRec
End Sub

 

[Minty]

No problem, sorry I wasn't meaning to be appear rude. Try this, and hopefully it will make some sense;

Private Sub cboEmpID_Change()

    Me.txtLegalName.Value = Me.cboEmpID.Column(1)
    Me.txtDOH.Value = Me.cboEmpID.Column(2)
    Me.txtLocation.Value = Me.cboEmpID.Column(3)
    Me.txtMgr.Value = Me.cboEmpID.Column(4)
    Me.txtHRBP.Value = Me.cboEmpID.Column(5)

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

    Dim sMsg As String
    
    If Nz(Me.txtDateIssued, "") = "" Then
        sMsg = "Date Issued " & vbCrLf
    End If
    
    If Nz(Me.Combo45, "") = "" Then
        sMsg = sMsg & "CA/PIP Level " & vbCrLf
    End If
    
    If Nz(Me.Combo33, "") = "" Then
        sMsg = sMsg & "Reason " & vbCrLf
    End If

    If Nz(Me.Combo37, "") = "" Then
       sMsg = sMsg & "HR Advisor " & vbCrLf
    End If
    
    If sMsg = "" Then Exit Sub
    
    MsgBox "The following fields are missing data; " & vbCrLf & sMsg, vbInformation, "Missing data!"
    
    Cancel = True
           
End Sub
Private Sub NextRecord_Click()

    DoCmd.GoToRecord , , acNewRec

End Sub

If the Cancel = True is fired , it will prevent the movement to the new record and preserve the existing entries.

 

[ITwannabe]

Minty,

Thanks for the quick response and no worries, I didn't think you were being rude. I used your code and it's working The only issue is that after you click ok on the message box regarding missing fields, the following run-time error message pops up:

Run-time error '2105':
You can't go to the specified record.

If you click end it takes you back to the form and you can continue, but I feel like my users will be thrown off by this. Is there a way to correct it so this doesn't pop up?

Thanks

 

[Minty]

You can capture that error in the form error property;

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Select Case DataErr
Case 2105
    Response = acDataErrContinue
Case Else
    Response = acDataErrDisplay
End Select

End Sub

 

[jdraw]

@ITwannabe

When you post code, please use code tags to highlight it as Minty's posts show.

Technique:
-- highlight the SQL or vba involved
-- click on the octothorpe/hash (#) in the message header

If you do indent your code using code tags will preserve such indentation.
Good luck with your project.

 

[ITwannabe]

Hi Pat,

Can you tell me what I need to remove and replace with this code? I'm new to VBA and form building in general, which explains the bad form on the control names I'm trying to learn on the fly.

Thanks!