Update query: Number of records.

[Mihail]

Hello !

I run this code:

Call DefaultParameter(Me.Name, "ID_Material", ID_Material) 'for write
x = DefaultParametru(Me.Name, "ID_Material") 'for read
 
Public Function DefaultParameter(Form As String, Param As String, Optional ValParam)
'Read / Write in table "tblDefault"
Dim SQL As String, Criteria As String
    If IsMissing(ValParam) Then 'Read
        Criteria = "(Form = """ & Form & """) AND (Parameter = """ & Param & """)"
        DefaultParameter = DLookup("Valoare", "tblDefault", Criteria)
    Else 'Write
        DoCmd.SetWarnings False
            SQL = "UPDATE _Default SET [tblDefault].Valoare = " & ValParam & _
                    " WHERE ((([tblDefault].Form)= """ & Form & """)" & _
                    " AND (([tblDefault].Parameter)= """ & Param & """));"
            DoCmd.RunSQL SQL
        DoCmd.SetWarnings True
    End If
End Function
 

As you can see, for write, I use an Update Query.
The "WHERE" clause can find ONE or ZERO record(s).
If WHERE will find ZERO records I wish to run an APPEND query, in order to create the record.
So, the question is: Can I "catch" how many records has been updated ?
If I don't use DoCmd.SetWarnings False, Access say how many records will be updated. So, this information is somewhere in his mind.

Please, do not speak about dCount function. I know about that.

Thank you very much !

[ADezii]

You can use the RecordsAffected Property of the Database or QueryDef Objects to accomplish this, as in (Expand to see Formatted):

Dim strSQL As String
 
strSQL = "UPDATE _Default SET [tblDefault].Valoare = " & ValParam & _
         " WHERE ((([tblDefault].Form)= """ & Form & """)" & _
         " AND (([tblDefault].Parameter)= """ & Param & """));"
 
With CurrentDb
  .Execute strSQL
 
   MsgBox "Number of Records Updated: " & .RecordsAffected
End With

[Mihail]

Cool !
Thank you, ADezii !!!

[ADezii]

@Mihail
You are quite welcome, Mihail.

[Mihail]

Your code, ADezii, is EXACTLY what I need but still I wonder if don't exist a method to test how many records WILL BE affected.
I wonder that because, if I use DoCmd.RunSQL and I do NOT use DoCmd.SetWarnings False, Access will inform me how many records will be updated, BEFORE to start.

Just for theory.

[Rabbit]

Change the update query to a select query and have it return a count(*). Run that before the update query.

But why do that when you can just turn warnings on? No need to replicate what the warnings does if you don't need to.