Solved

inserting into a form

Posted on 2014-01-24
6
281 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 119

Accepted Solution

by:
Rey Obrero 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 119

Expert Comment

by:Rey Obrero
ID: 39807926
which line is raising the error?
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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 119

Assisted Solution

by:Rey Obrero
Rey Obrero 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

920 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now