position cursor back to the original error field for correction

[Jimcb8]

I created VB event on the ClientID field when its focus is lost, to check for a possible duplicate id being entered.
When I tab from the ClientID to the next field (lastName) the msgbox appears to warn the user that they just entered a duplicate ClientID. I want to cursor to go back to the ClientID field from the lastname field, so that the user can correct the error. If I don't, the user can continue to enter data without correcting the duplicate ClientID.
I tried SETFocus, Repaint, and a few others, with no success, the cursor still remains in the LastName field after the dup message appears.


Your thoughts please.

 

[bob fitz]

Use the Client ID's Before Update event for your code instead of the Lost Focus event and use Cancel = True to prevent the control from updating.

 

[Jimcb8]

Bob,
I tried your code change and it solved most of my problem. When I enter the ClientID (which is a duplicate) and tab to the LastName field:
I correctly get my error message AND the cursor now stays in the ClientID field correctly,
BUT I get this message on the screen after I click ok on my error message screen.
"The value violates the validation rule for the field or record", if I click ok I can go on with my data entry.
I know that this message will confuse my users. It seems to happen only when I enter a duplicate the first time????
MY code as it stands now is:

Private Sub ClientID_BeforeUpdate(Cancel As Integer)
If DCount("ClientID", "Household Information", "ClientID=Forms!HomePage!NavigationSubform.Form!ClientID") > 0 Then
'already exists
MsgBox "ERROR!! This Client ID already exists in the database for another client...Duplicate ClientID's are not permitted Please change it. See documentation for help in creating a ClientID"
Cancel = True
End If
End Sub



Thanks so much for your help

Jim

 

[bob fitz]

Can you step through the code and tell us which line causes the error.

 

[Jimcb8]

Bob,
I moved the code to the "on exit" event and it works great now.
I believe that having the code at "before update" event, Access was attempting to update a record whose ClientID violated the unique requirement, hence the error message from Access letting me know that the key is a duplicate.
The cancel=True did the trick.

Thank you so much for your help. I'm new at this and don't know a lot of the power of VB and Access.

Thanks again.

Jim