Access VBA - Writing to records based on criteria in current and former records 1

[firebirdta84]

Good Morning,

I've been stumped on this problem for about a week now, hoping someone can help me out.

I have a table called "tbl_Messages", and it contains a message transmitted by a user. Depending on the length of the message, it could all appear in one record, or the message could be split up across multiple records. There is no limit to the number of records associated with a given message, most are 10-15 lines, but can go into the hundreds.Changing the way the data is received is not an option.

Each report number can have one or multiple messages attached to it, and each message sender can send one or multiple messages for a given report number.

The message "pieces" are always sequential, so there's no worry about messages getting jumbled.

The message sequence field indicates the number of lines for a given message. Each time this number resets to "1", that indicates the start of a new message.

I need help writing some VBA code to be used within MS Access 2010, that will read every record in tbl_Messages, and will join the pieces of the message into one complete line under the field "Full Message". If the message is only one line, then it simply copies that one messaged line into the "Full Message" field.

Attached is a sample of what the data looks like coming in, and what I want the outcome to be.

Any help is GREATLY appreciated!

Thanks
Joe

 

[MajP]

This gets real confusing moving back and forth through the sequence checking if you are at the end of a sequence or end of the file, but you can make this real easy.

You can first go through the list and uniquely number each message series. You could add a column for this and make it even easier. This then allows you you to grab each series individually. Makes the logic a lot simpler. Basically each message series is getting a unique key.

Public Sub fullMessage()
  Dim rs As DAO.Recordset
  Dim strSql As String
  Dim maxCounter
  Dim Sequence As Integer
  Dim fullMessage As String
  Dim SequenceCounter As Long
  Dim writeID As Long
  strSql = "Select * from tbl_Messages order by ID"
  Set rs = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
  
  'Uniquely number the series first, you could use a seperate field for this
  'I reused the [Full Message] field
  Do While Not rs.EOF
    Sequence = rs![message sequence]
    If Sequence = 1 Then
      SequenceCounter = SequenceCounter + 1
    End If
    rs.Edit
      rs![full message] = SequenceCounter
    rs.Update
    rs.MoveNext
 Loop
 maxCounter = SequenceCounter
 
 'Now that each message series has a unique ID it is easier to work with
 For SequenceCounter = 1 To maxCounter
   strSql = "select * from tbl_Messages where [Full Message] = '" & SequenceCounter & "' order by ID"
   Set rs = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
   Do While Not rs.EOF
     If rs![message sequence] = 1 Then writeID = rs!ID
     If fullMessage = "" Then
       fullMessage = rs![Message]
     Else
       fullMessage = fullMessage & " " & rs![Message]
     End If
     'remove numbers. This would be easier if using a seperate column
     rs.Edit
       rs![full message] = Null
     rs.Update
     rs.MoveNext
   Loop
   strSql = "Update tbl_Messages set[full message] ='" & fullMessage & "' where ID = " & writeID
   CurrentDb.Execute strSql
   fullMessage = ""
 Next SequenceCounter
End Sub

 

[firebirdta84]

MajP - thanks so much for your reply. I'm going to try it shortly and I will let you know the results!

Joe

 

[MajP]

After some thought here is a really simple version. Sometimes you have to step back and think of another way. It is difficult to read it forward but simple to read it backwards.

Public Sub fullMessage()
  Dim rs As DAO.Recordset
  Dim strSql As String
  Dim fullMessage As String
  strSql = "Select * from tbl_Messages order by ID [b]desc[/b][i][/i]"
  Set rs = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
  
  Do While Not rs.EOF
    If fullMessage = "" Then
      fullMessage = rs!Message
    Else
      fullMessage = rs!Message & " " & fullMessage
    End If
      If rs![Message Sequence] = 1 Then
        rs.Edit
          rs![Full Message] = fullMessage
        rs.Update
        fullMessage = ""
      End If
    rs.MoveNext
  Loop
 End Sub

The reason this is simpler is that the record you write to is the last record in the sequence, instead of the first where you would have to then come back to it.

 

[dhookom]

I would use the generic concatenate function found in the FAQs faq701-4233.

SELECT [Report Number], [Message Sender],
Concatenate("SELECT Message FROM [tbl_Messages] WHERE [Report Number] = " & [Report Number] & " ORDER BY ID"," ") As FullMessage
FROM [YourTable]
GROUP BY [Report Number], [Message Sender],
Concatenate("SELECT Message FROM [tbl_Messages] WHERE [Report Number] = " & [Report Number] & " ORDER BY ID"," ");

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[MajP]

Duane,
That will not work since you can have multiple messages from multiple users within the same report. So for 1234 you would get one big conversation instead of 3 full messages. However, you could do what I suggested first and uniquely ID the message series, then you could apply the technique you describe using the unique ID.

 

[firebirdta84]

MajP - Brilliant! Your code to analyze the data in reverse worked perfectly. Thank you very much!

Joe