|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Search this Thread | Display Modes |
|
|
|
#1
March 9th 07, 10:40 AM
posted to microsoft.public.outlook.program_forms
|
|||
|
|||
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 
|
|
#2
March 9th 07, 10:44 AM
posted to microsoft.public.outlook.program_forms
|
|||
|
|||
|
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
|
|
#3
March 9th 07, 10:45 AM
posted to microsoft.public.outlook.program_forms
|
|||
|
|||
|
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
|
|
#4
March 9th 07, 02:35 PM
posted to microsoft.public.outlook.program_forms
|
|||
|
|||
|
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
|
|
#5
March 9th 07, 03:47 PM
posted to microsoft.public.outlook.program_forms
|
|||
|
|||
|
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
|
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Using data in Access database in outlook forms | Weatherman | Outlook and VBA | 6 | April 20th 06 12:46 AM |
| getting data from Access Database in Outlook Form | Weatherman | Outlook - Calandaring | 1 | April 17th 06 01:49 PM |
| How do I import email addresses from an Access table? | Chris | Outlook - Using Contacts | 1 | April 7th 06 01:58 PM |
| Need to add a field to my contacts list and data table in Outlook | Bill_De | Outlook - Using Contacts | 1 | March 13th 06 06:26 PM |
| Can custom form data populate access database | jbtempe | Outlook - Using Forms | 1 | January 20th 06 03:02 PM |
Hybrid Mode
