annoying message

  • Thread starter Thread starter KM
  • Start date Start date
K

KM

This small VBA script adds a new post i the table. But when I run it
from, a messagebox like "You are about to add 1 row(s)" (translated from
danish). The messagebox contains a Yes an No button.

The script is like this:
Dim sSql As String
sSql = "INSERT INTO sagsoversigt (sagsnavn) values ('" &
Form_h2o_hovedmenu.txtSagsNavn.Value & "')"

DoCmd.RunSQL sSql

Is there anyway to avoid the message?

TIA
Kaare
 
From your main database menu bar Tools->Options->Edit/Find.

Uncheck everythin under Confirm.
 
KM said:
This small VBA script adds a new post i the table. But when I run it
from, a messagebox like "You are about to add 1 row(s)" (translated from
danish). The messagebox contains a Yes an No button.

The script is like this:
Dim sSql As String
sSql = "INSERT INTO sagsoversigt (sagsnavn) values ('" &
Form_h2o_hovedmenu.txtSagsNavn.Value & "')"

DoCmd.RunSQL sSql

Is there anyway to avoid the message?

TIA
Kaare
Click to expand...


You can use DoCmd.SetWarnings False - but just make sure you use error
handling so that they are always turned back on again. Here is an example:

Private Sub AddRecord()

On Error GoTo Err_Handler

Dim strSQL As String

strSQL = "INSERT INTO Table1 (ID) VALUES (10)"

DoCmd.SetWarnings False

DoCmd.RunSQL strSQL

Exit_Handler:
DoCmd.SetWarnings True
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub



However, what would happen if the statement didn't work - you would get no
feedback. It would be better to use a function which returned a true/false
value to check that the row really did get inserted. This example makes use
of the DAO object library:

Private Function AddRecord() As Boolean

On Error GoTo Err_Handler

Dim strSQL As String
Dim dbs As DAO.Database

strSQL = "INSERT INTO Table1 (ID) VALUES (10)"

Set dbs = CurrentDb

dbs.Execute strSQL, dbFailOnError

If dbs.RecordsAffected = 1 Then
AddRecord = True
End If

Exit_Handler:

If Not dbs Is Nothing Then
Set dbs = Nothing
End If

Exit Function

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Function
 
From your main database menu bar Tools->Options->Edit/Find.

Uncheck everythin under Confirm.
Click to expand...

This is really asking for trouble: it means that all kinds of deletes and
highly-destructive operations will happen without notification.

The better way to avoid warnings is to control them in code:

DoCmd.SetWarnings False
DoCmd.RunSQL etc
DoCmd.SetWarnings True ' dont forget to switch them back on again!!


The best way is just not to use RunSQL: use proper database access
instead:

Set cn = CurrentProject()
cn.Execute strSQL, adCmdText, etc

or even bester using DAO,

Set db = CurrentDB()
db.Execute strSQL, dbFailOnError

which gives you a trappable error if the SQL went wrong.

Hope that helps


Tim F
 
Back
Top