Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-14-2018, 04:47 AM   #1
ITwannabe
Newly Registered User
 
Join Date: Apr 2018
Posts: 16
Thanks: 4
Thanked 0 Times in 0 Posts
ITwannabe is on a distinguished road
On Click Event No Longer Working

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

ITwannabe is offline   Reply With Quote
Old 06-14-2018, 06:10 AM   #2
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,596
Thanks: 134
Thanked 1,513 Times in 1,485 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: On Click Event No Longer Working

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
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 06-14-2018, 11:38 AM   #3
ITwannabe
Newly Registered User
 
Join Date: Apr 2018
Posts: 16
Thanks: 4
Thanked 0 Times in 0 Posts
ITwannabe is on a distinguished road
Re: On Click Event No Longer Working

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

ITwannabe is offline   Reply With Quote
Old 06-15-2018, 12:15 AM   #4
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,596
Thanks: 134
Thanked 1,513 Times in 1,485 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: On Click Event No Longer Working

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.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 06-15-2018, 05:35 AM   #5
ITwannabe
Newly Registered User
 
Join Date: Apr 2018
Posts: 16
Thanks: 4
Thanked 0 Times in 0 Posts
ITwannabe is on a distinguished road
Re: On Click Event No Longer Working

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
ITwannabe is offline   Reply With Quote
Old 06-15-2018, 06:19 AM   #6
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,596
Thanks: 134
Thanked 1,513 Times in 1,485 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: On Click Event No Longer Working

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
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 06-15-2018, 08:47 AM   #7
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 11,554
Thanks: 54
Thanked 1,872 Times in 1,822 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: On Click Event No Longer Working

@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.

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 06-15-2018, 09:41 AM   #8
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,394
Thanks: 13
Thanked 1,416 Times in 1,350 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: On Click Event No Longer Working

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 06-19-2018, 04:17 AM   #9
ITwannabe
Newly Registered User
 
Join Date: Apr 2018
Posts: 16
Thanks: 4
Thanked 0 Times in 0 Posts
ITwannabe is on a distinguished road
Re: On Click Event No Longer Working

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!
ITwannabe is offline   Reply With Quote
Old 06-19-2018, 09:51 PM   #10
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,394
Thanks: 13
Thanked 1,416 Times in 1,350 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: On Click Event No Longer Working

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.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Undocumented Double Click event of TreeView OCX not working in Access 2010 mdlueck Modules & VBA 5 11-22-2013 05:17 AM
On Click event procedure skipping the On CLick code gblack Modules & VBA 3 05-06-2013 07:28 AM
Working form no longer working after change in tables Robert M Forms 5 11-19-2012 02:37 PM
Click event/buttons stopped working neilwebber General 0 07-23-2007 02:13 AM
Right-Click no longer working Cosmos75 General 1 02-20-2003 12:25 PM




All times are GMT -8. The time now is 04:53 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World