Need Help! Expression On Timer error message (1 Viewer)

maxinemnd

New member
Local time
Today, 04:58
Joined
Apr 1, 2020
Messages
15
Hi There!

You can consider me an intermediate in Access and I need your expert advice/help. I'm working on a due date form that would pull the information based on the date range specified. I have put the following and but when I open the form it gives me the error message "The expression On Timer you entered as the event property setting produced the following error: Method or data member not found." What am I missing?

Private Sub Form_Timer()
Me.TimeText.Value = Format(Time, "HH:mm:ss AM/PM")
Static iCount As Interger
iCount = iCount + 1
If iCount = 60 Then
Me.TimerInterval = 0
Call generateemail("SELECT * FROM Query1_due_date_Subform")
If Me.TimerInterval = 0 Then
Me.TimerInterval = 125
End If
Exit Sub
End If

End Sub



Also, when I click on the form it's supposed to send automated emails. Below is the AutoEmail module created:

Function AutoEmail(MySQL As String)
'On Error GoTo Exit Function:
Dim oOutlook As Outlook.Application
Dim oEmailItem As MailItem

Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset(MySQL)
If rs.RecordCount > 0 Then
rs.MoveFirst
Do Until rs.EOF
If IsNull(rs!Email) Then
rs.MoveNext
Else
'Set oOutlook = GetObject(, "Outlook.Application")
If oOutlook Is Nothing Then
Set oOutlook = New Outlook.Application
End If
Set oEmailItem = oOutlook.CreateItem(lEmailItem)
With oEmailItem

.To = rs!Email
.Subject = "Collaboration Project Expires in 30 days Reminder for " & rs!Collaboration_Owner
.Body = "CA Record #: " & rs!CA_Record_# & vbCr & _
"Institution: " & rs!Institution & vdCr & _
"Collaboration Owner: " & rs!Collaboration_Owner & vbCr & _
"PA Expiration Date: " & rs!PA_Expiration_Date & vbCr & vbCr & _
"This email is auto generated from the PMCD Collaboration Projects Database. Please Do Not Reply!"
.Display
' .Send
' rs.Edit
' rs!OnemonNotify = Date
' rs.Update
End With
Set oEmailItem = Nothing
Set oOutlook = Nothing
rs.MoveNext
End If
Loop

Else
' do nothing

End If



Would appreciate your suggestion/advice. Please note that I'm not quite into the Access terms yet. Would appreciate if you can tell me in layman's terms.

Maria
 

Micron

AWF VIP
Local time
Today, 07:58
Joined
Oct 20, 2018
Messages
3,476
If you put a break point on the form open event, are you get the error during that event or the timer event.

May not need to even check that as the message may mean that you've specified the event in the form property sheet but it is "disconnected" from the actual code. To fix that, in the property sheet click on the ellipses for the event and make sure that the vbe window opens to that event. I think that can happen if you create the event when in the vbe window by invoking the drop downs vs creating the event from the property sheet. I think that's rare, but I believe it has happened to me before.

P.S. please use code tags for code, and if you're posting code that raises an error, make sure you say which line it is as well as what the error is.
 

maxinemnd

New member
Local time
Today, 04:58
Joined
Apr 1, 2020
Messages
15
Thanks Micron. I hope I got what you are asking. I clicked on the 3 dots on the event within the form properties and it pops up to this. Seems it is attached to the correct form. Did I answer your question?

1585702102770.png
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:58
Joined
May 7, 2009
Messages
19,169
what does the generateemail() function does or does it exists?
there is no need to add Exit Sub statement. it will exit gracefully with or without it.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:58
Joined
May 7, 2009
Messages
19,169
I also notice your Module AutoMail has The Same function in it called AutoMail().
you need to rename the module to something else, otherwise error will occur.
rename it to modAutoMail or basAutoMail.

//edit: as June7 has good vision than me, its AutoEmail.
 
Last edited:

Micron

AWF VIP
Local time
Today, 07:58
Joined
Oct 20, 2018
Messages
3,476
It looks like you did. Did it change anything?
Not sure what June7 means because I also see a function and a module with the same name, which I would not do.
 

maxinemnd

New member
Local time
Today, 04:58
Joined
Apr 1, 2020
Messages
15
I also notice your Module AutoMail has The Same function in it called AutoMail().
you need to rename the module to something else, otherwise error will occur.
rename it to modAutoMail or basAutoMail.

//edit: as June7 has good vision than me, its AutoEmail.

Thanks I have changed the name as you have suggested:

1585786636696.png
 

maxinemnd

New member
Local time
Today, 04:58
Joined
Apr 1, 2020
Messages
15
what does the generateemail() function does or does it exists?
there is no need to add Exit Sub statement. it will exit gracefully with or without it.

Ok I believe you are correct. My understanding is when the form is clicked it's supposed to generate the email based on what is listed in the modAutoEmail module. My question is do I need to create template? is this why it is saying method is missing?

1585786786804.png
 

maxinemnd

New member
Local time
Today, 04:58
Joined
Apr 1, 2020
Messages
15
I did all the changes but I still get the same error message. Do I need to create an email template? It's supposed to do the email through Outlook. Is there anything special that needs to be done to have the connection between the two? I'm using Microsoft 360 and both Outlook and Access are the same versions.
 

maxinemnd

New member
Local time
Today, 04:58
Joined
Apr 1, 2020
Messages
15
Properly indenting nested code will make it easier to read and follow the execution flow.
Sorry I just copy pasted the code into the message and it didn't paste as formatted. This is how it shows in my DB:

1585787180314.png


1585787203202.png
 

maxinemnd

New member
Local time
Today, 04:58
Joined
Apr 1, 2020
Messages
15
I'm told that this database will be of no use if an email notification cannot be sent out concerning the due dates. Since the data have several due dates depending on the document, I guess I will need to recreate different forms. One question, I understand that in order for the email to be sent out, the database has to be open or accessed. Is there a way to automate this without having to access the database? I read somewhere about having Outlook automate it. I also read somewhere about doing some "coding" from the database? The DB is all set up as you can see and it is only this email notification that is what's holding it up. Please I really need your expert help.

1585787555779.png
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:58
Joined
May 7, 2009
Messages
19,169
It might be that generateemail() function on your timer event has same
function as in AutoEmail. so replace "generateemail" with "AutoEmail"
on you your project.
 

maxinemnd

New member
Local time
Today, 04:58
Joined
Apr 1, 2020
Messages
15
It might be that generateemail() function on your timer event has same
function as in AutoEmail. so replace "generateemail" with "AutoEmail"
on you your project.


I corrected it (see below)
1585793369834.png


But the same error popped up again. :-(

1585793309321.png
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:58
Joined
May 7, 2009
Messages
19,169
where is the TimeText textbox located?
do you have the query Query1_due_date_Subform?
 

Micron

AWF VIP
Local time
Today, 07:58
Joined
Oct 20, 2018
Messages
3,476
Very difficult to help when you post pictures of code - especially when we can't see what happens when the error is raised. I think you will have to post a copy of the db to get focused help, unless arnelgp is willing to continue fishing. I'm out of bait.

You definitely do not want to create the same form just because of different criteria. To answer your other question about automating this, yes it is possible, but if you are at a very novice level, quite difficult although not impossible. It depends on how much time and learning you are able to put into it. One method would be to use Windows Task Scheduler to open the db and execute code to send email, which by the way, is very doable with Office applications in Windows.
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:58
Joined
Sep 21, 2011
Messages
14,038
I was not aware you could Select from a form. ?
Certainly my 2007 balks at such a statement.?
 

Users who are viewing this thread

Top Bottom