asked on

Group field from an access recordset clone in vba

I have a subform and I have created a recordset clone. I would like to get the year field data from that recordset clone but grouped. The data in this case is a datefield and I want the year part only but grouped.

so I get
2016
2017
2018

and so on

I have this so far:

Dim rstTclientData As DAO.Recordset
Set rstTclientData = Forms![form1].[TblCombinedData subform].Form.RecordsetClone

With rstTclientData

End With

John Tsioumpris

Check my code

Dim rstTclientData As DAO.Recordset
Dim rstgrp As DAO.Recordset
Dim baseSQL As String

    Set rstTclientData = Forms![form1].[TblCombinedData subform].Form.RecordsetClone
    baseSQL = Replace(rst.Name, ";", "") ' ATTENTION THIS IS WHEN THE RECORDSOURCE IS THE SQL not the table/query ...maybe it will fail in some condition
    set rstgrp =CurrentDb.OpenRecordset("SELECT YEAR(S.YOURDATEFIELD) FROM (" & baseSQL & ") S GROUP BY Year(S.YOURDATEFIELD)) ")    
    
    End With

Open in new window

PeterBaileyUk

ASKER

Its not quite right on the image embedded here you can see the lone row 01057251 which is 2019

The lower form returns three vehicles 2011 and 2019 the recordset clone is those three rows and thats the year I want extracted the base sql in the code returns the complete dataset from the client.

In any event it gives an error in the from clause.

Dim TclientRecordCount, SMMTRecordCount As Long

Forms![form1].FrmTclientBatch.Form.RecordsetClone.MoveLast
TclientRecordCount = Forms![form1].FrmTclientBatch.Form.RecordsetClone.RecordCount
 
If TclientRecordCount = 1 Then

    Dim rstTclientData As DAO.Recordset
    Dim rstgrp As DAO.Recordset
    Dim baseSQL As String
    
    Set rstTclientData = Forms![form1].[TblCombinedData subform].Form.RecordsetClone
    
    baseSQL = Replace(rst.Name, ";", "") ' ATTENTION THIS IS WHEN THE RECORDSOURCE IS THE SQL not the table/query ...maybe it will fail in some condition
    Set rstgrp = CurrentDb.OpenRecordset("SELECT YEAR(S.DFrom) FROM (" & baseSQL & ") S GROUP BY Year(S.DFrom)) ")
    
    With rstgrp
    .MoveFirst
    Do While Not .EOF
    Debug.Print .Fields("DFrom").Value
    .MoveNext
    Loop
    End With
    



End If

Open in new window

John Tsioumpris

Put a breakpoint here:

Set rstgrp = CurrentDb.OpenRecordset("SELECT YEAR(S.DFrom) FROM (" & baseSQL & ") S GROUP BY Year(S.DFrom))

Open in new window

When the code reaches this point
Take this

"SELECT YEAR(S.DFrom) FROM (" & baseSQL & ") S GROUP BY Year(S.DFrom)

Open in new window

put it in the immediate window to get the actual SQL and check if there is a mispelling

PeterBaileyUk

ASKER

Ive amended to this:

    baseSQL = Replace(rstTclientData.Name, ";", "") ' ATTENTION THIS IS WHEN THE RECORDSOURCE IS THE SQL not the table/query ...maybe it will fail in some condition
    Set rstgrp = CurrentDb.OpenRecordset("SELECT YEAR(S.DFrom) FROM (" & baseSQL & ") S GROUP BY Year(S.DFrom)) ")

Open in new window

PeterBaileyUk

ASKER

I changed it around a little to get some sql but it now says too few parameters. if I ignore that and run the generated sql it gives me 117 rows which is every year in the original dataset.

Its only the recordsetclone years that I want. That recordset clone has been filtered down hence only three records in it as in the picture.

SELECT YEAR(S.DFrom) FROM QryCombinedData S GROUP BY Year(S.DFrom)

Open in new window

    Dim rstTclientData As DAO.Recordset
    Dim rstgrp As DAO.Recordset
    Dim baseSQL As String
    
    Set rstTclientData = Forms![form1].[TblCombinedData subform].Form.RecordsetClone
    
    baseSQL = Replace(rstTclientData.Name, ";", "") ' ATTENTION THIS IS WHEN THE RECORDSOURCE IS THE SQL not the table/query ...maybe it will fail in some condition
    strstring = "SELECT YEAR(S.DFrom) FROM " & baseSQL & " S GROUP BY Year(S.DFrom)"
    Debug.Print strstring
    
    Set rstgrp = CurrentDb.OpenRecordset(strstring)
    
    With rstgrp
    .MoveFirst
    Do While Not .EOF
    Debug.Print .Fields("DFrom").Value
    .MoveNext
    Loop
    End With

Open in new window

John Tsioumpris

Put this in the Query Designer
SELECT YEAR(S.DFrom) FROM QryCombinedData S GROUP BY Year(S.DFrom)
and check if it is what it should.

PeterBaileyUk

ASKER

No the query returns all of the years (grouped) in qrycombineddata but I wanted the group of the recordsetclone in the subform which has been filtered on the form.

I am just experimenting with an array and loop around the recordsetclone.

PeterBaileyUk

ASKER

This is giving me a correct array with the years from the form recordset clone but its not grouped

 Dim Rtotal As Integer
    Set rstTclientData = Forms![form1].[TblCombinedData subform].Form.RecordsetClone
'
'    baseSQL = Replace(rstTclientData.Name, ";", "") ' ATTENTION THIS IS WHEN THE RECORDSOURCE IS THE SQL not the table/query ...maybe it will fail in some condition
'    strstring = "SELECT YEAR(S.DFrom) FROM " & baseSQL & " S GROUP BY Year(S.DFrom)"
'    Debug.Print strstring
'
'    Set rstgrp = CurrentDb.OpenRecordset(strstring)

    With rstTclientData
    .MoveLast
    Rtotal = .RecordCount
    ReDim DVfrom(Rtotal - 1) As Variant
    Dim index As Long
    index = 0
        .MoveFirst

        Do While Not .EOF
            Debug.Print Year(.Fields("DFrom").Value)
            DVfrom(index) = Year(.Fields("DFrom").Value)
             index = index + 1
            .MoveNext
        Loop
    End With

Open in new window

John Tsioumpris

You can sort the array if this is the issue :Sort Array VBA

ASKER CERTIFIED SOLUTION

PeterBaileyUk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PeterBaileyUk

ASKER

Thank you