Crosstab Query In VBA

[MikeR]

Hopefully someone can help and hopefully I am asking in the right place. I've
looked far and wide in the discussion groups and I cant seem to find the
answer im looking for. I basically want to run a crosstab query in code... I
have successfully run other querys in vba, I don't know what I am missing
with the crosstab. I have started a new db file to get to a bare bones
situation that still reflects the original file, see below.

Windows XP Pro/Access 2003

Table1:
EMP JOB HRS QRYFILTER
emp1 J1 1 1
emp1 J2 2 1
emp2 J1 3 1
emp2 J2 4 1
emp1 J1 5 2

Form1:
Has a control Text0=1 for the Query1 to reference

Query1: (Select Type)
Add all fields from Table1 (EMP,JOB,HRS,QRYFILTER)
Criteria... QRYFILTER=[Forms]![Form1]![Text0]
Parameters Dialog... [Forms]![Form1]![Text0] as Long Integer (needed for
Query2 since its a crosstab query)

Query2: (Crosstab Type)
Add fields from Query1
EMP as Row Heading
JOB as Column Heading
HRS as Value (SUM)

Resulting SQL...
TRANSFORM Sum(Query1.hrs) AS SumOfhrs
SELECT Query1.emp
FROM Query1
GROUP BY Query1.emp
PIVOT Query1.job;


Trying to reproduce Query2 in VBA...
Form1/command button click event:

Dim rs As ADODB.Recordset
Dim strSQL As String
Set rs = New ADODB.Recordset
strSQL = "TRANSFORM Sum(Query1.hrs) AS SumOfhrs SELECT Query1.emp FROM
Query1 GROUP BY Query1.emp PIVOT Query1.job"
rs.Open strSQL, CurrentProject.Connection <==Runtime Error
-2147217904(80040e10) No Value given for one or more required parameters
rs.Close
Set rs = Nothing

Sorry for the long post, but I am desperate. ANY help getting the vba
portion to run would be great. I am at a loss. Thank you in advance.

 

[Alex Dybenko]

Hi,
you have to supply parameter of Query1 to your cross-tab query, see
parameters collection in access help for more info
Also you can get rid of Query1, and pass parameter value to your crosstab
query directly

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[MikeR]

Alex,

Thank you for your post. Would you or anyone be able to post some sample
code to achieve supplying Query1's parameter to the cross-tab? I am looking
through the help now and it looks alittle over my head. Thanks again.

Hi,
you have to supply parameter of Query1 to your cross-tab query, see
parameters collection in access help for more info
Also you can get rid of Query1, and pass parameter value to your crosstab
query directly

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

Hopefully someone can help and hopefully I am asking in the right place.
I've
looked far and wide in the discussion groups and I cant seem to find the
answer im looking for. I basically want to run a crosstab query in code...
I
have successfully run other querys in vba, I don't know what I am missing
with the crosstab. I have started a new db file to get to a bare bones
situation that still reflects the original file, see below.

Windows XP Pro/Access 2003

Table1:
EMP JOB HRS QRYFILTER
emp1 J1 1 1
emp1 J2 2 1
emp2 J1 3 1
emp2 J2 4 1
emp1 J1 5 2

Form1:
Has a control Text0=1 for the Query1 to reference

Query1: (Select Type)
Add all fields from Table1 (EMP,JOB,HRS,QRYFILTER)
Criteria... QRYFILTER=[Forms]![Form1]![Text0]
Parameters Dialog... [Forms]![Form1]![Text0] as Long Integer (needed for
Query2 since its a crosstab query)

Query2: (Crosstab Type)
Add fields from Query1
EMP as Row Heading
JOB as Column Heading
HRS as Value (SUM)

Resulting SQL...
TRANSFORM Sum(Query1.hrs) AS SumOfhrs
SELECT Query1.emp
FROM Query1
GROUP BY Query1.emp
PIVOT Query1.job;


Trying to reproduce Query2 in VBA...
Form1/command button click event:

Dim rs As ADODB.Recordset
Dim strSQL As String
Set rs = New ADODB.Recordset
strSQL = "TRANSFORM Sum(Query1.hrs) AS SumOfhrs SELECT Query1.emp FROM
Query1 GROUP BY Query1.emp PIVOT Query1.job"
rs.Open strSQL, CurrentProject.Connection <==Runtime Error
-2147217904(80040e10) No Value given for one or more required parameters
rs.Close
Set rs = Nothing

Sorry for the long post, but I am desperate. ANY help getting the vba
portion to run would be great. I am at a loss. Thank you in advance.

 

[MikeR]

Alex, thank you again for your direction. Help was a little too confusing but
I did search the disscussion group for some answers and i think I got what I
needed. Here's what I came up with which seems to run fine for me, hope this
helps someone.

Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command

Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command

cmd.ActiveConnection = CurrentProject.Connection

cmd.CommandText = "TRANSFORM Sum(Query1.hrs) AS SumOfhrs SELECT Query1.emp
FROM Query1 GROUP BY Query1.emp PIVOT Query1.job"

cmd.Parameters("[Forms]![Form1]![Text0]").Value = Me.Text0.Value

rs.Open cmd
'Do Stuff
rs.Close
Set rs = Nothing

Alex,

Thank you for your post. Would you or anyone be able to post some sample
code to achieve supplying Query1's parameter to the cross-tab? I am looking
through the help now and it looks alittle over my head. Thanks again.

Hi,
you have to supply parameter of Query1 to your cross-tab query, see
parameters collection in access help for more info
Also you can get rid of Query1, and pass parameter value to your crosstab
query directly

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

Hopefully someone can help and hopefully I am asking in the right place.
I've
looked far and wide in the discussion groups and I cant seem to find the
answer im looking for. I basically want to run a crosstab query in code...
I
have successfully run other querys in vba, I don't know what I am missing
with the crosstab. I have started a new db file to get to a bare bones
situation that still reflects the original file, see below.

Windows XP Pro/Access 2003

Table1:
EMP JOB HRS QRYFILTER
emp1 J1 1 1
emp1 J2 2 1
emp2 J1 3 1
emp2 J2 4 1
emp1 J1 5 2

Form1:
Has a control Text0=1 for the Query1 to reference

Query1: (Select Type)
Add all fields from Table1 (EMP,JOB,HRS,QRYFILTER)
Criteria... QRYFILTER=[Forms]![Form1]![Text0]
Parameters Dialog... [Forms]![Form1]![Text0] as Long Integer (needed for
Query2 since its a crosstab query)

Query2: (Crosstab Type)
Add fields from Query1
EMP as Row Heading
JOB as Column Heading
HRS as Value (SUM)

Resulting SQL...
TRANSFORM Sum(Query1.hrs) AS SumOfhrs
SELECT Query1.emp
FROM Query1
GROUP BY Query1.emp
PIVOT Query1.job;


Trying to reproduce Query2 in VBA...
Form1/command button click event:

Dim rs As ADODB.Recordset
Dim strSQL As String
Set rs = New ADODB.Recordset
strSQL = "TRANSFORM Sum(Query1.hrs) AS SumOfhrs SELECT Query1.emp FROM
Query1 GROUP BY Query1.emp PIVOT Query1.job"
rs.Open strSQL, CurrentProject.Connection <==Runtime Error
-2147217904(80040e10) No Value given for one or more required parameters
rs.Close
Set rs = Nothing

Sorry for the long post, but I am desperate. ANY help getting the vba
portion to run would be great. I am at a loss. Thank you in advance.