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.
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
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
F Outlook 2016 bulk email from access Outlook VBA and Custom Forms 3
J Programmatic access grayed out Using Outlook 10

Similar threads

Top