Hello UA members

I have a table that has appointments in it with the start time and date. I would like for a form to pop up as a reminder 30 mins prior to the start of the appointment. Is this possible? I already have a form that pops up when the DB is loaded to show all the appointments for the day but just wanting to take it a step furrther. Any thoughts? Thanks in advance.

TheCaptain

You could use the Timer event of a hidden form to run a query that looked for records from today within 30 minutes before the current time. If the query returned a result then open your pop up reminder form.......

Maybe set up the timer event to dlookup the next appointment and determine if it's 30 minutes away. Maybe put a field in the table that indicates if the reminder has been sent and set it to true so after the reminder, it will look for the next appointment that a reminder hasn't been sent. This way you only get one reminder per appointment.

Alan,

That's exactly what i'm trying to do or maybe just a msgbox to display the next appointment(s). where i'm confused is getting the syntax down. Do i use a DateDiff something like this:

DateDiff("n","starttime",time())

I'm just kinda lost on how to go about it.

TheCaptain

Mark,

Great suggestion, again, any idea on how i can set this up in VBA? i'm familiar with VBA but the time difference is kicking my butt.

TheCaptain.

I had a form that did that. It counted all of the records that were greater than 7days old and put up a message box saying that "There are X-number of tasks that are over 7 days old, would you like to display them?".

I'm looking for the coding now to give you an idea...

This is what I've found...



That code is pretty much verbatim, hope it helps you out.

Graffix,

how do i get the number of minutes between two times? for example 7:30 am and 8:00 am = 30 mins. How can i calculate that in VBA. Any Ideas?

Thanks for the code though. I have a function that i'm trying to create now and testing with a cmd button.

TheCaptain

if DateDiff("n",Now(),[appointmentfield]) = 30

This is what you'd do if you wanted the difference in time:

Dim lngTest As Long

lngTest = Format(DateDiff("h",[start_date],[end_date],"Short Time")

Maybe something like:

'This will look for the number of appointments for the rest of the day and whether to continue with the code.

if dcount("*","appointmenttable","appointmenttime > now and format(appointmenttime, 'm/d/yy') = format(now,'m/d/yy')) > 0 then

nextappointment = dmin("appoimtmenttime","appointmenttable","appointmenttime > now")

if DateDiff("n",Now(),nextappointmenttime) = 30 then

openform or msgbox

end if

end if

Graffix,

I got your code to work as long as i take out the "short date" part. Otherwise i get a "Type mismatch error". Not sure what is causing it. this is what i have:

Dim lngTest as Long

lngTest = Format(DateDiff("n", Time, intTime), "Short Time")

Maybe i'm getting in over my head. Do i need a counter to do this? if i have more than one appt during the day at different times will a form or msgbox be able to appear 30 mins before every appt? Or am i really over thinking this?

Thanks to all you guys for your continued help. It's getting late here since i'm in Iraq so i'll check back up on this in the morning.

TheCaptain.

It should work for each appointment. When I put the code into my old DB it ran through each record and found ALL records that were older than 7 days from the date that it was then. Then it displayed the message box saying "X number of records are older than 7 days". So it should work for ALL of your appointments without an issue.

The "Short Time" would have to be correlated to the time format of your appointment table. If you have it in Long or any of the other choices, that's probably why you're getting the error.

Sorry to re-post this, but it should do what you want. I've added more comments to it. And BTW, thanks for all you're doing over there. My brother was there twice.

First set the timer interval in your open or load event.

And place this code in the form_timer event:

'This will look for the number of appointments for the rest of the day and whether to continue with the code.

if dcount("*","appointmenttable","appointmenttime > now and format(appointmenttime, 'm/d/yy') = format(now,'m/d/yy')) > 0 then

'Now that we've determined that there are appointments later in the day, the timer will check at each interval for the first appointment that is greater than the current time.

nextappointment = dmin("appoimtmenttime","appointmenttable","appointmenttime > now")

'Check to see if the next appointment is 30 minutes from current time.

if DateDiff("n",Now(),nextappointmenttime) = 30 then

openform or msgbox

end if

end if

Hi

Put the following in the Timer event of your hidden form, changing the names to your actual names. You'll need to make sure you have a reference set to DAO to use the code. Set the Timer Interval to however long you want to wait to run the code between checks (1 second = 1000).........



***Note - aircode and not tested

Alan,

I made a form and in the timer event i put your code. I changed in your code the following:

AppDet to ApptDate
AppTime to ApptStartTime

no big deal there.

I added a button on one of my forms just for test purposes. This button just opens the form in acDialog mode so i can see it. I'll hide it later after it's working.

When i click on it i'm getting the following error:

RunTime Error: 3061

Too Few parameters - Expected 1.

I have no idea what this means. Any thoughts? thanks for your help as i think this is what i'm looking for. thanks again.

TheCaptain

i think i got the error fixed. My autonumber in my table was wrong. I'm testing it now. I'll let you know it if i have any other problems with it.

Thanks again.

TheCaptain

Hi

In my example the field AppDet would be the details of the appointment (eg Dentist or Doctor) and AppTime would store the date and time (eg 30/07/2008 21:30:00).

It's better if you store the date and time element together in a Date/Time datatype field rather than seperate fields. If you don't also store the details of the appointment, you wont need the AppDet field at all............

Alan,

Ok, nothing happened after 30 mins. NOt sure what's going on. any ideas? it's time for me to go to bed finally. I'll check back in the morning. Thanks again.

TheCaptain

Alan,

Well i'm not getting any errors but no msgbox's either. In my table i have a field for Appt Date and Appt Start Time, their not together. On the Appointment form the user inputs the Appt Date and Appt start time separtely. Not sure if this is why it's not working or not. Where you had AppDet i changed that to ApptNotes as that is where the details are about the appoint in the table. Table structure is below for viewing in case i'm missing something and for advisement. I got this appointment DB from UA in the archives and implented it in my own DB.

tblAppointments:
ApptNumberId = auto
Appt = Text This is just the subject of the meeting
ApptStartTime = date/time (on user form this time comes from a lookup table with times in it as date/time)
ApptEndTime = date/time
ApptLocation = text
ApptNotes = memo

tblHour
HourId = auto
Hour = date/time

I attached just the appointment DB with the hidden form and your code that i put in so you can see what it's doing if you have time. Thanks in advance for any further help.

TheCaptain

Here's something that works if you want to use it.

Markv,

I just got the attachment and opened it, set an appointment and the msgbox popped up. Works great. I did notice that the Appt field in the table now combines the two times. Thanks for the quick response as this is exactly what i was looking for. Thanks again.

TheCaptain.

Glad to help.