Full Version: Using a form to create a mailing list
UtterAccess Forums > Microsoft® Access > Access Forms
Just to note: I am developing this using MSA2007 but it has to work with MSA2003.
I have a large table called "Customers" with a number of fields but to simplify lets say I have...
FirstName | Surname | Email | CardType
CardType is a dropdown box of 6 values that I created. I didn't see the point in creating a table for the CardTypes but you may now tell me I made a boo boo.
I want to create a form that has a list of the 6 CardType values along with a checkbox. Underneath that I want a text box.
"Simply" i want to be able to select all the recipients' email addresses when I check their cardType value and pass these addresses and the text box value to a function to send email. I have already written the function to send email using CDO (I thought that was going to be the hard part) but i'm stumped with this form.
Any help much appreciated.
I also have an EmailSubscription checkbox in my Customers table and obviously I don't want to send email to those who have this box unchecked. However, this is complicating matters and we can add this functionality later.
Your question is a little unclear. If I understand you, you want to select a cardtype and send an e-mail to all records with that card type. What's confusing me is where the checkbox and textbox come in.
It sounds like you used a value list in creating the CardType dropdown. That's fine if you are sure those 6 will be the only types ever used. But it also sounds like you want to use an option group to select the card type to send. That makes things more complicated. Why not just use a combobox? You can copy the one you use to enter the cardtype.
If you do that, all you need to do is set the criteria in the query to:
If I use a combo box I get an entry for each record in my database. Also, I want to have the option to select more than one type of card if needed. This is the same reason why i didn't use an option group.
econdly, I am not sure those 6 are the only ones that will ever be used. I may change this in the future. Is it worth creating a new table for the card types?
Finally, "all you need to do is set the criteria in the query to: =Forms!formname!controlname" means nothing to me! I have experience in programming but these forms are foreign to me as is VBA.
Oknow how HTML forms work but this seems completely different.
So you can use a Multi-Select listbox. And yes I would use a table for the card types with an autonumber PK.
ormname is the name of your form and controlname is the name of the control on the form.
But if you want to use a multi-select listbox, you will need to build the query in VBA. Check Access Help for examples.
I have created a separate table for my card values and created multiple select list box populated with these values.
tried to view the values that i was selecting through a text box but the text box remains blank...
Private Sub List49_Click()
Select Case Me.List49.Value
Text51.Value = List49.Value
End Select
Eventually I want to replace the above with something like
Text51.Value = SELECT * FROM `customers` WHERE `cardType` = List49.Value
Am i along the right lines here?
Did you check Access Help like I suggested? As I said, when the multi-select property is turned on, you the Value property remains blank. You have to loop through the ItemsSelected collection.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.