Novice wants a more descriptive error message 1

[smibarb]

Please note: I am NOT familiar with VBA so any replies please talk as much as possible in layman terminology. Thank you!

I have created a simple form to add data to a table. I have added two buttons, a "next" button and a "exit" button. When a user tries to enter a duplicate value in a field that does not allow duplicates, the error message is non-specific "?????????????????????????"

I have succeeded in modifying the error message to be more descriptive for the "next" button but not the "exit" button. I attempted the same code for both so I am not sure why? I am including my code below. I am using Access 97. Thank you so much.

Option Compare Database
Option Explicit



Private Sub btnNext_Click()
On Error GoTo Err_btnNext_Click


DoCmd.GoToRecord , , acNext

Exit_btnNext_Click:
Exit Sub

Err_btnNext_Click:
MsgBox ("ID already ordered")
Resume Exit_btnNext_Click

End Sub
Private Sub btnExit_Click()
On Error GoTo Err_btnExit_Click


DoCmd.Quit

Exit_btnExit_Click:
Exit Sub

Err_btnExit_Click:
MsgBox ("ID already ordered")
Resume Err_btnExit_Click

End Sub

 

[PHV]

In both procedures you display the same message despite the error raised !
You may try to replace this:
MsgBox ("ID already ordered")
with something like this:
MsgBox "Error " & Err.Number & ": " & Err.Description

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lameid]

PHV Raises a valid concern about you displaying the same error message no matter what happens. I realize this is not your question but bare with me. It could be that the file is corrupt and it would tell you that it is already ordered. However, you do want a custom message for the duplicates. So you need to conditionally display error messages. The Select Case statment is ideal because it allows you to handle as many specific cases as you want.

The below will work... Just use PHV's code and try and enter a duplicate. Note the error number and replcae below.

Private Sub btnNext_Click()
On Error GoTo Err_btnNext_Click


    DoCmd.GoToRecord , , acNext

Exit_btnNext_Click:
    Exit Sub

Err_btnNext_Click:
    Select Case Err.number
    Case <Error number indicating duplicates>
         MsgBox ("ID already ordered")
    Case Else
         MsgBox "Error " & Err.Number & ": " & Err.Description 
    End Select
    Resume Exit_btnNext_Click
    
End Sub

I think your exit code does not work because the record is not saved as part of the statment that closes the form...



Instead of just DoCmd.Quit
Try...

     docmd.RunCommand acCmdSaveRecord
     DoCmd.Quit

docmd.RunCommand acCmdSaveRecord as you may have guessed saves the record and if there is a duplicate it should cause an error you can trap like in the next button code.

 

[bjb123]

I use the select case error handling, like lameid posted, when I want to give more descriptive error messages.

The duplicate record error number is 3022, so just plug that into the error handling code posted above.

 

[TheAceMan1]

How are ya smibarb . . .

[blue]When a user tries to enter a duplicate value in a field that does not allow duplicates, the error message is non-specific . . .[/blue]

What you can do is check for duplicates yourself using a common function and take approrpriate action accordingly (displaying your message if duplicates are found).

So . . . in the code module of your form copy/paste the following function:

[blue]Public Function IsDuplicate(tblName As String, _
                            fldName As String, _
                           fldType As Boolean) As Boolean
   [green]'fldType: use True for Numeric, False for Text[/green]
   Dim Criteria As String
   Dim Msg As String, Style As Integer, Title As String, DL As String
   
   DL = vbnwline & vbNewLine
   Criteria = "[" & fldName & "] = '" & Me(fldName) & "'"
   If fldType Then Criteria = Replace(Criteria, "'", "")
   
   If Not IsNull(DLookup("[" & fldName & "]", tblName, Criteria)) Then
      Msg = "Duplicate '" & fldName & "' Found!" & DL & _
            "Click 'OK' to modify '" & fldName & "." & DL & _
            "Click 'Cancel' to abort saving the record."
      Style = vbInformation + vbOKCancel
      Title = "Duplicate Field Detected! . . ."
      
      If MsgBox(Msg, Style, Title) = vbOK Then
         Me(fldName).SetFocus
      Else
         Me.Undo [green]'ESC the field[/green]
         Me.Undo [green]'ESC the record[/green]
      End If
      
      IsDuplicate = True
   End If
   
End Function[/blue]

As an example of using the function consider I have a field named [blue]MemberID[/blue] with index set for no duplicates, who's data type is [blue]numeric[/blue] and resides in table [blue]tblMembers[/blue]. Your [blue]btnNext[/blue] event would look like:

[blue]Private Sub btnNext_Click()
   
   If Not IsDuplicate("tblMembers", "MemberID", True) Then
      DoCmd.GoToRecord , , acNext
   End If
    
