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
 
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
 
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.
Similar threads
Thread starter Title Forum Replies Date
D populating listbox on custom form from Access Outlook VBA and Custom Forms 7
A Outlook Reminders not populating for certain events Using Outlook 2
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
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
J Outlook 365 Add keywords from Listbox to the message body where cursor is placed Outlook VBA and Custom Forms 0
O VBA Cases with Listbox - Can you use Multi-Select? Outlook VBA and Custom Forms 4
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 Using Outlook 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
Victor_50 Problem - Google Workspace will stop "unsafe" access to Outlook end 2024 Using Outlook 3
A Quick Access Toolbar Not Showing Description Using Outlook 0
W Outlook 365 File access denied attempting to import .pst Using Outlook 6
J Recover server side rules from OST/PST without access to the server Using Outlook 2
richardwing Outlook 365 VBA to access "Other Actions" menu for incoming emails in outlook Outlook VBA and Custom Forms 0
V How to add 'Previous Item' and 'Next Item' to the Quick Access Toolbar Using Outlook 1
J Text icon in Quick Access toolbar ? Using Outlook 2
Z Import Tasks from Access Using VBA including User Defined Fields Outlook VBA and Custom Forms 0
klpconsulting Programmatic Access Office 365 Pro Plus & RDS Using Outlook 1
Fozzie Bear Shared Public Folders Access and Use Exchange Server Administration 0
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

Similar threads

Back
Top