Populating a list box from a database

[Dingbat]

Can I populate a list box in word from values within an access database?

I am using Microsoft Word 2002/Access 2002.

I want to be able to dynamically build the values within the list box from
the result of a query within access

 

[Doug Robbins]

Use the following method (You need to set a reference to the DAO object
library)

Private Sub UserForm_Initialize()

Dim db As DAO.Database

Dim rs As DAO.Recordset

Dim NoOfRecords As Long

' Open the database

Set db = OpenDatabase("D:\Access\ResidencesXP.mdb")

' Retrieve the recordset

Set rs = db.OpenRecordset("SELECT * FROM Owners")

' Determine the number of retrieved records

With rs

.MoveLast

NoOfRecords = .RecordCount

.MoveFirst

End With

' Set the number of Columns = number of Fields in recordset

ListBox1.ColumnCount = rs.Fields.Count

' Load the ListBox with the retrieved records

ListBox1.Column = rs.GetRows(NoOfRecords)

' Cleanup

rs.Close

db.Close

Set rs = Nothing

Set db = Nothing

End Sub


--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP

 

[Mara M. Smith]

Doug,
I like your solution below. I tried it and it worked. I was wondering if
you or anyone else could provide a solution to one of my problems related to
this. I will be using the code you used when the form initializes but I used
my access database to populate a 'combo box'.

After making a selection in the 'combo box', if I click 'ok' on my user
form, I would like to pull a specific field(for example, the phone number of
the person I selected in the combo box) from that same database that
populated the 'combo box'. I would then want to insert that phone number in
a 'bookmark' named 'Phone_Number' in the Word file I am using.

How can I do this?

Thanks,
MM

 

[Alex Ivanov]

before you close the form, run
activedocument.Bookmarks("Phone_Number").Range.Text=combobox1.text

 

[Doug Robbins]

You first need to set the .BoundColumn property of the combobox to the
column that contains the phone number.

--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP