If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Access 2003 Problem
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. |
#2
|
|||
|
|||
Access 2003 Problem
"John H" wrote in message
oups.com... 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. 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 |
#3
|
|||
|
|||
Access 2003 Problem
"Keith Wilby" wrote in message
... with a filter for True on your Yes/No field. Ugh, FALSE, not True. |
#4
|
|||
|
|||
Access 2003 Problem
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 .. "John H" wrote in message oups.com... 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. |
#5
|
|||
|
|||
Access 2003 Problem
On May 23, 12:56 pm, "John Spencer" wrote:
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 . "John H" wrote in message oups.com... 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.- Hide quoted text - - Show quoted text - thnakyou very much for your feedback ! |
Thread Tools | |
Display Modes | |
|
|