Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

URGENT: collecting email addresses through tables based on a recordset of a form VBA

Posted on 2004-08-25
20
Medium Priority
?
256 Views
Last Modified: 2010-04-17
hi

i have a form which shows organisation details from an organisation table once a search has been performed on the organisation table.

the organisation table is linked to a contacts table which holds employees information.

i want there to be a button on the organisation details form that will get a list of all the employees email addresses for all the organisations in the recordset for whatever has been searched. the email addresses need to be seperated by " ; " . this will then be used to send an email.

 i have got my self all in a muddle and the code i need is very urgent.

the code i have i have pasted below and there are errors to do with the sql. and setting the recordset rst.

if you can see of a better way to do this than i have done or can see where my problem lies please tell me :)

many thanks
Sarah
x
0
Comment
Question by:swill2003
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 9
20 Comments
 

Author Comment

by:swill2003
ID: 11896921
Private Sub commemailall_Click()
'On Error GoTo Err_commemailall_Click
  Dim addressall2 As String
  Dim mysql As String
      Dim rst As Recordset
Dim emadd As String
Dim dbs As DAO.Database



DoCmd.GoToRecord , , acFirst
addressall2 = ""
Set Ors = Me.RecordsetClone
Ors.MoveFirst
With Ors
    Do While Not .EOF
       
    mysql = "SELECT [Organisation Details].[Company Name], [Contact Details2].Forename, [Contact Details2].Surname, [Contact Details2].Organisation, [Contact Details2].Email" & _
" FROM [Organisation Details] LEFT JOIN [Contact Details2] ON [Organisation Details].[Company Name] = [Contact Details2].Organisation" & _
" WHERE ((([Organisation Details].[Company Name])=[Forms]![Organisation Details1]![Company name]));"


Debug.Print mysql


    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("query 4")


        'Set rst = CurrentDb.OpenRecordset(mysql)
       
            With rst
                .MoveFirst
                    Do While Not .EOF
                        addressall2 = addressall2 & .Fields("Email") & ";"
                        If .Fields("Email") <> "" Then
                 CurrentDb.Execute "INSERT INTO tblLetters ( email, datesent, CompanyName, forename, surname ) SELECT '-1'" & ", '" & Now() & "', '" & .Fields("organisation") & "', '" & .Fields("forename") & "', '" & .Fields("surname") & "'"
                        End If
                        .MoveNext
                Loop
        End With

       
        .MoveNext
    Loop
End With

If addressall2 = "" Then
MsgBox ("There are no email addresses for any of the contacts")
Else


   
    Set myolapp = CreateObject("Outlook.Application")
    'Email.SetFocus
    emadd = addressall2
    Set myitem = myolapp.CreateItem(olMailItem)
    Set myRecipient = myitem.Recipients.add(emadd)
    myitem.subject = "Cardiff Credit Union"
    myitem.Display


End If
Set rst = Nothing
  Set Ors = Nothing


End Sub
0
 

Author Comment

by:swill2003
ID: 11898042
where it says

 Set rst = dbs.OpenRecordset("query 4")

it actually says

 Set rst = dbs.OpenRecordset(mySql)
and this gets highlighted with a problem saying too few parameters expected 1
0
 
LVL 2

Expert Comment

by:zonaltech
ID: 11898119
Ok, heres a KB article explaining the cause:

http://support.microsoft.com/default.aspx?scid=kb;en-us;216425

So it seems Access thinks one of the fields you passed to it doesnt exist, can you double-check each field in the SQL to make sure it exists/is in the table specified?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Expert Comment

by:zonaltech
ID: 11898146
[Forms]![Organisation Details1]![Company name]

I noticed that table isn't in the JOIN's.
0
 

Author Comment

by:swill2003
ID: 11898205
ok... i have read the microsoft bit...

 mysql = "SELECT [Organisation Details].[Company Name], [Contact Details2].Forename, [Contact Details2].Surname, [Contact Details2].Organisation, [Contact Details2].Email" & _
