Access 2003 Problem

  • Thread starter Thread starter John H
  • Start date Start date
J

John H

Hi, I am new to access and am trying to create a simple database which
does the following:
Record Serial numbers against users and locations. Once the records
are complete I would like to be able to mail a report to another
department. I have got this to work fine using the wizards in Access
2003, however as data is being added I only want the new data to be
mailed and not all of the historical data. I have created an extra
field to record if the data has already been mailed, But would
appricate some help in adding the code which
a). writes a YES to the table if the record has already been mailed
b). checks to see what records are to be mailed and only emails the
records which havn't been sent before.

I hope this makes sense.
Thanks.
John.
 
John H said:
Hi, I am new to access and am trying to create a simple database which
does the following:
Record Serial numbers against users and locations. Once the records
are complete I would like to be able to mail a report to another
department. I have got this to work fine using the wizards in Access
2003, however as data is being added I only want the new data to be
mailed and not all of the historical data. I have created an extra
field to record if the data has already been mailed, But would
appricate some help in adding the code which
a). writes a YES to the table if the record has already been mailed
b). checks to see what records are to be mailed and only emails the
records which havn't been sent before.

I hope this makes sense.
Thanks.
John.
Click to expand...

If your SendObject code is called from the form bound to your query then
something like this ought to work:

Me.chkMyCheckBox = True
Me.Refresh

However you should be aware that this code will run and update your data
even if the SendObject method fails because of, for example, a network or
e-mail server problem. You'd need to find out what errors occur in such
situations and trap them:

If Err.Number = xxxx Then Exit Sub

Picking the records you want to mail is just a case of using a select query
with a filter for True on your Yes/No field.

HTH - Keith.
www.keithwilby.com
 
First, I would recommend that you not use a Yes/No field for recording if a
particular record has been sent. Use a DateTime field so you can record
when a record has been sent. Then you can resend a batch if the email has
failed.

Assuming that you make that change you can use an UPDATE query to mark new
records that you want to send
UPDATE YourTable
SET [TransmitDate] = Date()
WHERE [TransmitDate] is Null

In the query grid, you can build the above
-- Add YourTable
-- Add the TransmitDate field
-- Enter Is Null in the WHERE "cell"
-- Select Query: Update from the menu
-- Enter Date() in the Update to "cell"

Now you can use another query as the source for your mailing and send only
the records with a transmit date equal to today's date. For retransmittals,
all you need to know is the date of the original transmittal and use that to
filter which records to retransmit.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
First, I would recommend that you not use a Yes/No field for recording if a
particular record has been sent. Use a DateTime field so you can record
when a record has been sent. Then you can resend a batch if the email has
failed.

Assuming that you make that change you can use an UPDATE query to mark new
records that you want to send
UPDATE YourTable
SET [TransmitDate] = Date()
WHERE [TransmitDate] is Null

In the query grid, you can build the above
-- Add YourTable
-- Add the TransmitDate field
-- Enter Is Null in the WHERE "cell"
-- Select Query: Update from the menu
-- Enter Date() in the Update to "cell"

Now you can use another query as the source for your mailing and send only
the records with a transmit date equal to today's date. For retransmittals,
all you need to know is the date of the original transmittal and use that to
filter which records to retransmit.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.




Hi, I am new to access and am trying to create a simple database which
does the following:
Record Serial numbers against users and locations. Once the records
are complete I would like to be able to mail a report to another
department. I have got this to work fine using the wizards in Access
2003, however as data is being added I only want the new data to be
mailed and not all of the historical data. I have created an extra
field to record if the data has already been mailed, But would
appricate some help in adding the code which
a). writes a YES to the table if the record has already been mailed
b). checks to see what records are to be mailed and only emails the
records which havn't been sent before.
Click to expand...
I hope this makes sense.
Thanks.
John.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

thnakyou very much for your feedback !
 
Back
Top