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

maxinemnd

New member
Local time
Today, 08:53
Joined
Apr 1, 2020
Messages
15
where is the TimeText textbox located?
do you have the query Query1_due_date_Subform?

Yes I have the query Query1_due_date_Subform. The TimeText textbox is located in the Form Header.
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.

Thanks Micron. I can post a copy of the DB. Where can I save a copy that you can have access?
 

Micron

AWF VIP
Local time
Today, 11:53
Joined
Oct 20, 2018
Messages
3,476
You upload it here. Best to make a copy, compact that then zip it then post. If it's too large, you might have to remove needless records/tables/forms/reports.
 

maxinemnd

New member
Local time
Today, 08:53
Joined
Apr 1, 2020
Messages
15
You upload it here. Best to make a copy, compact that then zip it then post. If it's too large, you might have to remove needless records/tables/forms/reports.


Here it is. Thanks a lot for all your guys' help. Here is the database
 

Attachments

  • PMCD Collaboration Projects - Copy.zip
    267.8 KB · Views: 139

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:53
Joined
May 7, 2009
Messages
19,169
test this.
you really need to add the "Option Explicit" declaration so there would be an error checking
for undeclared variables.
 

Attachments

  • PMCD Collaboration Projects - Copy.zip
    280.9 KB · Views: 136
Last edited:

maxinemnd

New member
Local time
Today, 08:53
Joined
Apr 1, 2020
Messages
15
test this.
you really need to add the "Option Explicit" declaration so there would be an error checking
for undeclared variables.

Thank you so much Arnel! It worked and an email popped up. One thing though, there were supposed to be 4 recipients but the email that was created was only for the 1st one (Employee 4). When I changed the "Query1_due_date_Subform" single form to continuous form, I see the message "Creating email, please wait..." on all 4 but still only the email to Employee 4 pops up. Also where do I add the "Option Explicit"? To the modAutoEmail or the form query1?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:53
Joined
May 7, 2009
Messages
19,169
sorry, I forgot to comment out the code "Exit Do" on automail() while testing the <table> formatting.
It is not commented on the attached.

also move lblNoticed to the Main form.
 

Attachments

  • PMCD Collaboration Projects - Copy.zip
    274.7 KB · Views: 130

Gasman

Enthusiastic Amateur
Local time
Today, 15:53
Joined
Sep 21, 2011
Messages
14,044
FWIW you do not need the creation and destroying of the Outlook Application object within the loop. You can set it once at the start and destroy at the end and just create a new mail item within the loop.?
 

maxinemnd

New member
Local time
Today, 08:53
Joined
Apr 1, 2020
Messages
15
sorry, I forgot to comment out the code "Exit Do" on automail() while testing the <table> formatting.
It is not commented on the attached.

also move lblNoticed to the Main form.

Hi Arnel, When I run the form I get this error. I have not touched anything from what you had sent. You mentioned to move lblNoticed to the main form. My apologies but I'm not quite familiar on what you are asking me to move and how to do it or where to place within the main form. When you say main form is that the main PMCD Collaboration Projects form or the Query1_due_date_Subform? Thanks again for all your help.

1586201164299.png
 

maxinemnd

New member
Local time
Today, 08:53
Joined
Apr 1, 2020
Messages
15
FWIW you do not need the creation and destroying of the Outlook Application object within the loop. You can set it once at the start and destroy at the end and just create a new mail item within the loop.?

I'm sorry but I'm a novice in this and am not sure what you mean? Thanks.
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:53
Joined
Sep 21, 2011
Messages
14,044
I'm sorry but I'm a novice in this and am not sure what you mean? Thanks.
You are creating an Outlook object for every record found. You only need to create a new mailitem for each record, the Outlook application object can be created just once at the start of the code. More efficient?

This is one part of one of my routines.

Code:
    Set objOutlook = CreateObject("Outlook.Application")
....
....
....
    Do While Not rs.EOF
        ' Set flag and field to check
        blnSameClientType = True
        strClientType = rs!Client & rs!TranType
        strType = rs!TranType
        
        ' Create the message if first time we are in a different client or tran type.
        Set objOutlookMsg = objOutlook.CreateItemFromTemplate(strTemplatePath & "\SSAFA Email.oft")
        With objOutlookMsg

Notice how the application object is set before I enter the recordset loop?
 

isladogs

MVP / VIP
Local time
Today, 15:53
Joined
Jan 14, 2017
Messages
18,186
The code in your screenshots is missing an End If. Two If lines and currently only one End If
Recommend you add Option Explicit as the second line in this (and all) code modules.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:53
Joined
May 7, 2009
Messages
19,169
I delete that line and instead show a "message" on the "status bar".
 

Attachments

  • PMCD Collaboration Projects - Copy.zip
    277.2 KB · Views: 136

maxinemnd

New member
Local time
Today, 08:53
Joined
Apr 1, 2020
Messages
15
I delete that line and instead show a "message" on the "status bar".

Thank you very, very much Arnel this worked!!! Appreciate all your help in getting this done. I kept your notes/name within the codes so whoever will look at it will see the help you did. Again, thank you so much.
 

June7

AWF VIP
Local time
Today, 07:53
Joined
Mar 9, 2014
Messages
5,423
A VBScript can call a procedure in db even if it is closed. I use Windows Task Scheduler to run this script. Example:
Code:
Dim ObjAccess
Set ObjAccess = CreateObject("Access.application")
ObjAccess.visible = false
ObjAccess.OpenCurrentDatabase("C:\Users\Owner\June\LL\Umpires.accdb")
ObjAccess.UserControl = False

'uncomment and use one of the following 3 lines
'ObjAccess.Run ("mySub")
'ObjAccess.Run "mySub", "argument"
'ObjAccess.DoCmd.RunMacro "myMacro"

ObjAccess.Quit
Set ObjAccess = Nothing
Create this with Notepad and change file extension to vbs.
 

Users who are viewing this thread

Top Bottom