Check for Duplicate (1 Viewer)

mike60smart

Registered User.
Local time
Today, 10:55
Joined
Aug 6, 2017
Messages
1,899
Hi Everyone

Where am I going wrong with the following Code?

The correct Message pops up and says there is a Duplicate.

I click the OK button and it closes the Form vice setting Focus to the Control.

Any help appreciated.

Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)


10        On Error GoTo Form_BeforeUpdate_Error
20    If DCount("[MChip]", "tblDogs", "[MChip] = '" & Me.Mchip & "'") <> 0 Then
30      Me.Undo
40      MsgBox "This Microchip Nr already exists:-" & vbCrLf, vbOKOnly + vbExclamation
50    Exit Sub
60        End If
70      Me.CallName.SetFocus


          
80        On Error GoTo 0
90        Exit Sub


Form_BeforeUpdate_Error:


100       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_BeforeUpdate, line " & Erl & "."


End Sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:55
Joined
May 7, 2009
Messages
19,169
the BeforeUpdate is Validation event, so you Cant set the Focus to any control.
if you really want to Setfocus, use a timer in your code:

Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)


10        On Error GoTo Form_BeforeUpdate_Error
20    If DCount("[MChip]", "tblDogs", "[MChip] = '" & Me.Mchip & "'") <> 0 Then
30      Me.Undo
40      MsgBox "This Microchip Nr already exists:-" & vbCrLf, vbOKOnly + vbExclamation
50    Exit Sub
60        End If
70      'Me.CallName.SetFocus

'arnel
Me.TimerInterval = 50
          
80        On Error GoTo 0
90        Exit Sub


Form_BeforeUpdate_Error:


100       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_BeforeUpdate, line " & Erl & "."


End Sub

your timer:
Code:
Private Sub Form_Timer()
'kill it Immediately
Me.TimerInterval = 0
Me.CallName.Setfocus
End Sub
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:55
Joined
Sep 21, 2011
Messages
14,038
How on earth is it even going to set focus (even if one could), when you Exit Sub ! :(
 

mike60smart

Registered User.
Local time
Today, 10:55
Joined
Aug 6, 2017
Messages
1,899
It tells the user that its a duplicate and closes the Form.

Does not set Focus

I will leave as it is as it does what the user wants.

Thanks
 

bastanu

AWF VIP
Local time
Today, 03:55
Joined
Apr 13, 2010
Messages
1,401
When I want to set focus to another control I use the AfterUpdate event and clear the control (the "offending" value that doesn't pass validation); if that is the only value you want to validate do it in that control's BeforeUpdate where you can cancel it and the focus stays there.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:55
Joined
Feb 19, 2002
Messages
42,970
Indentation is only your friend if you use it correctly. It should not be random as it is in your code:
Code:
    On Error GoTo Form_BeforeUpdate_Error
    If DCount("[MChip]", "tblDogs", "[MChip] = '" & Me.Mchip & "'") <> 0 Then
        Me.Undo
        MsgBox "This Microchip Nr already exists:-" & vbCrLf, vbOKOnly + vbExclamation
        Cancel = True
        Me.CallName.SetFocus
        Exit Sub    
    End If
   
Exit Sub
   
Form_BeforeUpdate_Error
    Select Case Err.Number
        Case Else
            Msgbox Err.Number  & "--" & Err.Description
    End Select

Yes, you CAN set focus to a control in the FORM's BeforeUpdate event. In the CONTROL's BeforeUpdate event, you are already on the control you want to retain focus.

What you were missing was the "Cancel = True". You have to tell Access to cancel the event so the record save will NOT continue.
 

mike60smart

Registered User.
Local time
Today, 10:55
Joined
Aug 6, 2017
Messages
1,899
Indentation is only your friend if you use it correctly. It should not be random as it is in your code:
Code:
    On Error GoTo Form_BeforeUpdate_Error
    If DCount("[MChip]", "tblDogs", "[MChip] = '" & Me.Mchip & "'") <> 0 Then
        Me.Undo
        MsgBox "This Microchip Nr already exists:-" & vbCrLf, vbOKOnly + vbExclamation
        Cancel = True
        Me.CallName.SetFocus
        Exit Sub   
    End If
  
Exit Sub
  
Form_BeforeUpdate_Error
    Select Case Err.Number
        Case Else
            Msgbox Err.Number  & "--" & Err.Description
    End Select

Yes, you CAN set focus to a control in the FORM's BeforeUpdate event. In the CONTROL's BeforeUpdate event, you are already on the control you want to retain focus.

What you were missing was the "Cancel = True". You have to tell Access to cancel the event so the record save will NOT continue.
Hi Pat

Tried your Code and I now get the following error?
 

Attachments

  • error.JPG
    error.JPG
    26.5 KB · Views: 250

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:55
Joined
Feb 19, 2002
Messages
42,970
What event is the code in? you cut off the header and the error procedure so I assumed the code was in the FORM's BeforeUpdate event. How do I know you implemented it correctly?

Please start with posting the entire procedure.
 

mike60smart

Registered User.
Local time
Today, 10:55
Joined
Aug 6, 2017
Messages
1,899
Hi Pat

My apologies here again with the code:-

Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)


10        On Error GoTo Form_BeforeUpdate_Error
20    If DCount("[MChip]", "tblDogs", "[MChip] = '" & Me.Mchip & "'") <> "" Then
30      Me.Undo
40      MsgBox "This Microchip Nr already exists:-" & vbCrLf, vbOKOnly + vbExclamation
50    Cancel = True
60      Me.CallName.SetFocus
70          Exit Sub
80        End If




90      Me.TimerInterval = 50
100       On Error GoTo 0
110       Exit Sub


Form_BeforeUpdate_Error:


120       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_BeforeUpdate, line " & Erl & "."


End Sub
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:55
Joined
Feb 19, 2002
Messages
42,970
Have you compiled the code?
Have you compacted the database?
Are you positive your dCount() is working as you expect?
Have you put a stop on the code after the dCount() to follow the logic from there?

When your form stops due to an error, you may need to clear Access' "memory" in order to continue normally so if you get an unexpected error like this, do the cleanup steps first. Then we can move on to see what is wrong.

I guess you didn't hear me when I talked about aligning your code.
 

mike60smart

Registered User.
Local time
Today, 10:55
Joined
Aug 6, 2017
Messages
1,899
Hi Pat

Yes I have Compiled, Compacted and set a Break Point on the DCount.
The Dcount indicates that the Microchip is a Duplkicate.

The Code now runs through the check - indicates it is a Duplicate - Click OK and the Form Closes.

This will work for the user but I was trying to make the Form stay open and SetFocus to the Control "Callname"
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:55
Joined
Sep 21, 2011
Messages
14,038
Hi Pat

Yes I have Compiled, Compacted and set a Break Point on the DCount.
The Dcount indicates that the Microchip is a Duplkicate.

The Code now runs through the check - indicates it is a Duplicate - Click OK and the Form Closes.

This will work for the user but I was trying to make the Form stay open and SetFocus to the Control "Callname"
You will be familiar with this statement :)

