Restrictions Update Message (1 Viewer)

azhar2006

Registered User.
Local time
Yesterday, 22:38
Joined
Feb 8, 2012
Messages
289
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
Code:
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
 
Please do not delete threads. That does not help others. :(
It was not spelling errors at all, but incorrect field names.
 
Immediately after your line that reads:

Code:
db.Execute StrSQL, dbFailOnError
Insert a line that is more or less equivalent to
Code:
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.
 
I think you are going to have to sum x from docs post as you are working on each record in your loop.
 
34.PNG

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."
 
Last edited:
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.
 
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? :(

Code:
intCounter = rs.RecordCount
MsgBox " You are about to update " &amp; intCounter &amp; " 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. :)
 
Last edited:
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.
 
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.
post a sample db so others may see your query result.
 
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.
Good evening.
Thank you Pat Hartman
for your interaction with my topic and thanks to all the brothers in the wonderful forum. I will attach a part of the project with an explanatory image. Note the image that only those who have the same number in the (StatisticalNO) field will be updated if their data is changed in Table (A). Let's assume that we have updated the data of only two soldiers. The content of the message is supposed to say that a record has been updated. It is not supposed to calculate all records in the database. If you look at the project, it works fine and it updates the records that have been changed. The problem lies in the message only. Thank you very much
22222.PNG
 

Attachments

Your code is incorrect, you do not seem to undertand the logic? :(

You display
1727086408578.png

Yet as I have already mentioned, you and the code have no idea yet, what you are going to update. :(

I changed the bottom message and added code to identify how many records were updated.
Think about it, you can only find out how many records were updated AFTER you have done the update, OR by doing a check with the same criteria before the update. Plus you are only doing one input record at a time? What if there are two matching records in Table B for a record in Table A?
1727086370560.png
 
here test the code.
note that i make backup of table2 so you can restore it if you want.
 

Attachments

@arnelgp Now you are really going to confuse the O/P :)

He is trying to show he has only updated 3 out of 4 records and you are updating/adding all 4 records? :)

@azhar2006 Probably the easiest option is to remove that first message, as it means nothing to the process. What if you had 2000 records in that query? Is the user meant to know how many records are going to be updated out of that 2000 ?
 
He is trying to show he has only updated 3 out of 4 records and you are updating/adding all 4 records?
probably, but i don't know, for sure if i am the 4th record, i don't want to be left out of the promotion.
 
probably, but i don't know, for sure if i am the 4th record, i don't want to be left out of the promotion.
I think that is the whole crux of the issue, else his first message would always be correct. :)
 
Just use this query and you will get what you want. :(
Plus the prompt of how many records will be updated. :)

Code:
UPDATE TableA INNER JOIN TableB ON TableA.StatisticalNO = TableB.StatisticalNO SET TableB.Rank = [TableA].[Rank], TableB.PromotionNumber = [Tablea].[PromotionNumber], TableB.PromotionDate = [tablea].[promotionDate];
 
Last edited:

Users who are viewing this thread

Back
Top Bottom