E-Mail only updated data

[lucour]

I have a database that tracks sales information. Once the data has been updated I have a command setup to e-mail this updated info. to a supervisor. This gets sent as an Excel attachment file. How can I set this up so that I am sending just what I have recently updated instead of all the data stored in the table ?

thanks...

 

[Travis]

This answer depends on how you are storing the changes.

Is it transactional (ie. when ever a change occurs the changes are a seperate record).

Do you populate a table with data that references what was change?

 

[lucour]

I have 3 tables: tblCompanyInfo ; tblProducts ; and tblSalesLog. They are each related to one another by the ID field (Autonumber). I have a main form to enter the Company info. and then 2 subforms which tracks Products and Sales. The Products and Company info. only ever has to be entered once. The Sales info. changes day by day. I have this setup as a continuous form and each time an update is made, a new record is created. The fields updated are: txtLogDate, txtMetWithContact (Yes or No filled in from a Combo box), and txtComments. It is ONLY these three fields, from tblSalesLog, that I want to send via email to a supervisor each day. However I only want to send the most recent updates to C:\DailyLog.xls. When I run the code to send the e-mail, it basically contains the updated data along with the previously entered data.
I hope this is not too confusing. Thanks !

 

[Travis]

Add a Yes/No Field to the tblSalesLog. On the AfterUpdate Event of the Continuous form set this field to False (This field does not need to be visible on the Continuous form). Then base your query by limiting it to records where this Yes/No Field = False. After Sending the report, have you code run an Update Query that sets this Yes/No Field = True.

 

[lucour]

Thanks for the help, Travis. I kind of thought of the same solution you mentioned, only different. I have an 'Update' field in my tblSales log. When a change is made to the record in the Sales subform, an Update query runs that changes this field to "Yes". Then in the query for e-mailing the document I have a criteria of 'Yes' setup in the Update field. At the end of the code for e-mailing the document, another Update Query runs that changes the value to "No". However, I only want the 'Current' record to be changed to 'Yes' after it is updated. All the records in the Continuous form get changed to 'Yes' after I make an update. I want the rest of them to stay at 'No' and only the one I am updating to change to 'Yes.

 

[Travis]

You have to limit your update query to the current record. You can do that in the Criteria section of the QBE of the Update Query.