Solved

Slow Form

Posted on 2004-10-12
10
687 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
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…
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

743 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