Writing back to access database from within VBA 1

[WhiskyMac]

I am creating a report (a letter) under VBA from within Access 2000, passing through a customer ID number. When the letter has been created for that customer, I want to write today's date back to a field on the Access database so that the same letter will not be produced again for that customer, and will also provide an audit trail.

Can anyone suggest a nice SIMPLE way of achieving this?

 

[dhookom]

After your DoCmd.OpenReport... line add code like

Docmd.RunSQL "Update tblYourTable SET LastDate = #" & Date & "# Where ID = " & Me.txtID

Duane
MS Access MVP

 

[WhiskyMac]

Thank you Duane. That works very well.

I now want to test each CustomerID to see if that letter (report) has been raised before. Using the same technique as before, I have created the following SQL statement, but it dosen't work. The statement line is as follows:

DoCmd.RunSQL "SELECT Count(CustomerID) AS CountOfCustomerID FROM tblCustomerLetter GROUP BY LetterType, CustomerID HAVING LetterType = 'Initial Welcome Letter' AND CustomerID = " & nCustomerID

 

[dhookom]

RunSQL is only for action queries. I would create a query and form that would show the same results but apply the filter in the OpenForm method.
DoCmd.OpenForm "frmRaisedLetters", , , "CustomerID=" nCustomerID


Duane
MS Access MVP

 

[Chalco]

you could also throw all the work to the database, rather than code it yourself, i.e ..

Docmd.RunSQL "Update tblYourTable SET LastDate = date() Where ID = " & Me.txtID

Makes little diference though

 

[WhiskyMac]

In order to stop the same letter (report) being sent twice to the same person, I was trying to find a way of checking to see if the letter had already been produced, and if so, disallow the option. I am storing the CustomerID, date and Letter Name in a table so that a correspondance audit trail can be shown. This all works and I was trying to populate a variable with the count of the specific letter by CustomerID. If the count was greater than zero, don't produce the letter.

Any help would be much appreciated.

Regards

Iain

 

[dhookom]

Curious... what do you mean by "throw all the work to the database"?

Duane
MS Access MVP