Why RecordsAffected returns 0 for this query

K

KitaYama

Well-known member
Local time
Tomorrow, 01:54
Joined
Jan 6, 2022
Messages
2,131
I'm executing this sql.
But AffectedRecords returns 0.
The record is successfully added to the table.

Why is that?

Thanks.

Code: 
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
 
Last edited:
Sort by date Sort by votes
I agree. When you call CurrentDb, you instantiate a new object.
 
Upvote 0 Downvote
As I had guessed, adding CurrentDB to a variable and use the variable to run the qury has the same problem.

Thanks though.

Code: 
Dim db As DAO.Database
Set db = CurrentDb
    
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 db.CreateQueryDef("", sql)
    .Parameters("P1") = txtManuPK
    .Parameters("P2") = ProcessFK
    .Parameters("P3") = cmbUser
    .Parameters("P4") = GetCurrentDate
    .Parameters("P5") = txtManuCount
    .Parameters("P6") = cmbInspector
    .Execute dbFailOnError
    .Close
    If db.RecordsAffected > 0 Then
        fltr = fltr & "," & .OpenRecordset("select @@identity")(0)
    Else
        MsgBox "Failed"
        Exit Sub
    End If
End With
 
Upvote 0 Downvote
KitaYama said:
As I had guessed, adding CurrentDB to a variable and use the variable to run the qury has the same problem.

Thanks though.

Code: 
Dim db As DAO.Database
Set db = CurrentDb
   
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 db.CreateQueryDef("", sql)
    .Parameters("P1") = txtManuPK
    .Parameters("P2") = ProcessFK
    .Parameters("P3") = cmbUser
    .Parameters("P4") = GetCurrentDate
    .Parameters("P5") = txtManuCount
    .Parameters("P6") = cmbInspector
    .Execute dbFailOnError
    .Close
    If db.RecordsAffected > 0 Then
        fltr = fltr & "," & .OpenRecordset("select @@identity")(0)
    Else
        MsgBox "Failed"
        Exit Sub
    End If
End With
Click to expand...
I would try it this way:
Code: 
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 .RecordsAffected > 0 Then
       fltr = fltr & "," & .OpenRecordset("select @@identity")(0)
     Else
        MsgBox "Failed"
        Exit Sub
     End If
End With
Hope that helps...
 
Upvote 1 Downvote
Don't call .Close until after you have looked at .RecordsAffected
Code: 
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
       fltr = fltr & "," & .OpenRecordset("select @@identity")(0)
     Else
        MsgBox "Failed"
        Exit Sub
     End If
    .Close
End With
You probably don't even need it at all
 
Upvote 1 Downvote
theDBguy said:
I would try it this way:
Code: 
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 .RecordsAffected > 0 Then
       fltr = fltr & "," & .OpenRecordset("select @@identity")(0)
     Else
        MsgBox "Failed"
        Exit Sub
     End If
End With
Hope that helps...
Click to expand...
@theDBguy I think I'm blind.
I can't find the difference.
Any hint?

thanks.
 
Upvote 0 Downvote
cheekybuddha said:
Don't call .Close until after you have looked at .RecordsAffected
Code: 
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
       fltr = fltr & "," & .OpenRecordset("select @@identity")(0)
     Else
        MsgBox "Failed"
        Exit Sub
     End If
    .Close
End With
You probably don't even need it at all
Click to expand...
I've already tested it.
The same result.
I closed it after setting the filter.
And the problem was there.

I'll test one more time.
thanks.
 
Upvote 0 Downvote
After several test using given hints, the following seems to be OK.

Code: 
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 .RecordsAffected > 0 Then
        fltr = fltr & "," & CurrentDb.OpenRecordset("select @@identity")(0)
    Else
        MsgBox "Failed"
        Exit Sub
    End If
End With

Now the question is why if I use :
If CurrentDB.RecordsAffected=0
The result is always true.

And

Why removing CurrentDB from following line returns error.
fltr = fltr & "," & CurrentDb.OpenRecordset("select @@identity")(0)

I mean isn't it strange :
If .RecordsAffected > 0 Then
fltr = fltr & "," & CurrentDb.OpenRecordset("select @@identity")(0)

Why the first (blue) doesn't need currentDB, but the second(red) needs?
 
Upvote 0 Downvote
The last result is ;
.RecordsAffected ---> Currentdb (or the variable) should be omitted.
.OpenRecordset ------> currentDb (or the variable) should be added.

It seems that .RecordsAffected is a member of CurrentDB.CreateQueryDef("",sql)
But for .OpenRecordset, CurrentDB should be accessed.
 
Upvote 1 Downvote
KitaYama said:
later I tested your code.
You had the same error on the following line. The same as @cheekybuddha

fltr= fltr & " .openRecordset("Select@@identity")(0)
Click to expand...
Ah, okay. I couldn't test your code, so I was only focusing on the original problem. Glad to hear you got it sorted out.
 
Upvote 0 Downvote
You must log in or register to reply here.

Similar threads

M
Fun/side project: Build dynamic SELECT query using ADODB.Command / .Parameters objs
Replies
1
Views
3,813
mdlueck
M
M
Example of SQL INSERT / UPDATE using ADODB.Command and ADODB.Parameters objects
Replies
3
Views
117,241
mdlueck
M
E
Return a value
Replies
1
Views
705
Rich
R

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom