Solved

inserting into a form

Posted on 2014-01-24
6
284 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel Macro 9 22
VBA working with shapes 6 11
Macro show matches 3 32
Excel - VBA "Object doesn't support this property or method" 7 24
As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

821 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