Email record count from Access 2000

  • Thread starter Thread starter J. Shrimp, Jr.
  • Start date Start date
J

J. Shrimp, Jr.

Need to email myself the number of records
in a table from Access when a scheduled
macro is completed. I plan on using the
DoCmd.sendobject, but placing
& strCount & in the subject line doesn't work.
(just need to know the count of the recordset)
Is there any way to pass a variable in the header
using DoCmd.sendobject?
 
dim StrCount as integer
Set rsReturned = CurrentDb.OpenRecordset("SELECT Count
(tblItems.Item) AS totCount FROM tblItems")
strCount = rsReturned!totCount

DoCmd.SendObject
acSendNoObject, , , "(e-mail address removed)",,,&
StrCount,,false

StrCount is the variable I would like in the
subject line.
 
Ok. Your parameter format is incorrect:

... , , , & StrCount, ...
^^^

You use the ampersand (in this context) to add the string StrCount to some
other< string, variable, or expression, that precedes the ampersand.
Click to expand...

For example:
"my string" & StrCount
MyVariable & StrCount
Date() & " - " & StrCount

Find out which SendObject parameter is for the email Subject. (I don't have
Access here to check.) Let's say, purely for the sake of example, that it is
the second parameter, and that the first parameter is not required. Here is
what you'd need to code:

docmd.sendobject , "The number was: " & StrCount, ...

Just make sure you use the right number of leading commas, to put: "The
number was: " & StrCount in the Subject parameter of the sendobject call.

BTW, to get the number of records in a table, the simplest way is probably
this:

strCount = DCount ("*", "tblItems")

There's nothing wrong with storing the count temporarily in a string
variable, as you have done. In fact, that often makes your code easier to
read, understand & maintain. But just for the sake of illustration, note
that you could use the DCount() function directly in the sendobject
statement:

docmd.sendobject , "The number was: " & DCount ("*", "tblItems"),
....

HTH,
TC
 
Back
Top