Full Version: auto form
UtterAccess Forums > Microsoft® Access > Access Forms
TheCaptain
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
Alan_G
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.......
markv
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.
TheCaptain
Alan,
hat'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
TheCaptain
Mark,
reat 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.
Grafixx01
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...
CODE

**Placed in the "On Close" event of the form**
Dim lngCount As Long
lngCount = Nz (DCount("DaysSinceOriginated" , "qryReminder"),0)
If lngCount >0 then
If MsgBox "Would you like to see the X number of over-due tasks? (yes / no)"
Else
End If
Else
DoCmd.OpenForm "frmMainMenu"
End If

That code is pretty much verbatim, hope it helps you out.
TheCaptain
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
markv
if DateDiff("n",Now(),[appointmentfield]) = 30
Grafixx01
This is what you'd do if you wanted the difference in time:
im lngTest As Long
lngTest = Format(DateDiff("h",[start_date],[end_date],"Short Time")
markv
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
TheCaptain
Graffix,
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.
Grafixx01
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.
markv
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.
irst 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
Alan_G
Hi
But 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).........
CODE
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strMessage As String
strSQL = "SELECT tblAppointments.AppID, tblAppointments.AppDet, Format([AppTime],'hh:nn ampm') AS TimeBit " _
& "FROM tblAppointments " _
& "WHERE tblAppointments.AppTime Between Now() And DateAdd('n',30,Now()) " _
& "ORDER BY tblAppointments.AppTime"
Set db = Currentdb()
Set rs = db.OpenRecordset(strSQL ,dbOpenSnapshot)
With rs
If .RecordCount > 0 Then
.MoveLast
.MoveFirst
Do Until .EOF
strMessage = strMessage & "Appointment Detail: " & .Fields("AppDet") & "    Time: " & .Fields("TimeBit") & vbCrLf
.MoveNext
Loop
End If
End With
If Len(strMessage) > 0 Then
Msgbox "The following appointment(s) are imminent:" & vbCrLf & vbCrLf & strMessage, vbOKOnly,"Appointments"
End If
rs.Close
Set rs = Nothing
Set db = Nothing

***Note - aircode and not tested
TheCaptain
Alan,
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.
Oadded 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
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
Alan_G
Hi
On 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............
TheCaptain
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
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
Oattached 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
markv
Here's something that works if you want to use it.
TheCaptain
Markv,
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.
markv
Glad to help.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.