Help. Slow form

[mckee118]

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") & "# AND " _
    & "DateValue(ApptEnd) >= #" & 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 then
            vDate = DateValue(vFirstDate) & " 00:00:01"                                 'start at first time slot in calendar for current date
        Else                                                                            'else
            vDate = rst!ApptStart                                                       'start at first time slot of appt
        End If

        If DateValue(rst!ApptEnd) > vFirstDate Then                                     'if appt ends after selected date then
            vDateStop = DateValue(vFirstDate + 1) & " 00:00:01"                         'stop at 00:00:01 on next day (added 1 sec to set time part)
        Else                                                                            'else
            vDateStop = DateValue(vFirstDate) & " " & TimeValue(rst!ApptEnd)            'stop at last time slot of appt
        End If

        'Now copy appt info into each row in array for col 0
        Do                                                                              'do--
            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) <= 0                                 'until all cells filled
        rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing

'Now copy contents of all rows in temp array into table (tblWeekData)
    For vRow = 0 To (1440 \ conPeriod) - 1                                              'for each row in array
        CurrentDb.Execute "UPDATE tblWeekData SET " _
        & "RSU2Day1Data = " & QUOTE & RTrim(vArray(0, vRow)) & QUOTE & " " _
        & "WHERE RowNo = " & vRow + 1                                                   'copy to tblWeekData table (only Day1Data field used in this mode)
    Next
    Exit Sub

ErrorCode:
    Beep
    MsgBox Err.Description

End Sub

there are 6 of these in the module, plus other code but its these 6 that are slowing the process.

Thanks in advance

 

[Bilbo_Baggins_Esq]

What version of Access are you and/or the users running?

By "slow" do you mean the form loads, but the expected contents lag behind and are slow to populate?

 

[mckee118]

What version of Access are you and/or the users running?

By "slow" do you mean the form loads, but the expected contents lag behind and are slow to populate?

We are all running Access 2003 (not by choice lol)

Well when i press the next button on the form, access freezes up until the contents is displayed(if there are any contents to be displayed). On click on the button all 6 of these processes are called. If i comment these out of the 'on click' event in the code then the form moves to the next day smoothly.

 

[mckee118]

Bump Bump!