Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

inserting into a form

Posted on 2014-01-24
6
Medium Priority
?
294 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 1332 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 21

Assisted Solution

by:Boyd (HiTechCoach) Trimmell, Microsoft Access MVP
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 668 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 1332 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

971 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