Datafield not found for datareport child fields

[BenjaminB]

Dear Reader,

I've created a datareport in VB6 without using the data environment. There is one grouping level, achieved using the SHAPE command. The report displays data in the correct format (group header and correct number of child records for each parent), but only works if I assign a parent column as the datafield of the child fields in the detail section. If I try to assign one of the child columns to the child field then I get the &quot;DataField <name> not found&quot;.

Code is below, any ideas greatly appreciated.

Dim strSQL As String, strSQL2 As String
Dim rep As New rptStudGradesHT2
Dim rstRepData As ADODB.Recordset

strSQL = &quot;SELECT tblStudents.ID, CONCAT(tblStudents.LName, ', ', tblStudents.FName) AS Name, tblHouses.Name as House, If(tblClassTut.Year<10, Right(tblClassTut.Name, 2), CONCAT(Trim(tblClassTut.Year), Left(tblHouses.Name,1))) as Class, tblClassTut.Year &quot;
strSQL = strSQL & &quot;FROM tblClassTut, tblHouses, tblStudents &quot;
strSQL = strSQL & &quot;WHERE tblClassTut.ID = tblStudents.Class AND tblHouses.ID = tblStudents.House &quot;
strSQL = strSQL & &quot;AND tblStudents.Class =&quot; & avarClasses(Me.cboClasses.ListIndex, 2) & &quot; &quot;
strSQL = strSQL & &quot;ORDER BY tblClassTut.Name, Name&quot;

strSQL2 = &quot;SELECT tblGrades.StudentID, tblGrades.OptID, tblOptions.PrintName as Subject, tblGrades.Eff&quot; & Me.intPeriod & &quot; as Eff, tblGrades.Att&quot; & Me.intPeriod & &quot; as Att, tblOptions.AttChar, tblOptions.Order &quot;
strSQL2 = strSQL2 & &quot;FROM tblGrades, tblOptions &quot;
strSQL2 = strSQL2 & &quot;WHERE tblGrades.OptID = tblOptions.ID &quot;
strSQL2 = strSQL2 & &quot;ORDER BY tblOptions.Order&quot;

strSQL = &quot;SHAPE {&quot; & strSQL & &quot;} AS Student APPEND ({&quot; & strSQL2 & &quot;} AS Subjects RELATE ID TO StudentID) AS Subjects&quot;

Set rstRepData = New ADODB.Recordset
rstRepData.Open strSQL, cnn.ConnectionString, adOpenStatic, adLockReadOnly

Set rep.DataSource = rstRepData

With rep.Sections(&quot;Student&quot.Controls ' Group header section
' Next 3 lines work fine
.Item(&quot;txtName&quot.DataField = &quot;Name&quot;
.Item(&quot;txtHouse&quot.DataField = &quot;House&quot;
.Item(&quot;txtClass&quot.DataField = &quot;Class&quot;
End With

With rep.Sections(&quot;Subjects&quot.Controls ' Detail section
' Next 3 lines don't work, only if I change to -
' .Item(&quot;txtSubject&quot.DataField = &quot;Name&quot; or &quot;House&quot; or &quot;Class&quot;

.Item(&quot;txtSubject&quot.DataField = &quot;Subject&quot;
.Item(&quot;txtEff&quot.DataField = &quot;Eff&quot;
.Item(&quot;txtAtt&quot.DataField = &quot;Att&quot;
End With

rep.Show vbModal
Unload rep
Set rep = Nothing

 

[BenjaminB]

Found it! The DataMember value for the child fields must be set to &quot;Subjects&quot; and then the DataFields values can be set to those fields within Subjects - Subject, Eff & Att.

Easy when you know how!

 

[cardiacresearch]

Dear Reader-

I've created a datareport in VB6 without using the data environment. There is one grouping level, achieved using the SHAPE command. (Code below)

Unfortunately, the shape command does not seem to work in this situation. I get the following error…

Run-time error ‘-2147217900 (80040e14)’:
Invalid sql statement; expected ‘delete’, ‘insert’, ‘procedure’, ‘select’ or ‘update’.

Any thoughs?

Dim strSQL As String, strSQL2 As String
Dim db As Connection, rst As ADODB.Recordset
Set db = New Connection
db.CursorLocation = adUseClient
db.Open &quot;PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\VB\test dbase.mdb;&quot;

strSQL = &quot;SHAPE {SELECT tblpat.Age FROM tblpat} AS Command1 APPEND ({SELECT tblpat.Age, tbldata.RandNum FROM tblpat, tbldata WHERE tblpat.RandNum = tbldata.RandNum ORDER BY tblpat.Age, tbldata.RandNum} AS Command2 RELATE 'Age' TO 'Age') AS Command2&quot;

Set rst = New ADODB.Recordset
rst.Open strSQL, db, adOpenStatic, adLockOptimistic
Set PoweredBySQLMulti.DataSource = rst

With PoweredBySQLMulti.Sections(&quot;section6&quot.Controls ' Group header section
.Item(&quot;txtAge&quot.DataField = &quot;age&quot;
End With

With PoweredBySQLMulti.Sections(&quot;section1&quot.Controls ' Detail section
.Item(&quot;txtLastNm&quot.DataField = &quot;Last Name&quot;
End With

PoweredBySQLMulti.Show