Crosstab query criteria for command button

[jones]

Good day to all,



I'm new in ACCESS DATABASE user and i find difficulties in linking the crosstab tab query into drop down list and command button.

As you can see in my attachment, that i have a form then in my drop down list (project code)i want to filter my crosstab query by what is in current dropdown list value and i click the command button MsP then the datasheet will come up that is what i want.

But the problem is in design view for crosstab query criteria, i can input the [form]![frm RSC Project]![Combo0] but when i try to run it, it say access database does not recognize as a valid name or expression. If anayone can advice me what to do i really really appreciate it, thanks in advance.

Plaese see the attachment file.

[aytee111]

This is a known Access bug - it cannot be done.

Replace the macro with the following code:
Private Sub Command14_Click()

Dim sqlstr As String, qdf As QueryDef
On Error Resume Next
DoCmd.DeleteObject acQuery, "testy"
On Error GoTo errx

sqlstr = "TRANSFORM Sum([tbl MsP vs PR:PO].[Qty Req'd]) AS [SumOfQty Req'd] " & _
"SELECT [tbl MsP vs PR:PO].[Project Code], [tbl MsP vs PR:PO].[Resource ID], [tbl MsP vs PR:PO].Description, Sum([tbl MsP vs PR:PO].[Qty Req'd]) AS Total " & _
"FROM [tbl MsP vs PR:PO] WHERE ((([tbl MsP vs PR:PO].[Project Code]) = '" & Me!Combo0 & "')) " & _
"GROUP BY [tbl MsP vs PR:PO].[Project Code], [tbl MsP vs PR:PO].[Resource ID], [tbl MsP vs PR:PO].Description PIVOT Format([Date Needed],'Medium Date');"

Set qdf = CurrentDb.CreateQueryDef("testy", sqlstr)
DoCmd.OpenQuery "testy"
Exit Sub

errx:
MsgBox Err.Number & " " & Err.Description

End Sub

[jones]

Thank you very much Aytee111, i'll try to execute this one. you are a big help for me.