End Sub[/blue]

Hope this helps . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[smibarb]

Thank you to all for your replies, but I have not been able to achieve anything except errors - did I mention I am a novice and not a programmer?

I want users to be able to use this form to add records that cannot be duplicated. From our users' point of view, after adding a record they either want to 'save and add next record', or 'save and exit'. Regardless of which button they use, if the record exists in the table already I want a descriptive error message (the same error message in both cases).

I am perplexed as to why I can achieve descriptive error messages with the Next button but not the Exit button.

When I try PHV's suggestion I get a descriptive message for the Next button but it is not the 3022 message I expect. It is "Error 2105: You can't go to the specified record. You may be at the end of a record set." My users will not interpret this to mean Duplicate Record. When I try this suggestion for the Exit button I do not get a descriptive message, I still just get ??????? and only know it is error 3022 if I click on Help.

When I try Lameid's suggestion for Select Case Error Handling and insert Case<3022> in Case<Error number indicating duplicates> I get a syntax error. I tried the suggestion for 'docmd.RunCommand acCmdSaveRecord ' and a blank message box pops up that I cannot escape.

When I try TheAceMan1's suggestion (my field is text not numeric):

Private Sub btnNext_Click()
   
   If Not IsDuplicate("BARCODE ORDERS", "EmployeeID", False) Then
      DoCmd.GoToRecord , , acNext
   End If
        
End Sub

I get a "Compile error: Sub or Function not defined" and it takes me back to the code with IsDuplicate highlighted.

So...I guess I am stuck. I am sure all these suggestions are very valid but I have not been able to implement. Thank you all very much for your time.

 

[lameid]

When I try Lameid's suggestion for Select Case Error Handling and insert Case<3022> in Case<Error number indicating duplicates> I get a syntax error. I tried the suggestion for 'docmd.RunCommand acCmdSaveRecord ' and a blank message box pops up that I cannot escape.

I've seen it so many times that I forget not everyone expects it... the triangle/carrot brackets are often used to mean replace this whole thing including the brackets.

or

Private Sub btnNext_Click()
On Error GoTo Err_btnNext_Click


    DoCmd.GoToRecord , , acNext

Exit_btnNext_Click:
    Exit Sub

Err_btnNext_Click:
    Select Case Err.number
    Case 3022
         MsgBox ("ID already ordered")
    Case Else
         MsgBox "Error " & Err.Number & ": " & Err.Description 
    End Select
    Resume Exit_btnNext_Click
    
End Sub

 

[smibarb]

Thanks lameid but even removing the <> did not seem to work, I got a compile error. However, good news! My colleague read TheAceMan1 post and was able to tweak it and make it work for our application.

Thanks again to everyone for suggestions and to TheAceMan1 for our solution!

Option Compare Database
Option Explicit


Private Sub btnNext_Click()
Dim msg As String

On Error GoTo Err_btnNext_Click

If Not IsDuplicate("BARCODE ORDERS", "Employee ID") Then
      DoCmd.GoToRecord , , acNext
   End If

Exit_btnNext_Click:
    Exit Sub

Err_btnNext_Click:
        msg = "Error " & Err.Number & ": " & Err.Description
        MsgBox msg
    
       Resume Exit_btnNext_Click
    
End Sub


Private Sub btnExit_Click()
Dim msg As String

On Error GoTo Err_btnExit_Click


      DoCmd.Quit

Exit_btnExit_Click:
    Exit Sub

Err_btnExit_Click:
    msg = "Error " & Err.Number & ": " & Err.Description
    MsgBox msg
    Resume Exit_btnExit_Click



    
End Sub

Public Function IsDuplicate(tblName As String, fldName As String) As Boolean
                           
   'fldType: use True for Numeric, False for Text
   Dim Criteria As String
   Dim msg As String, Style As Integer, Title As String, DL As String
   
   DL = vbNewLine
   Criteria = "[" & fldName & "] = '" & Me(fldName) & "'"
      
   If Not IsNull(DLookup("[" & fldName & "]", tblName, Criteria)) Then
      msg = "Duplicate '" & fldName & "' Found!" & DL
      msg = msg & "Click 'OK' to modify '" & fldName & "'." & DL
      msg = msg & "Click 'Cancel' to abort saving the record or exit."

      Style = vbInformation + vbOKCancel
      Title = "Duplicate Field Detected! . . ."
      
      If MsgBox(msg, Style, Title) = vbOK Then
         Me(fldName).SetFocus
      Else
         Me.Undo 'ESC the field
         Me.Undo 'ESC the record
      End If
      
      IsDuplicate = True
   End If
   
End Function