Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-23-2019, 06:55 AM   #1
HangoutGuy
Newly Registered User
 
Join Date: Aug 2019
Posts: 28
Thanks: 18
Thanked 0 Times in 0 Posts
HangoutGuy is on a distinguished road
Event procedure not working on opening until VBA code is opened and closed

Hi all,

I am having this odd problem that I really don't understand. I have a button that when clicked it runs some code that I have written that builds an email out of the data in my db.

When I first open my db and click the button I get this error: "The expression On Click you entered as the event property setting produced the following error: Object or class does not support the set of events."

Once I go into design view and open the VBA for the button and then I close it, everything is okay and button works as expected.

Not sure what I'm doing wrong, let me know if there is any more information I need to provide.

Thanks!

HangoutGuy is offline   Reply With Quote
Old 08-23-2019, 06:59 AM   #2
NauticalGent
Pristine Curmudgeon
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 2,035
Thanks: 457
Thanked 302 Times in 261 Posts
NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about
Re: Event procedure not working on opening until VBA code is opened and closed

Going to need to see the code, almost impossible to assist with so little to go on...
__________________
“It follows then as certain that night succeeds the day, that without normalization, we can do nothing definitive, and with it, everything honorable and glorious.” - with apologies to George Washington
NauticalGent is offline   Reply With Quote
Old 08-23-2019, 07:00 AM   #3
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,253
Thanks: 10
Thanked 232 Times in 220 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Event procedure not working on opening until VBA code is opened and closed

suggest you put a break on the beginning of the code, start the procedure, step through (usually F8) and identify which line causes the error. The post the code (in code tags please) and identify which line causes the error in your next post.

__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
Old 08-23-2019, 07:02 AM   #4
HangoutGuy
Newly Registered User
 
Join Date: Aug 2019
Posts: 28
Thanks: 18
Thanked 0 Times in 0 Posts
HangoutGuy is on a distinguished road
Re: Event procedure not working on opening until VBA code is opened and closed

Code:
Private Sub Command58_Click()
    Dim dbs As DAO.Database
    Dim rsEmps As DAO.Recordset
    Dim rsPrj As DAO.Recordset
    Dim strEmpQry As String
    Dim strPrjQry As String
    
    Dim strTo As String
    Dim strSubject As String
    Dim strMessage As String
    
    '-- Query for the employees loop
    strEmpQry = "SELECT Employees.EmployeeID, Employees.EmployeeName, Employees.Email " & _
                "FROM Employees "
    
    '-- Query for the employee projects loop
    strPrjQry = "SELECT EmployeeXProject.Percentage, Projects.ProjectName, Projects.PMName FROM Projects " & _
                "INNER JOIN EmployeeXProject ON Projects.[ProjectID] = EmployeeXProject.[ProjectID] " & _
                "WHERE EmployeeXProject.EmployeeID = "
    
    '-- Set the database object to the current Access DB
    Set dbs = CurrentDb
    
    '-- Loop thru the non-supervisor employees
    Set rsEmps = dbs.OpenRecordset(strEmpQry, dbOpenForwardOnly)
    Do While Not rsEmps.EOF
        '-- Check for null emails, skip them
        If Not IsNull(rsEmps.Fields("Email")) Then
            '-- Start setting-up the employee's email
            strTo = rsEmps.Fields("Email")
            strSubject = "Your Project Assignment(s)"
            strMessage = "Dear " & rsEmps.Fields("EmployeeName") & "," & vbNewLine & vbNewLine & _
            "Here is your assigned project(s)." & vbNewLine & vbNewLine & _
            "Project" & vbTab & vbTab & "Percentage" & vbTab & vbatb & _
            "Project Manager" & vbNewLine & _
            "-----------------------------------------------------------------" & _
            vbNewLine & vbNewLine
            
            '-- Loop thru the employee projects
            Set rsPrj = dbs.OpenRecordset(strPrjQry & rsEmps.Fields("EmployeeID") & ";", dbOpenForwardOnly)
            Do While Not rsPrj.EOF
                '-- Add to the email message string per project record
                strMessage = strMessage & rsPrj.Fields("ProjectName") & vbTab & vbatb & vbTab & _
                             rsPrj.Fields("Percentage") & vbTab & vbatb & vbTab & _
                             rsPrj.Fields("PMName") & vbNewLine
            
                rsPrj.MoveNext
            Loop
            rsPrj.Close
        
            '-- This sends the email via Outlook.
            DoCmd.SendObject To:=strTo, _
                             Subject:=strSubject, _
                             OutputFormat:=acFormatTXT, _
                             MessageText:=strMessage, _
                             EditMessage:=0
                             
        
        End If
        rsEmps.MoveNext
    Loop
    rsEmps.Close
    
    '-- Cleanup!
    Set rsPrj = Nothing
    Set rsEmps = Nothing
    Set dbs = Nothing
