populate listbox from Access Database

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How can I populate a listbox in a userform in outlook using values from a
database in access? The field in access has 50+ entries, and I have been
trying to figure out how to use the "add item" method in a while loop, but
can't figure out how to get to the next record in my recordset in the macro.
Thanks for your help.
 
Am Mon, 15 Aug 2005 17:59:42 -0700 schrieb stevo:

Stevo, if you´re using ADO then the method is called MoveNext.
 
Can you post your code? I'd like to use it in my own solution. If you're
still having problems with it, I'll probably end up fixing them.
 
I am very much a novice at this, and I'm not finished yet, but this is what I
have so far. I hope it helps:

Sub AccessSelData()
On Error Resume Next

Dim myOlSel As Outlook.Selection
Dim msgbody As String
Dim msg As Object
Dim course As String
Dim msgname As String
Dim msgdate As Date
Dim msgemail As String

Set myOlSel = Outlook.ActiveExplorer.Selection
' I want to be able to export more than one message body at a time, but
haven't gotten it to work yet

'For x = 1 To myOlSel.Count
' Seltxt = myOlSel.Item(x).Body
'Next x

x = myOlSel.Count

Set msg = myOlSel
msgbody = msg.Item(x).Body
msgname = msg.Item(x).SenderName
msgdate = msg.Item(x).SentOn
msgemail = msg.Item(x).SenderEmailAddress

'the DAO reference is from a code example on www.outlookcode.com


Set appAccess = CreateObject("Access.Application")
strAccessPath = appAccess.SysCmd(9)
strDBName = "d:\Documents and Settings\swh24\My Documents\Course
Analysis Database files\" & "Course Analysis.mdb"
Set dbe = CreateObject("DAO.DBEngine.36")
Set wks = dbe.Workspaces(0)
Set dbs = wks.OpenDatabase(strDBName)
'These are the names of the table data in Access
Set rst = dbs.OpenRecordset("Course Comments")
Set rst1 = dbs.OpenRecordset("Course Analysis-Complete")
Load frmDatabaseAdd 'this is a userform that I created to check the
data

rst1.MoveFirst
Do
frmDatabaseAdd.cboCourse.AddItem (rst1.course.Value)
rst1.MoveNext
If rst1.course.Value = "" Then Exit Do
Loop

frmDatabaseAdd.Show
rst.AddNew
rst.course = course
rst.SendName = msgname
rst.SendDate = msgdate
rst.Comment = msgbody
rst.Update
rst.Close
dbs.Close

MsgBox "Information sent to Course Analysis Database"

End Sub
 
How do you set up an ADO reference? I copied a DAO reference from Sue
Mosher's site, and it works, but I don't really understand what I am doing.
See my reply to David for my code.
BTW- Thanks for the help- it was exactly what I needed.
 
Some notes...

the rs1.moveFirst is no neccessary
also the Access geeks like me have pref for

while NOT rs1.EOF
wend

as the loop of preference in that it checks if any records were returned
by rs1.
Also if you're not going to use rs until later down, you may want to
move the >Set rst = dbs.OpenRecordset("Course Comments")
statement immediately before the .AddNew.

Also you never close rs1. As a side note, its also prefered to explicity
set the object variables to NOTHING once your finished with them. Rumor
has it that VBA not automatically destroys object variable once they go
out of scope, but then do you really trust MS? There's no harm in
explicity setting them.
 
..MoveNext applies to both DAO and ADO. Far as which one is preferred,
DAO is the native the Jet database engine, ADO came about as a means to
provide an access data in a more 'open' environment. ADO was intended to
replace DAO, however it appears that ADO has not quite lived up to
expectations. As long as your developing within the Office family of
products, I would stick with DAO.
 
Thanks for the suggestions, I made the changes, and looks like I'm set--I
especially liked the "While Not rst1.EOF...Wend". That is smoother.
 
Am Tue, 16 Aug 2005 15:03:09 -0400 schrieb David C. Holley:

Thanks for jumping in.

Important to mention are the ability of disconnected recordsets with
ADO.
 
Am Tue, 16 Aug 2005 11:41:42 -0700 schrieb stevo:

Stevo, you can add a ref on it by checking "Microsoft ActiveX Data
Objects 2.x"
 
Back
Top