swill2003
asked on
URGENT: collecting email addresses through tables based on a recordset of a form VBA
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
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
ASKER
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
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
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?
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?
[Forms]![Organisation Details1]![Company name]
I noticed that table isn't in the JOIN's.
I noticed that table isn't in the JOIN's.
ASKER
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]![O rganisatio n 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! :)
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])=
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! :)
ASKER
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?
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])=
what is the syntax to refer to a variable in this context?
ASKER
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
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
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).
" WHERE [ThisField] = '" & VarName & "'" since anything in the quotes is a literal text (unless it's referring to something within the remote Access database).
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.
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.Ena bled = True
[Form_Organisation Details1].Company_Name.Set Focus
name1 = [Form_Organisation Details1].Company_Name.Tex t
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
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.Ena
[Form_Organisation Details1].Company_Name.Set
name1 = [Form_Organisation Details1].Company_Name.Tex
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
ASKER
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(my sql)
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.Appl ication")
'Email.SetFocus
emadd = addressall2
Set myitem = myolapp.CreateItem(olMailI tem)
Set myRecipient = myitem.Recipients.add(emad d)
myitem.subject = "Cardiff Credit Union"
myitem.Display
End If
Set Pop = Nothing
Set Ors = Nothing
End Sub
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(my
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.Appl
'Email.SetFocus
emadd = addressall2
Set myitem = myolapp.CreateItem(olMailI
Set myRecipient = myitem.Recipients.add(emad
myitem.subject = "Cardiff Credit Union"
myitem.Display
End If
Set Pop = Nothing
Set Ors = Nothing
End Sub
Cool, so what's left to figure out now?
ASKER
( i changed rst to Pop as it didnt seem to like rst )
Haha, talk about timing! We both posted at the same time :D
No problem! I'm glad we could get it solved so quickly.
No problem! I'm glad we could get it solved so quickly.
ASKER
lol :)
So does everything work now?
ASKER
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
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
Sounds great, Good luck!
ASKER
'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]![Organisati
Debug.Print mysql
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("query 4")
'Set rst = CurrentDb.OpenRecordset(my
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.Appl
'Email.SetFocus
emadd = addressall2
Set myitem = myolapp.CreateItem(olMailI
Set myRecipient = myitem.Recipients.add(emad
myitem.subject = "Cardiff Credit Union"
myitem.Display
End If
Set rst = Nothing
Set Ors = Nothing
End Sub