Hello this is my first post so bare with me please
I am a university placement student in a business that use MS access for DBs and front ends. One of the databases i have been
asked to 'better'.
Part of this is a calander for appointments. I have several forms that all work perfectly but they are very slow. I have run
a few trial and error tests with vb and found that it is a specific module that is slowing the form down. Could anyone point
me in the right direction as to how to speed it up?
the code is as follows:
Public Sub ShowDayAppts3(vDate As Date)
'Copies the appointments info from tblAppointments into tblWeekData for the selected day
'Entry (vDate) = Date to be displayed
'Exit tblWeekData holds the appointments for the selected week (only Day1Data field used in this case)
Dim rst As Recordset
Dim vFirstDate As Date, vDateStop As Date
Dim vRow As Long, vTemp As Long
Dim vArray(0, (1440 conPeriod) - 1) As Variant '1 column (day) x 48 (time slots) rows
On Error GoTo ErrorCode
'Calc week numbers and dates from starting date and copy to table
vFirstDate = vDate 'set vFirstDate to selected date
'Fetch all appts for selected day
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblAppointments3 WHERE " _
& "DateValue(ApptStart) = #" & format(vDate, "yyyy/m/d") & "# ORDER BY ApptStart") 'fetch appts for selected day
Do Until rst.EOF
'Calc start and end dates for current appointment time and save
If DateValue(rst!ApptStart) < vFirstDate Then 'if appt starts before selected date
vDate = DateValue(vFirstDate) & " 00:00:01" 'start at first time slot in calendar
for current date
vDate = rst!ApptStart 'start at first time slot of appt
If DateValue(rst!ApptEnd) > vFirstDate Then 'if appt ends after selected date
vDateStop = DateValue(vFirstDate + 1) & " 00:00:01" 'stop at 00:00:01 on next day (added
1 sec to set time part)
vDateStop = DateValue(vFirstDate) & " " & TimeValue(rst!ApptEnd) 'stop at last time slot of appt
'Now copy appt info into each row in array for col 0
vRow = (((Hour(vDate) * 60) + Minute(vDate)) conPeriod) 'calc Row No from date & time & period
If rst!ApptID vTemp Then 'if temp store different than data then
vArray(0, vRow) = vArray(0, vRow) & rst!ApptSubject & " - " & rst!Researcher & " - " & IIf(rst!Exported,
"Yes", "No") & " " 'add appt data to array
vTemp = rst!ApptID 'update temp store !!!
Else 'else !!!
vArray(0, vRow) = "''" 'show " char !!!
End If 'end if !!!
vDate = DateAdd("n", conPeriod, vDate) 'inc time ref by 30 mins
Loop Until DateDiff("n", vDate, vDateStop)