How To Populate DBGrid from Crosstab Parameter Query

MORE INFORMATION

1. Create a new Project in Visual Basic.
2. From the menu, select Project - References and make a reference to Microsoft DAO 3.5 Object Library. In Visual Basic 4.0, under Tools references make, a reference to DAO 3.0 object library.
3. From the menu, select Project - Components and select a Data Bound Grid control. In Visual Basic 4.0, this is under Tools - Custom Controls.
4. Add a CommandButton, a Data Control and a DBGrid Control to the new Form.
5. Accept the default Names for the Command Button, DBGrid, and Data Control.
6. Enter the sample code below into the General Declarations of the Form.
7. In the properties Window, set the DataSource property of the DBGrid to Data1 to bind the DBGrid to the Data control.
8. Run the application and click the CommandButton.
   
   Sample code:

         Private Sub Command1_Click()
           Dim Sql As String
           Dim DatabaseFile As String
           Dim MyDB As Database
           Dim RS As Recordset
           Dim QD As QueryDef
   
         On Error GoTo errorHand
   
         'This is the Path & Database filename.
         'Change to match your path and filename.
         DatabaseFile = "C:\VB5\Nwind.mdb"
   
         'Create the querydef SQL string
         Sql = "Parameters [Enter first date] DateTime, " _
            & "[Enter second date] DateTime;"
         Sql = Sql & " TRANSFORM Sum(CCur([Order Details].[UnitPrice]" _
            & " *[Quantity]*(1-[Discount])/100)*100) AS ProductAmount"
         Sql = Sql & " SELECT Products.ProductName, Orders.CustomerID," _
            & " Year([OrderDate]) AS OrderYear"
         Sql = Sql & " FROM Products INNER JOIN (Orders INNER JOIN" _
            & " [Order Details] ON Orders.OrderID ="
         Sql = Sql & " [Order Details].OrderID) ON" _
            & " Products.ProductID = [Order Details].ProductID"
         Sql = Sql & " WHERE (((Orders.OrderDate) Between" _
            & " [Enter first date] And [Enter second date]))"
         Sql = Sql & " GROUP BY Products.ProductName, Orders.CustomerID," _
            & " Year([OrderDate])"
         Sql = Sql & " PIVOT 'Qtr ' & DatePart('q',[OrderDate],1,0) In" _
            & " ('Qtr 1','Qtr 2','Qtr 3','Qtr 4');"
   
         'This code creates the parameter query
   
         Set MyDB = DBEngine(0).OpenDatabase(DatabaseFile)
         Set QD = MyDB.CreateQueryDef("CrossTabParamQuery")
         With QD
             .Sql = Sql
             .Parameters("[Enter first date]") = #1/1/95#
             .Parameters("[Enter second date]") = #1/1/96#
         End With
         Set Rs = QD.OpenRecordset(dbOpenDynaset)
         Set Data1.Recordset = Rs
         Exit Sub
   
         errorHand:
         If Err.Number = 3012 Then   ' The querydef exists
             MyDB.QueryDefs.Delete "CrossTabParamQuery"
             Resume
         Else
             MsgBox Err.Number & "  " & Err.Description
         End If
         End Sub
   
         Private Sub Form_Unload(Cancel As Integer)
            RS.Close
            QD.Close
            MyDB.Close
         End Sub