End Sub
Please excuse my code if it isn't very good. This is my first time with VBA.
HangoutGuy is offline   Reply With Quote
Old 08-23-2019, 07:16 AM   #5
HangoutGuy
Newly Registered User
 
Join Date: Aug 2019
Posts: 28
Thanks: 18
Thanked 0 Times in 0 Posts
HangoutGuy is on a distinguished road
Re: Event procedure not working on opening until VBA code is opened and closed

Quote:
Originally Posted by Micron View Post
suggest you put a break on the beginning of the code, start the procedure, step through (usually F8) and identify which line causes the error. The post the code (in code tags please) and identify which line causes the error in your next post.
I'm a bit confused because the code itself isn't causing the error, it runs perfectly fine. It just doesn't work when i click the button after first opening the db. But if I simply open the code up and close it the button works. This is why I'm baffled because the code works but only when the code is opened and closed.
HangoutGuy is offline   Reply With Quote
Old 08-23-2019, 07:28 AM   #6
NauticalGent
Pristine Curmudgeon
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 2,035
Thanks: 457
Thanked 302 Times in 261 Posts
NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about
Re: Event procedure not working on opening until VBA code is opened and closed

Out of curiosity, do you have an AutoExec Macro?
__________________
“It follows then as certain that night succeeds the day, that without normalization, we can do nothing definitive, and with it, everything honorable and glorious.” - with apologies to George Washington
NauticalGent is offline   Reply With Quote
The Following User Says Thank You to NauticalGent For This Useful Post:
HangoutGuy (08-23-2019)
Old 08-23-2019, 07:31 AM   #7
HangoutGuy
Newly Registered User
 
Join Date: Aug 2019
Posts: 28
Thanks: 18
Thanked 0 Times in 0 Posts
HangoutGuy is on a distinguished road
Re: Event procedure not working on opening until VBA code is opened and closed

Quote:
Originally Posted by NauticalGent View Post
Out of curiosity, do you have an AutoExec Macro?
I do not. Not everything in the db should run upon opening. Is there a way to AutoExec the VBA code to open and close on startup?

HangoutGuy is offline   Reply With Quote
Old 08-23-2019, 07:34 AM   #8
NauticalGent
Pristine Curmudgeon
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 2,035
Thanks: 457
Thanked 302 Times in 261 Posts
NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about
Re: Event procedure not working on opening until VBA code is opened and closed

Quote:
Originally Posted by HangoutGuy View Post
I do not. Not everything in the db should run upon opening. Is there a way to AutoExec the VBA code to open and close on startup?
There is, but that is not where I was going. My IT dept has got our network so locked down that when I attempt to make an AE macro, it throws a similar error. I was wondering if that may have been your issue as well.

Are you using OutLook?
__________________
“It follows then as certain that night succeeds the day, that without normalization, we can do nothing definitive, and with it, everything honorable and glorious.” - with apologies to George Washington
NauticalGent is offline   Reply With Quote
The Following User Says Thank You to NauticalGent For This Useful Post:
HangoutGuy (08-23-2019)
Old 08-23-2019, 07:37 AM   #9
HangoutGuy
Newly Registered User
 
Join Date: Aug 2019
Posts: 28
Thanks: 18
Thanked 0 Times in 0 Posts
HangoutGuy is on a distinguished road
Re: Event procedure not working on opening until VBA code is opened and closed

Quote:
Originally Posted by NauticalGent View Post
There is, but that is not where I was going. My IT dept has got our network so locked down that when I attempt to make an AE macro, it throws a similar error. I was wondering if that may have been your issue as well.

Are you using OutLook?
Actually I have been running into issues with security and outlook as well due to lock down. The button is supposed to automatically send the email but when it works correctly it only opens outlook with the prewritten email and only sends with the user clicking the send button.

I know this doesn't send because of security but I wouldn't think opening outlook would be an issue. Think this is it?
HangoutGuy is offline   Reply With Quote
Old 08-23-2019, 07:40 AM   #10
NauticalGent
Pristine Curmudgeon
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 2,035
Thanks: 457
Thanked 302 Times in 261 Posts
NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about
Re: Event procedure not working on opening until VBA code is opened and closed

