Solved

Slow Form

Posted on 2004-10-12
10
689 Views
Last Modified: 2008-02-01
Hello...,

I have a form that I open in two ways depending on if I'm adding or editing records.

If it opens with acFormAdd then it opens real slow. Why is this so..., and how can I speed it up?


Thanks
0
Comment
Question by:Moother
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 16

Assisted Solution

by:GreymanMSC
GreymanMSC earned 100 total points
ID: 12293249
Form opening speeds usually depend on two things.  How many controls have to be populated (list and combo boxes are the main offenders) and how complex is the row source query.  Sub forms can also slow things down as each one has to be loaded to.  Opening in FormAdd mode will be faster simply because there is less data used to fill the form.
 
I've had some luch speeding up the opening time of complex forms by using load-on-demand tab controls.  By this I mean that you do not populate controls or child forms on a tab-page until the user actualy selects that page for viewing.  This requires quite a bit of coding behind the OnChange property of the tab control, but does improve performance.
 
On simpler forms, the problem will likely be the recordset used to populate the form's rowsource.
0
 
LVL 32

Assisted Solution

by:jadedata
jadedata earned 50 total points
ID: 12293292
Hi Moother,
  In Add mode the form must load the whole dataset and move to the end of it before displaying the ready-to-accept data form you see.  Comboboxes need populated, listboxes etc...

  Do you think you could get away with adding records to an unbound form and post the record to the table on the fly from the form?

regards
:)-j-
0
 
LVL 16

Expert Comment

by:GreymanMSC
ID: 12293549
(opps... FormEDIT is faster because it needs to load less data into the recordset, as jadedata pointed out why.)
 
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 32

Expert Comment

by:jadedata
ID: 12293673
I tried to state my case with the least possible "contrary" look to it GreymanMSC!  
Didn't want to make a big thing outta that.
If you hadn't said anything it might have slid by.

:)
0
 
LVL 26

Accepted Solution

by:
Alan Warren earned 350 total points
ID: 12294365
You can restrict the recordset by giving an impossible WHERCONDITION to the openform method
Docmd.OpenForm("YourFormName",acNormal,,"[id]=-9999",acFormAdd)
or some other unlikely value like Thedate = #1-jan- 2155#


Forty-Three Ways to Make DAO Faster--Programming to DAO Using Microsoft Access
http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccessdev/html/ODC_MakeDAOFaster.asp


Alan
0
 
LVL 3

Author Comment

by:Moother
ID: 12296069
You guys are GOOD...,

I used A.W.'s solution to limit the record set. Opens like a bunker-buster...

Only thing is..., have a sub-form and this now opens slowly still... How can I now trap it's record source to limit it's record set...?
0
 
LVL 34

Expert Comment

by:flavo
ID: 12296410
Hijacking...
Nice link Alan.. have to bookmark that one..

esp like this one >> 11. Replace DAO code loops with the equivalent SQL statements - but not always
0
 
LVL 3

Author Comment

by:Moother
ID: 12297017
Hijacking?
0
 
LVL 16

Assisted Solution

by:GreymanMSC
GreymanMSC earned 100 total points
ID: 12304695
Comment from alanwarren
    You can restrict the recordset by giving an impossible WHERE-CONDITION
    to the openform method
        Docmd.OpenForm("YourFormName",acNormal,,"[id]=-9999",acFormAdd)
        or some other unlikely value like Thedate = #1-jan- 2155#
---
Or simply use:
    Docmd.OpenForm("YourFormName",acNormal,,"FALSE",acFormAdd)
---
Comment from Moother
    Only thing is..., have a sub-form and this now opens slowly still...
    How can I now trap it's record source to limit it's record set...?
----
Do not set the child's SourceObject property until the form's bookmark actually sits on an active record.  In design view, remove the subform's name from the source property and place it in the Tag property instead.  In the Form's Current and AfterUpdate events, place something like the following code (substiuting control names where appropriate):

    Private Sub Form_Current()
        Dim C as SubForm
        Set C = Me.ChildForm '<!-- put your subform here -->
        If IsNull(Me.ID.Value) Then
            If Not (C.SourceObject = C.Tag) Then
                Let C.SourceObject = C.Tag
            End If
            Let C.Enabled = True
        Else
            Let C.Enabled = False
        End If
        Set C = Nothing
    End Sub
    Private Sub Form_AfterUpdate()
        Dim C as SubForm
        Set C = Me.ChildForm '<!-- put your subform here too -->
        If Not (C.SourceObject = C.Tag) Then
            Let C.SourceObject = C.Tag
        End If
        Let C.Enabled = True
        Set C = Nothing
    End Sub

This means that you will need to save a new record before you can access the subform.  I find this to be a useful data entry trap - since you often don't want the user to try to create related records until the main record is properly constructed.
0
 
LVL 3

Author Comment

by:Moother
ID: 12324309
OK,

So, I killed the subform and simply changed the criteria of ID into [OpenArgs] hoping that it would work, IT DID!!!

Just set the OpenArgs to "<1" if i'm adding and to Me.txtID if I'm editing.

Not bloody bad.

Thanks,

M
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question