" FROM [Organisation Details] LEFT JOIN [Contact Details2] ON [Organisation Details].[Company Name] = [Contact Details2].Organisation" & _
" WHERE ((([contact details2].[Organisation])=[Forms]![Organisation Details1]![Company name]));"

the only fields on the form are the ones from the organisation details table.

is this why?

or if it is as you say

[Forms]![Organisation Details1]![Company name]));" maybe i could write this in as a variable and refer to it that way?

the form is bound to the organisation details table

thankyou for taking a look :) currently just gone midnight here! :)
0
 

Author Comment

by:swill2003
ID: 11898218
maybe like this?

Dim name As String


DoCmd.GoToRecord , , acFirst
addressall2 = ""
Set Ors = Me.RecordsetClone
Ors.MoveFirst
With Ors
    Do While Not .EOF

       name = Company_Name.Text

    mysql = "SELECT [Organisation Details].[Company Name], [Contact Details2].Forename, [Contact Details2].Surname, [Contact Details2].Organisation, [Contact Details2].Email" & _
" FROM [Organisation Details] LEFT JOIN [Contact Details2] ON [Organisation Details].[Company Name] = [Contact Details2].Organisation" & _
" WHERE ((([contact details2].[Organisation])= name;"

what is the syntax to refer to a variable in this context?
0
 

Author Comment

by:swill2003
ID: 11898476
ok i am giving up lol
i have to go to bed!

thanks 4 the help neway. i will still need to fix this problem so if you see ne miracles in the code please let me know

Good night
x
0
 
LVL 2

Expert Comment

by:zonaltech
ID: 11899906
When you refer to variables, you must include them like:
" WHERE [ThisField] = '" & VarName & "'" since anything in the quotes is a literal text (unless it's referring to something within the remote Access database).
0
 
LVL 2

Expert Comment

by:zonaltech
ID: 11899910
I'll be here tomorrow (I'm in EST time zone, by the way), and I'll try and help you get this resolved if the variable syntax doesn't fix it.
0
 

Author Comment

by:swill2003
ID: 11900567
hi :) back again. i have been given an extensionm to get it sorted so i am a bit more relaxed now. it still says type mismatch when using the variable instead of the reference to the form.

0
 
LVL 2

Accepted Solution

by:
zonaltech earned 2000 total points
ID: 11901981
" WHERE [contact details2].[Organisation] = '" & name & "';"
0
 

Author Comment

by:swill2003
ID: 11903878
i created a new form and pasted this code behind a command button and it worked

Private Sub Command0_Click()

 Dim addressall2 As String
  Dim mysql As String
      Dim rst As Recordset
Dim emadd As String
Dim dbs As DAO.Database
Dim name1 As String
Dim Ors


addressall2 = ""



        [Form_Organisation Details1].Company_Name.Enabled = True
        [Form_Organisation Details1].Company_Name.SetFocus
   
       name1 = [Form_Organisation Details1].Company_Name.Text
       
 mysql = "SELECT [Contact Details2].Forename, [Contact Details2].Surname, [Contact Details2].Organisation, [Contact Details2].Email" & _
" FROM [Contact details2] " & _
" WHERE [Contact details2].[Organisation] = '" & name1 & "';"
Set dbs = CurrentDb
    Set Ors = dbs.OpenRecordset(mysql)
   
   

With Ors
                .MoveFirst
                    Do While Not .EOF
                        addressall2 = addressall2 & .Fields("Email") & ";"
                        If .Fields("Email") <> "" Then
                 CurrentDb.Execute "INSERT INTO tblLetters ( email, datesent, CompanyName, forename, surname ) SELECT '-1'" & ", '" & Now() & "', '" & .Fields("organisation") & "', '" & .Fields("forename") & "', '" & .Fields("surname") & "'"
                        End If
                        .MoveNext
                Loop
        End With
       
        MsgBox (addressall2)
       
End Sub
0
 

Author Comment

