Restrictions Update Message (2 Viewers)

[azhar2006]

Hello guys.
Thank you very much to everyone. I deleted the previous topic because I found the error in the code. The error was in the variable type and also spelling errors in the field names. I apologize to all of you, especially Mr. (Gasman).
Now I have another problem, which is the message that tells me that the records that I made changes to have been updated. But the message appears to me with all the records in the table and I want it to only tell me the number of records that have been updated or changed.
Thank you everyone

Private Sub cmdExecute_Click()


'On Error Resume Next
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strStatisticalNO As Long
Dim strRank As Variant
Dim strPromotionNumber As Variant
Dim strPromotionDate As Variant

Dim intCounter As Integer
Dim StrSQL As String

Set db = CurrentDb
Set rs = db.OpenRecordset("qryUnifiedNumber", dbOpenSnapshot)

'Make sure we have records and then
'make sure we are at the first record
If rs.RecordCount < 1 Then
    MsgBox "There is no new procedure for updating."
    Set rs = Nothing
    Set db = Nothing
    Exit Sub
End If
'rs.MoveFirst

rs.MoveLast
rs.MoveFirst
intCounter = rs.RecordCount

MsgBox " You are about to update " & intCounter & " Restrictions ", , "AZ"

'We need to loop through all of the records
'that our query object found
While rs.EOF = False

    strStatisticalNO = rs![StatisticalNO]
    strRank = rs![Rank]
    strPromotionNumber = rs![PromotionNumber]
    strPromotionDate = rs![PromotionDate]

    StrSQL = "UPDATE TableB SET TableB.Rank = '" & strRank & _
    "', PromotionNumber = '" & strPromotionNumber & "',  PromotionDate = #" & _
    strPromotionDate & "# WHERE ((TableB.StatisticalNO)=" & strStatisticalNO & ")"

    db.Execute StrSQL, dbFailOnError

rs.MoveNext

Wend
rs.Close
Set rs = Nothing
Set db = Nothing
MsgBox "update has been completed successfully."
End Sub

 

[Gasman]

Please do not delete threads. That does not help others.
It was not spelling errors at all, but incorrect field names.

 

[The_Doc_Man]

Immediately after your line that reads:

db.Execute StrSQL, dbFailOnError

Insert a line that is more or less equivalent to

x = db.RecordsAffected

where x is whatever integer or long variable you wanted. (Probably better to be LONG if there are tens of thousands of records to be considered.)

EDIT: DO NOT allow another instruction between the .Execute and the reference to .RecordsAffected. If ANYTHING touches variable db before you attempt to harvest the .RecordsAffected count, it will not contain the correct count.

 

[Gasman]

I think you are going to have to sum x from docs post as you are working on each record in your loop.

 

[azhar2006]

The same message appears to me, which is the total number of records in the table. While I have only updated two records. There is a technique in the query that tells you that you are about to run an update query, which will update a certain number of records.

Also, if I do not update the records, I get the same message, and it should show me another message saying: MsgBox "There is no new procedure for updating."

 

[Pat Hartman]

Thank you very much to everyone. I deleted the previous topic because I found the error in the code. The error was in the variable type and also spelling errors in the field names.

Do not delete threads that experts have replied to. The fact that you had a typo could easily have helped someone else.

The whole update procedure is wrong Why are you looping through a recordset and then running a separate update query for each row? You probably shouldn't be updating tableB anyway when you can simply join tableA to tableB to get the values.

 

[CJ_London]

Do not delete threads that experts have replied to.

I’ve undeleted it

 

[Gasman]

There is a technique in the query that tells you that you are about to run an update query, which will update a certain number of records.

Well, when you find it you can tell me, as that comes from a manual run of an update query.?

The only way I can think of doing this is to issue a DCount() with the same criteria as your update SQL.

You are not really getting the hang of the logic are you?

intCounter = rs.RecordCount
MsgBox " You are about to update " & intCounter & " Restrictions ", , "AZ"

That just tells you how many records you have in that recordset. You have not even started to update anything, so how can you expect the code to know at that time.

Your code is reading a recordset
For each record you *could* be updating one or more records if the StatNumber is found (which it was not in your last query)
You repeat that until the end of the recordset

As Doc has mentioned, he has told you a way to get how many records were updated, but that is at that time. Run the sql again and you will get another value. So you need to accumulate those numbers.

Remember, we do not know your data.
In that query, there could be a record for every record in tableB. We do not even know where that data is coming from. Do you?

Walk your code with F8 and breakpoints. See my signature link for debugging. That way you can see what the code is doing and then amend accordingly.

Just slapping code together and hoping it is going to work, or accepting code from others who *think* they know what you want, needs to be reviewed.

YOU have to do some of the work yourself, which at the moment, to me at least seems you do not want to. You just want it handed to you on a plate. I will not do that. I will however point you in the direction of where you are making mistakes, and where you should be looking. Sometimes I am wrong as well, but never intentionally. I am not malicious, but I do not suffer fools gladly, and that includes myself, when I make stupid mistakes, like I just did recently with a Voice question. But I learn from it and try to remember it.

Some do not and repeat the same mistakes over and over again.

So you put some effort into it, by that I mean take time to *understand* the logic. Then it should start to come natural to you.

If this was brain surgery, you would be out of a job,at a drop of the hat.

 

[Gasman]

I’ve undeleted it

Perhaps that should be prevented?
Some sites, there is even no way to delete a post, just amend it.

 

[Pat Hartman]

There is a technique in the query that tells you that you are about to run an update query, which will update a certain number of records.

The query did tell you how many records were being updated. Check your criteria. Run the query as a select query and you will see that it selects 6 records.