Number of record updated

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hello to everybody,
I have an update query and at the end of the process, I would a message box
with for example: "total record updated: 35".
How my I do this as I am able to write only a few line of VBA

Thanks for your help
Regards
John
 
Dim dbCurr As DAO.Database
Dim lngRecordsAffected As Long
Dim strSQL As String

Set dbCurr = CurrentDB()
strSQL = "UPDATE..."
dbCurr.Execute strSQL, dbFailOnError
lngRecordsAffected = dbCurr.RecordsAffected
MsgBox "You updated " & lngRecordsAffected & _
IIf(lngRecordsAffected = 1, " record.", " records.")

Note: The only reason for lngRecordsAffected is because I'm not 100% certain
that you can call dbCurr.RecordsAffected twice (and I'm too lazy to test at
the moment <g>). If you don't care about the record vs records subtlety, you
could easily just use:

Dim dbCurr As DAO.Database
Dim strSQL As String

Set dbCurr = CurrentDB()
strSQL = "UPDATE..."
dbCurr.Execute strSQL, dbFailOnError
MsgBox "You updated " & dbCurr.RecordsAffected & _
" records."
 
Hi,
thanks for your help; however, if possible could you be so kind to help me
to combine your linecode with the one I am using with a click on a button?

Thanks for your co-operation
Regards
John



Private Sub Update_Click()
On Error GoTo Err_Update_Click
Dim LResponse As Integer
Dim StrText As String

StrText = "Do you want to update the form Public? "
LResponse = MsgBox(StrText , vbYesNo + vbCritical, "Public")

If LResponse = vbYes Then
Dim stDocName As String
stDocName = "Public_Update"
DoCmd.OpenQuery stDocName, acNormal, acEdit
MsgBox ("Update finished"), vbOKOnly, "Public"
Else
DoCmd.CancelEvent
End If
 
Assuming "Public_Update" is the name of a stored query, try:

Private Sub Update_Click()
On Error GoTo Err_Update_Click
Dim LResponse As Integer
Dim StrText As String

StrText = "Do you want to update the form Public? "
LResponse = MsgBox(StrText , vbYesNo + vbCritical, "Public")

If LResponse = vbYes Then
Dim qdfUpdate As DAO.QueryDef

Set qdfUpdate = CurrentDb.Querydefs("Public_Update")
qdfUpdate.Execute, dbFailOnError

MsgBox "You updated " & qdfUpdate.RecordsAffected & " records",
vbOKOnly, "Public"

Set qdfUpdate = Nothing
Else
DoCmd.CancelEvent
End If
 
Unfortunatly I am still experienced difficulties.
The message error is "to few parameters, expected 4".
I think because the update query is parameterized query and I should pass
the parameter in the code.

The SQL view is

UPDATE Esercizi_pubblici SET Esercizi_pubblici.Attività =
[Forms]![Esercizi_pubblici_Update]![CboAttività],
Esercizi_pubblici.Tipologia_Esercizio =
[Forms]![Esercizi_pubblici_Update]![CboEsercizio]
WHERE
(((Esercizi_pubblici.Attività)=[Forms]![Esercizi_pubblici_Update]![Attivita])
AND
((Esercizi_pubblici.Tipologia_Esercizio)=[Forms]![Esercizi_pubblici_Update]![Esercizio]));

How may I integrated this in the code?

Thanks again for your help and time.
John
 
You'd be far better off using the original code I gave, where you build the
SQL in code and execute that string, rather than using a stored query.

Depending on what the data type of your fields are, it would be something
like:

strSQL = "UPDATE Esercizi_pubblici " & _
"SET Esercizi_pubblici.Attività = " & _
[Forms]![Esercizi_pubblici_Update]![CboAttività] & _
", Esercizi_pubblici.Tipologia_Esercizio = " & _
[Forms]![Esercizi_pubblici_Update]![CboEsercizio] & _
"WHERE Esercizi_pubblici.Attività = " & _
[Forms]![Esercizi_pubblici_Update]![Attivita] & _
" AND " & _
"Esercizi_pubblici.Tipologia_Esercizio) = " & _
[Forms]![Esercizi_pubblici_Update]![Esercizio]

That assumes that all of the fields are numeric. For text fields, you need
to ensure that there are quotes around the values that are bing passed. For
instance, if Attività is a text field, you'd use

strSQL = "UPDATE Esercizi_pubblici " & _
"SET Esercizi_pubblici.Attività = " & _
Chr$(34) & _
[Forms]![Esercizi_pubblici_Update]![CboAttività] & _
Chr$(34) & _
", Esercizi_pubblici.Tipologia_Esercizio = " & _
[Forms]![Esercizi_pubblici_Update]![CboEsercizio] & _
"WHERE Esercizi_pubblici.Attività = " & _
[Forms]![Esercizi_pubblici_Update]![Attivita] & _
" AND " & _
"Esercizi_pubblici.Tipologia_Esercizio) = " & _
[Forms]![Esercizi_pubblici_Update]![Esercizio]


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


John said:
Unfortunatly I am still experienced difficulties.
The message error is "to few parameters, expected 4".
I think because the update query is parameterized query and I should pass
the parameter in the code.

The SQL view is

UPDATE Esercizi_pubblici SET Esercizi_pubblici.Attività =
[Forms]![Esercizi_pubblici_Update]![CboAttività],
Esercizi_pubblici.Tipologia_Esercizio =
[Forms]![Esercizi_pubblici_Update]![CboEsercizio]
WHERE
(((Esercizi_pubblici.Attività)=[Forms]![Esercizi_pubblici_Update]![Attivita]
)
AND
Click to expand...
((Esercizi_pubblici.Tipologia_Esercizio)=[Forms]![Esercizi_pubblici_Update]!
[Esercizio]));

How may I integrated this in the code?

Thanks again for your help and time.
John
Click to expand...
 
Thanks again for your patients. Now it works well. The reasons why I wasn't
able to work with the original code is my lack of knowledge in this mattern.

Cordially
Giovanni
 
Back
Top