Is it possible to create a report that will have different record source depeneds on a combo field?
If user picks "english" for example and clicks the button, it will open the report that is based on eng_query, if he picks biology, same report will open based on bio_query...

How can I do that?

Is there an ID field that would determine what record should be displayed?

Is there an ID field that would determine what record should be displayed?


Hello again! :)

Let me explain a bit more, you already saw my database a bit so it should be easy I guess: I have those subjects, I have crosstab query that shows residuals for each subject. Now I need to generate report that shows those residuals in specific way: report shows it only for one subject, columns are: year, level, girls, biys, all. Simple, right?
Now thing is my all subject residual crosstab query has year, level, gender, art_resisuals, biology_residuals, etc...
So I need to do pivot again (crosstab query form that crosstab query:) to pivot that gender to girls and boys, but now because you can choose only one valuse field in crosstab, I pick one subject, so as value I got girls and boys art residuals. This way I got 25 crosstab queries for each subject.
Now,I could create 25 reports for each, but I want to make it better:)
I would like to have one report that will pick a query for it.

Hello there :)

The problem with your pivot is that the subjects are not dynamic so if you want it to be then you can do so by changing the sql of the querydef by code. Have a look at this thread (http://www.access-programmers.co.uk/forums/showthread.php?t=195515), my post #2. So what you would need to amend is the PIVOT part of the sql string. Create a copy of your original query and use the copy for this.

Report wise you need just one report.

1. Set the Record Source of the report to the cloned query
2. Open the report in design view and change the Control Sources of the relevant textboxes in code
3. Then open the report as you would normally

I stated the problem with making design changes in that thread so for closing the report you would need a custom close button if you don't want the message to pop-up.

Another way would be to ensure that the dynamic field name has the same name for the textbox's control source. So what you do is create another query based on the cloned query (mentioned above) and use an alias. That alias field will now be the textbox's Control Source and there will be no need to make design changes.

I think I know what you mean, but can you please take a look on this:
I came up with this, but of course it doesn't work:)
I made a button on the form next to combo box with list of subjects. Now, under on click I wrote this:

Private Sub Open_Report_Button_Click()
On Error GoTo Err_Open_Report_Button_Click

Dim stDocName As String
Dim ReportSource As String

Select Case Form_select_options_Resisduals.Department = "Art"
ReportSource = Query_Residuals_Departments_F&M_Crosstab_art

Report_residuals.RecordSource = ReportSource

stDocName = "Report_residuals"
DoCmd.OpenReport stDocName, acPreview

Exit_Open_Report_Button_Click:
Exit Sub

Err_Open_Report_Button_Click:
MsgBox Err.Description
Resume Exit_Open_Report_Button_Click

End Sub
I'm totally new to VB so this is all wrong, but let me explain what I'm trying to achieve.
I'm trying to make this button open "Report_residuals" report but set its record source as a value form case function, something like, in case combobox "Department" on "Form_select_options_Resisduals" form is "Art", open report with record source as query called "Query_Residuals_Departments_F&M_Crosstab_art".

How can I make it work? Please help! :(

Yes, I understood what you were trying to do which was prompted my response.:) First of all create a blank query, this is what you will use. Re-read what is in this thread:

http://www.access-programmers.co.uk/forums/showthread.php?t=195515

... post #2 and create the first two steps as explained then show us what you've done. To get the SQL string of your query, just go into SQL VIEW of the crosstab query.

OK, so you say it better to send sql query every time, instead of pulling it via its name, got it.

Where should I add this "QueryDef" code? To report or form?

You add it to the click event of the button (on the form).

You add it to the click event of the button (on the form).

OK.
So, here is what I got so far, button code:

Private Sub Open_Report_Button_Click()
On Error GoTo Err_Open_Report_Button_Click

Dim stDocName As String

Dim qdf As QueryDef

Const sqlString = "SELECT * FROM [TableName] "

Set qdf = CurrentDb.QueryDefs("Query_Residuals_Departments_F&M_Crosstab_option")

qdf.SQL = sqlString & "WHERE " & IIf(Forms!Form_select_options_Residuals.Department = "Art", )


stDocName = "Report_residuals"
DoCmd.OpenReport stDocName, acPreview

Exit_Open_Report_Button_Click:
Exit Sub

Err_Open_Report_Button_Click:
MsgBox Err.Description
Resume Exit_Open_Report_Button_Click

End Sub


and here is my crosstab query:

TRANSFORM First([Query_Residuals_Departments_F&M].[AvgOfart-res]) AS [FirstOfAvgOfart-res]
SELECT [Query_Residuals_Departments_F&M].Year, [Query_Residuals_Departments_F&M].Level, [Query_Residuals_Departments_F&M].school_year
FROM [Query_Residuals_Departments_F&M]
GROUP BY [Query_Residuals_Departments_F&M].Year, [Query_Residuals_Departments_F&M].Level, [Query_Residuals_Departments_F&M].school_year
ORDER BY [Query_Residuals_Departments_F&M].Year DESC , [Query_Residuals_Departments_F&M].Level
PIVOT [Query_Residuals_Departments_F&M].Gender;



Now, based on my query, should this [TableName] actually be my "Query_Residuals_Departments_F&M"? Cause this is the source of crosstab query.
I haven't finished IIF sattement there - what should I do next? First IIF expression is if the combobox=Art, now true part should be this query with "AvgOfart-res" field, if not then second IIF would be biology and "AvgOfbio-res" field, ..etc.

What should I do next?

What was given in that link was actually generic and should have been amended to your code. In any case, show me a screen shot of the output of the cross tab query.

Can I do sth like this?
Create two constant sql strings, first part transform, second rest of the sql statement and middle part based on iif:

Const sqlString = "TRANSFORM "
Const sqlString2 = "SELECT [Query_Residuals_Departments_F&M].Year, [Query_Residuals_Departments_F&M].Level, [Query_Residuals_Departments_F&M].school_year"
FROM [Query_Residuals_Departments_F&M]
GROUP BY [Query_Residuals_Departments_F&M].Year, [Query_Residuals_Departments_F&M].Level, [Query_Residuals_Departments_F&M].school_year
ORDER BY [Query_Residuals_Departments_F&M].Year DESC , [Query_Residuals_Departments_F&M].Level
PIVOT [Query_Residuals_Departments_F&M].Gender;"

Set qdf = CurrentDb.QueryDefs("Query_Residuals_Departments_F&M")

qdf.SQL = sqlString & IIf(Forms!Form_select_options_Residuals.Department = "Art",
First([Query_Residuals_Departments_F&M].[AvgOfart-res]) AS [FirstOfAvgOfart-res] & sqlString2, IIF...
)

You don't need the IIF() part. That was related to that post. Let me a screen shot of the datasheet of the query.

You don't need the IIF() part. That was related to that post. Let me a screen shot of the datasheet of the query.

No problem, this one is art query:
Code:
TRANSFORM First([Query_Residuals_Departments_F&M].[AvgOfart-res]) AS [FirstOfAvgOfart-res]
SELECT [Query_Residuals_Departments_F&M].Year, [Query_Residuals_Departments_F&M].Level, [Query_Residuals_Departments_F&M].school_year
FROM [Query_Residuals_Departments_F&M]
GROUP BY [Query_Residuals_Departments_F&M].Year, [Query_Residuals_Departments_F&M].Level, [Query_Residuals_Departments_F&M].school_year
ORDER BY [Query_Residuals_Departments_F&M].Year DESC , [Query_Residuals_Departments_F&M].Level
PIVOT [Query_Residuals_Departments_F&M].Gender;


..and screenshot attached.

Where are you getting the list of subjects that is displayed in the combo box? Are they values you typed in or it's coming from a table?

Where are you getting the list of subjects that is displayed in the combo box? Are they values you typed in or it's coming from a table?

They come from simple query:

SELECT [imported data - RAW].Subject
FROM [imported data - RAW]
GROUP BY [imported data - RAW].Subject
ORDER BY [imported data - RAW].Subject;

Let's see the original query, that is this query - Query_Residuals_Departments_F&M

Let's see the original query, that is this query - Query_Residuals_Departments_F&M

No problem:
Code:
SELECT Query_Residuals_ALL.Year, Query_Residuals_ALL.Level, Query_Residuals_ALL.Gender, Query_Residuals_ALL.school_year, Avg(Query_Residuals_ALL.[art-res]) AS [AvgOfart-res], Avg(Query_Residuals_ALL.[bio-res]) AS [AvgOfbio-res], Avg(Query_Residuals_ALL.[bus-res]) AS [AvgOfbus-res], Avg(Query_Residuals_ALL.[che-res]) AS [AvgOfche-res], Avg(Query_Residuals_ALL.[cla-res]) AS [AvgOfcla-res], Avg(Query_Residuals_ALL.[com-res]) AS [AvgOfcom-res], Avg(Query_Residuals_ALL.[dra-res]) AS [AvgOfdra-res], Avg(Query_Residuals_ALL.[eco-res]) AS [AvgOfeco-res], Avg(Query_Residuals_ALL.[eng-res]) AS [AvgOfeng-res], Avg(Query_Residuals_ALL.[esol-res]) AS [AvgOfesol-res], Avg(Query_Residuals_ALL.[fre-res]) AS [AvgOffre-res], Avg(Query_Residuals_ALL.[gae-res]) AS [AvgOfgae-res], Avg(Query_Residuals_ALL.[geo-res]) AS [AvgOfgeo-res], Avg(Query_Residuals_ALL.[his-res]) AS [AvgOfhis-res], Avg(Query_Residuals_ALL.[ita-res]) AS [AvgOfita-res], Avg(Query_Residuals_ALL.[lat-res]) AS [AvgOflat-res], Avg(Query_Residuals_ALL.[mat-res]) AS [AvgOfmat-res], Avg(Query_Residuals_ALL.[med-res]) AS [AvgOfmed-res], Avg(Query_Residuals_ALL.[mod-res]) AS [AvgOfmod-res], Avg(Query_Residuals_ALL.[mus-res]) AS [AvgOfmus-res], Avg(Query_Residuals_ALL.[pe-res]) AS [AvgOfpe-res], Avg(Query_Residuals_ALL.[phy-res]) AS [AvgOfphy-res], Avg(Query_Residuals_ALL.[pro-res]) AS [AvgOfpro-res], Avg(Query_Residuals_ALL.[re-res]) AS [AvgOfre-res], Avg(Query_Residuals_ALL.[spa-res]) AS [AvgOfspa-res]
FROM Query_Residuals_ALL
GROUP BY Query_Residuals_ALL.Year, Query_Residuals_ALL.Level, Query_Residuals_ALL.Gender, Query_Residuals_ALL.school_year
ORDER BY Query_Residuals_ALL.Year DESC;


and screenshot attached - cut off some part, but you can see how it goes - basically all subject results.

So your subjects do not have an ID?

So your subjects do not have an ID?

Nope, they come from table in Subject field. Each student has few rows with level of exam he took, subject it was, results, etc...

In the last query you posted, I would want to code it so it refers to the 3 letter unique ID of each subject. For example, I could do this:
"Avg(Query_Residuals_ALL.[" & "art" & "-res]"Where art is that 3 letter that uniquely identifies art. You can't use Left("Subject_Name", 3) because you have subjects like English Language and English Literature, hence, trying to use the first 3 letters would mean those two subjects would end up being "eng" which will cause an error.

In the last query you posted, I would want to code it so it refers to the 3 letter unique ID of each subject. For example, I could do this:
"Avg(Query_Residuals_ALL.[" & "art" & "-res]"Where art is that 3 letter that uniquely identifies art. You can't use Left("Subject_Name", 3) because you have subjects like English Language and English Literature, hence, trying to use the first 3 letters would mean those two subjects would end up being "eng" which will cause an error.

You can do this way, cause it will always be 3 letters, art-res, bio-res, che-res, med-res ...etc

What if in future you had English Literature and English Language (for example)? Both will be "eng" so there's no distinction.

If you think the likelihood of that happening is slim then here:
Dim qdf As QueryDef

Const sqlString = "SELECT [Query_Residuals_Departments_F&M].Year, [Query_Residuals_Departments_F&M].Level, " & _
"[Query_Residuals_Departments_F&M].school_year " & _
"FROM [Query_Residuals_Departments_F&M] " & _
"GROUP BY [Query_Residuals_Departments_F&M].Year, [Query_Residuals_Departments_F&M].Level, " & _
"[Query_Residuals_Departments_F&M].school_year " & _
"ORDER BY [Query_Residuals_Departments_F&M].Year DESC , [Query_Residuals_Departments_F&M].Level " & _
"PIVOT [Query_Residuals_Departments_F&M].Gender;"

Set qdf = CurrentDb.QueryDefs("Query_Residuals_Departments_F&M_Crosstab_option")

qdf.SQL = "TRANSFORM First([Query_Residuals_Departments_F&M].[AvgOf" & ucase(left(Combobox1.value, 3)) & "-res]) AS " & _
[FirstOfAvgOf" & ucase(left(Combobox1.value, 3)) & "-res] " & sqlStringChange the bit in blue to the name of your combo box.

What if in future you had English Literature and English Language (for example)? Both will be "eng" so there's no distinction.

If you think the likelihood of that happening is slim then here:
Dim qdf As QueryDef

Const sqlString = "SELECT [Query_Residuals_Departments_F&M].Year, [Query_Residuals_Departments_F&M].Level, " & _
"[Query_Residuals_Departments_F&M].school_year " & _
"FROM [Query_Residuals_Departments_F&M] " & _
"GROUP BY [Query_Residuals_Departments_F&M].Year, [Query_Residuals_Departments_F&M].Level, " & _
"[Query_Residuals_Departments_F&M].school_year " & _
"ORDER BY [Query_Residuals_Departments_F&M].Year DESC , [Query_Residuals_Departments_F&M].Level " & _
"PIVOT [Query_Residuals_Departments_F&M].Gender;"

Set qdf = CurrentDb.QueryDefs("Query_Residuals_Departments_F&M_Crosstab_option")

qdf.SQL = "TRANSFORM First([Query_Residuals_Departments_F&M].[AvgOf" & ucase(left(Combobox1.value, 3)) & "-res]) AS " & _
[FirstOfAvgOf" & ucase(left(Combobox1.value, 3)) & "-res] " & sqlStringChange the bit in blue to the name of your combo box.

After this code add
stDocName = "Report_residuals"
DoCmd.OpenReport stDocName, acPreview

to open report, is that correct?
All this code goes under onClick in the button, right?
Should this Query_Residuals_Departments_F&M_Crosstab_option be empty?
Should I leave report record source empty as well?

That's correct, put the code before opening the report.

Make the report's record source the query which you're building - Query_Residuals_Departments_F&M_Crosstab_option

That's correct, put the code before opening the report.

Make the report's record source the query which you're building - Query_Residuals_Departments_F&M_Crosstab_option

Sweet Jesus Holy Batman! It works!:eek:
You my friend.. You.. no.. You!!:D
Thank you again - this is fantastic, I have to practice more of that VB, you can make amazing stuff in the background of that Access. Fantastic, thank you so much !:D

I never knew Batman was holy, I thought he drank and partied alot :D

Glad it's working. :)

I never knew Batman was holy, I thought he drank and partied alot :D

Glad it's working. :)


Hi there again! :)

Now I have a pickle...:rolleyes:
I have same thing as before, report based on combo box, only thing is this time the name in combo box has the name of the "set", but it is not always first 3 characters - sometimes it is something like "5APhyH1", where the name I would need to use is Phy. That means i cannot use Left function and set it on 3 characters...:(

How can I define it so it will "search" thsoe 3 letters in a string?
Please help! :o