Ask Experts Questions for FREE Help !
Ask
    nurmala's Avatar
    nurmala Posts: 23, Reputation: 1
    New Member
     
    #1

    Nov 14, 2011, 01:33 AM
    Pivot table directly in FORM for Ms. Access 2003
    Hey pals, need your hand on this one.

    I have a FORM Code:

    Private Sub RUN_Click()
    On Error Go to Err_CHSWB_Final1_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    DoCmd.OpenQuery "CHSWB_Final1", acViewNormal, acReadOnly

    Exit_CHSWB_Final1_Click:
    Exit Sub

    Err_CHSWB_Final1_Click:
    MsgBox Err.Description
    Resume Exit_CHSWB_Final1_Click
    End Sub

    Can I pivot the resulted table directly in that FORM Code (by adding more lines), or should I pivot the table manually? And if I can do that in that FORM Code, could you guys give me the pivot syntax?

    Do I get myself clear on this? Need your response...

    Thanks indeed :)
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #2

    Nov 14, 2011, 04:48 AM
    I'm not sure what you want here. In Access a Pivot table is a view of the form. Once you define the pivot (which has to be done manually), you can switch to the view in code, by setting the DefaultView property.

    If you want to display the results of a query as a pivot form, then you would not use DoCmd.OpenQuery, but OpenForm and set the view to Pivot (again predefine the pivot). Just bind the form to the query.
    nurmala's Avatar
    nurmala Posts: 23, Reputation: 1
    New Member
     
    #3

    Nov 14, 2011, 06:26 PM
    Yes, indeed I want to display the result of a query as a pivot form. Where can I find the DefaultView property? And how I bind the form to the query? Could you give any syntax for it?

    Thanks
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #4

    Nov 14, 2011, 07:57 PM
    You do it manually. Use the Pivot Form wizard to create the form. Then just open the form. It will default to Pivot Table view.
    nurmala's Avatar
    nurmala Posts: 23, Reputation: 1
    New Member
     
    #5

    Nov 14, 2011, 08:31 PM
    I already created Pivot Form and when I open it, it default to Pivot Table view. But when I bind it with query and using OpenForm, it is not like the one that showed in Pivot Form, but it showed only one row...

    What happened?

    I still can not find why the pivot turned into just one row (after binding it to query), instead of a view as it looks in pivot view.

    Another question about pivot table : can we re calculate from pivot table, or pivot is just a view? Recalculate I mean, I want to use "SUMPRODUCT" function...
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #6

    Nov 15, 2011, 04:01 AM
    When you originally created the Pivot Form, what did you use as the Recordsource? Are you checking the query to see if that only returns one row?

    A pivot form does some internal calculations. It will display the current real time values in its Recordsource. But you can't do calculations in the controls of a Pivot Form Any calculations need to be done in the source.

    SumProduct is not an Access function, I believe its an Excel function. Some Excel functions can be used in Access though.

    Can you attach a screenshot of what the Pivot form is supposed to show. The advantage of a Pivot, is that the user can manipulate the way the data is displayed by moving the grouping values in both Columns and Rows without having to create a new form.
    nurmala's Avatar
    nurmala Posts: 23, Reputation: 1
    New Member
     
    #7

    Nov 16, 2011, 12:46 AM
    I created AutoForm : Pivot Table and used query as RecordSource. And saved the From. When I run the Form, the pivot turned normal. But, when I bind the Pivot Form to other Form, and changed DoCmd.OpenQuery to OpenForm, and viewPivot, it turned in to one raw...

    I attached the pivot table as it turned into one raw...
    Attached Images
     
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #8

    Nov 16, 2011, 04:10 AM
    That does not look like an applicable use of a pivot Form. Not all data lends itself to use as a Pivot form. Why do you want to use a Pivot Form here?

    Also you can't bind a form to another form.

    Did you run the query to see if the query only returned one row?

    You might want to zip up a copy of your database and attach it here so I can take a look. Because its not making a lot of sense.
    nurmala's Avatar
    nurmala Posts: 23, Reputation: 1
    New Member
     
    #9

    Nov 16, 2011, 11:49 PM
    Hey ScottGem, I got a better Idea. I created Crosstab Query instead of using pivot, and I got what I wanted.

    Still can not figure our problem yet.

    But thanks anyway Pal.. :)
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #10

    Nov 17, 2011, 04:13 AM
    A Crosstab may have been better. A Pivot is interactive. Unless you needed that interactivity a Crosstab is more suitable.
    nurmala's Avatar
    nurmala Posts: 23, Reputation: 1
    New Member
     
    #11

    Nov 18, 2011, 08:00 PM
    Hey guys, I want to simplify my form. I have a form just like this : (attached)

    What I want is, when I click the "RUN" button, it shows only parameter from those two combo boxes.

    What I have now for code in "RUN" button is :

    Private Sub RUN_Click()

    Dim stDocName As String
    Dim stLinkCriteria As String

    DoCmd.OpenQuery "Que_Drop_Down", acViewNormal, acReadOnly

    Exit_Que_Drop_Down_Click:
    Exit Sub

    Err_Que_Drop_Down_Click:
    MsgBox Err.Description
    Resume Exit_Que_Drop_Down_Click

    End Sub

    Could any one give me a hand on this?

    Thank you very much

    Attached Images
     
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #12

    Nov 19, 2011, 09:08 AM
    This is very simple. You can reference a value in any control on an open form using the syntax:

    Forms!formname!controlname

    where formname is the name of YOUR form and controlname the name of the control.

    So you can enter the criteria row of the query in the appropriate column:

    =Forms!formname!controlname
    nurmala's Avatar
    nurmala Posts: 23, Reputation: 1
    New Member
     
    #13

    Nov 19, 2011, 06:58 PM
    Bingoo!! Thanks a lot Scott.. :)
    nurmala's Avatar
    nurmala Posts: 23, Reputation: 1
    New Member
     
    #14

    Nov 23, 2011, 06:36 PM
    Hi Scott,

    Another question. Can we use "=Forms!formname!controlname" in textbox for Report in ms. Access? And can we use the same formula for query, so it is like "=Queries!Queryname!field" and use it in textbox for Report?

    Thanks a lot
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #15

    Nov 23, 2011, 07:09 PM
    Yes you can use =Forms!formname!controlname anywhere. No you can't reference a value from a query that way. Use a DLookup instead.
    nurmala's Avatar
    nurmala Posts: 23, Reputation: 1
    New Member
     
    #16

    Nov 23, 2011, 07:15 PM
    The DLookup, could you provide the formula please?

    Thanks
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #17

    Nov 23, 2011, 08:21 PM
    DLookup("[fieldname]","table or query name", criteria)

    You can find examples in Access Help.
    nurmala's Avatar
    nurmala Posts: 23, Reputation: 1
    New Member
     
    #18

    Nov 23, 2011, 08:38 PM
    Will try.

    Thanks

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Question about using a date variable in a Pivot table [ 4 Answers ]

