Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Need to improve form filtering via combo boxes

Posted on 2012-03-28
7
Medium Priority
?
290 Views
Last Modified: 2012-04-02
Hello ,

I'm working on a form (Default View is Continuous Forms" that is populated at form load time via the following
trRecordSource = "SELECT dbo_vwPivotedReviews.USI, dbo_vwPivotedReviews.WorkStream, dbo_vwPivotedReviews.GFP, " & _
                       "dbo_vwPivotedReviews.Review1 , dbo_vwPivotedReviews.Status1, " & _
                       "dbo_vwPivotedReviews.Review2 , dbo_vwPivotedReviews.Status2, " & _
                       "dbo_vwPivotedReviews.Review3 , dbo_vwPivotedReviews.Status3, " & _
                       "dbo_vwPivotedReviews.Review4 , dbo_vwPivotedReviews.Status4, " & _
                       "dbo_vwPivotedReviews.Review5 , dbo_vwPivotedReviews.Status5, " & _
                       "dbo_vwPivotedReviews.Review6 , dbo_vwPivotedReviews.Status6, " & _
                       "dbo_vwPivotedReviews.Review7 , dbo_vwPivotedReviews.Status7, " & _
                       "dbo_vwPivotedReviews.Review8 , dbo_vwPivotedReviews.Status8 " & _
                       "FROM dbo_vwPivotedReviews "
                       
    strClearFilterSource = strRecordSource
                       
    Me.RecordSource = strRecordSource

dbo_vwPivotedReviews is a linked view to a sql server database.  The form seems to be populated quickly enough.  The problem lies in filtering the form
I have about 10 comboboxes on the form which allows the user to filter the forms recordsource via selections made from the comboboxes in the following manner

  If strWhereClause <> "" Then
        strWhereClause = strWhereClause & " AND " & "[Review1] = '" & cmbFilterByReview1 & "'"
    Else
        strWhereClause = " WHERE " & "[Review1] = '" & Me.cmbFilterByReview1 & "'"
    End If
   
    Me.RecordSource = strRecordSource & strWhereClause

This is the part that is running a bit slow.  I'm thinking that perhaps the filtering would have less of an impact on performance, if I do it at the server via T-SQL.  Would that be the way to go.  By the way the application is an Access 2007/Sql Server 2005 application
0
Comment
Question by:chtullu135
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1200 total points
ID: 37777525
Let me suggest you read the EE article below, by EE's Harfang ... and his other articles also.  They will contain everything you need to know re how to improve performance in this regard:

Start HERE

I can assure that this is as good as it's going to get.  I have tested and implemented these concepts.

mx
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37777714
<This is the part that is running a bit slow. >
Please quantify "a bit slow",  in real chronological terms.
"A bit" is not that much in my opinion...

It might be a bit much to investigate a 100ms time difference, that might just be "what it is"

I know that running a query on the server can be faster, I am not sure about a form's recordsource, though...?
0
 

Author Comment

by:chtullu135
ID: 37777909
It takes about 4 seconds to filter the form after a selection is made via a combo box
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 800 total points
ID: 37778584
The issue is that while you seem to be just "Filtering", you are also "reloading" the entire recordsource.
This is like doing a requery in terms of the resources Acess needs to do this.
So you are pulling the entire new recordsource over from the server each time.

The "Multiple comboboxes" used for this will also slow things down.

The network itself may also be a factor,...only testing will tell...

Try this...
Put a button on the form that simply does this:
    me.requery

...(see how long it takes)

Then try this:
me.recordsource=me.recordsource
me.requery

Then try this:
me.recordsource=SELECT * FROM dbo_vwPivotedReviews
me.requery

JeffCoachman
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 37780290
You can also try filtering the form instead of reloading the recordsource.
Me.Filter =  "[Review1] = '" & cmbFilterByReview1 & "' [Review2] = ...
Me.FilterOn = True

Another way is to index your fields but in your case, indexing 10 fields may impose some overhead whenever you try to update, insert to, or delete from your table.
0
 

Author Closing Comment

by:chtullu135
ID: 37796575
Thanks everyone.  I particulary liked the link to the article posted by DatabaseMX.  boag2000 test suggestions were also very helpful.  I've decided to create a stored procedure to do the actual filtering. In addition, the tables in question will get additional indexes to speed filtering
0
 
LVL 75
ID: 37797063
I highly recommend reading ALL the articles by Marcus, as I consider him and Leigh Purvis (LPurvis) to be the top two Access minds on the planet.

mx
0

Featured Post

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!

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Implementing simple internal controls in the Microsoft Access application.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

571 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