Solved

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

Posted on 2004-08-25
20
247 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
  • 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
 
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 2

Accepted Solution

by:
zonaltech earned 500 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
scoresClump  challenge 31 108
array11 challenge 16 52
Path of Workbook 3 45
Not needed 13 58
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
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 …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

744 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