Timed Reminder Box; Event Timer?

[WEJ]

Hi Guys,

I'm new to this forum but thought I'd give it a go . Hope your all well.


I've got a Request table in my database which has an ActionTime field. This field contains the time I want a reminder box to appear and say 'Call XX'. I think it's something along the lines of an timer pointing at the field with an event when the time occurs; only problem is I don't have the faintest how to code it!
I'm also aware that Email reminders could be easier, I wouldn't mind doing this in all honesty but it would again have to be time specific. And one last thing ... If I have a multiple reminders set at one 'ActionTime' will there be a problem in sending through the reminders i.e. cause it will want to execute the event at the same time, or will it stack it?

Best,
Will

[June7]

Never used timer event. I understand they can hog processor resources. Here is example http://msdn.microsoft.com/en-us/libr.../ff192530.aspx

Pulling the value from table is easy, use DLookup() function, something like:

If Format(Now(),"Short Time") >= DLookup("fieldname", "tablename", "some criteria here") Then
MsgBox "Make Call"
End If

What would determine the who to call?

[WEJ]

Hi June7,

First of all, thanks for the reply .

The link you posted was where I had been directed to through my own research, however I wasn't aware that they 'hog' processor resources, thanks for the heads up!
Where would I code the If statement, perhaps it would help if I explained the process a bit more accurately;
I currently have a form which is called;

frmRequests

The frmRequests fields contain a variety of different pieces of information (for the sake of this query I won't go into to full detail). One of the fields is an 'ActionTime' field, this is set to time validation.

I want a piece of code that auto-executes a pop-up box or sends an email, when the time occurs, containing information contained in the other fields in the table. These fields will be 'ActionType', this is the type or action required by the user i.e. Call Back, Email Required etc (these are acronyms in the database), 'ContactID/CompanyID/LocalAuthorityID) this is the entity/person associated to the request.

If you'd be kind enough to guide me as to how I'd arrange this I'd really appreciate it!

Many Thanks,

Will

NB: The person/entity associated to the request would determine the 'who to call'.

[June7]

Timer event is something of a mystery to me but maybe something like:

Private Sub Form_Timer()
If Format(Now(),"Short Time") = "17:00" Then
   strAction = DLookup("ActionType", "tablename", "ContactID=" & Me.ContactID)
   Select Case strAction
       Case "Call Back"
         MsgBox "Call " & Me.ContactID
       Case "Email Required"
         'code to send email
       Case ...
   Select End
End If
End Sub

This requires the form to remain open at all times.

[orange]

If you don't want to have Access running continuously and constantly checking the Time, you could look into using the Windows Task Scheduler.

see http://support.microsoft.com/kb/308569
You can google using your on OS to get specifics

[WEJ]

Hi June & Orange,

Thanks for the replies and apologies for the late response.
I've had a bash at coding it to my requirements and can't seem to get it to work. The error is stating that their is "wrong number of arguments or invalid property assignment", highlighting the DLookup function.

#
Private Sub Form_Load()
If Format(Now(), "Short Time") = "17:00" Then
strAction = DLookup("ActionStatus", "tblRequests", "CompanyID=" & Me.CompanyID, "EventID=" & Me.EventID, "LocationID=" & Me.LocationID, "UniversityID=" & Me.UniversityID, "LocalAuthorityID=" & Me.LocalAuthorityID)
Select Case strAction
Case "CB"
MsgBox "Call back required to:" & Me.ContactID, Me.EventID, Me.LocationID, Me.UniversityID, Me.LocalAuthortyID
Case "ER"
MsgBox "Emailed required to:" & Me.ContactID, Me.EventID, Me.LocationID, Me.UniversityID, Me.LocalAuthortyID
Case "MR"
MsgBox "Meeting required from:" & Me.ContactID, Me.EventID, Me.LocationID, Me.UniversityID, Me.LocalAuthortyID
Case "LR"
MsgBox "Letter required to:" & Me.ContactID, Me.EventID, Me.LocationID, Me.UniversityID, Me.LocalAuthortyID
Case "ECB"
MsgBox "Expecting call back from:" & Me.ContactID, Me.EventID, Me.LocationID, Me.UniversityID, Me.LocalAuthortyID
Case "EEB"
MsgBox "Expecting email back from:" & Me.ContactID, Me.EventID, Me.LocationID, Me.UniversityID, Me.LocalAuthortyID
Case "ELB"
MsgBox "Expecting letter back from:" & Me.ContactID, Me.EventID, Me.LocationID, Me.UniversityID, Me.LocalAuthortyID
Select End
End If
End Sub
#

I've just assigned this to the form, 'frmRequests' on the OnLoad event and want this event to be assigned to the 'ActionTime' field and reminding me at the time the action should be ... well ... actioned.
I have also input in the DLookup query the variety of different tables a request can be assigned to.
If you guys would be kind enough to drop me some advice that'd be greatly appreciated. I'd also really appreciate any recommendations on literature for teaching myself the basics so that I can give myself a bit more help in programming .

Many Thanks,

William

[orange]

Take a look at the examples (multiple field is the criteria)
http://support.microsoft.com/kb/285866