Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    311
    Thanks
    3
    Thanked 0 Times in 0 Posts

    How to trigger an event when next/previous record is clicked on a form

    I have 2 sub queries on a tab that have their .sourceobject = ""

    My reason is that the sub queries do a big sort for purchase orders related to the job and were going off every time the next/previous button was clicked, causing substantial lag.

    In the taboptions_change () event I have the following code:

    Code:
    If Me.TabOptions.Value = 3 Then
    sbfQBOrderInfo.SourceObject = "sbfQBOrderInfo1"
    sbfQBOrderInfoUP.SourceObject = "sbfQBOrderInfo2"
    End If
    that fires if the purchase order tab is selected and this part works well.

    I was clearing the fields in the Form_BeforeUpdate event but found that I lost the query info if I tried to navigate in the sub queries.

    I tried putting it in the Form_Current event but found that it did the sort for the new record before being dismissed.

    Q> Where do I put the

    sbfQBOrderInfo.SourceObject = ""
    sbfQBOrderInfoUP.SourceObject = ""

    statements so that it only triggers when the next/previous button is clicked?
    "Heading for the deep end"

  2. #2
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Manchester, United Kingdom
    Posts
    115
    Thanks
    8
    Thanked 16 Times in 15 Posts
    Have you defined indexes on the fields the shfQBOrderInfo queries use for linking/sorting? Doing so might speed them up enough that you don't have to do these manipulations.

    Once upon a time Access had a RecordExit event that fired as you left a record whether changed or not and before the Current event for the next record, but I can't find it in recent versions.

    You might look at these:

    http://office.microsoft.com/en-gb/ac...005186761.aspx
    http://msdn.microsoft.com/en-us/libr.../jj249049.aspx
    http://support.microsoft.com/kb/304139

    Ian.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,519
    Thanks
    3
    Thanked 50 Times in 50 Posts
    I'm a bit confused about the terminology being used. A subquery is usually defined as a SQL SELECT statement embedded in another SELECT statement. I think you are actually referring to subforms and subform controls on Access forms. If you are using a query to populate a subform the initial performance when the form is opened can be quite long, and it can be very useful to delay linking the subform control to the appropriate subform until the tab is actually selected. We use that trick in ann application that contains a dozen or so subforms, some of which are displaying selected records from a recordset with several million rows, and achieve subsecond response time in changing the record in the main form. And as Ian indicates, proper indexing is the key to making that happen. Are you using an Access back-end, or is it a SQL Server or Oracle back-end?
    Wendell

  4. #4
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    311
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Wendell,

    Yes, sorry about using the wrong terminology, I have subforms shown as datasheets that only link the .sourceobject specified when the tab is selected. What I need is the best point to unlink the .sourceobject so that it is unlinked before moving to the next record but doesn't unlink when I click in the subform. I will look at Ian's suggestions.

    I am running an Access backend.

    Thanks Ian for the addresses, the recordexit event seems to be what I want. I will look into it at work on monday.

    Indexes.???........Ummm.........looks like they will be my next big step towards the deep end. My source query pulls from at least 2 tables so any help, explanations, suggestions will be most welcome.

    Regards, Allan
    "Heading for the deep end"

  5. #5
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Manchester, United Kingdom
    Posts
    115
    Thanks
    8
    Thanked 16 Times in 15 Posts
    Indexes.

    Don't go wild, as maintaining indexes on big tables can be slow, but well chosen indexes can make all the difference.

    Obviously the primary key field(s) in your tables are automatically indexed by Access.

    The foreign key fields (link or join fields) will be indexed by Access if you created relationships for them. If you didn't create relationships then you should define a non-unique (duplicates OK) index on the fields you are linking (I'm assuming these mostly or entirely link to the primary key of another table, in which case you obviously don't need to re-create that index). E.g if orders.customer links to customers.id put an index on orders.customer if Access hasn't done it for you.

    Finally, if you are regularly selecting records from a big table using a value in a field that is not otherwise indexed, or sorting on a field, you should consider adding a non-unique index on that field, but only if it make a worthwhile difference to a commonly used query.

    Indexes hurt performance every time you add records to the table, but benefit you whenever you look up records using that index - it's a straight trade-off.

    Ian

  6. #6
    2 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    162
    Thanks
    0
    Thanked 24 Times in 24 Posts
    Quote Originally Posted by weyrman View Post
    I have 2 sub queries on a tab that have their .sourceobject = ""
    I think you mean sub form since a query does not have the .sourceobject property.

    If you are using a sub form then ...

    What you are doing is "late binding" controls on a form for performance. I have been using this technique for many years.


    Quote Originally Posted by weyrman View Post
    Q> Where do I put the

    sbfQBOrderInfo.SourceObject = ""
    sbfQBOrderInfoUP.SourceObject = ""

    statements so that it only triggers when the next/previous button is clicked?
    Use the On Current event to run your code above. You will also need to be sure to move to a different tab.

    I would recommend looking into this: VelociTab (Click Here)
    VelociTab is a class module for Microsoft® Access® that will automatically late bind any controls on non-visible tab control pages. Late binding of controls on tab control pages results in faster loading forms. This utility allows you to automatically late bind controls by simply adding 3 lines of code to your form OnOpen event procedure.
    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •