query data into listbox? How?

[Morpheus]

I have a databse in access 2000. i'm running a query called mailinglist. It filters records and list clients according to my specs, that works fine.
Now on a form named mllist i have a command button called cmdmail & a list box called lstmail.

What i want to happen, is upon clicking cmdmail, it fills lstmail with the contents of the query. Below is the sql for the query.

I am totally lost and would appreciate any help.


------------------------------------------------------------------------------------
SELECT [Roll].[ContactID], [Roll].[LastName], [Roll].[FirstName], [Roll].[title], [Roll].[address], [Roll].[postal address]
FROM Roll LEFT JOIN [Find duplicates for Roll] ON [Roll].[address]=[Find duplicates for Roll].[address]
WHERE ((([Find duplicates for Roll].[address]) Is Null));
------------------------------------------------------------------------------------

How do i call the query from the button (I can do that) i just want to know about outputing it to the textbox.

All help appreciated.

[kaandorp]

Just query the database using ADO.
Walk to the resulting recordset with a loop.
For every record do a lstMail.listitem.add

And you're ready.
Pretty standard stuff...
Or read a tutorial on VB-world

[Wokawidget]

Have a look at this code. It has a crappy database and displays the query results in a msgbox, but you can't change that to a listbox...
Uses MDAC 2.0 drivers. You should already have them on ya machine. Just give it a run...

[Ecniv]

Well the easiest way is to set the list box to filter on the form (check the build option) so the filter needs the form open and say the text or combo box referenced. Then on the onclick/change get the list box to be requeried.


forms!frmName!cboName in the filter bit of the query.

Depends how you are doing the listbox, via code, or query or what?

Vince

[Wokawidget]

Now I am as confused as a fat fish in the middle of a pond full of girraffes...

[Ecniv]

Access - you can use the recordsource property - a query
Oooooor you can use coding to fill the list box (a little slower but gives those coders wonderful control over what goes into the listbox).

So I asked how you are filling the list box atm.


As to the filter/Sql :
Select ..... where address like '[forms]![frmName]![cboName]*' and....


Which needs refreshing after the users enters a character or more into the text field on the form.

Regards

Vince

[Wokawidget]

Errr...you can only add things to a list box by using code. There is no other way. I am lost in this thread, have no idea where it is going. What's all this [forms]![frmName]![cboName]* about?

Oh bugger. Hang on. Is the form in Access 2000 and are you using VBA? If so then I have no idea what you are on about, and I appologies for talking rubbish.

What's got 2 legs and bleeds in the middle?
1/2 a dog

BOOM BOOM, sorry, bored and tired.

[Morpheus]

This is being done in VBA, and well i'm stumped still, i'm not sure how to connec to ADO using VBA.
Just need to know how to output access query into listbox on button click. any tips appreciated. Below is the query code. this is under the click event for the button, what do i change to output to lstbox.

------------------------------------------------------------------------------------

Private Sub Command19_Click()
On Error GoTo Err_Command19_Click

Dim stDocName As String

stDocName = "Roll Without Matching Find duplicates for Roll"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command19_Click:
Exit Sub

Err_Command19_Click:
MsgBox Err.Description
Resume Exit_Command19_Click

End Sub

[Ecniv]

Hi

Whipped this up in hmm 10 mins ish.

D/l and see if it work ok ?

Might give you some ideas - note had to use forms!frmname!txtfield.text to get it to register though

Vince