I'm trying to tweak a piece of my code to allow me to use a date as a variable in selecting information in my pivot table. Here's my code: Sub RS_IBOB_Open_Select() ' ' RS_IBOB_Open_Select Macro ' ' SelectDate = Format(Date - 1, "YYYYMMDD") WShtDate = Format(Date - 1, "mm.dd.yy")

MS Access 2003 Creating a form with input of ranges of data [ 4 Answers ]

Dear Scott, I tried attaching the front and back end of my database and I got the following message, "Your submission could not be processed because a security token was missing. If this occurred unexpectedly, please inform the administrator and describe the action you performed before you...

Using fuction "Mode" within a Pivot Table? Or calculating the mode with conditional? [ 4 Answers ]

I want to know which number appears most frequently within a table by instructor code. For example, I want to produce a report that searches column A for an instructor code (for example, any instructor code "ba*") and then finds the mode of the corresponding cell in column B. I know I could...

Want Button on Access Form for Inserting OLE Object into Table [ 1 Answers ]

Hi. I'm looking for a simple simple simple solution that will allow me to add a button to an Access form. When the button is clicked, a File Dialog box should appear allowing me to pick any file to insert into the OLE package. I've searched every corner of the web and have found a lot of...

Excel - frequency / mode / pivot table. [ 0 Answers ]

Hi there, I'm struggling to achieve my end goal in Excel; I have a list of dates (presently the list of dates is divided into individual worksheets with 20 rows and 5 columns of dates per worksheet. I have 30-40 worksheets). My objective is to obtain a list of the dates that match...


View more questions Search