Results 1 to 3 of 3
  1. #1
    Euler271 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2017
    Posts
    63

    Pivot table form question


    I'm not sure this is the right forum but here's what I want to do:

    I have a pivot table form in which the user selects an ID to filter the pivot table. I want to be able to capture that selected ID and use it in the recordsource SQL statement for a second pivot table form.

    Can someone tell me if this is feasible?

    Thanks for your help.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    i have that very same form...user picks item to pivot:
    The user picks a Set from the combo box cboSet, then it builds the query
    qsXtab0 is a base query with fields in it.

    Code:
    Private Sub MakeXtabSql(ByVal pvQry)
    Dim sSql As String
    Dim qdf As QueryDef
    
    
    Set qdf = CurrentDb.QueryDefs(pvQry)
    
    
    sSql = "TRANSFORM Sum(qsXtab0." & cboSet & ") AS SumOfFld " & _
    "SELECT qsXtab0.ProdLine, '" & cboSet & "' AS DataSet, Sum(qsXtab0." & cboSet & ") AS [TotalOf " & cboSet & "] " & _
    "FROM qsXtab0 " & _
    "GROUP BY qsXtab0.ProdLine, '" & cboSet & "'" & _
    "PIVOT Format([ProdDate],'yyyy-mm');"
    
    
    qdf.SQL = sSql
    qdf.Close
    
    docmd.openquery pvQry
    
    
    Set qdf = Nothing
    End Sub

  3. #3
    Euler271 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2017
    Posts
    63
    So it sounds like what you're doing is having the user select something from a Access combo box on a form then creating the pivot table based on the selection. From that point you can do whatever you want with the user's selection. What I was trying to do was capture the selected item from the filter list after the first pivot table was created. I couldn't get that to work.

    I think your way is the most feasible. Thank you very much for your reply. I will try it immediately.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Format numbers in Pivot table form
    By JayDee in forum Forms
    Replies: 3
    Last Post: 06-24-2015, 08:18 AM
  2. Pivot Table and Chart Question
    By rjgriffin46 in forum Access
    Replies: 2
    Last Post: 04-05-2015, 06:04 AM
  3. Replies: 0
    Last Post: 09-22-2014, 07:07 AM
  4. Advanced Pivot Table Question
    By jsmith3313 in forum Access
    Replies: 6
    Last Post: 04-17-2012, 08:56 AM
  5. Pivot Table from Query to Form
    By polpolpol in forum Forms
    Replies: 0
    Last Post: 03-20-2012, 12:11 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums