Event procedure not working on opening until VBA code is opened and closed (1 Viewer)

HangoutGuy

Registered User.
Local time
Today, 14:16
Joined
Aug 6, 2019
Messages
28
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!
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 17:16
Joined
Apr 27, 2015
Messages
6,286
Going to need to see the code, almost impossible to assist with so little to go on...
 

Micron

AWF VIP
Local time
Today, 17:16
Joined
Oct 20, 2018
Messages
3,476
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.
 

HangoutGuy

Registered User.
Local time
Today, 14:16
Joined
Aug 6, 2019
Messages
28
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

Registered User.
Local time
Today, 14:16
Joined
Aug 6, 2019
Messages
28
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.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 17:16
Joined
Apr 27, 2015
Messages
6,286
Out of curiosity, do you have an AutoExec Macro?
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 17:16
Joined
Apr 27, 2015
Messages
6,286
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?
 

HangoutGuy

Registered User.
Local time
Today, 14:16
Joined
Aug 6, 2019
Messages
28
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?
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 17:16
Joined
Apr 27, 2015
Messages
6,286
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?
 

HangoutGuy

Registered User.
Local time
Today, 14:16
Joined
Aug 6, 2019
Messages
28
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

Registered User.
Local time
Today, 14:16
Joined
Aug 6, 2019
Messages
28
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.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 17:16
Joined
Apr 27, 2015
Messages
6,286
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!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:16
Joined
May 7, 2009
Messages
19,169
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.
 

HangoutGuy

Registered User.
Local time
Today, 14:16
Joined
Aug 6, 2019
Messages
28
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.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:16
Joined
May 7, 2009
Messages
19,169
not necessarily on separate module, form module.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:16
Joined
May 21, 2018
Messages
8,463
This message has nothing to do with your code. That message is saying that a button does not support a click event, which is not true. I would try to compact and repair, but I would expect that not to help. You most likely need to decompile and recompile. You can google how to do a decompile.
 

HangoutGuy

Registered User.
Local time
Today, 14:16
Joined
Aug 6, 2019
Messages
28
This message has nothing to do with your code. That message is saying that a button does not support a click event, which is not true. I would try to compact and repair, but I would expect that not to help. You most likely need to decompile and recompile. You can google how to do a decompile.

Is there any reason why my button just started working when I started up my db again? Because now I'm having no problems and I didn't change anything or even compact & repair for that matter. And this wasn't the first time I shut it down and reopened it.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:16
Joined
May 7, 2009
Messages
19,169
maybe outlook is causing the problem.
you code does not stop and wait for outlook to finish, when you put the command SendObject.
it continues to the next record and so on.
 

Users who are viewing this thread

Top Bottom