Populate a listbox using DAO

[Peter L Kessler]

Hi Group

I don't know if I'm posting to the right place here but perhaps someone
could help me out.

I've just read the Word MVP feature "Load a ListBox from a Named Range in
Excel using DAO." I needed it to display the data from an Excel Range in my
Word 2000 VBA listbox, which it does. But, the line:

Set myRecordSet = myDatabase.OpenRecordset("SELECT * FROM `DataList`")
'DataList is the named range

causes me a problem in that I need to be able to specify the range name
elsewhere and apply it here as a string. This line won't work that way, so
would anyone be able to show me how to do this, please?

Best wishes
Peter




Kessler Associates
E: (e-mail address removed)
W: http://homepages.tesco.net/~plk33/plk33/index.htm

 

[Peter L Kessler]

Hello Again

After fiddling about with the syntax I stumbled upon the solution! All I did
was change:

Set myRecordSet = myDatabase.OpenRecordset("SELECT * FROM `DataList`)

to:

Set myRecordSet = myDatabase.OpenRecordset("SELECT * FROM " + myString)

and it worked.

Now I need a hand in displaying the data properly, so that it appears in the
format:

[company name][comma][space][contact name]

in just one column, with the other columns not displayed.

Despite my piece of luck above, I'm still fairly thick at this so any help
would be much appreciated.

Best wishes
Peter




Kessler Associates
E: (e-mail address removed)
W: http://homepages.tesco.net/~plk33/plk33/index.htm

 

[Jezebel]

Now I need a hand in displaying the data properly, so that it appears in the
format:

[company name][comma][space][contact name]

The results of your query are available in the fields of your recordset. If
Company name and contact name are the first and second columns in the source
data, use something like

Output = myRecordSet(0) & ", " & myRecordset(1)

 

[Peter L Kessler]

Hello Jezebel

Thanks for the help. The listbox now displays my data correctly.

Best wishes
Peter




Kessler Associates
E: (e-mail address removed)
W: http://homepages.tesco.net/~plk33/plk33/index.htm

Now I need a hand in displaying the data properly, so that it appears in the
format:

[company name][comma][space][contact name]

The results of your query are available in the fields of your recordset. If
Company name and contact name are the first and second columns in the source
data, use something like

Output = myRecordSet(0) & ", " & myRecordset(1)