String Building Error (1 Viewer)

cage4000

Registered User.
Local time
Today, 05:35
Joined
Oct 14, 2015
Messages
49
Greatings everyone,

I'm looking to build my String with all the values of the field "DmgTypeNCom" from my recordset but i'm having no luck. The String should look like this:

"Concealed" & ";" & "Dropping" & ";" & "Forklift" & ";" & "On Divert" & ";"

but instead it only gives me the last record of "On Divert" in the String. Any help is most appreciated.


Code:
Dim strSQL As String
Dim rs As DAO.Recordset
    
On Error Resume Next


       strSQL = "SELECT tblAdjDmgCommDropDown.DmgTypeNCom FROM tblAdjDmgCommDropDown " & _
                "WHERE (((tblAdjDmgCommDropDown.AdjCode)='" & Me.AdjCode.Value & "')) " & _
                "ORDER BY tblAdjDmgCommDropDown.DmgTypeNCom;"


Set rs = sqlDB().OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

Dim GroupThis As String

With rs
        .MoveFirst
        Do Until .EOF
        
            GroupThis = !DmgTypeNCom & ";"
            .MoveNext
        Loop
    End With

   Me.DmgType.RowSource = GroupThis ' set new RowSource for combobox
   Me.DmgType.Requery              ' referesh it
   Me.DmgType.Enabled = True       ' make sure it's enabled
   Me.DmgType.SetFocus
 

MarkK

bit cruncher
Local time
Today, 05:35
Joined
Mar 17, 2004
Messages
8,178
This line...
Code:
   GroupThis = !DmgTypeNCom & ";"
See how the result of the previous loop, as stored in GroupThis, is overwritten in this iteration of the loop? To concatenate, you will need GroupThis to appear also on the right side of the equals sign, so that data from previous loops is not overwritten.
hth
Mark
 

plog

Banishment Pending
Local time
Today, 07:35
Joined
May 11, 2011
Messages
11,611
You are using GroupThis is like a chalk board you write a value on, erase and then write a new value on, erase write a new value on and then expect it in the end to hold the entire value somehow.

Don't erase the old value everytime you add a new value.

GroupThis = GroupThis & NewValue
 

cage4000

Registered User.
Local time
Today, 05:35
Joined
Oct 14, 2015
Messages
49
That worked perfectly! thank you MarkK and plog, you both rock!
 

Users who are viewing this thread

Top Bottom