Solved

Slow Form

Posted on 2004-10-12
10
688 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
 
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

930 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now