Aaargh. SendObject Nightmare!!!

[Raistlin]

OK, firstly please forgive the crossposting (and feel free to remove it, I
wasn't sure where this belonged).

Secondly, down to the problem (which I'm hoping someone can help me sort
out).

I am using the SendObject method from with VBA as the on click action for a
button on a form.
The idea is that when you click on the button Access creates an email using
info from the form.
The "To:" field for the email is an address contained in a completely
separate table in the database. The table has only 2 fields and 1 record
and it's sole purpose is to provide this email address. The reason I have a
separate table is because I want to be able to edit this address as and when
required without having to edit the code each time.

What I have so far is:

Table:

Table Name: HelpDeskDetails
Table Fields: HelpDeskID (Primary key, the value of this field for the one
record in the table is 1)
HelpDeskEmail (The email address I need to find)

Now, I'm guessing that I need to store the email address in a variable as a
string and then use the name of that string as the reference in the "To:"
part of the SendObject method. So what I've tried to do is open a recordset
based on the current database and then use a SELECT query to find the email
address (I'm doing this code from memory as the database is at work so
please bare with me):

'Declare Variables

Dim StrEmail As String
Dim rst As Recordset
Dim dbs As Database
Dim StrSQL As String

'Set Values

Set dbs = CurrentDb
Set StrSQL As "SELECT [HelpDeskEmail] FROM HelpDeskDetails WHERE
[HelpDeskID] = 1"
Set rst As dbs.OpenRecordset(StrSQL)

As I say, I'm not sure that I've got the code here exactly as it appears in
my database (but hopefully you can see what I'm trying to achieve), what I
need to do (I think) is pass the result of the SELECT query (which should be
only one email address) to the variabl StrEmail and then insert it into the
"To:" parameter of the SendObject method.

Hopefully what I've put above should make some sense, if anyone needs any
more information to help me sort this mess out then please let me know.

Regards, and thanks.

Raist.

 

[Gary Miller]

Raistlin,

A few ways available to do this. You also may want to play
with just using DLookup to retrieve the value as there is
only one record in the table. Also, since there is only one,
you could modify your code not to use the ID = 1 as it
doesn't really matter. Here is a revision with no error
handling...

Dim StrEmail As String
Dim rst As Recordset
Dim dbs As Database
Dim StrSQL As String

'Set Values

Set dbs = CurrentDb
Set StrSQL As "SELECT [HelpDeskEmail] FROM HelpDeskDetails"
Set rst = dbs.OpenRecordset(StrSQL)

With rst
If Not .EOF and Not .BOF Then
.MoveFirst
StrEmail = !HelpDeskEmail
End If
End With

rst.Close
dbs.Close
Set rst = Nothing
Set dbs = Nothing

' Go on to your email routine now...

The DLookup method would be...

StrEmail = DLookup("HelpDeskEmail","HelpDeskDetails")

This would get you the value of the first record, which is
what you want any way in this case.
--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

OK, firstly please forgive the crossposting (and feel free to remove it, I
wasn't sure where this belonged).

Secondly, down to the problem (which I'm hoping someone can help me sort
out).

I am using the SendObject method from with VBA as the on click action for a
button on a form.
The idea is that when you click on the button Access creates an email using
info from the form.
The "To:" field for the email is an address contained in a completely
separate table in the database. The table has only 2 fields and 1 record
and it's sole purpose is to provide this email address. The reason I have a
separate table is because I want to be able to edit this address as and when
required without having to edit the code each time.

What I have so far is:

Table:

Table Name: HelpDeskDetails
Table Fields: HelpDeskID (Primary key, the value of this field for the one
record in the table is 1)
HelpDeskEmail (The email address I need to find)

Now, I'm guessing that I need to store the email address in a variable as a
string and then use the name of that string as the reference in the "To:"
part of the SendObject method. So what I've tried to do is open a recordset
based on the current database and then use a SELECT query to find the email
address (I'm doing this code from memory as the database is at work so
please bare with me):

'Declare Variables

Dim StrEmail As String
Dim rst As Recordset
Dim dbs As Database
Dim StrSQL As String

'Set Values

Set dbs = CurrentDb
Set StrSQL As "SELECT [HelpDeskEmail] FROM HelpDeskDetails WHERE
[HelpDeskID] = 1"
Set rst As dbs.OpenRecordset(StrSQL)

As I say, I'm not sure that I've got the code here exactly as it appears in
my database (but hopefully you can see what I'm trying to achieve), what I
need to do (I think) is pass the result of the SELECT query (which should be
only one email address) to the variabl StrEmail and then insert it into the
"To:" parameter of the SendObject method.

Hopefully what I've put above should make some sense, if anyone needs any
more information to help me sort this mess out then please let me know.

Regards, and thanks.

Raist.

 

[Dirk Goldgar]

[snip]

Also, since there is only one,
you could modify your code not to use the ID = 1 as it
doesn't really matter. [snip]
Set rst = dbs.OpenRecordset(StrSQL)

With rst
If Not .EOF and Not .BOF Then
.MoveFirst
StrEmail = !HelpDeskEmail
End If
End With

Nitpick time. If there's only one record (or you don't care which one
you get), and the recordset is not at EOF or BOF, then what's the point
of .MoveFirst? You're already there!

 

[Gary Miller]

Dirk,

Interesting point that I guess I had never actually put to
the test. Just did and you are certainly correct, although
it will throw err 3021, of course, if the record is missing.

I know that when doing loops through a recordset the
..MoveFirst seems to be a standard before initiating the loop
and the .MoveNext calls. Would the .MoveFirst then be
superfluous in reality?

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

[snip]
Also, since there is only one,
you could modify your code not to use the ID = 1 as it
doesn't really matter. [snip]
Set rst = dbs.OpenRecordset(StrSQL)

With rst
If Not .EOF and Not .BOF Then
.MoveFirst
StrEmail = !HelpDeskEmail
End If
End With

Nitpick time. If there's only one record (or you don't care which one
you get), and the recordset is not at EOF or BOF, then what's the point
of .MoveFirst? You're already there!

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Raistlin]

Raistlin,

With rst
If Not .EOF and Not .BOF Then
.MoveFirst
StrEmail = !HelpDeskEmail
End If
End With

rst.Close
dbs.Close
Set rst = Nothing
Set dbs = Nothing

The DLookup method would be...

StrEmail = DLookup("HelpDeskEmail","HelpDeskDetails")

Gary,

That's absolutely brilliant! I'll make a not of the code there and try it
out on Monday (I'll be sure to let you know how I get on). This has been
bothering me for about 3 days now and I haven't been able to get anything to
work, I couldn't find any help in Access help (probably because I didn't
know what I was looking for.

Once again, thanks very much.

Raist.

 

[Rick Brandt]

Dirk,

Interesting point that I guess I had never actually put to
the test. Just did and you are certainly correct, although
it will throw err 3021, of course, if the record is missing.

I know that when doing loops through a recordset the
.MoveFirst seems to be a standard before initiating the loop
and the .MoveNext calls. Would the .MoveFirst then be
superfluous in reality?

The only time I have ever used MoveFirst is if I had previously done a
MoveLast to get an accurate RecordCount. AFAIK if you open a RS and you
are not at EOF/BOF then you are at record 1.

 

[Dirk Goldgar]

Dirk,

Interesting point that I guess I had never actually put to
the test. Just did and you are certainly correct, although
it will throw err 3021, of course, if the record is missing.

I know that when doing loops through a recordset the
.MoveFirst seems to be a standard before initiating the loop
and the .MoveNext calls. Would the .MoveFirst then be
superfluous in reality?

A bunch of us had a discussion about this a couple of years ago, and the
conclusion was that in any DAO recordset you just opened (unless some
future version of DAO implements a special "read-backwards" recordset),
you will be positioned at the first record if there is one. So you do
not need to do a .MoveFirst if you have just opened the recordset.

A lot of the code examples are misleading, simply because they don't
make assumptions about where you stand with respect to the opening of
the recordset. If you were handed a recordset as an argument to a
procedure, for example, and you wanted to loop from beginning to end,
then you'd better check first that the recordset has records, and then
move to the first record, and then start your loop:

Sub LoopThroughArgumentRecordset(rs As DAO.Recordset)

With rs
If .BOF And .EOF Then
' no records.
Else
.MoveFirst
Do Until .EOF

' ... do something with each record ...

.MoveNext
Loop
End If
End With

End Sub

If, on the other hand, you open the recordset yourself, you know a lot
more about the situation, and can write just this:

Sub LoopThroughLocalRecordset()

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset( ... )

With rs
Do Until .EOF

' ... do something with each record ...

.MoveNext
Loop
.Close
End With

Set rs = Nothing

End Sub

At the time we were discussing it, some people were concerned that
future changes to DAO might invalidate this assumption. DAO is in
maintenance mode now, so that is very unlikely. Furthermore, IIRC we
did find explicit statements in MS documentation to the effect that a
freshly opened DAO recordset will be positioned to the first record, so
my personal take on it is that we can rely on this behavior.

 

[Gary Miller]

Thanks for the great clarification.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

 

[Raistlin]

Thanks for the great clarification.

Seconded. Thanks to everyone for the great information on this subject. I
guess I'm still a "Newbie" when it comes to databases etc but I'm trying to
learn and it's great to know that there are so many people ready and willing
to help.

Regards.

Raist.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You could do it this way:

dim strTo as string
strTo = rst!HelpDeskEmail

DoCmd.SendObject To:=strTo, < other parameters >

or, this way

DoCmd.SendObject To:=rs!HelpDeskEmail, < other parameters >

HTH,

MGFoster:::mgf
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP7Wto4echKqOuFEgEQKDhQCfceqQ02PDRMwxDyJFLTW2QxnZ/P8AoLNG
H8bD8NXHDv7gnMEKVbBHB2Dn
=kB/V
-----END PGP SIGNATURE-----

 

[Raistlin]

You could do it this way:

dim strTo as string
strTo = rst!HelpDeskEmail

DoCmd.SendObject To:=strTo, < other parameters >

or, this way

DoCmd.SendObject To:=rs!HelpDeskEmail, < other parameters >

HTH,

<Snip>

Thanks for that, I'll give it a go and see how that works.

Regards

Raist.

 

[TC]

OK, firstly please forgive the crossposting (and feel free to remove it, I
wasn't sure where this belonged).

Nuttin' wrong with cross-posting! MULTI-posting is the bad one.

TC

 

[Raistlin]

Nuttin' wrong with cross-posting! MULTI-posting is the bad one.

TC

Thanks for clearing that up TC ;-)

Raist.

 

[Raistlin]

<Snip>

As Per Subject Line