Solved Dynamic display titles of crosstab query

[Kayleigh]

Hi
I'm revisiting some forms I started building last year (see here which I've successfully implemented thanks to @MajP !)

Now my problem is that I would like to display a subform on the main form to summarise the details for each related student. I've considered the options and thought it would be best to write a crosstab query with the students along the top and the details/categories as the rows. So I've written several suitable crosstabs and used a union query to join.

My trouble started when I implemented on the form - I can't put the student names as titles because it changes for each record. So I would like to dynamically display the titles. I've used this code in the past successfully but now I keep getting an object variable not set error - I think at the part where its iterating through recordset (between done 3 and 4). It is on the subform in attached DB (not yet linked to the main form but the main form will need to be opened to work).

If anyone can shed light on this issue because I really don't know what is causing this error.

Thank you

 

[Pat Hartman]

Instead of using a form control on the mainform, just drag the crosstab query onto the form. It should display the crosstab whatever the column headings are. And since the crosstab is not updateable, you don't have to worry about validation or locking the subform to prevent updates.

 

[Kayleigh]

Sorry not sure what you mean - I pulled the query onto the form but it brings up the wizard to create a subform with the query as the record source. This does not show up at all in the form view for some reason??

 

[Pat Hartman]

Sorry, I thought that will work but it won't. I don't remember Access creating a form but then I haven't done this in many years.

You would be better off if you made the students the rows and the details/categories as columns. The students will always vary but the details/categories might be able to be fixed. If they can be, you can add column headings to the crosstab and that will "fix" the heading. You will need to modify the headings if you add new details/categories though.

If that doesn't work, you would need to resort to code. Here's an example of how I do it with a report. The technique will be similar. Just keep in mind that the number of columns must be fixed for the technique to work. I included a pdf with a little explanation.

There is a slightly different example at FMSINC.com. Sorry I don't have a direct link but their samples are worth browsing through.

 

[Minty]

The wizard will create the query as a subform but the answer is to ignore that and set the subform source object directly to the query.
In the subform properties, If you click the dropdown on the source it will list forms, then tables and queries.

I've used this a number of times.

 

[Kayleigh]

Thanks @Minty your method seems to suit my needs best.
Only issue is that the query shows as a datasheet including navigation buttons - any way to remove or cover this?

 

[Minty]

I'm not sure you can.
Let me ponder on that for a moment, but I think that's one disadvantage is a lack of control over the interface.

 

[Kayleigh]

Another disadvantage is you can't automate any events directly on the fields.

 

[Minty]

As @Pat Hartman suggested you may be better with a subform and control the headings.
It isn't as awkward as you think might think.

 

[Kayleigh]

@Pat Hartman looking at your sample DB now. Its amazing how generic the code is! Is the whole dynamic column process happening in the report load event or is there other procedures involved?

 

[Pat Hartman]

The sample uses an intermediate table. It takes the recordset the crosstab will use and identifies the unique values for the column headers and loads them into the table. The first value becomes "1", the second "2", etc. This is the method that simplifies the code. The code then looks up 1,2, 3, etc. to find the value to use for the column name.

Remember, this method only works for a specific number of columns. Both Access Forms and Reports are limited in width and so depending on the width, probably can't hold more than 20 or 30 columns.

You can just copy the report template and change the headings and add breaks if you need them. The detail section can probably stay as is along with the code behind it.

 

[Pat Hartman]

Thanks Minty, I was sure you could plop the query on the form directly. I just couldn't remember how.

Krayna, what is wrong with ds view and embedded navigation control? The embedded query is the only no-code solution.

 

[Kayleigh]

Thanks - very informative.

I actually thought through the best way for my purposes - I don't need the autonumbered columns as I will be using the student names for headers and each is a unique record. So instead I iterated through the base query to find the names of the students and assign as control source / captions of textbox/labels. There is a variable keeping track of which columns are to be visible and which are not.

I am happy to share my working code for those who have a similar issue.
1. I used the crosstab query as the record source of the form and iterated through the base query to obtain the column headers.
2. In the form I numbered the labels and textboxes 1 - 10.
3. I also filtered in the base query to reduce recordset.

code in load event:

Option Compare Database
Option Explicit

Private Sub Form_Load()
   Dim ctl As Control
   Dim noStudents As Integer
   Dim i As Integer
   Dim rs As DAO.Recordset
   On Error GoTo ErrProc
 
   noStudents = Nz(DCount("fldIncidentLogID", "qryIncidentLogDetailsSummary"), 0)
    If noStudents = 0 Then
        MsgBox "No students to display for this incident.", vbInformation + vbOKOnly, gstrAppTitle
        Exit Sub
    End If
   
    i = 1
    Set rs = CurrentDb.OpenRecordset("qryIncidentLogDetailsSummary")
    If Not rs.BOF And Not rs.EOF Then
        rs.MoveFirst
           
                   While (Not rs.EOF)
                    For Each ctl In Me.Controls
                    Select Case ctl.ControlType
                    Case acLabel
                        If IsNumeric(ctl.Caption) Then
                            If Trim(ctl.Caption) = CInt(i) Then
                            ctl.Visible = True
                            ctl.Caption = rs!Student
                        End If
                        End If
                    Case acTextBox
                        If IsNumeric(ctl.Name) Then
                        If Trim(ctl.Name) = CInt(i) Then
                            ctl.Visible = True
                            ctl.ControlSource = rs!Student
                        End If
                        End If
                    End Select
                 Next ctl
                 rs.MoveNext
                 i = i + 1
                 Wend
             
              If i > noStudents Then
                    For Each ctl In Me.Controls
                    Select Case ctl.ControlType
                    Case acLabel
                    If IsNumeric(ctl.Caption) Then
                        If Trim(ctl.Caption) >= i Then ctl.Visible = False
                    End If
                    Case acTextBox
                    If IsNumeric(ctl.Name) Then
                        If Trim(ctl.Name) >= i Then ctl.Visible = False
                    End If
                    End Select
                    Next ctl
                End If
           
           
    Else
       MsgBox "No information found.", vbInformation + vbOKOnly, gstrAppTitle
        Exit Sub
    End If

   
    rs.Close
    Set rs = Nothing
   

ExitProc:

    Set ctl = Nothing
    Set rs = Nothing
    Exit Sub

ErrProc:
    Select Case Err.Number
        Case 13     'ignore
            Resume Next
        Case Else
            MsgBox Err.Number & "--" & Err.Description
    End Select
    Resume ExitProc
   
End Sub

EDIT: Embedded query was very limited in terms of the interface and events which could be associated with it. I have no issue with using a code solution - when it works! Thanks again