Code runs slow - unbound form

[Vic Spainhower]

Hello,

I have an unbound form and I'm populating the fields using a recordset that
is created and the field value is selected for each of the fields. However,
this is slow and takes about 6-7 seconds to load the form. I changed from
using the Dlookup function which was taking 7 seconds so there was very
little improvement. The fields EntryNbr and ClassEnt are part of the
primary key and the fields Place1-8 are indexed. Without the DLookup or the
recordset it loads in a fraction of a second. The table only has 362
records.

Any ideas on how to improve this code?

Thanks a lot,

Vic

Set DB = CurrentDb
For j = 1 To 8
For p = 1 To 11
Me("Place" & j & p).Visible = True
' Get the back number if class was already placed
Me("Place" & j & p).SetFocus
On Error Resume Next
If p < 10 Then
strPlace = "0" & p
Else
strPlace = CStr(p)
End If
' criteria = "ClassEnt = " & Chr(39) & Me!ClassNumber & Chr(39) & "
AND Place" & j & " = " & Chr(39) & strPlace & Chr(39)
' Me("Place" & j & p).Text = tLookup("EntryNbr", "ClassesEntered",
criteria)
SQL = "SELECT EntryNbr FROM ClassesEntered" & " WHERE ClassEnt = " &
Chr(39) & Me!ClassNumber & Chr(39) & " AND Place" & j & " = " & Chr(39) &
strPlace & Chr(39)
Set rs1 = DB.OpenRecordset(SQL, DB_OPEN_DYNASET)
Me("Place" & j & p).Text = rs1!EntryNbr
rs1.Close
Set rs1 = Nothing
Next p
Next j

 

[Dirk Goldgar]

Hello,

I have an unbound form and I'm populating the fields using a
recordset that is created and the field value is selected for each of
the fields. However, this is slow and takes about 6-7 seconds to load
the form. I changed from using the Dlookup function which was taking
7 seconds so there was very little improvement. The fields EntryNbr
and ClassEnt are part of the primary key and the fields Place1-8 are
indexed. Without the DLookup or the recordset it loads in a fraction
of a second. The table only has 362 records.

Any ideas on how to improve this code?

Thanks a lot,

Vic

Set DB = CurrentDb
For j = 1 To 8
For p = 1 To 11
Me("Place" & j & p).Visible = True
' Get the back number if class was already placed
Me("Place" & j & p).SetFocus
On Error Resume Next
If p < 10 Then
strPlace = "0" & p
Else
strPlace = CStr(p)
End If
' criteria = "ClassEnt = " & Chr(39) & Me!ClassNumber &
Chr(39) & " AND Place" & j & " = " & Chr(39) & strPlace & Chr(39)
' Me("Place" & j & p).Text = tLookup("EntryNbr",
"ClassesEntered", criteria)
SQL = "SELECT EntryNbr FROM ClassesEntered" & " WHERE
ClassEnt = " & Chr(39) & Me!ClassNumber & Chr(39) & " AND Place" & j
& " = " & Chr(39) & strPlace & Chr(39)
Set rs1 = DB.OpenRecordset(SQL, DB_OPEN_DYNASET)
Me("Place" & j & p).Text = rs1!EntryNbr
rs1.Close
Set rs1 = Nothing
Next p
Next j

You're opening and closing a recordset 88 times. That's going to chew
up some CPU cycles, for sure. What's your table structure? Quite
likely you can arrange to open the recordset only once, and either make
one pass through it or load it into an array for subscripting.