Finding the Count in an Update Query

[SC in Texas]

I have an update query that should only have 1 record to update or no records
to update when kicked off by a command button in my form. How can I find the
count of records to be updated to create my own message box, if the count is
zero instead of using the warning message box that Access brings up showing
the number of rows to be updated?
Option 2 How can I edit the message in that message box?
Thanks in advance.

 

[Dirk Goldgar]

I have an update query that should only have 1 record to update or no
records
to update when kicked off by a command button in my form. How can I find
the
count of records to be updated to create my own message box, if the count
is
zero instead of using the warning message box that Access brings up
showing
the number of rows to be updated?
Option 2 How can I edit the message in that message box?
Thanks in advance.

If you execute the query via the DAO Execute method, rather than RunSQL or
OpenQuery, then you can examine the database's RecordsAffected property.
Thus:

With CurrentDb
.Execute "YourUpdateQuery", dbFailOnError
If .RecordsAffected = 0 Then
MsgBox "No records were updated."
Else
MsgBox "You just updated " & .RecordsAffected & " record(s)."
End If
End With

 

[SC in Texas]

Dirk,
How do I pass through the parameter from my form to the query when I kick it
off using this method.

Thanks

 

[Dirk Goldgar]

Dirk,
How do I pass through the parameter from my form to the query when I kick
it
off using this method.

If you have a query with parameters, as for example a reference to a control
on a form, you can use the QueryDef object and resolve its parameters before
you execute it. Like this:

Dim DAO As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter

Set db = CurrentDb
Set qdf = db.QueryDefs("YourUpdateQuery")

With qdf

' Resolve parameters using Eval function
For Each prm In .Parameters
prm.Value = Eval(prm.Name)
Next prm

' Execute the query and check the result
.Execute dbFailOnError
If .RecordsAffected = 0 Then
MsgBox "No records were updated."
Else
MsgBox "You just updated " & .RecordsAffected & " record(s)."
End If

End With

 

[SC in Texas]

Dirk,
This worked great.
Thanks,