Problems filling Disconnected Hierarchical recordset

[Vachaun22]

I'm having an issue filling a hierarchical recordset with the data from 2 connected recordsets. It's disconnected because I have to create fields that cannot be created in the SQL statements.

Here is the code that does the hard work (and doesn't function for some reason):



rsH.ActiveConnection = "Provider=MSDataShape;Data Provider=None;" '' Data Shaped recordset
rsH.Open "SHAPE APPEND NEW adVarChar(2) as [State], " & _
"((SHAPE APPEND NEW adInteger AS [Unit], " & _
"NEW adSingle AS [State Miles], " & _
"NEW adSingle as [Fuel], " & _
"NEW adSingle AS [MPG], " & _
"NEW adSingle AS [Toll Miles], " & _
"NEW adVarChar(2) as [State]) " & _
"AS CHILD RELATE [State] TO [State])", , adOpenStatic, adLockOptimistic

Set rsTemp = GetRecordset(SQL)

If Not rsTemp.EOF Then
rsTemp.MoveFirst

While Not rsTemp.EOF
rsH.AddNew "State", rsTemp![FD_STATE]
rsH.Update

rsTemp.MoveNext
Wend

rsH.MoveFirst
Else
QuarterlyReport = False
Exit Function
End If

Set rsHCh = rsH("Child").Value

Set rsTemp = GetRecordset(SQL)

If Not rsTemp.EOF Then
rsTemp.MoveFirst

While Not rsTemp.EOF

sMPG = 0 'rsTemp![SumOfFD_TOT_MILES] / rsTemp![SumOfFD_FUEL]

rsHCh.AddNew
rsHCh![Unit] = rsTemp![FD_UNIT]
rsHCh![State Miles] = rsTemp![SumOfFD_TOT_MILES]
rsHCh![Fuel] = rsTemp![SumOfFD_FUEL]
rsHCh![MPG] = 0
rsHCh![Toll Miles] = rsTemp![SumOfFD_TOLL_MILES]
rsHCh![State] = rsTemp![FD_STATE]

rsHCh.Update
rsTemp.MoveNext
Wend
Else
QuarterlyReport = False
Exit Function
End If


The SQL statements are left out just for ease of reading, but they function properly, and the GetRecordset function simply takes in the SQL string and returns the recordset from it.

What is happening is that only the first or last record in the parent recordset is being linked with the child recordset. All other records from the child are lost.

For instance, say record 1 in the parent is equal to CT and there are 78 records in the child that match CT, then there are only 78 records added to the child...not the full 998 records in the recordset.

The code that I am using mimics exactly the code in another application I wrote that works fine. The only difference is the fields in the recordsets are different. Everything else is exactly the same. Any ideas what might be happening?

Also, one more thing. I did check the contents of the parent recordset, and all the records are in there that should be in there. Both SQL statements pull from the same table using the same selection parameters, just different fields are returned.

 

[JAG14]

My advice would be check the Field Names very carefully,
I understand the use of Square Brackets, but a typo is a very easy mistake to make. (personal experience nonwithstanding!)

HTH

JaG

[tt]'Very funny, Scotty... Now Beam down my clothes.'[/tt]
​

 

[Vachaun22]

Well, it can't be a field name typed incorrectly as this would throw an error. Also, it is putting some data in the child recordset.

It just so happens though, that if I move the parent recordset to the first record, then only the records in the child that correspond to that first record get inserted. If I move the parent to the last record, then only the child records corresponding to that value get added.

For some reason, it drops all other records that the parent is not currently at cursor wise.

I'm really confused because this code worked perfectly fine in another application, but now it doesn't. It doesn't make sense to me. Any other ideas?

 

[JAG14]

OK, try changing the following lines

        While Not rsTemp.EOF
            rsH.AddNew "State", rsTemp![FD_STATE]
            rsH.Update
            
            rsTemp.MoveNext
        Wend

to

        Do
            rsH.AddNew "State", rsTemp![FD_STATE]
            rsH.Update
            
            rsTemp.MoveNext
        Loop Until rsTemp.EOF

Using Do.. Loop.. loops generally provided a more structured and controllable way of looping.

Also, look at the EOF value, and the record position.
If you move any of the RS's to the end, it is going to encounter the EOF after looping just once, therefore only copying one record.

Then you may want to try using the last modified property to make the record you have just updated current, because if not, logic suggests that you will simply be overwriting the record you have just created. This may or may not be true, but there is no harm in trying.

Try:

            rsH.AddNew "State", rsTemp![FD_STATE]
            rsH.Update
            rsH.Bookmark = rsH.LastModified
 
            rsTemp.MoveNext

and

            rsHCh.AddNew
            rsHCh![Unit] = rsTemp![FD_UNIT]
            rsHCh![State Miles] = rsTemp![SumOfFD_TOT_MILES]
            rsHCh![Fuel] = rsTemp![SumOfFD_FUEL]
            rsHCh![MPG] = 0
            rsHCh![Toll Miles] = rsTemp![SumOfFD_TOLL_MILES]
            rsHCh![State] = rsTemp![FD_STATE]

            rsHCh.Update
            rsHCh.Bookmark = rsHCh.LastModified
            rsTemp.MoveNext

HTH

JaG

[tt]'Very funny, Scotty... Now Beam down my clothes.'[/tt]
​

 

[Vachaun22]

Thanks for the help JAG14, but I figured out what I was doing. I'm a bit slow and not paying attention to what I'm doing.

The shaped dataset is actually fine. It's only returning the number of records in the child recordset for the record the parent is on because that's all the records that the child knows about at that given time. If I would have stepped throught the parent, returning the child count, I would have received the correct values all the way through, adding up to the grand total at the end. That was my mistake.

The whole problem originally stemmed from an arror when trying to load the DataReport with the values from the recordset telling me that

DataField '(Empty)' not found

Which I have yet to discover the problem with that yet. Thanks again for your help.

 

[JAG14]

Glad you got it sorted!

Thanks for the response.

JaG

[tt]'Very funny, Scotty... Now Beam down my clothes.'[/tt]
​