asked on

inserting into a form

I have written vba to go through a table and concatenate all the email addresses into a string separated by semi-colons. The idea is to be able to just copy and paste the list into a "TO" field on someones email account for emailing everyone on the list. The end result looks like:

email1; email2; email3;..........

I want to take that string and insert it into a form named Email_addresses with a text box name email. At that point it would be just opening that form and doing the copy and paste.

There's my problem. I don't know how to get the variable list of the emails into the form field. All the information I can find is on inserting into a table not a form I need some help(or maybe a lot).

Thanks,
Ric

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Joppa

ASKER

I get a "There was an error executing the command." when I run it. Here's the code I have. Everything is okay through the end of the loop.

Dim myQueryDef As QueryDef
Dim EmailStr As String
Dim db As dao.Database
Dim rsl As dao.Recordset

'q below just calls all recs from table with emails
Set myQueryDef = CurrentDb.QueryDefs("email")
Set rsl = myQueryDef.OpenRecordset(, dbOpenDynaset)

strCurrentYear = "[Dues" & Format(Now(), "yy") & "]"

'Sets the email distro to blank
EmailStr = ""

strSQL = "SELECT Email " & " FROM Table1 WHERE " & strCurrentYear & " <> 0 AND (Table1.[Email] Is Not Null)"

Set db = CurrentDb
Set rsl = db.OpenRecordset(strSQL)

rsl.MoveFirst
EmailStr = rsl.Fields("Email") & "; "

Do While Not rsl.EOF
EmailStr = EmailStr & rsl.Fields("Email") & "; "
rsl.MoveNext
Loop

Forms!Email_addresses!email = EmailStr

rsl.Close

The name of the from is correct....Email_addresses, and the only field in the form is a text box named "email". I have something else wrong.

Thanks,
Ric

Rey Obrero (Capricorn1)

which line is raising the error?

SOLUTION

Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Joppa

ASKER

HiTechCoach,

Noted and fixed

Rey,

This is the line that give me the error:

Forms!Email_addresses!email = EmailStr

and since you asked maybe that's the problem. No, I didn't do anything to open the form.

So........ I opened the form and it works.

Thanks for the help everyone,
Ric