Doubtful. And since it isn't breaking in this module (I noticed that you had no error handling), I suspect the issue is somewhere else as Micron has suggested.

Is it possible to upload a copy of your sanitized (no sensitive information) here so that we can have a look?
__________________
“It follows then as certain that night succeeds the day, that without normalization, we can do nothing definitive, and with it, everything honorable and glorious.” - with apologies to George Washington
NauticalGent is offline   Reply With Quote
The Following User Says Thank You to NauticalGent For This Useful Post:
HangoutGuy (08-23-2019)
Old 08-23-2019, 07:48 AM   #11
HangoutGuy
Newly Registered User
 
Join Date: Aug 2019
Posts: 28
Thanks: 18
Thanked 0 Times in 0 Posts
HangoutGuy is on a distinguished road
Re: Event procedure not working on opening until VBA code is opened and closed

Quote:
Originally Posted by NauticalGent View Post
Doubtful. And since it isn't breaking in this module (I noticed that you had no error handling), I suspect the issue is somewhere else as Micron has suggested.

Is it possible to upload a copy of your sanitized (no sensitive information) here so that we can have a look?
Sorry but posting bits of text and code is about all I can do, I am a lowly intern and I don't want to accidentally make a major screw up.

I am happy to answer as many questions as you're willing to ask and I will do my best to answer them with my limited access knowledge. I appreciate the help so far.
HangoutGuy is offline   Reply With Quote
Old 08-23-2019, 07:52 AM   #12
HangoutGuy
Newly Registered User
 
Join Date: Aug 2019
Posts: 28
Thanks: 18
Thanked 0 Times in 0 Posts
HangoutGuy is on a distinguished road
Re: Event procedure not working on opening until VBA code is opened and closed

Quote:
Originally Posted by HangoutGuy View Post
Sorry but posting bits of text and code is about all I can do, I am a lowly intern and I don't want to accidentally make a major screw up.

I am happy to answer as many questions as you're willing to ask and I will do my best to answer them with my limited access knowledge. I appreciate the help so far.
Okay something really weird just happened. I closed down my db and opened it again and it just worked without my opening the VBA.

This is so odd.
HangoutGuy is offline   Reply With Quote
Old 08-23-2019, 07:52 AM   #13
NauticalGent
Pristine Curmudgeon
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 2,035
Thanks: 457
Thanked 302 Times in 261 Posts
NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about
Re: Event procedure not working on opening until VBA code is opened and closed

I see, that is unfortunate. My skills are not that good to where I can imagine where the issue might be. I am too hands-on oriented.

However, there are those here can and they will be engaging soon enough. Sorry I could not help, best of luck!
__________________
“It follows then as certain that night succeeds the day, that without normalization, we can do nothing definitive, and with it, everything honorable and glorious.” - with apologies to George Washington
NauticalGent is offline   Reply With Quote
The Following User Says Thank You to NauticalGent For This Useful Post:
HangoutGuy (08-23-2019)
Old 08-23-2019, 07:56 AM   #14
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Event procedure not working on opening until VBA code is opened and closed

add the declaration "Option Explicit" to each module you made.
compile your VBA, if you are lucky you will find what is causing the error.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 08-23-2019, 08:00 AM   #15
HangoutGuy
Newly Registered User
 
Join Date: Aug 2019
Posts: 28
Thanks: 18
Thanked 0 Times in 0 Posts
HangoutGuy is on a distinguished road
Re: Event procedure not working on opening until VBA code is opened and closed

Quote:
Originally Posted by arnelgp View Post
add the declaration "Option Explicit" to each module you made.
compile your VBA, if you are lucky you will find what is causing the error.
Now I think this might be where the issue lies. So I wrote all the code in the VBA window that pops up after I hit design view on the form where the button is. Then I opened the property sheet for the button and under "Event" and "On Click" I hit the ... box that opened the VBA window.

I did not make any individual module to my knowledge because there is no module in my objects side bar. I feel like there is a simple copy and past to build an individual module but I don't know the right steps.

HangoutGuy 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
Multiple event procedure windows opened FrankP Forms 2 03-02-2013 06:54 AM
OnClick Event Procedure not working. mailman26 Macros 0 06-13-2012 11:23 AM
on Focus event procedure not working mcgraw Forms 7 12-22-2009 06:31 AM
After Update Event Procedure Code Del_Piero_3 Forms 0 08-19-2005 04:25 AM
Event Procedure in Acess not working mtreske Modules & VBA 4 10-16-2003 01:41 PM




All times are GMT -8. The time now is 01:06 AM.


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

Featured Forum post


Sponsored Links


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