Split records giving separate ID

[barbola]

I wrote some code that will loop through the records in a table and add them to a collection which is then viewed in a listview.

If the "counter" field is greater than 1 then it will split this into two records in the collection. When I step through the code, I can see it is assigning proper ID numbers (1, 2, 3, 4, 5). There are 3 records in the table and the counter is "2" for two of them so I end up with 5 members in the collection.

When I get back to my form for filling the listview, I did a watch on the object and it shows ID numbers as 1, 3, 3, 5, 5 so I get a duplicate key error.

Here is my code:

'frmMain
Option Explicit

Private mobjDetails As clsDetails

Private Sub DisplayData() 'called in the form load event
    
  Set mobjDetails = New clsDetails
  mobjDetails.LoadAllDetail
    
  Dim objDetail As clsDetail
   
  If Not mobjDetails Is Nothing Then
    lvwDetail.ListItems.Clear

    For Each objDetail In mobjDetails

      Dim objListItem As ListItem
      Dim objListSubItem As ListSubItem

      With objDetail
        Set objListItem = lvwDetail.ListItems.Add
        objListItem.Key = "S" & Str(.OID)
        objListItem.Text = .LotNumber
        Set objListSubItem = objListItem.ListSubItems.Add(, , .Weight)
        Set objListSubItem = objListItem.ListSubItems.Add(, , .Head)
        Set objListSubItem = objListItem.ListSubItems.Add(, , .BasePrice)
      End With
    Next
  End If
End Sub

This is the code in my class:

'clsDetail
Option Explicit

Private mstrLotNumber   As String
Private mlngWeight      As Long
Private mstrHead        As String   
Private mstrBasePrice   As String   
Private mlngOID         As Long     

Public Property Get LotNumber() As String
    LotNumber= mstrLotNumber
End Property

Public Property Let LotNumber(ByVal strLotNumber As String)
    mstrLotNumber= strLotNumber
End Property

Public Property Get Weight() As Long
    Weight= mlngWeight
End Property

Public Property Let Weight(ByVal lngWeight As Long)
    mlngWeight= lngWeight
End Property

Public Property Get Head() As String
    Head = mstrHead
End Property

Public Property Let Head(ByVal strHead As String)
    mstrHead = strHead
End Property

Public Property Get BasePrice() As String
    BasePrice = mstrBasePrice
End Property

Public Property Let BasePrice(ByVal strBasePrice As String)
    mstrBasePrice = strBasePrice
End Property

Public Property Get OID() As Long
    OID = mlngOID
End Property

Public Property Let OID(ByVal lngOID As Long)
    mlngOID = lngOID
End Property

Here is my collection:

'clsDetails

Option Explicit

Private mDetails As Collection

Public Function Item(ByVal strKey As String) As clsDetail
     Set Item = mDetails.Item(strKey)
End Function

Public Sub Class_Initialize()
    Set mDetails = New Collection
End Sub

Public Sub Class_Terminate()
    Set mDetails = Nothing
End Sub

Public Function NewEnum()
    'Allow for For Each..Next enumeration
    Set NewEnum = mDetails.[_NewEnum]
End Function

Public Function LoadAllDetail()
    Dim oRset As New ADODB.Recordset
    Dim oCnn As New ADODB.Connection
    Dim oCmd As New ADODB.Command
    Dim objNewDetail As clsDetail
        
    oCnn.Open "Provider=sqloledb;" & _
           "Data Source=XXXXX;" & _
           "Initial Catalog=XXXXX;" & _
           "Persist Security Info=True;" & _
           "User Id=XXXXX;" & _
           "Password=XXXXX"
           
     'set command properties
    oCmd.ActiveConnection = oCnn
    oCmd.CommandType = adCmdStoredProc
    oCmd.CommandText = "LoadAllDetail"

    'set recordset properties
    Set oRset = oCmd.Execute

    'Set oCmd.ActiveConnection = Nothing
    Dim i, n, NextID
    
    'load all the records into the collection
    oRset.MoveFirst
    Do Until oRset.EOF = True
      n = 1
      Set objNewDetail = New clsDetail

      'load all the properties
      With objNewDetail   'This is where we split
         i = oRset.Fields("Head").Value
         For n = 1 To i
           NextID = NextID + 1
           .LotNumber = oRset.Fields("Lot").Value  
           .Weight = CLng(oRset.Fields("Weight").Value)/i
           .Head = 1
           .BasePrice = oRset.Fields("BaseValCwt").Value
            mDetails.Add objNewDetail, CStr(NextID)
          Next
      End With
        oRset.MoveNext
    Loop
End Function

Here is the sample data:

Lot Head Weight BaseValCwt
999 1 250 99
999 2 425 97
999 2 500 96

Here is what I want in my collection (and listview):
OID LotNumber Head Weight BaseValCwt
1 999 1 250 99
2 999 1 212.5 97
3 999 1 212.5 97
4 999 1 250 96
5 999 1 250 96

But the OID is trying to come in as I said 1, 3, 3, 5, 5 insetad of 1, 2, 3, 4, 5.

Any ideas?

thanks

 

[gmmastros]

In case anyone else is interested in seeing a sql server solution for this... thread183-1291459

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom