Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 302
  • Last Modified:

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
0
Joppa
Asked:
Joppa
  • 3
  • 2
3 Solutions
 
Rey Obrero (Capricorn1)Commented:
after you have concatenated the email to a variable "strEmail", you can simply do this

Forms!Email_addresses!EMail= strEmail

if you are running the codes in the form's module, you can simply use

Me.Email=strEmail
0
 
JoppaAuthor Commented:
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
0
 
Rey Obrero (Capricorn1)Commented:
which line is raising the error?
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Your current code will duplicate the first email address. Need to add a rsl.MoveNext before the loop.


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

           ' get next record before starting the loop

           rsl.MoveNext

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

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
is the form "Email_addresses"  open when you run the codes?
0
 
JoppaAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now