Solved: Access Database feeding word listbox and excecuting word procedure
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
