Solved: Access Database feeding word listbox and excecuting word procedure

[buhay]

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]
Private Sub UserForm_Initialize()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long
' 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]

- 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]

- the three procedures:

[VBA]

Sub Sabine()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long
' 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 As Long
' 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 As Long
' 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]

- 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

[geekgirlau]

Actually once you've populated the list box, you shouldn't need to call the database again.

[vba]MsgBox "Hello " & UserForm1.ListBox1.Text[/vba]

should display the selected name in the message.

If you want to capture additional fields from the mailing list, you add those fields to your original SELECT statement:

[VBA]"SELECT [Clients Name], [Field2], [Field3] FROM [Mailing List]"[/VBA]

You can set the additional columns as hidden for the list box (width=0), and then refer to those values using Columns. For example, to show the value in the 2nd column,

[VBA]MsgBox UserForm1.ListBox1.Columns(1)[/VBA]

[buhay]

Actually once you've populated the list box, you shouldn't need to call the database again.

[vba]MsgBox "Hello " & UserForm1.ListBox1.Text[/vba]

should display the selected name in the message.

If you want to capture additional fields from the mailing list, you add those fields to your original SELECT statement:

[vba]"SELECT [Clients Name], [Field2], [Field3] FROM [Mailing List]"[/vba]

You can set the additional columns as hidden for the list box (width=0), and then refer to those values using Columns. For example, to show the value in the 2nd column,

[vba]MsgBox UserForm1.ListBox1.Columns(1)[/vba]

geekgirlau, thank you very much for your help

I really appreaciate it