Sorry - very basic VB Question

[ckennerdale]

Sorry if this is so basic but I keep on returning a string instead of the values of a query.

Basically I have a table ("Contact&quot which has an Email field ("E-Mail&quot. I want to gett all email addresses as one string and put into an email.

My email function is all working by taking the value of a form (as followed in the FAQs on this site) but that only works for one email address / likewise using DLookup is the same.

I would like to execute a sQL statement such as SELECT E-MAIL FROM CONTACT, and the values be returned as a string(separated by semi-colons) which can then go into my email

I know what I want to do I just dont know the syntax

Thanks in advance

Caspar Kennerdale

 

[LonnieJohnson]

Have you tried using recordsets?

Dim rs as Recordset
Set rs = database.OpenRecordset("Select Email from Contacts&quot
Dim MyEmails as String

Do Until rs.EOF
If rs.BOF = True Then
MyEmails = rs.Email
Else
MyEmails = MyEmails & ";" rs.Email
End If
rs.MoveNext
Loop

This is the basic code which would vary in the fisrt couple of lines depending upon if you were using A97 or anything after that (dao or ado)


ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!

 

[ckennerdale]

Database.OpenRecordset("Select E-mail from Distribution Contacts&quot

I get variable not defined with 'Database'

I presume I need to define my database?

I'm using A97

Caspar Kennerdale

 

[ckennerdale]

I fixed that, but now I get this error

"Method or data memebr not found"

on this line
MyEmails = rs.Email

any ideas?

Caspar Kennerdale

 

[AlanKW]

Is "Select E-mail from Distribution Contacts" the name of your query? If not, try putting the query name within the quotes.

 

[LonnieJohnson]

Yeah, what is Distribution Contacts a table? You may have to put that in brackets because of the space. It's not a good practice to put spaces in table or query names, it causes little problems in your syntax sometimes.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!

 

[AlanKW]

And I think that the line [tt]MyEmails = rs.Email [/tt], the Email after the . refers to the field name...

 

[ckennerdale]

Thanks for your help.

As I understand my code the variable MyEmails should be a string containing all values of the field NAME in the table JOBS, each separated by a semi colon, however Access now just crashes.

Here is my code-

Dim dbs As Database
Dim rs As Recordset
Dim strSQL As String
Dim MyEmails As String

strSQL = "SELECT * FROM Jobs"
Set rs = dbs.OpenRecordset("strSQL&quot

Do Until rs.EOF
If rs.BOF = True Then
MyEmails = rs.Name
Else
MyEmails = MyEmails & ";" & rs.Name
End If
rs.MoveNext
Loop


Thanks in advance

 

[mrf1xa]

Try it using rs!Name instead of rs.Name....

Nigel
Didn't someone say work is supposed to be fun? They didn't have computers then I guess....

 

[RoyVidar]

Agree with mrf1xa, and a little addition. Name is a property of (almost?) all objects in access/VBA, which means it's a reserved word, and thus should be avoided as field name. Sometimes using the recomended reference AND surround it with [brackets] works.

Another thing, when .bof, there's no record (checking the length of the variable)

Since you are using a string variable to open the recordset, you'll need to pass the contents (without quotes):

[tt] Set rs = dbs.OpenRecordset(strSQL)
Do Until rs.EOF
If len(MyEmails) = 0 Then
MyEmails = rs![Name]
Else
MyEmails = MyEmails & ";" & rs![Name]
End If
rs.MoveNext
Loop[/tt]

Roy-Vidar

 

[ckennerdale]

Thanks guys. Unfortunately none of the changes prevented access from crashing.

Below is the entire code from my button. Essentially the variable MyEmails is building up a cellection of emails addresses which will be placed the the 'To' box on an email. This side of things works (if you comment out the loop and uncomment 'MyEmails= "test@test.com' you will see this in action.

I would be interested to know of this code crashes on another computer or is there a really obvious error I am doing.

Sorry but I am just familiar enough with VB


Private Sub Email_Distribution_Contact_Click()

' Prevent error screen if user cancels without sending mail.
On Error Resume Next

Dim strToWhom As String '<---sets up a variable object for who we are going to send out email to
Dim strMsgTitle As String '<---sets up a variable object for the title of the email
Dim strMsgBody As String '<---sets up a variable object for the body of our email

Dim dbs As Database
Dim rs As Recordset
Dim strSQL As String
Dim MyEmails As String

strSQL = &quot;SELECT * FROM Jobs&quot;
Set rs = dbs.OpenRecordset(&quot;strSQL&quot

Do Until rs.EOF
If Len(MyEmails) = 0 Then
MyEmails = rs![Name]
Else
MyEmails = MyEmails & &quot;;&quot; & rs![Name]
End If
rs.MoveNext
Loop

'MyEmails = &quot;test@test.com&quot;
strMsgTitle = &quot;Hello&quot;
strMsgBody = &quot;Dear &quot; & Me!Name & Chr(13)
strMsgBody = strMsgBody & &quot;Re Job Number IR&quot; & Me!ID & Chr(13)
strMsgBody = strMsgBody & Me!JobName & Chr(13) & Chr(13)
strMsgBody = strMsgBody & &quot;Please find attached the appropriate files.&quot; & Chr(13) & Chr(13)
strMsgBody = strMsgBody & &quot;If you have an questions please do not hesitate to contact me.&quot; & Chr(13) & Chr(13)
strMsgBody = strMsgBody & &quot;Thanks&quot; & Chr(13)
strMsgBody = strMsgBody & &quot;Corporate Policy Team&quot;

' Provide Subject title bar and message text.
DoCmd.SendObject , , , MyEmails, , , strMsgTitle, strMsgBody, True

End Sub

Caspar Kennerdale

 

[randy700]

You haven't defined your database before attempting to open a recordset.

Set dbs = currentdb
Set rs = dbs.OpenRecordset(&quot;strSQL&quot

 

[RoyVidar]

Why it crashes I don't know (some guessing, the openrecordsetline (see previous reply, randy700's comment (for some reason I didn't spot that) and below) and the on error resume next), but I think you should do your own testing, here's some debugging tips.

Set a breakpoint on the first executable line (strsql line) by hitting F9. Then go back to the form and hit the button. This should take you to the VBE with that line highlighted. Then step thru each line with hitting F8. You might study the variables by hovering the mouse over them.

(There might be corruption issues, of course...)

Again - remove the quotes from the openrecordset. You are getting an error on that line, and since resume next is specified, and not resolved, this error follows thru the whole routine...

Could be either with randy700's comment:

[tt]Set dbs = currentdb
Set rs = dbs.OpenRecordset(strSQL)[/tt]

or

[tt]Set rs = currentdb.OpenRecordset(strSQL)[/tt]

Comment the on error resume next line, this should make the routine halt at the offending line. OR rather you should use your own error handler, more in the line of:

[tt]on error goto MyErr
' just after the declarations and before
' any executable line

' all the rest of the code

MyExit:
Exit sub
MyErr:
if err.number <> 2501 then ' the cancel error
msgbox err.description
end if
resume MyExit
End Sub[/tt]

Sometimes qualifying the sendcommand might work:

[tt] DoCmd.SendObject acSendNoObject, , , MyEmails, , , strMsgTitle, strMsgBody, True[/tt]

Roy-Vidar

 

[ckennerdale]

Cheers guys

Almost there now

Caspar Kennerdale

 

[RoyVidar]

BTW - it doesn't crash on my setup...

- another little tip, is to use fylly qualified declarations of both the recordset and database variables:

[tt]dim rs as dao.recordset
dim dbs as dao.database[/tt]

which might prevent access from having a confusion regarding which recordset library to use (if you're using access 2000+ versions).

Roy-Vidar