Check for Duplicate

[mike60smart]

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.

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]

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:

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:

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

 

[Gasman]

How on earth is it even going to set focus (even if one could), when you Exit Sub !

 

[mike60smart]

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]

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.

 

[mike60smart]

Indentation is only your friend if you use it correctly. It should not be random as it is in your 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?

 

[mike60smart]

Hi Pat

My apologies here again with the 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

 

[mike60smart]

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]

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.

 

[arnelgp]

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.