Need to improve form filtering via combo boxes
Posted on 2012-03-28
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 & "'"
strWhereClause = " WHERE " & "[Review1] = '" & Me.cmbFilterByReview1 & "'"
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