Concat Listbox selected fields to comma delimited single record (1 Viewer)

[Rx_]

This is just another example. But, here is a noteworthy example of developing a table in MS Access with the intention of moving it to SQL Server.

Of interest, this worked perfect when the table was prototype on MS Access table (the rest were linked to SQL Server).
Moving the single table from Access to Microsoft SQL Server Migration Assistant for Microsoft Access - the code did not work. (SQL Server Native Client 10.0)

By adding the rst.movelast and rst.movefirst (see comment below) the code worked again. Otherwise, the rst.count was always zero or 1.

The ID_Wells is much like a Customer_ID. A list box has several items associated to a ID_Well. This simply takes zero to many records selected fields and creates a single comma delimited record.

Option Compare Database
Option Explicit
' Use this in a form to read a list box for 1 well and then update a read-only field for a legacy text box
' that has several reports tied to that legacy text box
' To use in Update Query: UPDATE Wells_Lease SET Wells_Lease.Dir_HzPass = ConCat_hz([ID_Wells]);
Public Function ConCat_hz(ID_Wells) As String
      ' Pass in ID - filter only the Active A in Activity column
      Dim rst As DAO.Recordset
      Dim strSQL As String
      Dim OutPutString As String
      Dim CountX As Integer
      Dim Well_ID_NO As String
      ' Request on 1/16 to remove the field(0) Federal, state, tribal...
10    On Error Resume Next
        Well_ID_NO = ID_Wells
20    strSQL = "SELECT Wells_Lease_Type.Lease_Type, Wells_Lease_DirHz.Dir_HzPass, Wells_Lease_DirHz.Desc " & _
      " FROM Wells_Lease_DirHz INNER JOIN Wells_Lease_Type ON Wells_Lease_DirHz.HZ_Type_Num = Wells_Lease_Type.ID_Lease_Type " & _
      " WHERE(((Wells_Lease_DirHz.Activity) = 'A') And ((Wells_Lease_DirHz.ID_Wells) = " & Well_ID_NO & " )) " & _
      " ORDER BY Wells_Lease_DirHz.Wells_Lease_DirHz_ID DESC;"
30            OutPutString = ""
40            Set rst = CurrentDb.OpenRecordset(strSQL, 2, dbSeeChanges)
                ' added the next two lines for sql server - worked on msaccess table but not on sqlserver
                rst.MoveLast
                rst.MoveFirst
50            If rst.RecordCount > 0 Then
60                For CountX = 1 To rst.RecordCount
70                    OutPutString = OutPutString & rst.Fields(1) & IIf(CountX <> rst.RecordCount, ", ", "")
       ' to add another field use  rst.Fields(1) & " " & rst.Fields(2)
        '     the & " " &   can be substituted with a " - " or another delimieter
                      rst.MoveNext
80                Next CountX
90            End If
100           rst.Close
110       ConCat_hz = OutPutString
        Debug.Print ConCat_hz
End Function