query parameter in vba

[FGM]

I make crosstab queries to build a table. The crosstab query table:

Proj source Rec1 Rec2 Rec3
7 1 25 26 34
7 2 22 24 15
7 3 17 18 22
8 1 16 22 18
8 2 14 23 19


This works okay when each project has the same Rec names. However, if they
do not then in the above example I could have Rec1 R1 Rec2 R2 Rec3
R3.

The data gets written with the Excel.Application to excel with each project
on a separate worksheet and I do not want R1, R2, R3 showing up for Proj 7 if
it is not part of Proj 7.

I use VBA to run by output and need to be able to loop through each Proj
running the crosstab for just that project and sending it to excel.

My problem is that I do not know how to send a parameter to the crosstab
query from vba to run it through a loop so that one project is fed to the
crosstab at a time.

I have read the entries for qdf.parameters on the internet and in the books
I have but cannot seem to understand them or if they can be used with
crosstab queries..

Any help would be most appreciated. I am using windows 2000 / Access 2002

Thank you.

 

[Roger Carlson]

Have you actually defined a parameter for the crosstab query? When you
right-click in the top part of the Query Window, there is a Parameters
property. Have you defined your parameter there as well as in your Where
clause? Crosstabs are funny in that they need the parameter property set.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[FGM]

Hi Roger,
Just looked at that. I have never used it. If I name a parameter there...
Then how does it know what field to go with? Do you give the parameter a
value and a field it goes with in vba?
thanks

 

[Roger Carlson]

No, all you define there is the parameter name and a datatype. The
parameter name has to match the parameter you put in your Where clause.

I thought I had a sample somewhere that illustrates this, but I can't find
it. However, take a look at this MS Knowledgebase article:
http://support.microsoft.com/kb/209778

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[FGM]

Thank you Roger,
I will have to read more about this. I think I may still have a problem as
I have several queries and the one in vba actually builds a table from the
crosstab query. Therefore, I wonder if I execute the make table query that
has the crosstab query as its base and the crosstab query is the one that
needs the parameter how do I feed the parameter into the crosstab that is
secondary?
I don't know if this is very clear ...

thank you.... you are appreciated...