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

[Frosty]

I think your methodology is flawed, but without the database, I can't troubleshoot exactly what is going wrong (I also am not sure why your have a Private Sub UserForm_Initialize in your Module1, rather than the userform code).

But I think it is because your initial grabbing of data has no explicit sort, and yet when you actually call the macros you assume Sabine was first, Rolf second and David third.

I seem to recall that SQL queries (and, by extension, access queries) do not necessarily return queries in the same order they exist in the database. And even if they do, you have made an assumption which is likely to get you into trouble (since, even if this worked... someone could insert "Jack" between Sabine and Rolf, and then you're in trouble again).

Quick fix: you need to return more than the Client Name from the database when you populate the listbox... return the keyfield as well (probably that number), and then check that column value (rather than the listindex, although you still need to check whether it is -1 in order to make sure something is selected) in order to determine which procedure to run.

Big picture:
I think it would be better for you to set your structure up like this:

(pseudo code)
[vba]
Sub MainRoutine
dim f as UserForm1

'call your database one time
set db = db.OpenRecordset ("blahblah sql query")

'load your form, but don't show it by doing the following
set f = new UserForm1
'set your list to the database above here
f.Listbox1.Column yada yada

'now show it-- fyi, doing UserForm1.Show is bad practice,
'a longer post would explain why -- but I'm short on time
f.Show

'take care of validating whether your ListBox1.ListIndex is > -1 before
'allowing the user to dismiss the form (so CommandButton1_Click should
'only have your Case -1 code, and then Me.Hide

'then run the rest of your code, based on the f.ListBox1 column values
'using the db you've opened in this routine, passing the db to your
'Sabine/Rolf/etc routines.

'The more times you open close a database with hard coded queries
'the more likely you are to have a typing mistake
'just open it once, and use what you need, then get out

'do your clean up of the db here

End Sub
[/vba]
Couple additional comments:
1. Don't reference Userforms by their exact name... set a variable to the userform name, then explicitly set it to a new reference. Otherwise you can end up with multiple instances of the userform if something causes it to fall out of scope. For example:

Userform1.Show
'then user hits the Red X, unloading the fomr
If Userform1.ListIndex = - 1
msgbox "please select something"
End If

You would get a messagebox saying "Please select something" every single time your user used the Red X to close the form... because you reloaded it with the second reference... what you'd prefer to get is an error saying it doesn't know what object you're talking about, so that you can error trap what to do when the user gets a Red X.

2. I don't know if you naming conventions were simply for the mockup... but you should always name your userforms and code routines to something other than the microsoft standard Listbox1 type stuff. It's a lot easier to troubleshoot, especially if you're asking others to do the troubleshooting for you It's also easier to divine what you're actually doing, since this is clearly fake code you've uploaded.

3. Use indents and make your code readable. The vba tags on this website made the code somewhat readable... but your actual project is awful to read. I won't advocate a particular style, but I will advocate *some* kind of coding style. The following is not a style:

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

etc etc

Hope that all helps.

[Frosty]

Should have checked the cross post before posting-- looks like you got your answer in the Access forum (different approach, but also the same info-- call your DB once, and go from there).

Please provide links when you cross post, so people trying to help you for free don't have to go hunting down links.

[buhay]

Should have checked the cross post before posting-- looks like you got your answer in the Access forum (different approach, but also the same info-- call your DB once, and go from there).

Please provide links when you cross post, so people trying to help you for free don't have to go hunting down links.

I just got an answer in the access forum after posting this thread.

Thank you as well for all your support

[buhay]

http://www.vbaexpress.com/forum/showthread.php?t=37023