By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,077 Members | 1,237 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,077 IT Pros & Developers. It's quick & easy.

Update subform Pivot table off Listbox selection

P: 3
I have a pivottable built off a query that is used to populate the listboxes, 1 for location 1 for category.

Those fields are included in the query to generate the sums based on the age of each record. ie, <15, 15-20 and >20 days old.

After selections made in listboxes, I am able to generate a detail page that allows users to be able to review the individual records. They would like to have the pivot table change to show how many records are selected by age

I have tried this to reset the record
Expand|Select|Wrap|Line Numbers
  1.   Dim stdocname As String
  2.     Dim strwhere As String
  3.     Dim sSql As String
  4.  
  5.     strwhere = "1=1 "
  6.     strwhere = strwhere & ListSelections(Me.WorkByCommodity, "[category]", "'")
  7.     strwhere = strwhere & ListSelections(Me.workbyLocation, "[Location]", "'")
  8.     Debug.Print strwhere
  9.     sSql = "SELECT qry_Expedites_With_Comments_1.Category, qry_Expedites_With_Comments_1.Location, Sum(IIf([expr1]<15,1,0)) AS [<15], "
  10.     sSql = sSql & " Sum(IIf([expr1]>=15 And [expr1]<=20,1,0)) AS [15-20], Sum(IIf([Expr1]>20,1,0)) AS [>20]"
  11.     sSql = sSql & " FROM qry_Expedites_With_Comments_1"
  12.     sSql = sSql & " where " & strwhere
  13.     sSql = sSql & " GROUP BY qry_Expedites_With_Comments_1.Category, qry_Expedites_With_Comments_1.Location;"
  14.  
  15.     Debug.Print sSql
  16.  
  17.     Me!sfrm_ExpeditesbyLocationchart.Form.RecordSource = sSql
  18.  
But when that runs, it changes the pivot table to show nothing. Instead of the 77 records that show up on the details page.
Here is what the sSql showed when ran.
Expand|Select|Wrap|Line Numbers
  1. SELECT qry_Expedites_With_Comments_1.Category, qry_Expedites_With_Comments_1.Location, Sum(IIf([expr1]<15,1,0)) AS [<15],  Sum(IIf([expr1]>=15 And [expr1]<=20,1,0)) AS [15-20], Sum(IIf([Expr1]>20,1,0)) AS [>20] FROM qry_Expedites_With_Comments_1 GROUP BY qry_Expedites_With_Comments_1.Category, qry_Expedites_With_Comments_1.Location HAVING 1=1  AND [Location] In ('MAP') ;
  2.  
Jun 24 '10 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.