How to catch (and save) the *number* of updated records

  • Thread starter Thread starter JH
  • Start date Start date
J

JH

My office is responsible for certifying the company's IT systems.

I recently set up a database to keep track of all the system data relevant
to us as well as serve as a ticket tracking system for our internal reports
(open tickets, status, etc).

I have several tables set up, the most important of which are, tblRequest
and tblSystem, which hold the ticket/review information and the system
specific info (respectively).

In order to keep things as "simple" as possible, I put a "review completed"
box on the form where ticket info is recorded – the form is based off a
vueTblRequest query. However, tblSystem needs to record the last
*successful* review. So, I made an update query that will run when you click
an "Update System Data" button (on another form) and it goes through the
*open* tickets (tblRequest has an “IsClosed†yes/no field) to find any ticket
that *passed* the review and then update the respective system info in
tblSystem.

Well, it works fine, but my supervisor does not like that *two* dialogue
boxes come up (“You are about to change dataâ€, continue? and “You are about
to edit XX recordsâ€, continue?). She would like to see only one box come up
asking if you are sure, then just TELL you how many records were updated.


So, my question is, is there any way for me to catch the number of records
updated so I can display it in a message box instead of a Yes/No dialogue?


What I've got so far is as follows:

Dim vbMsgBoxResult As Long
Dim numRecordsUpdated As Integer

vbMsgBoxResult = msgBox("Are you sure you want to update C&A dates?",
vbYesNo + vbDefaultButton2, "Update Confirmation")

If vbMsgBoxResult = vbYes Then
DoCmd.SetWarnings False
On Error GoTo Err_CAUpdateButton_Click

Dim stDocName As String

stDocName = "frmQryUpdate"
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.SetWarnings True

msgBox ("You updated " & numRecordsUpdated & " records.")
 
JH,

You can use a variable and the DCount function, providing the necessary
criteria and it will return the number of records. The criterie should be
the same as the criteria for your query that is update the records.

Like:
Dim varRecCnt as Variant
varRecCnt = DCount(NameOfFieldToCount, TableName, Criteria as a string)

The just use the variable in your message.

-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm
 
You, good sir, are awesome.

Took me a few tries to realize that all three inputs for the DCount function
have to be in quotes.

DCount("RequestID","tblRequest","(tblRequest.IsClosed = No) AND
(tblRequest.ReviewDate IS NOT NULL)")


But hey, it works. Thanks again!
 
Actually, there's a much simpler way.

Dim qdf As DAO.QueryDef
Dim vbMsgBoxResult As Long

vbMsgBoxResult = msgBox("Are you sure you want to update C&A dates?",
vbYesNo + vbDefaultButton2, "Update Confirmation")

If vbMsgBoxResult = vbYes Then
On Error GoTo Err_CAUpdateButton_Click

Dim stDocName As String

stDocName = "frmQryUpdate"
Set qdf = CurrentDb.QueryDefs(stDocName)
qdf.Execute dbFailOnError

msgBox ("You updated " & qdf.RecordsAffected & " records.")
 
Back
Top