Why RecordsAffected returns 0 for this query (1 Viewer)

[KitaYama]

I'm executing this sql.
But AffectedRecords returns 0.
The record is successfully added to the table.

Why is that?

Thanks.

sql = "INSERT INTO tblManuProcess ("
sql = sql & "ManuProcessManuFK, ManuProcessProcessFK, ManuProcessUserFK, "
sql = sql & "ManuProcessDate, ManuProcessCount, ManuProcessInspector"
sql = sql & ") VALUES ("
sql = sql & "P1,P2,P3,P4,P5,P6"
sql = sql & ")"

With CurrentDb.CreateQueryDef("", sql)
    .Parameters("P1") = txtManuPK
    .Parameters("P2") = ProcessFK
    .Parameters("P3") = cmbUser
    .Parameters("P4") = GetCurrentDate
    .Parameters("P5") = txtManuCount
    .Parameters("P6") = cmbInspector
    .Execute dbFailOnError
    .Close
    If CurrentDb.RecordsAffected > 0 Then
       fltr = fltr & "," & .OpenRecordset("select @@identity")(0)
     Else
        MsgBox "Failed"
        Exit Sub
     End If
End With

 

[cheekybuddha]

Since both the CurrentDB (or an object variable assigned from it) and the QueryDef object include .RecordsAffected, the question becomes "which one do you see?" for the case of WITH CurrentDB.CreateQueryDefs() - and whichever one you see, the other one would probably return zero or something else. There is a rule for OOP that, when a name reference is not qualified and a name overlap occurs, the closest object with that name is the one you see. If that is the wrong one, you need to qualify the reference.

I'm thinking that though the querydef that has a .RecordsAffected property might be "closer" in programming terms, the query was executed by ACE, which didn't execute that query in the context of the QueryDef, but rather in the context of the database. So in this case of name overlap, you have to qualify which name (property) you want. Kind of guessing here, because as previously stated, Access inner code is not visible to us.

Can anyone see why the OpenRecordset requires the db. prefix with this particular code?

Colin wasn't using a nested With block for the QueryDef, so @KitaYama explains it exactly in Post #54

The QueryDef.OpenRecordset method does not take a table/sql argument.

Here's one other way you can do this (untested), without a nested With block, and without assigning an object variable:

' sql = ...

With CurrentDb.CreateQueryDef("", sql)
    .Parameters("P1") = txtManuPK
    .Parameters("P2") = ProcessFK
    .Parameters("P3") = cmbUser
    .Parameters("P4") = GetCurrentDate
    .Parameters("P5") = txtManuCount
    .Parameters("P6") = cmbInspector
    .Execute dbFailOnError
    If .RecordsAffected > 0 Then
        .SQL = "select @@identity"   ' <-- reset the QueryDef sql here, then open recordset
        fltr = fltr & "," & .OpenRecordset()(0)
    Else
        MsgBox "Failed"
        Exit Sub
    End If
End With

 

[The_Doc_Man]

Even though there is no nesting, the WITH block contains TWO objects that each contain a .RecordsAffected property - the CreateQueryDef results in a querydef which is passed in to CurrentDB which ALSO contains a .RecordsAffected property - all of that in a single WITH block. So, between the query def object and the current DB object, which one is closer?

 

[cheekybuddha]

So, between the query def object and the current DB object, which one is closer?

You will be using the .RecordsAffected of the QueryDef. (which i supect is just a pointer to the Database.RecordsAffected (CurrentDb) property!)

Within that With block you do not have direct access to any of the CurrentDb properties, only the QueryDef properties

 

[The_Doc_Man]

Within that With block you do not have direct access to any of the CurrentDb properties, only the QueryDef properties

Sure you do... if you qualify the reference. The WITH block doesn't HIDE things; it merely exposes some things more openly. The DB reference is external to the block but local to the routine holding that code. If you found a DB property that was NOT a querydef property, you would be able to see it even if not qualified, I think, since the DB is part of the WITH reference. OK, that's a guess - but I think that is the way it works.

 

[GregDataReno]

CurrentDB() always creates a new instance - I recommend always declaring an object for CurrentDB() and using that object since CurrentDB() will always instantiate a new instance each time you use it.

 

[nector]

My friends why wasting time over nothing simply use the select query once you are sure that what you want to see is correct then convert that select query to either Update or Append within the query design. After that , then go to your VBA and reference your query like below:

DoCmd.SetWarnings False
DoCmd.OpenQuery "QryGrnUpdates"

 

[ebs17]

If you have very low demands, you can do it very easily ...