Using Access 2007 (adodb) data to fill a user form

[LA Lawyer]

I am able to access my Access 2007 data, but the commands to add the item in
the control fails; I have moved the with/end with commands to different
places, but none works. Obviously, I am running this when the form
initializes. Here is my code with the bad code noted:

BAD---With CaseNames
Dim cn As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim SqlStr As String
SqlStr = "SELECT * FROM " & TableName & " WHERE ([ActiveCase] = True) ORDER
BY CaseName"
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & _
DBFullName & ";"
Set Rs = New ADODB.Recordset

With Rs
.Open SqlStr, cn, adOpenStatic, adLockOptimistic, adCmdText
Dim rcd As Record
Do Until Rs.EOF
.AddItem Rs!casename
Rs.MoveNext
Loop
End With
End With
Rs.Close
Set Rs = Nothing
cn.Close
Set cn = Nothing
BAD ----End With

 

[Doug Robbins - Word MVP]

See http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[LA Lawyer]

Thank you!

See http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

I am able to access my Access 2007 data, but the commands to add the item
in the control fails; I have moved the with/end with commands to
different places, but none works. Obviously, I am running this when the
form initializes. Here is my code with the bad code noted:

BAD---With CaseNames
Dim cn As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim SqlStr As String
SqlStr = "SELECT * FROM " & TableName & " WHERE ([ActiveCase] = True)
ORDER BY CaseName"
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & _
DBFullName & ";"
Set Rs = New ADODB.Recordset

With Rs
.Open SqlStr, cn, adOpenStatic, adLockOptimistic, adCmdText
Dim rcd As Record
Do Until Rs.EOF
.AddItem Rs!casename
Rs.MoveNext
Loop
End With
End With
Rs.Close
Set Rs = Nothing
cn.Close
Set cn = Nothing
BAD ----End With