turnoff warning message when execute update query

T

thecuongjapan

Hi all
I'm really stuck. Please help me.

I have a simple form with a button that have following code:
dim strSQL as string
strSQL="update empoyees table set salary = " & me.lstsalary.column(0) & _
" where workingyears = " & me.lstworkingyears.column(0)
docmd.runSQL

Above code is run OK but each time I hit this bitton, a warning message said
that you are about to updated n recordes or something like that. Very
annoying.
Could anyone tell me how to turn this message off by using VBA.
Thanks
 
D

Dirk Goldgar

thecuongjapan said:
Hi all
I'm really stuck. Please help me.

I have a simple form with a button that have following code:
dim strSQL as string
strSQL="update empoyees table set salary = " & me.lstsalary.column(0)
& _ " where workingyears = " & me.lstworkingyears.column(0)
docmd.runSQL

Above code is run OK but each time I hit this bitton, a warning
message said that you are about to updated n recordes or something
like that. Very annoying.
Could anyone tell me how to turn this message off by using VBA.
Thanks
Click to expand...

You can turn the message off, but I prefer to use a method that doesn't
raise the warning at all.

First, turning the message off:

'----- start of example code #1 -----

On Error GoTo Err_Handler

Dim strSQL As String

strSQL = _
"update empoyees table set salary = " & _
me.lstsalary.column(0) & _
" where workingyears = " & me.lstworkingyears.column(0)

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

Exit_Point:
Exit Sub ' or function

Err_Handler:
DoCmd.SetWarnings True
Msgbox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point
'----- end example code #1 -----

You'll notice that I included the error-handling code. That's because,
if you turn warnings off, you'd better make very sure that there's no
way to exit your procedure without turning them back on again.

Now that I've shown you the cumbersome way, here's the easy way:

'----- start of example code #2 -----

Dim strSQL As String

strSQL = _
"update empoyees table set salary = " & _
me.lstsalary.column(0) & _
" where workingyears = " & me.lstworkingyears.column(0)

CurrentDb.Execute strSQL, dbFailOnError

'----- end of example code #2 -----

Because you'v built a SQL statement that doesn't rely on Access to
resolve references to controls on forms, you can just have the database
execute it via DAO. Look, Ma -- no warnings!
 
J

John Vinson

Hi all
I'm really stuck. Please help me.

I have a simple form with a button that have following code:
dim strSQL as string
strSQL="update empoyees table set salary = " & me.lstsalary.column(0) & _
" where workingyears = " & me.lstworkingyears.column(0)
docmd.runSQL

Above code is run OK but each time I hit this bitton, a warning message said
that you are about to updated n recordes or something like that. Very
annoying.
Could anyone tell me how to turn this message off by using VBA.
Thanks
Click to expand...

Two ways:

1.

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

2. (better, albeit more complex, because you can trap errors)

Dim db As DAO.Database
Dim qd As DAO.Querydef
Dim strSQL As String
On Error GoTo Proc_Error
strSQL = "... <your query> ..."
Set db = CurrentDb\
' create an unnamed and therefore unsaved Query
Set qd = db.CreateQuerydef("", strSQL)
qd.Execute dbFailOnError
If qd.RecordsAffected = 0 Then
<maybe post an error message if it did nothing>
End If
Set qd = Nothing
Proc_Exit: Exit Sub

Proc_Error:
<handle the error condition appropriately>
Resume Proc_Exit
End Sub

John W. Vinson[MVP]
 
D

Dirk Goldgar

John Vinson said:
[...]
2. (better, albeit more complex, because you can trap errors)

Dim db As DAO.Database
Dim qd As DAO.Querydef
Dim strSQL As String
On Error GoTo Proc_Error
strSQL = "... <your query> ..."
Set db = CurrentDb\
' create an unnamed and therefore unsaved Query
Set qd = db.CreateQuerydef("", strSQL)
qd.Execute dbFailOnError
If qd.RecordsAffected = 0 Then
<maybe post an error message if it did nothing>
End If
Set qd = Nothing
Proc_Exit: Exit Sub

Proc_Error:
<handle the error condition appropriately>
Resume Proc_Exit
End Sub
Click to expand...

John, any reason you prefer creating a temporary querydef to just
executing the SQL statement via the Database object; e.g.,

With CurrentDb
.Execute strSQL, dbFailOnError
If .RecordsAffected = 0 Then
' ... whatever
End If
End With

?

I would certainly use a querydef if I needed to evaluate parameters,
such as control references, but I can usually embed the control values
when I'm building SQL in code.
 
Top