Microsoft Outlook email - UserForm Listbox with Access Database Table data

Outlook Banter (http://www.outlookbanter.com/)

- Outlook - Using Forms (http://www.outlookbanter.com/outlook-using-forms/)

- - UserForm Listbox with Access Database Table data (http://www.outlookbanter.com/outlook-using-forms/42978-userform-listbox-access-database-table.html)

UserForm Listbox with Access Database Table data

Hi there,

I want to create a UserForm in Outlook containing a listbox. This
listbox should contain data stored in an access database table. I have
tried with following code, but get an error on the line with the
command to "Transpose" the array. Any ide...?

Many Thanks,
Roberto

************************************************** **********

Sub PopulateListBox1()

Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim acc As Access.Application
Dim rcArray As Variant
Dim sSQL As String

Set acc = CreateObject("Access.Application")

'Set the location of your database, the connection string and the SQL
query
sSQL = "SELECT tbl_Outlook.* FROM tbl_Outlook;"

'Open connection to the database
cnt.Open glob_sConnect

'Open recordset and copy to an array
rst.Open sSQL, cnt
rcArray = rst.GetRows

rst.MoveFirst
MsgBox rst!UserId

'Place data in the listbox
With Me.ListBox1
.Clear
.ColumnCount = 6
.List = acc.Transpose(rcArray)
.ListIndex = -1
End With

'Close ADO objects
rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing

End Sub

UserForm Listbox with Access Database Table data

Sorry, here's some additional info. I have defined the following
Global Constant as well as referenced the Microsoft ActiveX Data
Objects 2.7 Library.

Const glob_sdbPath = " DATABASE PATH "
Const glob_sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
& glob_sdbPath & ";"

Thanks,
Roberto

RobLo schrieb:

Hi there,

I want to create a UserForm in Outlook containing a listbox. This
listbox should contain data stored in an access database table. I have
tried with following code, but get an error on the line with the
command to "Transpose" the array. Any ide...?

Many Thanks,
Roberto

************************************************** **********

Sub PopulateListBox1()

Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim acc As Access.Application
Dim rcArray As Variant
Dim sSQL As String

Set acc = CreateObject("Access.Application")

'Set the location of your database, the connection string and the SQL
query
sSQL = "SELECT tbl_Outlook.* FROM tbl_Outlook;"

'Open connection to the database
cnt.Open glob_sConnect

'Open recordset and copy to an array
rst.Open sSQL, cnt
rcArray = rst.GetRows

rst.MoveFirst
MsgBox rst!UserId

'Place data in the listbox
With Me.ListBox1
.Clear
.ColumnCount = 6
.List = acc.Transpose(rcArray)
.ListIndex = -1
End With

'Close ADO objects
rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing

End Sub

UserForm Listbox with Access Database Table data

Does this statement work instead:

.Column = rcArray

For future reference, please include information on the nature of any errors, not just the fact that an error occurred.
--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003
http://www.turtleflock.com/olconfig/index.htm
and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/jumpstart.aspx

"RobLo" wrote in message oups.com...
Hi there,

I want to create a UserForm in Outlook containing a listbox. This
listbox should contain data stored in an access database table. I have
tried with following code, but get an error on the line with the
command to "Transpose" the array. Any ide...?

Many Thanks,
Roberto

************************************************** **********

Sub PopulateListBox1()

Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim acc As Access.Application
Dim rcArray As Variant
Dim sSQL As String

Set acc = CreateObject("Access.Application")

'Set the location of your database, the connection string and the SQL
query
sSQL = "SELECT tbl_Outlook.* FROM tbl_Outlook;"

'Open connection to the database
cnt.Open glob_sConnect

'Open recordset and copy to an array
rst.Open sSQL, cnt
rcArray = rst.GetRows

rst.MoveFirst
MsgBox rst!UserId

'Place data in the listbox
With Me.ListBox1
.Clear
.ColumnCount = 6
.List = acc.Transpose(rcArray)
.ListIndex = -1
End With

'Close ADO objects
rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing

End Sub

UserForm Listbox with Access Database Table data

Hi Sue,
That worked just perfectly now. Many thanks!
Best regards,
Roberto

Sue Mosher [MVP-Outlook] schrieb:

Does this statement work instead:

.Column = rcArray

For future reference, please include information on the nature of any errors, not just the fact that an error occurred.
--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003
http://www.turtleflock.com/olconfig/index.htm
and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/jumpstart.aspx

"RobLo" wrote in message oups.com...
Hi there,

I want to create a UserForm in Outlook containing a listbox. This
listbox should contain data stored in an access database table. I have
tried with following code, but get an error on the line with the
command to "Transpose" the array. Any ide...?

Many Thanks,
Roberto

************************************************** **********

Sub PopulateListBox1()

Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim acc As Access.Application
Dim rcArray As Variant
Dim sSQL As String

Set acc = CreateObject("Access.Application")

'Set the location of your database, the connection string and the SQL
query
sSQL = "SELECT tbl_Outlook.* FROM tbl_Outlook;"

'Open connection to the database
cnt.Open glob_sConnect

'Open recordset and copy to an array
rst.Open sSQL, cnt
rcArray = rst.GetRows

rst.MoveFirst
MsgBox rst!UserId

'Place data in the listbox
With Me.ListBox1
.Clear
.ColumnCount = 6
.List = acc.Transpose(rcArray)
.ListIndex = -1
End With

'Close ADO objects
rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing

End Sub