>> Delay loading form

  • Thread starter Thread starter Jonathan Parminter
  • Start date Start date
J

Jonathan Parminter

Hi,

Access XP with front end and back end.

I have a search form that lists records. The user selects
a record to display in a 'main' form As the user is only
interested in this one record I set the recordsource of
the 'main' form rather than a simple filter.

The problem is that due to the number of records, even
only one person is using the system, it takes a couple of
seconds to load. During these loading seconds the form
displays #name. Is there a way to delay the display of the
form until the fields are loaded?

I have tried to hide the form before setting the
recordsource. But it makes no difference :-(

the following is the code I'm using...

strSQL = "SELECT * FROM qryMainCallDetail " _
& "WHERE ((Call)='" & txtCallNo & "');"

strForm = "frmMainCallDetail"

DoCmd.OpenForm FormName:=strForm, WindowMode:=acHidden

With Forms(strForm)
.RecordSource = strSQL
.Controls("lstAction").Requery
End With

DoCmd.OpenForm FormName:=strForm, WindowMode:=acDialog

Any ideas or suggestions appreciated :-)

Cheers
Jonathan
 
Hi the answer my friend is the change the lines
from...

With Forms(strForm)
.RecordSource = strSQL
.Controls("lstAction").Requery
End With

DoCmd.OpenForm FormName:=strForm, WindowMode:=acDialog

to...

With Forms(strForm)
.RecordSource = strSQL
.Controls("lstAction").Requery
DoCmd.Restore
.Modal = True
.Visible = True
End With

However, if you have a better way to acheive this please
share it :-)

Cheers
Jonathan
 
You don't explain why you are doing a requery on the lstAction control.

However, you can simply use the where clause of the open form. The following
does assume that the form already has a record source.

In your example code, you are first opening the form (and does it have a
record source set?...I bet it does. So, now you are causing records to be
dragged down into the form). N E X T you then go on to set the forms record
source again with your strSQL. So, likely the form is having to load two
recordsets.

So, it seems to me, that you want to eliminate the first recorset load that
the form goes through. (you may thus want to make the forms reocrdset blank
to save on the initial load time).

Further, it is CRITICAL that the field "call" have a index on it? Does it?

So, why not let access load the reocrdset only once, and restrict the form
load tot he one record. I suggest you try the following MUCH simpler code,
and it likely will also load a good deal faster:

So, try leaving the form with a legitimate reocrdsouce set if you try the
following:

dim strWhere as string

strWhere = "Call = '" & txtCallNo & "'"

DoCmd.OpenForm strForm,,,strWhere

I count two lines of code, and to be fair, you could do all of the above
with one line like:

docmd.OpenForm strForm,,,"Call = '" & txtCallNo & "'"

Anyway, give the above a try, and as mentioned, MAKE SURE that Call field
has a index.

Placing a index on the field should fix this, and then you can sue the above
simple code.

You for the sake of testing, you can also try your original approach where
you stuff the sql in, but you do want to ensure that the form load occurs
WITH NO record source set for the form BEFORE you set the sql. Otherwise,
you are doing extra work on the forms intial load of the reocrdset, and then
throwing out this work when you stuff in your own sql.


--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.attcanada.net/~kallal.msn
 
If the 'Call' field is indexed, the SELECT should be extremely fast. Maybe
there is something else in the form, that makes it load slowly. Try opening
it with a blank recordsource. If it is still slow, you should find out why
that is.

HTH,
TC
 
thanks TC and Albert for the ideas.

Maybe we were having network problems as using the form
openargs works without first showing #name in all fields.

Cheers
Jonathan
 
Back
Top