Trapping specific errors

[GLT]

Hi,

I have the code below on a button click. The code itself works fine,
however I would like the specific error when a dulicate record is added, to
be displayed in a message box with an exclamation point.

Whenever I force this error, nothing happens. Can anyone advise what I am
doing incorrectly?

Any help is always greatly appreciated...

Cheers,
GLT.

Private Sub Command21_Click()

On Error GoTo Command21_Click_Err

Dim strSQL As String

DoCmd.SetWarnings False

strSQL = "INSERT INTO tblPermSrvcsIgnore " & _
"( Type, Server, [Service Name] )" & _
" SELECT [FORMS]![frmAddPermNoMon]![fldSelShutType] AS Type" & _
", IIf([FORMS]![frmAddPermNoMon]![Frame7]=2, 'ALL', " & _
"[FORMS]![frmAddPermNoMon]![fldSelServer]) AS Server" & _
", [FORMS]![frmAddPermNoMon]![fldSelService] AS [Service Name];"

DoCmd.RunSQL strSQL

Me![tblPermSrvcsIgnore SubForm].Requery

DoCmd.SetWarnings True

Command21_Click_Exit:
Exit Sub
Command21_Click_Err:
DoCmd.SetWarnings True
MsgBox "This record already exists..."
Resume Command21_Click_Exit

End Sub

 

[Tom van Stiphout]

On Sun, 31 Jan 2010 21:14:01 -0800, GLT

Rather than DoCmd.RunSQL use:
CurrentDB.Execute strSQL, dbFailOnError

-Tom.
Microsoft Access MVP

 

[GLT]

Hi Tom,

Thanks for your reply - I did as you suggest and when I forct it to error
(by adding a duplicate record), it comes up with the following error:

Too Frew Parameters. Expected 4.

So, would i test the Error word for the above string, and then use a message
box of my own, or is there a message # or equivelent that I can check on?

Cheers,
GLT.

On Sun, 31 Jan 2010 21:14:01 -0800, GLT

Rather than DoCmd.RunSQL use:
CurrentDB.Execute strSQL, dbFailOnError

-Tom.
Microsoft Access MVP

Hi,

I have the code below on a button click. The code itself works fine,
however I would like the specific error when a dulicate record is added, to
be displayed in a message box with an exclamation point.

Whenever I force this error, nothing happens. Can anyone advise what I am
doing incorrectly?

Any help is always greatly appreciated...

Cheers,
GLT.

Private Sub Command21_Click()

On Error GoTo Command21_Click_Err

Dim strSQL As String

DoCmd.SetWarnings False

strSQL = "INSERT INTO tblPermSrvcsIgnore " & _
"( Type, Server, [Service Name] )" & _
" SELECT [FORMS]![frmAddPermNoMon]![fldSelShutType] AS Type" & _
", IIf([FORMS]![frmAddPermNoMon]![Frame7]=2, 'ALL', " & _
"[FORMS]![frmAddPermNoMon]![fldSelServer]) AS Server" & _
", [FORMS]![frmAddPermNoMon]![fldSelService] AS [Service Name];"

DoCmd.RunSQL strSQL

Me![tblPermSrvcsIgnore SubForm].Requery

DoCmd.SetWarnings True

Command21_Click_Exit:
Exit Sub
Command21_Click_Err:
DoCmd.SetWarnings True
MsgBox "This record already exists..."
Resume Command21_Click_Exit

End Sub

.

 

[Tom van Stiphout]

On Sun, 31 Jan 2010 22:41:01 -0800, GLT

Error numbers can be found using Err.Number.

However, that error should be fixed first; it is almost a design-time
error. Access can't evaluate the form references in your sql
statement. You can rewrite as:
" SELECT " & [FORMS]![frmAddPermNoMon]![fldSelShutType] & " AS Type" &
_
etc.

-Tom.
Microsoft Access MVP

Hi Tom,

Thanks for your reply - I did as you suggest and when I forct it to error
(by adding a duplicate record), it comes up with the following error:

Too Frew Parameters. Expected 4.

So, would i test the Error word for the above string, and then use a message
box of my own, or is there a message # or equivelent that I can check on?

Cheers,
GLT.

On Sun, 31 Jan 2010 21:14:01 -0800, GLT

Rather than DoCmd.RunSQL use:
CurrentDB.Execute strSQL, dbFailOnError

-Tom.
Microsoft Access MVP

Hi,

I have the code below on a button click. The code itself works fine,
however I would like the specific error when a dulicate record is added, to
be displayed in a message box with an exclamation point.

Whenever I force this error, nothing happens. Can anyone advise what I am
doing incorrectly?

Any help is always greatly appreciated...

Cheers,
GLT.

Private Sub Command21_Click()

On Error GoTo Command21_Click_Err

Dim strSQL As String

DoCmd.SetWarnings False

strSQL = "INSERT INTO tblPermSrvcsIgnore " & _
"( Type, Server, [Service Name] )" & _
" SELECT [FORMS]![frmAddPermNoMon]![fldSelShutType] AS Type" & _
", IIf([FORMS]![frmAddPermNoMon]![Frame7]=2, 'ALL', " & _
"[FORMS]![frmAddPermNoMon]![fldSelServer]) AS Server" & _
", [FORMS]![frmAddPermNoMon]![fldSelService] AS [Service Name];"

DoCmd.RunSQL strSQL

Me![tblPermSrvcsIgnore SubForm].Requery

DoCmd.SetWarnings True

Command21_Click_Exit:
Exit Sub
Command21_Click_Err:
DoCmd.SetWarnings True
MsgBox "This record already exists..."
Resume Command21_Click_Exit

End Sub

.