Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 707
  • Last Modified:

Slow Form

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
Moother
Asked:
Moother
  • 3
  • 3
  • 2
  • +2
4 Solutions
 
GreymanMSCCommented:
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
 
jadedataMS Access Systems CreatorCommented:
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
 
GreymanMSCCommented:
(opps... FormEDIT is faster because it needs to load less data into the recordset, as jadedata pointed out why.)
 
0
Technology Partners: 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!

 
jadedataMS Access Systems CreatorCommented:
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
 
Alan WarrenCommented:
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
 
MootherAuthor Commented:
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
 
flavoCommented:
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
 
MootherAuthor Commented:
Hijacking?
0
 
GreymanMSCCommented:
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
 
MootherAuthor Commented:
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

Independent Software Vendors: 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!

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now