Upload a copy of the DB that will show the problem.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:55
Joined
Feb 19, 2002
Messages
42,970
Your code is still a mystery. WHY is the code closing the form? I don't see that in what you posted.

If the BeforeUpdate event is running because the user chose to CLOSE the form or the database, you can stop the form from closing if there is a failure in the BeforeUpdate event but it takes a little more work. You need a form level variable. I call mine:
Code:
Dim SaveYesNoErr as Integer  '1 = yes, 2 = no, 3 = err'

Now you have code in several places to set it and check it. I used a "save" button to indicate that the record should be saved and you have to trap the cancel of the save so I do it this way. You might be able to use the Form Dirty to set the value to 2 but, test carefully because you probably have to trap the cancel in the Unload event if you don't use the Save Button.

In the current event of the form:
Code:
SaveYesNoErr = 2   '2= No save
In the Click event of the save button:
Code:
Private Sub CmdSave()

    On Error GoTo Err_Proc
    SaveYesNoErr = 1  '1 = Yes save
    DoCmd.RunCommand acCmdSaveRecord
    
Exit_Proc:
    Exit Sub
Err_Proc:
    Select Case Err.Number
        Case 2501
            'ignore error
            Resume Next
        Case Else
            Msgbox Err.Number & "--" & Err.Description
            Resume Exit_Proc
    End Select

In the Form's BeforeUpdate event:
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)

    SaveYesNoErr = 3 '' error.  This value will be changed to 2 if save succeeds.
''all your validation code

End Sub

In the Form's AfterUpdate event:
Code:
SaveYesNoErr = 2  'Save = No/Complete'

In the Form's Unload event:
Code:
Private Sub Form_Unload(Cancel As Integer)
    If SaveYesNoErr = 3 Then
        Cancel = True
    End If
End Sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:55
Joined
May 7, 2009
Messages
19,169
like i said you can't set Focus on another control when you are in Validation Event.
specially when you are Cancelling it. It needs to be on the Same control since
it did not passed the Validation. what you can do is put a timer. after the validation
exits the validation sub, the timer will fire, then will it focus on the control of
your choice.
 

Users who are viewing this thread

Top Bottom