VBA, Access, ListBoxes and Querys!

Associate
Joined
19 Jun 2006
Posts
162
Location
Swansea, Wales
Hi guys,

I am totally new to VBA but through googling and trial and error I have cobbled together a form which is designed to choose recipients from a database and then email them. However, I'm missing a few important pieces in the jigsaw and am looking for some help.

I have a form with multiselect listbox which comprises all the values from a table called CardType.

Firstly, how can i get the values selected in vba to put in my query?

listboxname.value doesn't give me anything.

Secondly, how do i create a multi WHERE sql statement? Is it an AND keyword e.g.
SELECT * FROM customers WHERE x = y AND a = b

Finally, when i have run my query, how do i loop through the results and create a string of comma separated list of email addresses?

I have programming experience and could do this in java or php but i'm lost when it comes to vba.
 
Not sure about the listbox in VBA not used it for years, will have a play in a minute with it for you though. Regarding the SQL you can do:
Code:
SELECT Col1 FROM Table1 WHERE (CardType = 'A') OR (CardType = 'B');
or you could do:
Code:
SELECT Col1 FROM Table1 WHERE CardType IN ('A', 'B');

EDIT: This shou.d solve you multiselect ListBox issue:
Code:
    Dim i As Integer
    
    For i = 0 To ListBox1.ListCount - 1
        If (ListBox1.Selected(i) = True) Then
            MsgBox ListBox1.List(i)
        End If
    Next

TrUz
 
Last edited:
Sorted thanks.

Just having a problem with the emails now. Sending as HTML but neither hotmail or gmail are applying the styles from my external stylesheet. Does it block them by default?
 
Back
Top Bottom