Build a recordset and populate listbox

[dabruins]

I have a text file that contains record information that i would like to display within a listbox on a form. I've written code to parse out the data within the file. In order to display the parsed data within the listbox I thought I should construct an ADODB.Recordset with the appropriate fields and then add each record in turn. After adding all of the rows into the recordset i assumed i should be able to then set the recordset of the list box using the new recordset I constructed. When i do this I am not getting the data to appear in the list box. I am getting no errors thrown so maybe I am missing something here. I've set the list box Row Sorce type to Table/Query and left the Row Source unset.

Please see the following code.

Thanks.

Private Sub ParseSearchPlansFile(strfile As String, fso As Scripting.FileSystemObject)

Dim filPlanSearch As Scripting.File
Dim ts As TextStream

Set filPlanSearch = fso.GetFile(strfile)
Set ts = filPlanSearch.OpenAsTextStream(ForReading, TristateUseDefault)

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

rst.Fields.Append "planno", adVarChar, 10, adFldUpdatable
rst.Fields.Append "mer", adVarChar, 3, adFldUpdatable
rst.Fields.Append "rge", adVarChar, 4, adFldUpdatable
rst.Fields.Append "twp", adVarChar, 5, adFldUpdatable
rst.Fields.Append "sec", adVarChar, 4, adFldUpdatable
rst.Fields.Append "qtr", adVarChar, 12, adFldUpdatable
rst.Fields.Append "plantype", adVarChar, 25, adFldUpdatable
rst.Fields.Append "hasplan", adBoolean, adFldUpdatable
rst.Fields.Append "planfile", adVarChar, 255, adFldUpdatable

rst.Open

Do While Not ts.AtEndOfStream
s = ts.ReadLine

If Trim(s) = "" Then
Exit Do
End If

intPlannopos = InStr(s, " ")
Dim strPlanno As String
strPlanno = Trim(Left(s, intPlannopos))

intLocPos = InStr(s, ";") - 2
strLoc = Right(s, Len(s) - intLocPos)
arrLocsplit = Split(strLoc, ";")
strMer = arrLocsplit(0)
strRge = arrLocsplit(1)
strTwp = arrLocsplit(2)
strSec = arrLocsplit(3)
strQtr = arrLocsplit(4)

strDescRemainer = Left(s, intLocPos)
strPlanDesc = Right(strDescRemainer, Len(strDescRemainer) - intPlannopos)

blnFound = False

If fso.FileExists(strFilepathCal) Then
strPlanFile = strFilepathCal
blnFound = True
Else
strPlanFile = ""
End If

Next

With rst
.AddNew "planno", strPlanno
.Update
.Fields("mer") = strMer
.Fields("rge") = strRge
.Fields("twp") = strTwp
.Fields("sec") = strSec
.Fields("qtr") = strQtr
.Fields("plantype") = strPlanDesc
.Fields("hasplan") = blnFound
.Fields("planfile") = strPlanFile
.Update
End With

Loop

Set Me.lstSearchedPlans.Recordset = rst

 

[dabruins]

It seems that all of the rows added to the recordset are returned in the listbox however they are all Null values????

 

[dabruins]

I found that you have to set the lock type of the recordset.
rst.LockType = adLockOptimistic

This works now. Hope this helps someone else?