Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Slow Form

Posted on 2004-10-12
10
Medium Priority
?
705 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 16

Assisted Solution

by:GreymanMSC
GreymanMSC earned 400 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 200 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
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
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 1400 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 400 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

722 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