On Click Event No Longer Working (1 Viewer)

ITwannabe

Registered User.
Local time
Today, 12:00
Joined
Apr 25, 2018
Messages
16
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

AWF VIP
Local time
Today, 16:00
Joined
Jul 26, 2013
Messages
10,354
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

Registered User.
Local time
Today, 12:00
Joined
Apr 25, 2018
Messages
16
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

AWF VIP
Local time
Today, 16:00
Joined
Jul 26, 2013
Messages
10,354
No problem, sorry I wasn't meaning to be appear rude. Try this, and hopefully it will make some sense;
Code:
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

Registered User.
Local time
Today, 12:00
Joined
Apr 25, 2018
Messages
16
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

AWF VIP
Local time
Today, 16:00
Joined
Jul 26, 2013
Messages
10,354
You can capture that error in the form error property;
Code:
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

Super Moderator
Staff member
Local time
Today, 12:00
Joined
Jan 23, 2006
Messages
15,364
@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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:00
Joined
Feb 19, 2002
Messages
42,970
You are also incorrectly using the change event. The Change event runs once for each and every character you type in a control. You should use the BeforeUpdate event of the combo instead of the Change event. That way, the code will only run once.


Your use of the Form's BeforeUpdate event is also incorrect. You are always cancelling the update. I'm sure you don't want to do that.

My validation code is a little more verbose but it makes it easy to move around and insert new validation logic without rethinking what is already there. Here is an example:
Code:
    If Me.txtDateIssued & "" = "" Then
        sMsg = "Date Issued " & vbCrLf
        Me.txtDateIssued.SetFocus
        Cancel = True
        Exit Sub
    End If
    
    If Me.Combo45 & "" = "" Then
        sMsg = sMsg & "CA/PIP Level " & vbCrLf
        Me.Combo45.SetFocus
        Cancel = True
        Exit Sub
    End If

I removed the Nz() functions since they are redundant. Concatenating a ZLS to a null value results in a ZLS so the function just makes the code slower (not so you'd notice but eventually, it adds up and so if you learn best practice, you will not likely have to refactor the code to clean it up.)

Also, it is really poor practice to not give your controls meaningful names.
 

ITwannabe

Registered User.
Local time
Today, 12:00
Joined
Apr 25, 2018
Messages
16
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!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:00
Joined
Feb 19, 2002
Messages
42,970
Look at the first two If statements in your BeforeUpdate procedure and compare them to what I wrote.

If you are going to fix up the control names, the sooner you do it the less code you will need to change. Do the fields one at a time. Rename combo45 in the Name property of the form. Then switch to the VBA and using find and replace, find all instances of "combo45" and replace with your new name. Then go and do the next one.

Keep in mind that you also should not be using embedded spaces or special characters.
 

Users who are viewing this thread

Top Bottom