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
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