Create crosstab report from Crosstab query

[theDBguy]

Well wouldn't ya know I managed to work it out so thank you for your patience. It took a bit of working out as to how to set the parameter up in VBA but that works perfectly now too. So the only thing left to do is for me to change the hard-coded column names in the PrintDetail event to a generic bit of code that will loop through the column values in the same way but without having the column names hard-coded. So what I currently have is:

If IsNull(Me.[Fforest fields XC - MEN]) Or IsNull(Me.[Croft Castle XC race -MEN]) Or IsNull(Me.[Queenswood XC Race -MEN]) Then
Cancel = vbCancel
Me.MoveLayout = False
End If
So I need to know how many fields there are in total to loop through and then refer to each field in turn to see if it is null or not. Is there a way of doing that without hardcoding the field names like I have here?

Thanks
Stephen

Hi Stephen. Glad to hear you're making good progress. Normally, you won't know how many column the crosstab query will produce, but you should know beforehand how many textboxes you have on the report, because it obviously have a limited amount of space, plus you would have to place those textboxes yourself at design time.


The usual approach is to name the textboxes with a number suffix, so you can loop through them.

 

[GoodyGoody]

Hi DBGuy, thanks. I was thinking that the actual text box names are being overridden. Or is that not the case? I have text boxes in my report txtRaceData1,2,3,4 etc. Do those names remain available to me? I'm still not exactly clear what technically the whole pre-processing has done to the report controls. Perhaps someone could explain a bit more of the theory? Thanks Stephen

 

[theDBguy]

Hi DBGuy, thanks. I was thinking that the actual text box names are being overridden. Or is that not the case? I have text boxes in my report txtRaceData1,2,3,4 etc. Do those names remain available to me? I'm still not exactly clear what technically the whole pre-processing has done to the report controls. Perhaps someone could explain a bit more of the theory? Thanks Stephen

Hi Stephen. No, the textbox names stay the same. What gets changed are the Control Sources of those textboxes to the new column names from the Crosstab query.


So, you would loop through the column names of the Crosstab query and assign each one to the Control Source property of the Textboxes in your report.

 

[isladogs]

Hi Stephen
Just to confirm that is exactly what my dynamic crosstab reports do

For example in the extended properties report example database, the report has
a) 36 labels lbl1, lbl2...lbl36 in the page header for the crosstab column headers
b) 36 checkboxes chk1, chk2...chk36 in the detail section for the data (you would have textboxes instead for your data values)
c) 36 unbound textboxes Tot1,Tot2 ...Tot36 in the report footer for displaying total number of records for each crosstab column (you may not need that feature)

By default each of these items are hidden and made visible only if the crosstab column has data

You can add as many columns as you can fit on a page.
In my exam results example I have 70 (very narrow) crosstab columns.

There are 2 'fixed' columns before the crosstab data and as column numbering is zero based that means the crosstab data starts at column '2'
The report open event code loops through each of these to populate them starting at column '2'.
The code

Set Rs = CurrentDb.OpenRecordset(strSQL)

    'Populate column headers - start at column 2 
    'show columns only where data exists
    For intI = 2 To Rs.Fields.Count - 1
       Me("Lbl" & intI - 1).Caption = Rs.Fields(intI).Name
       Me("Lbl" & intI - 1).Visible = True
          
    Next intI

    'Populate checkbox controls
    For intI = 2 To Rs.Fields.Count - 1
        Me("chk" & intI - 1).ControlSource = Rs.Fields(intI).Name
        Me("chk" & intI - 1).Visible = True
    Next intI
    
    
    'Populate totals
    'each filled value = -1 (true) so do -SUM to get total
    For intI = 2 To Rs.Fields.Count - 1
        Me("Tot" & intI - 1).ControlSource = "=-SUM([" & Rs.Fields(intI).Name & "])"
        Me("Tot" & intI - 1).Visible = True
    Next intI

I hope the idea is starting to get a bit clearer now

 

[GoodyGoody]

