Error in MSDATASHAPE

[ailensamson]

everyone...

I am having problem with this code:

Private Sub lvButtons_H6_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)

Dim ag As String
Call dbConek
 If Text4 = "" Or Text3 = "" Then

    MsgBox "Kindly Input the Academic Year", vbOKOnly, "Error in Printing"
    Text4.SetFocus

 Else
ag = Text5.Text & "-" & Text6.Text
 With ar
     .Open "SHAPE {SELECT * FROM `Student_Masterlist`}  AS Student_Masterlist APPEND ({select * from Student_Masterlist order by stud_idnum}  AS Command1 RELATE 'year = '" & sort & " ' TO 'Academic_Year= '" & ag & "'') AS Command1", strConek, adOpenStatic, adLockOptimistic
        
        If .RecordCount = 0 Then
            MsgBox "No record found.", vbExclamation
        Else
            Set DataReport5.DataSource = ar
            DataReport5.Show

         End If

    
End With
End If
End Sub

I would like to print records from a single table whose year = sort and academic_year = ag...

Thanks

[wes4dbt]

What's your error? Here's an example,

            ' Open a DATA Shape Connection For Reports
            With conReport
                .Mode = adModeShareDenyNone
                .Provider = "MSDataShape" 'Microsoft Provider for Data Shaping
                .ConnectionString = "Data Provider = Microsoft.Jet.OLEDB.4.0;" & _
                                    "Data Source = " & conDb
                .Open
            End With
            ' open ADODB recordset for report
            cSql$ = "SHAPE {SELECT * FROM `tagsjournal`}  AS tagsjournal APPEND ({select * from tjdetail order by tagnumber,date}  AS Command1 RELATE 'userid' TO 'userid') AS Command1"
            If (conReport.State And adStateOpen) > 0 Then
                With rsData
                    .ActiveConnection = conReport
                    .LockType = adLockReadOnly
                    .CursorType = adOpenStatic
                    .Open cSql
                End With
            End If
            
      
            Set rptTagsjournal.DataSource = rsData
            rptTagsjournal.Show
            Set rsData = Nothing

An easy way to create the datashape SQL is to first create a DataEnvironment that has the SQL and grouping that you want. Then right click the top level command, like "Command1". then select Hierarchy Info. The datashape will be displayed. You may have to tweek it but it's a good start.