Dynamic Array reading from Recordset

[catsoncars]

My first post and I am pretty clueless so be nice

I have a recordset
Set rst = db.OpenRecordset("SELECT qryWorkInfoText.WIIncidentID, qryWorkInfoText.WIText FROM qryWorkInfoText;")

The WIIncidentID is just a alphanumeric number and WIText is a string. I need to take multiple rows of WIText with the same WIIncidentID and combine WIText into a single field, stringing one right after the other to create a pseudo comments field. I had that part working but sometimes the resulting comments field exceeded 1200 characters and the SQL would bomb (too long) when trying to load the data into CombWork below. I do have CombWorkInfo set as a memo so it is the actual strWIText that is to long for the sql statement.

After reading many, many posts I thought an array would work but have no idea how to read enough records from the recordset for each WIIncidentID as they vary in number of rows.

Basically I want to read the first rows that have the same WIIncidentID, put those text strings in an array and then load that new record into CombWork. Go back to the recordset and get the next WIIncidentID and text strings, and continue until they are all read and loaded.

If there is an easier way, great, but I really need help.

This is the code I have which bombs on the insert when the comments are too long.

strWIText = strWIText & " " & Nz(rst!WIText, vbNullString)
strIncidentID = rst![WIIncidentId]
'
'move to next record
'
rst.Move 1
'
If rst.EOF Then Exit Sub
'
Loop Until rst![WIIncidentId] <> strIncidentID
DoCmd.RunSQL "INSERT INTO CombWork ( WIIncidentID, CombWorkInfo ) SELECT '" & strIncidentID & "','" & strWIText & "'"
'
'reset comments
'
strWIText = vbNullString
'
Loop Until rst.EOF

 

[c_smithwick]

Try the following

Public Sub populateConbWork()
Dim rst As DAO.Recordset
Dim rstCombWork As DAO.Recordset
Dim strWIText As String
Dim strIDTest As String
Set rst = CurrentDb.OpenRecordset("SELECT qryWorkInfoText.WIIncidentID, qryWorkInfoText.WIText FROM qryWorkInfoText ORDER BY qryWorkInfoText.WIIncidentID", dbOpenDynaset)
Set rstCombWork = CurrentDb.OpenRecordset("CombWork")
With rst
Do While Not .EOF
If strIDTest <> ![WIIncidentID] Then 'new unique WIIncidentID or first record
'if this is not the first record, then add a new record to CombWork
If Len(strIDTest) > 0 Then
rstCombWork.AddNew
rstCombWork![WIIncidentID] = strIDTest
rstCombWork![CombWorkInfo] = strWIText
rstCombWork.Update
End If
'reset temporary comparison variable
strIDTest = ![WIIncidentID]
strWIText = strWIText & " " & Nz(![WIText], "") 'add Text to comment string
Else
strWIText = ""
End If
.MoveNext
Loop
End With
rst.Close
rstCombWork.Close
'free up memory
Set rst = Nothing
Set rstCombWork = Nothing
End Sub

 

[gemma-the-husky]

information about presentation

if you click advanced on your post, select some text and click the # symbol (code) - then code tags are placed around your post, it respects indentation/tabs, and is altogether easier to read.

 

[c_smithwick]

Try the following:

Public Sub populateCombWork()

Dim rst As DAO.Recordset
Dim rstCombWork As DAO.Recordset
Dim strWIText As String
Dim strIDTest As String

Set rst = CurrentDb.OpenRecordset("SELECT qryWorkInfoText.WIIncidentID,qryWorkInfoText.WIText FROM qryWorkInfoText ORDER BY qryWorkInfoText.WIIncidentID", dbOpenDynaset)
Set rstCombWork = CurrentDb.OpenRecordset("CombWork")

With rst
    Do While Not .EOF

        If strIDTest <> ![WIIncidentID] Then 'new unique WIIncidentID or first record
        'if this is not the first record, then add a new record to CombWork

            If Len(strIDTest) > 0 Then
                rstCombWork.AddNew
                rstCombWork![WIIncidentID] = strIDTest
                rstCombWork![CombWorkInfo] = strWIText
                rstCombWork.Update
            End If

            'reset temporary comparison variable
            strIDTest = ![WIIncidentID]
            strWIText = strWIText & " " & Nz(![WIText], "") 'add Text to comment string
         Else
             strWIText = ""
         End If

         .MoveNext

  Loop

End With

rst.Close
rstCombWork.Close

'free up memory
Set rst = Nothing
Set rstCombWork = Nothing

End Sub

 

[catsoncars]

The very first WIIncidentID has 5 rows, it loaded the WI IncidentID but nothing in the CombWorkInfo. If there was only 1 row for anWIIncidentID then it populated the CombWorkInfo also but if multiple rows, only the WIIncidentID was populated.

 

[c_smithwick]

Play with the code - I wrote it hastily and didn't test it, but the general idea is sound. Step through the code and use the immediate window to check variable values and make modifications as needed. Basically you want to step throught the recordset and combine Text strings where the ID is the same, and then reset, add a new record to your second table and begin a new combine operation when the ID changes. Code should set you on the right track, but it might require a bit of tweaking on your part - you do have to do some work yourself you know!