Populating listbox with MS Access table

Status
Not open for further replies.

Techworks

Member
Outlook version
Outlook 2013 64 bit
Email Account
Exchange Server
I have the following code to populate a listbox in Outlook from an MS Access database table

------------------------

Dim db As DAO.Database

Dim rs As DAO.Recordset

Dim strSQL As String

Dim rsArray As Variant

strSQL = "SELECT tbl_PJ_Project.PJ_Name, tbl_PJ_Project.PJ_ProjectUID " & _

"From tbl_PJ_Project " & _

"WHERE (((tbl_PJ_Project.PJ_Name) Like '*" & Me.txtSearchProjName & "*')) " & _

"ORDER BY tbl_PJ_Project.PJ_Name;"

'Debug.Print strSQL

'Stop

Set db = OpenDatabase(fncProjectDB)

Set rs = db.OpenRecordset(strSQL)

If rs.RecordCount > 0 Then

'there are records so do something

rsArray = rs.GetRows

Else

'no records do something else

rsArray = "------No results found--------"

End If

'Place data in the listbox

With Me.lstResult

.Clear

.ColumnCount = 2

.BoundColumn = 2

.Column = rsArray

.ListIndex = -1

End With

rs.Close

Set rs = Nothing

Set db = Nothing

----------------------

Problem is its only populating the listbox for the first record.

Any suggestions why this is

Thanks Grant
 
E

EricLegault

Maybe try looping through the recordset instead?

e.g.

Code:
Do Until  rs.EOF = True[INDENT]lstResult.AddItem(rs.Fields("MyField").Value)[/INDENT] 
[INDENT]rs.MoveNext[/INDENT] 
 Loop
 

Techworks

Member
Outlook version
Outlook 2013 64 bit
Email Account
Exchange Server
ok figured out why not working. Obviously most of the examples out there are not real world solutions that work as they have flawed code.

I finally managed to find one example that was different and it works. The rst.GetRows line need to specify how many rows to return, so should look like this:
rsArray = rst.GetRows(lngRowCount)
 
Status
Not open for further replies.
Thread starter Similar threads Forum Replies Date
D populating listbox on custom form from Access Outlook VBA and Custom Forms 7
T populating multiple calendars without invites Using Outlook 1
microsvc Populating everyone's calendar with campus events Exchange Server Administration 1
S Custom field not populating in Custom View or Mail Merge Exchange Server Administration 2
B Populating Additional Outlook Column with Date \ Time Using Outlook 0
O Reply and Reply All not populating the To: field Using Outlook 5
M Managed Folder not populating? Exchange Server Administration 2
T E-mail addresses not populating when first letter is typed in address bar ??? they used to ?? Using Outlook 2
T How to stop outlook from auto populating names when i type email addresses Using Outlook 1
R My email merge/business contact manager fields are not populating BCM (Business Contact Manager) 1
V populating worksheet with Outlook mail Outlook VBA and Custom Forms 2
T populating fields with information from the address book Outlook VBA and Custom Forms 5
M BCM History 'From' field not populating BCM (Business Contact Manager) 2
A Populate Listbox from Txt File Outlook VBA and Custom Forms 0
A Multi-select Listbox Outlook VBA and Custom Forms 6
K Selecting a folder from an entry in a Listbox Outlook VBA and Custom Forms 1
G How to Copy Multi Select Listbox Data to Appointment Outlook VBA and Custom Forms 3
Diane Poremsky Outlook VBA: Use a Text File to Populate a ListBox New Slipstick.com Articles 0
F Adding textbox filter to listbox? Outlook VBA and Custom Forms 2
G Colors in Listbox Outlook VBA and Custom Forms 2
T Listbox bound Outlook VBA and Custom Forms 6
G How to add a folder shortcut to outlook quick access toolbar? Using Outlook 6
D Lost Access to Custom Form Outlook VBA and Custom Forms 4
Jennifer Murphy Grant R/W (update) access to a friend Using Outlook 3
O The page that you are trying to access cannot be loaded. Using Outlook 0
T Render Outlook emails in MS access as they appear in Outlook Outlook VBA and Custom Forms 2
T Outlook "A program is trying to access Outlook" Using Outlook 3
GregS Can't access archive file Using Outlook 5
J What is the best EntryID format to set on MS Access table Outlook VBA and Custom Forms 3
J No access to a Database BCM BCM (Business Contact Manager) 3
R How can I access my emails from corrupt offline files of MS Outlook ? Using Outlook 1
V Change default default save location to Quick Access Using Outlook 1
R Quick Access view in File Explorer when saving attachments Using Outlook 0
D Disable or hide "reply" and "reply to all" and "forward" in email from access vba Outlook VBA and Custom Forms 1
J Open an outlook email by Subject on MS Access linked table with VBA Outlook VBA and Custom Forms 10
G Windows Update Causes BCM Database Access Problem? BCM (Business Contact Manager) 4
O Synchronize safe persons Outlook 2016 -> Outlook Web Access (OWA) Using Outlook 30
K How to access emails found to be located in "Top of Outlook data file"? Using Outlook 3
R Categorize Button in Quick Access Toolbar Disappears on New Email Using Outlook 1
O Outlook Web Access - how to disable spam filter Using Outlook 6
T Lost access to remote BCM database BCM (Business Contact Manager) 4
F Outlook 2016 bulk email from access Outlook VBA and Custom Forms 3
J Programmatic access grayed out Using Outlook 10
Diane Poremsky Synchronize Quick Access Toolbar and Ribbons? New Slipstick.com Articles 0
G Can't open .pst. Message could not access default folder (Outlook 2010 (.pst). Before that was backi Using Outlook 0
D Creating an outlook session from Access vba but run silently. With A specific profile Outlook VBA and Custom Forms 1
T Cannot access outlook.com accounts in Outlook 2016 Using Outlook 1
B Can't expose sender email address when linking outlook to access Using Outlook 3
Diane Poremsky Change Outlook's Programmatic Access Options New Slipstick.com Articles 0
S how to access the properties of a contact given distlist.member object Outlook VBA and Custom Forms 1
Similar threads


















































Top