by:swill2003
ID: 11904751
yey! i fixed it! it all works now :)
thankyou again for all your help!

i have posted the final code below...

thanks again
:)
Sarah
x

Private Sub commemailall_Click()


'On Error GoTo Err_commemailall_Click
  Dim addressall2 As String
  Dim mysql As String
      Dim Pop
Dim emadd As String
Dim dbs As DAO.Database
Dim name As String
Dim Ors

DoCmd.GoToRecord , , acFirst
addressall2 = ""
Company_Name.Enabled = True
        Company_Name.SetFocus
   
Set Ors = Me.RecordsetClone
Ors.MoveFirst
MsgBox ("This may take a few seconds please wait")
With Ors
    Do While Not .EOF
       
       name = .Fields("Company Name")
       
       If InStr(name, "'") > 0 Then
    name = Left(name, InStr(name, "'")) & Right(name, (Len(name) - InStr(name, "'")) + 1)
End If


   mysql = "SELECT [Contact Details2].Forename, [Contact Details2].Surname, [Contact Details2].Organisation, [Contact Details2].Email" & _
" FROM [Contact details2] " & _
" WHERE [Contact details2].[Organisation] = '" & name & "';"


Debug.Print mysql


    Set dbs = CurrentDb
    Set Pop = dbs.OpenRecordset(mysql)


        'Set rst = CurrentDb.OpenRecordset(mysql)
       
            With Pop
           
           
           
            If Pop.RecordCount <> 0 Then
                .MoveFirst
                    Do While Not .EOF
                    If .Fields("Email") <> "" Then
                        addressall2 = addressall2 & .Fields("Email") & ";"
                       
                 CurrentDb.Execute "INSERT INTO tblLetters ( email, datesent, CompanyName, forename, surname ) SELECT '-1'" & ", '" & Now() & "', '" & .Fields("organisation") & "', '" & .Fields("forename") & "', '" & .Fields("surname") & "'"
                        End If
                        .MoveNext
                Loop
                End If
        End With

       
        Ors.MoveNext
    Loop
    Command80.SetFocus
    Company_Name.Enabled = False
End With

If addressall2 = "" Then
MsgBox ("There are no email addresses for any of the contacts")
Else


   
   Set myolapp = CreateObject("Outlook.Application")
    'Email.SetFocus
    emadd = addressall2
    Set myitem = myolapp.CreateItem(olMailItem)
    Set myRecipient = myitem.Recipients.add(emadd)
    myitem.subject = "Cardiff Credit Union"
    myitem.Display


End If
Set Pop = Nothing
  Set Ors = Nothing


End Sub
0
 
LVL 2

Expert Comment

by:zonaltech
ID: 11904756
Cool, so what's left to figure out now?
0
 

Author Comment

by:swill2003
ID: 11904758
( i changed rst to Pop as it didnt seem to like rst )
0
 
LVL 2

Expert Comment

by:zonaltech
ID: 11904768
Haha, talk about timing! We both posted at the same time :D
No problem! I'm glad we could get it solved so quickly.
0
 

Author Comment

by:swill2003
ID: 11904957
lol :)

0
 
LVL 2

Expert Comment

by:zonaltech
ID: 11905589
So does everything work now?
0
 

Author Comment

by:swill2003
ID: 11906128
i think so yes!

thankyou for looking at my other open questions :)
the server one i wont know until i take the database to the company either 2 morrow or saturday so i shall try it out then if the way i have done it doesnt work.

The opening outlook one i gave up on as i figured as many different people are using the database with different outlook accounts they wouldnt be able to open each others ne way.

Kinda wish i had read that b4 thought then i could look into it more but i dont have time now really :(
might have a look at it 2morrow

Thankyou very much for all your help, ill close the other questions when i give them a try over the weekend :)
Thanks again!
S.
xx
0
 
LVL 2

Expert Comment

by:zonaltech
ID: 11906168
Sounds great, Good luck!
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

In this post we will learn different types of Android Layout and some basics of an Android App.
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
Starting up a Project

660 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