MS Access - Checkboxes?

Question

I have a contact database where I can send an email to a single contact or to a group of contacts based on a query - however I would like to add a checkbox function so if I decide I want to send an email only to a handful of people in one of my query lists, I can check their name and it will send.

The issue I'm having is I don't understand how to link the checkbox to the email addresses.

Thus far I have it set up so when the form based on the query is open (eg. only architects are contained in the form) a button can be clicked which opens the form in table view, check boxes can then be ticked accordingly, table saved and closed ... I want to then click a button so outlook opens with only selected contacts in the Bcc field.

I already have a code that will open outlook with ALL contacts from the query in Bcc and it goes like so:

Dim r As Recordset
Dim Email As String
Set r = CurrentDb.OpenRecordset("select Email from FranksFinanceBrokers")
Do While Not r.EOF
    Email = Email & r(0) & ";"
    r.MoveNext
Loop

If Err.Number = 2295 Then
Resume Next
End If

r.Close

DoCmd.SendObject acSendNoObject, Null, Null, "", "", Email, "", "", True, Null


End Sub

Is it possible I can adjust this code so it only uses the email addresses who have their checkbox ticked?

My thought was adjusting OpenRecordset line to Set r = CurrentDb.OpenRecordset("select Email from FranksFinanceBrokers where SendEmail = true") but this doesn't work.

Any suggestions would be greatly appreciated, especially as I don't really know how the checkboxes even work!

Thanks!

Afterthought: Also, is there anyway to be able to "select all" checkboxes instead of ticking each one individually? Similarly with a way to "unselect all" ??

Answer 1

I suggest using the ListBox control with your query as the data source.

The “Simple” multi-select mode works like a checkbox.

• Use the ItemsSelected property to retrieve the selected rows.
• Use the ItemData property to retrieve the appropriate data.

If you choose to display multiple columns you would use the Columns property to retrieve the data.

The resulting code would look something like:

For i = 0 To candidateList.ItemsSelected.Count - 1
    email = email & candidatList.ItemData(candidatListItemsSelected(i))
Next

Use the Selected property to select or clear rows:

For i = 0 To candidateList.ListCount - 1
    candidateList.Selected(i) = true
Next

Answer 2

So it turns out I'm having problems with my original code - it's not by passing that error (which I'm sure it did before!!) and it's occuring because I don't have an email address for every contact, so it sees that as an invalid address instead of just not including it. Does anyone know how to fix that?

As for your solution Harry, I don't really understand what you mean. I've done a query that includes all relevant fields (approx. 20) and then defined the search by type or sector, so criteria is, for example "architects" in the 'Type' field. If I want to email all those architects except maybe 5, which I have unselected by unchecking the respective checkboxes, how do I write that as a code, and where in my current code does it go?

I'm so so new to VBA, coding, prgramming etc. that I don't actually understand it fully. I'm just doing a favour for a friend but your help would be greatly appreciated!

Answer 3

Hi Kate,

First, try changing this code:

Do While Not r.EOF
    Email = Email & r(0) & ";"
    r.MoveNext
Loop

If Err.Number = 2295 Then
    Resume Next
End If

To this:

Do While Not r.EOF
    IF Not IsNull(r("Email")) _
            AND r("Email") <> "" THEN
        Email = Email & r("Email") & ";" 'Note: I use the field name rather
                                         '      than a number because it is
                                         '      easier for the next guy to 
                                         '      understand (and it means I can
                                         '      add another field without
                                         '      breaking the rest of the code.
                                         '      Quite often the next guy is 
                                         '      me anyway :)
    End If
    r.MoveNext
Loop

'If Err.Number = 2295 Then   'Delete these three lines. Prevent the error 
'    Resume Next             'instead of trying to trap it.
'End If

Second, I expect that you have a field in your table called "SendEmail" and you want to use that as your indicator as to whether they are to be e-mailed or not. So you should be able to change you select to look like this:

"select Email " & _ 
"from FranksFinanceBrokers " & _
"where SendEmail = true"

This is functionally equivalent to

"select Email from FranksFinanceBrokers where SendEmail = true"

but easier to read. You could also do this:

Set r = CurrentDb.OpenRecordset( _
        "select Email, SendEmail " & _ 
        "from FranksFinanceBrokers ")
'... blah
Do While Not r.EOF
    IF Not IsNull(r("Email")) _
            AND r("Email") <> "" _
            and r("SendEmail") <> true THEN
        Email = Email & r("Email") & ";" 
    End If
    r.MoveNext
Loop

Answer 4

CodeSlave ... I'm getting Compile Error 3265 (Item not found in this collection) when I run your code - I reckon if I sorted that out I'd have it!

When the error pops up and I click debug, the following is highlighted and I guess that's where my problem is.

IF Not IsNull(r('Email')) _
            AND r('Email') <> "" _
            and r('SendEmail') <> true THEN

Any ideas?

Answer 5

Never mind I worked it out :) thank you! You're all just way too smart :)

Answer 6

This is the code I used in the end:

    Dim r As Recordset
Dim Email As String
Set r = CurrentDb.OpenRecordset("SELECT Email FROM FranksFinanceBrokers WHERE SendEmail = True")

Do While Not r.EOF
        Email = Email & r("Email") & ";"
    r.MoveNext
Loop
r.Close

DoCmd.SendObject acSendNoObject, Null, Null, "", "", Email, "", "", True, Null

End Sub

No errors occur and because I can uncheck those email fields that have no email addresses in them, everything runs smoothly. Tell you what, three weeks ago I didn't even know MS Access was even a program. Now I've created an entire database and can do cool things like this! Probably really basic stuff for you guys, but it's such an awesome skill to have!

Thanks so so much for all your help, I know where to come next time I'm stuck ;)