Solved

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

Posted on 2004-08-25
20
248 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
This is an explanation of a simple data model to help parse a JSON feed
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…

867 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

21 Experts available now in Live!

Get 1:1 Help Now