Report with CrossTab query with parameter as Recordsource (1 Viewer)

ontopofmalvern

Registered User.
Local time
Today, 06:53
Joined
Mar 24, 2017
Messages
53
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

I’m here to help
Staff member
Local time
Yesterday, 22:53
Joined
Oct 29, 2018
Messages
13,202
Hi. What is the value of strCrossTabQuery? If it's something like "TRANSFORM..." then maybe try the Recordset property instead of RecordSource.
 

ontopofmalvern

Registered User.
Local time
Today, 06:53
Joined
Mar 24, 2017
Messages
53
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

1588266815727.png
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:53
Joined
Oct 29, 2018
Messages
13,202
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

Registered User.
Local time
Today, 06:53
Joined
Mar 24, 2017
Messages
53
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

I’m here to help
Staff member
Local time
Yesterday, 22:53
Joined
Oct 29, 2018
Messages
13,202
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

Registered User.
Local time
Today, 06:53
Joined
Mar 24, 2017
Messages
53
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’m here to help
Staff member
Local time
Yesterday, 22:53
Joined
Oct 29, 2018
Messages
13,202
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.
 

Users who are viewing this thread

Top Bottom