Solved

inserting into a form

Posted on 2014-01-24
6
283 Views
Last Modified: 2014-01-24
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
Comment
Question by:Joppa
  • 3
  • 2
6 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 333 total points
ID: 39807808
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
 

Author Comment

by:Joppa
ID: 39807867
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39807926
which line is raising the error?
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 21

Assisted Solution

by:Boyd (HiTechCoach) Trimmell, Microsoft Access MVP
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 167 total points
ID: 39807982
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
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 333 total points
ID: 39807986
is the form "Email_addresses"  open when you run the codes?
0
 

Author Comment

by:Joppa
ID: 39807997
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

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

822 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question