Using MS Access/Error Message

Advertisement


Question
QUESTION: Hi Scott,
Is there any way to get rid of a system generated error message (3022) and replace it with my own?  I don't wnat the user to be able to click on a debug button on an error message that shows the code on the back end of a command button.  Thanks for all your help!!
Carla  :)

ANSWER: Sure its called Error Trapping. Here's an example:

Private Sub MfgBatchorLotNum_AfterUpdate()
On Error GoTo Err_MfgBatchorLotNum

If Me.cboVendor = 68 Then
   Me.MfgDate = DateSerial(CInt(Mid(Me.MfgBatchorLotNum, 4, 2)), CInt(Mid(Me.MfgBatchorLotNum, 7, 2)), 1)
End If

Exit_MfgBatchorLotNum:
   Exit Sub

Err_MfgBatchorLotNum:
   If Err.Number = 13 Then
       MsgBox "The Batch number doesn't include the Mfg Date, please enter the Mfg Date!"
       Me.MfgDate.SetFocus
   Else
       MsgBox Err.Number & "-" & Err.Description
   End If

End Sub

When an error is encountered it goes to Err_MfgBatchorLotNum where it tests the error #. If a specific error is found it deals with it or passes to a generic error message.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Ok, so if I have the following code on my button which opens a report showing the current record, how exactly do I write this code along with what I already have.  The code above looks like an example using date criteria.  I want the error to pop up in the case that the user has attempted to enter an ID number that has already been entered.  Thanks again for all of your help.  I appreciate it more than words can say.
Carla  :)

Private Sub Command149_Click()

Dim strReportName As String
Dim strCriteria As String

Me.Dirty = False

strReportName = "REQUISITION"
strCriteria = "[PO_ID]='" & Me![PO_ID] & "'"
DoCmd.OpenReport strReportName, acViewPreview, , strCriteria

End Sub

Answer
First I suggest you use more descriptive object names. Command149 doesn't tell you what the button does.So I would rename the button cmdPreview and change the code to:

Private Sub cmdPreview_Click()

Dim strReportName As String
Dim strCriteria As String

On Error goto Err_cmdPreview

Me.Dirty = False

strReportName = "REQUISITION"
strCriteria = "[PO_ID]='" & Me![PO_ID] & "'"
DoCmd.OpenReport strReportName, acViewPreview, , strCriteria

Exit_cmdPreview:
    Exit Sub

Err_cmdPreview:
    MsgBox Err.Number & "-" & Err.Description

End Sub

I don't know what error number the condition you refer to will throw. So you have to test it, see what number it is then trap for that number, as my example trapped for Error code 13.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.