Thanks Isladogs and DBGuy. Yes, it helps to understand the theory (for me anyway) otherwise it's just monkey say monkey do so I appreciate the lesson in the underlying principles. Actually quite straightforward really once I got what exactly was happening. I did have a bit of an issue with parameters but sorted that too using Querydef.parameters etc first before opening the recordset. All I need to know now in the report is how many columns in total I have. Is THAT possible to know like I can query a recordset using rst.fields.count can I do the same on a report? If not I'm just going to have to pass the number of Crosstab query columns there are so I can then know how many fields I need to check for no data as EVERY column that could contain data MUST have data (i.e. a team needs to be in all included races in the series (in this instance anyway: more variables to follow... ) Thanks again

 

[theDBguy]

Thanks Isladogs and DBGuy. Yes, it helps to understand the theory (for me anyway) otherwise it's just monkey say monkey do so I appreciate the lesson in the underlying principles. Actually quite straightforward really once I got what exactly was happening. I did have a bit of an issue with parameters but sorted that too using Querydef.parameters etc first before opening the recordset. All I need to know now in the report is how many columns in total I have. Is THAT possible to know like I can query a recordset using rst.fields.count can I do the same on a report? If not I'm just going to have to pass the number of Crosstab query columns there are so I can then know how many fields I need to check for no data as EVERY column that could contain data MUST have data (i.e. a team needs to be in all included races in the series (in this instance anyway: more variables to follow... ) Thanks again

Hi. To avoid having to do the querydef.parameters every time you need a recordset, consider using this generic recordset next time.

With regards to checking how many columns in the query, as I mentioned earlier, there should be no need to worry about it. You could/should put as many columns can fit in your report (remember, you will have a limited space there, you won't have a limit in the query). Then, in your code, you simply limit the number of columns to assign to the report based on the number of textboxes you have.

For example, let's say you designed your report to have 30 textboxes. When you process your query, you basically just go from the first column all the way to the last one. If you hit 30 before you get to the last one, you'll have to stop because there won't be any more place left on the report to put the rest of the columns from your query. If you don't hit the limit, then you're good to go. If you hit the limit, you could add a label on your report header to let the user know there were missing data on the report.

 

[isladogs]

Agree with DBG. You do not need to know the number of columns in your query output as the code handles that ...as long as there are enough

So with my example of 36 numbered controls for each section, it will handle anything up to 36 crosstab columns. If you have 37 columns, the code will error during the loop telling you it can't find Lbl37 in order to populate it with the column header.

My advice is to determine the likely maximum and add a few for safety margin. Use vertically aligned text in the column header so less width is needed. Consider using e.g. Arial Narrow for the textbox values for the same reason.

I normally use filter criteria instead of PARAMETERS but the principle is the same

 

[GoodyGoody]

Hi Guys, I didn;t realise I could use the generic recordset for ALL recordsets. I'll update the code. The reason for knowing the number of columns is that I only want to print the line on the report if ALL columns that SHOULD have data actually have data. If you can imagine for example that TEAM 'C' only ran in 2 of the 3 races then that line shouldn't print, hence why I need to know the number of fields that should have data (currently 3 races have been run in the series so I need to just check the first 3 text boxes. Clearly the other 2 columns do not currently have data (I have hard-coded 5 columns for now - working on the variations!). Does that make sense? Unless the reprt collection has a method for the number of columns, I can just pass the number of columns through from the VBA which does the pre-processing as I know it there from rst.fields.count so it's not a problem. It would be neater of course to pick the information up from a report method if it exists...

 

[isladogs]

That is an extra complication and a somewhat unusual criterion for use with a crosstab query.

To manage that, I suggest you consider modifying the original query used for the crosstab so it only includes fields where none of the records are null. Then you will know that is also true for the crosstab without hard coding any fields.

Hope that helps

 

[GoodyGoody]

The reason I don't want to do that is because I want a report which shows all the races tha ANY team has been in for control purposes. SO....IS THERE a way of knowing how many columns there are on a report like in a recordset using rst.fields.count or must I pass the number as a parameter to the report? In advance of answers I have passed the number in the Openargs property :

varOpenArgs = rst.Fields.Count - 3 '3 is the number of initial columns in the query: Club, Team, Total
DoCmd.OpenReport strReportName, acPreview, , , , varOpenArgs

BUT when I try to assign this to a variable in the report Load or Open event it shows as Null? What am I doing wrong? (BTW, I'm testing if isnull(Me.Openargs) in the report)

Thanks

 

[isladogs]

The reason I don't want to do that is because I want a report which shows all the races tha ANY team has been in for control purposes.

So why not use two different queries?
One for 'control purposes' & the other for your more restricted conditions?

BUT when I try to assign this to a variable in the report Load or Open event it shows as Null? What am I doing wrong? (BTW, I'm testing if isnull(Me.Openargs) in the report)

Sorry but doing that doesn't make sense to me
If you are opening a report using OpenArgs then testing for IsNull(Me.OpenArgs) will be False

 

[GoodyGoody]

Hi Isladogs, If I am passing data from the docmd.openreport why should me.Openargs return null? Anyway, I have a more pressing issue as real world data has shown a hole in the logic for the race series calculations.

I have looked on the web and the issue I have is common but all the solutions seem to be using dates or hard-coded data and ones that aren't are using T-SQL, Oracle or some other query language which seems to do other things Access sql won't. Take the Top 'n' records for a group within a recordset: I want the top 3 positions for EACH runner Sample data would be:

Runner, Race, Position
1234, 1, 6
1234,2,6
1234,3,7
1234,4,7
5678,1,20
5678,2,21
5678,3,8
5678,4,9
4321,1,1
4321,2,2
4321,3,1

Surely there must be a neat way of doing this in Access query? Thanks as ever for your help

 

[isladogs]

I've answered your top 3 question in the other thread:
https://www.access-programmers.co.uk/forums/showpost.php?p=1662655&postcount=9

To answer the OpenArgs question, I've added the following code to a form:

Private Sub Form_Load()
    Debug.Print "1." & Me.OpenArgs
    Debug.Print "2." & IsNull(Me.OpenArgs)
End Sub

Opening the form with OpenArgs = "Test", gives this output:

DoCmd.OpenForm "Results",,,,,,"Test"

1.Test
2.False

When opening the form without OpenArgs, the output is

DoCmd.OpenForm "Results"

1.
2.True

Hope that helps
NOTE I've just corrected my previous answer. Sorry for any confusin