Solved: Feeding word listbox from access database and excecuting word procedure

[buhay]

I have already posted this thread in the access forum but without any success. Maybe someone from the word forum is able to help me out on this.

I have created a word userform listbox linked to the source from an access database.

How do I make the word macros select the right source from the accessdatabase.

I am using the following procedures in word macro modules:

- to call and populate the listbox

[vba]
PrivateSub UserForm_Initialize()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords AsLong
' Open the database
Set db = OpenDatabase("h:\My Documents\Ha Dang\Macro Test\Access Database\Test.mdb")
' Retrieve the recordset
Set rs = db.OpenRecordset("SELECT [Clients Name] FROM [Mailing List]")
' Determine the number of retrieved records
With rs
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With
' Set the number of Columns = number of Fields in recordset
UserForm1.ListBox1.ColumnCount = rs.Fields.Count
' Load the ListBox with the retrieved records

UserForm1.ListBox1.Column = rs.GetRows(NoOfRecords)
UserForm1.Show

' Cleanup
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub
[/vba]

VBA tags courtesy of www.thecodenet.com

- To execute the macro in the listbox by button click:

[vba]Private Sub CommandButton1_Click()
Select Case ListBox1.ListIndex
Case -1
MsgBox "Select a Client from the list."
Exit Sub

Case 0: Call Sabine
Case 1: Call Rolf
Case 2: Call David

End Select

'Unload UserForm1
End Sub
[/vba]

VBA tags courtesy of www.thecodenet.com

- the three procedures:

[vba]
Sub Sabine()
Dim db As DAO.Database
Dim rs As DAO.Recordset Dim NoOfRecords AsLong
' Open the database
Set db = OpenDatabase("h:\Macro Test\Access Database\Test.mdb")
Set rs = db.OpenRecordset("SELECT [Clients Name] FROM [Mailing List]")

' Retrieve the recordset


MsgBox "hello " & rs.Fields("Clients Name")
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub
Sub Rolf()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords AsLong
' Open the database
Set db = OpenDatabase("h:\Macro Test\Access Database\Test.mdb")
Set rs = db.OpenRecordset("SELECT [Clients Name] FROM [Mailing List]")

' Retrieve the recordset

MsgBox "hello " & rs.Fields("Clients Name")
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub
Sub David()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords AsLong
' Open the database
Set db = OpenDatabase("h:\Macro Test\Access Database\Test.mdb")
Set rs = db.OpenRecordset("SELECT [Clients Name] FROM [Mailing List]")

' Retrieve the recordset

MsgBox "hello " & rs.Fields("Clients Name")
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub
[/vba]

VBA tags courtesy of www.thecodenet.com

- the access database (Test.mdb - Mailinglist) is set up as follows:

Number - Clients Name
1 - Sabine
2 - David
3 - Rolf

- The listbox is populated with the following items:

- Sabine
- David
- Rolf

How do I amend the procedures Sabine, David and Rolf, so that by choosing Sabine in the userform textbox,pops up the message "hello Sabine" and by choosing Rolf pops up the message "hello Rolf" and so on.

Any help is highly appreciated