When Update Query update No rows in a field (Because Condition not met) Error Message not Appear

[Hamatto]

Hello friends,
Please, I want your help about the following Code
it is about Update Query (Query1) which update a field of a table by a value ( 1 for example) in the range when a condition of another field met and give message"That's Good"
Put when the condition not met (the query not update any thing Or update 0 Rows) give message "That's wrong"
The problem is
When the condition not met the error message not appear.What is the wrong in this code?
I want your help to solve this problem
Any help will be really appreciated
Thanks a lot

Private Sub Button1_Click()
On Error GoTo err:
   SetOption "Confirm Action Queries", False
    Dim stDocName As String
    stDocName = "Query1"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    MsgBox "That is True"
    Exit Sub
err:
    MsgBox "That is wrong"
End Sub

 

[The_Doc_Man]

If Query1 is an action query, failing to satisfy the constraints of a WHERE clause is not a reportable error; it is a logic error. It won't trip the error trap. Only a syntax or object-name/usage error would trigger the error.

You could use a sequence such as this:

Private Sub Button1_Click()
DIM CurDB as DAO.Database
DIM RecAff as Long
On Error GoTo SawError
    SET CurDB=CurrentDB
    CurDB.Execute Query1, acFailOnError
    RecAff = CurDb.RecordsAffected
    If RecAff = 0 Then
        MsgBox "Did nothing"
    Else
        MsgBox "Hit " & CStr(RecAff) & " records"
    End If
WeGone:
    Exit Sub
SawError:
    MsgBox Err.Description
    GoTo WeGone
End Sub

You don't need to turn off the Confirm Action Queries option because .Execute avoids that interaction altogether. The .Execute is only used for action queries and the acFailOnError option triggers a rollback if the query fails for syntax/usage errors. Otherwise you ask the CurDb object (which is a separate instance of CurrentDB) how many records were affected by the last action. If your WHERE clause was too restrictive, you would be told that it was ineffective. Otherwise you would get a count of how many records actually changed.

 

[MarkK]

You could also do...

Private Sub Button1_Click()
    With CurrentDb.QueryDefs("Query1")
        .Execute
        MsgBox .RecordsAffected & " record(s) were affected."
    End With
End Sub

 

[Gasman]

Perhaps carry out a DCount() with same criteria to see if anyyhing will ne updated?

 

[Hamatto]

Perhaps carry out a DCount() with same criteria to see if anyyhing will ne updated?

Please, May you give me Example with my Code?

 

[Gasman]

Please, May you give me Example with my Code?

No, because no one can see your criteria?
It is just a simple DCOUNT()

Start learning Access., and do not expect everything to be handed to you on a plate.
You will learn very little, if anything if that happens.

 

[Hamatto]

Thanks a lot my friends to tried helping me
Really,I tried all you advices but Some not work with me
I tried to make changes ,then I get the following Code which work with me.
1- when the condition met ,the Update query works , and I see that the Table updated exactly as I want
2- When the condition not met the Update query not update the Table exactly as I want
THE PROBLEM is in 1 Or 2 (if the condition met or no met I get the same message "Sorry.No Records Updated" after the query work
The problem is only in message"Good. Records Updated" which not appear even the condition met
I tried many things and found that the problem is with the Veriable (RecAff) which take the same value either the Records updated or not updated
I want your help to solve this problem in this Code
Any help will be really appreciated
Thanks a lot

Private Sub Button1_Click()
Dim CurDB As DAO.Database
Dim stDocName As String
Dim RecAff As Long

On Error GoTo err
   SetOption "Confirm Action Queries", False
   Set CurDB = CurrentDb
    
   stDocName = "Query1"
   DoCmd.OpenQuery stDocName, acNormal, acEdit
  
   RecAff = CurDB.RecordsAffected

    If RecAff = 0 Then
        MsgBox "Sorry.No Records Updated"
    Exit Sub
    Else
        MsgBox "Good. Records Updated"
    Exit Sub
    End If
err:
    MsgBox err.Description
    Exit Sub
    End Sub

 

[ebs17]

RecAff = CurDB.RecordsAffected

A value other than 0 can only be generated here if the database object (CurrDb) is also used in the query.
Using OpenQuery is something completely different and particularly counterproductive here.
Not just copy a little bit, copy everything.

Small correction
CurDB.Execute Query1, acFailOnError dbFailOnError

 

[Hamatto]

RecAff = CurDB.RecordsAffected

A value other than 0 can only be generated here if the database object (CurrDb) is also used in the query.
Using OpenQuery is something completely different and particularly counterproductive here.
Not just copy a little bit, copy everything.

Small correction
CurDB.Execute Query1, acFailOnError dbFailOnError

Thanks a lot to reply
Excuse me, I didn't understand
Please May you tell me How Use CurrDB in the query or How to correct my Code ? the easier ,Please

 

[ebs17]

tell me How Use CurrDB in the query

Use the code from @The_Doc_Man, with the small correction mentioned.

Dim CurDB As DAO.Database ' => this is a DAO object
DoCmd => this is an Access object

Two objects that know nothing about each other. If you let one object work, the other one doesn't know what's happening. So
DoCmd.OpenQuery "Query1", acNormal, acEdit
CurDB.Execute "Query1", dbFailOnError

 

[The_Doc_Man]

Small correction
CurDB.Execute Query1, acFailOnError dbFailOnError

@Hamatto - Apologies - I ALWAYS get the acxxx symbols and the dbxxx symbols with the wrong prefix. My late-night memory isn't what it used to be... (or maybe never was.) But the idea was right.

If you want to see the records affected, use the CurDB.Execute, dbFailOnError to run the query rather than using DoCmd to run the query. Using DoCmd doesn't use the CurDB object so there is no way CurDB can count affected records. You must stay consistent in your usage for this to work.

And just to be clear, if you use DoCmd for your action queries then you cannot take advantage of any internal action counting because of the way Access uses the default database pathway for DoCmd.

The only OTHER relatively easy way to know if you had any effect involves previously running a DCOUNT() function that has the same criteria as the WHERE clause in your action query. Then you could see how many records it thinks it would touch.

 

[The_Doc_Man]

Can I update any data with the help of phpMyadmin?

Just as a suggestion, you would do better to post a divergent question like this one as a new post rather than a reply to another post. You might bet more attention that way. The original post was about queries. You are bringing in a new product not mentioned by the original poster.

 

[Isaac]

Hello friends,
Please, I want your help about the following Code
it is about Update Query (Query1) which update a field of a table by a value ( 1 for example) in the range when a condition of another field met and give message"That's Good"
Put when the condition not met (the query not update any thing Or update 0 Rows) give message "That's wrong"
The problem is
When the condition not met the error message not appear.What is the wrong in this code?
I want your help to solve this problem
Any help will be really appreciated
Thanks a lot

Private Sub Button1_Click()
On Error GoTo err:
   SetOption "Confirm Action Queries", False
    Dim stDocName As String
    stDocName = "Query1"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    MsgBox "That is True"
    Exit Sub
err:
    MsgBox "That is wrong"
End Sub

Please study error handling, CurrentDb.Execute, and SetWarnings.
Do not open queries to execute them. This is like opening up the internal wiring of an electronics toy just in order for a child to play with it.