Report with CrossTab query with parameter as Recordsource

[ontopofmalvern]

Hi
I have a report which I want to use a CrossTab query with parameters as the recordsource.

I have found that the only way I can get the parameters into crosstab query is in code (no problem there), but I cannot get the SQL accepted as recordsource. e.g.

me.RecordSource = strCrossTabQuery doesn't work on reports (or at least I can't get it to, it works for me on forms)

I have found that as long as i don't use paramenters I can use the crosstab query as the source for a simple "SELECT * FROM qryCrossTab" query that I can use that as my recordsource on report.

I have tried to set Report recordsource to a recordset which I believe is something Access won't allow (again it does I isn't working for me)

Any pointers where I can take this.

Tah

 

[theDBguy]

Hi. What is the value of strCrossTabQuery? If it's something like "TRANSFORM..." then maybe try the Recordset property instead of RecordSource.

 

[ontopofmalvern]

Hi, Thanks for your thoughts
That was ine of the many things I tried, my code in the report 'on load' eventwas

strSQL = " TRANSFORM First(tblLessons.subject &
.......... de da de da (the SQL works)

Set rs = db.OpenRecordset(strSQL)

Me.Recordset = rs

Then I get

 

[theDBguy]

Hi, Thanks for your thoughts
That was ine of the many things I tried, my code in the report 'on load' eventwas

strSQL = " TRANSFORM First(tblLessons.subject &
.......... de da de da (the SQL works)

Set rs = db.OpenRecordset(strSQL)

Me.Recordset = rs

Then I get

View attachment 81600

Just a guess but did you try the Open event too?

 

[ontopofmalvern]

Same response - read something that using recordsets as recordsource is a bit of a no go in on reports in access (forms seem a bit more friendly)

 

[theDBguy]

Same response - read something that using recordsets as recordsource is a bit of a no go in on reports in access (forms seem a bit more friendly)

Same here. I remember somewhat the same thing and can't remember if or what the fix was. If nothing else, how about using a QueryDef object and just manipulate the query itself? Will that work for you? What exactly is the problem?

 

[ontopofmalvern]

I have tried query def, but I can't get my head round them, if you have a link to a guide to query defs for the old that would be good.

On this particualr issue I have a soultion by a different means. Basicaly I have taken the WHERE clause out of the SQL (and hence the parameters) and then use a filter on load. Thanks for your input and helped me consider other options.

 

[theDBguy]

I have tried query def, but I can't get my head round them, if you have a link to a guide to query defs for the old that would be good.

On this particualr issue I have a soultion by a different means. Basicaly I have taken the WHERE clause out of the SQL (and hence the parameters) and then use a filter on load. Thanks for your input and helped me consider other options.

This is the only link I could suggest for now.

QueryDef object (DAO)

Office developer client VBA reference documentation

docs.microsoft.com