VBA code command not setting focus on a specific field (1 Viewer)

[Alhakeem1977]

Hi All,
I've got a VBA code on a field called [ActualAC] it should trigger in a specific condition but the issue is, when it triggers it returns an error message and then the cursor should set focus on another field called [AccountNo], but it not setting the focus.

How can I overcome this issue? Please find below my code:

Private Sub ActualAC_BeforeUpdate(Cancel As Integer)
On Error Resume Next
Dim strLinkCriteria As String
    strLinkCriteria = "[CustTypeID] = " & Me!CustTypeID & " AND " & _
                      "[ActualAC] = '" & Me!ActualAC & "'"

    If DCount("*", "Customerstbl", strLinkCriteria) > 0 Then
Cancel = True
        Me.ActualAC.Undo
        MsgBox "This customer already exists!" & vbCrLf & _
        "Please type the A/C No. in the Customer's A/C field.", vbCritical, "Wrong Entry"
End If
    Me.AccountNo.SetFocus
End Sub

Thanks in advance!

 

[arnelgp]

if you want to move to another control, don't set Cancel to True.

 

[June7]

Why would you go to another control and not stay on the one that just triggered 'incorrect data' message so user can enter valid data?

Is ActualAC a required field?

 

[theDBguy]

Hi. Is it "always" not setting the focus or only when you get the message?

 

[Alhakeem1977]

if you want to move to another control, don't set Cancel to True.

How are you arnelgp? Actually I removed the cancel command but it's still the same.

 

[Alhakeem1977]

Why would you go to another control and not stay on the one that just triggered 'incorrect data' message so user can enter valid data?

Thanks for your reply, it will take another value later when the submitted the record.

Is ActualAC a required field?

No, it's not a required field.

 

[Alhakeem1977]

Hi. Is it "always" not setting the focus or only when you get the message?

Hello, how are you theDBguy?
Yes, it's always not setting the focus.

 

[arnelgp]

hmm, do it in AfterUpdate event

Private Sub ActualAC_AfterUpdate()
On Error Resume Next
Dim strLinkCriteria As String
    strLinkCriteria = "[CustTypeID] = " & Me!CustTypeID & " AND " & _
                      "[ActualAC] = '" & Me!ActualAC & "'"

If DCount("*", "Customerstbl", strLinkCriteria) > 0 Then
        MsgBox "This customer already exists!" & vbCrLf & _
        "Please type the A/C No. in the Customer's A/C field.", vbCritical, "Wrong Entry"
    Me. ActualAC = Me.ActualAC.OldValue
    Me.AccountNo.SetFocus
End If
End Sub

 

[theDBguy]

Hello, how are you theDBguy?
Yes, it's always not setting the focus.

Are you getting any error messages regarding the setting of the focus?

 

[Alhakeem1977]

Are you getting any error messages regarding the setting of the focus?

No, I am not getting any error.

 

[theDBguy]

No, I am not getting any error.

Did you try stepping through the code? Have you tried setting the focus somewhere else just to see if it will work with other controls?

Or, maybe Arnel is correct, you can't set the focus using the BeforeUpdate event.

 

[Alhakeem1977]

hmm, do it in AfterUpdate event

Private Sub ActualAC_AfterUpdate()
On Error Resume Next
Dim strLinkCriteria As String
    strLinkCriteria = "[CustTypeID] = " & Me!CustTypeID & " AND " & _
                      "[ActualAC] = '" & Me!ActualAC & "'"

If DCount("*", "Customerstbl", strLinkCriteria) > 0 Then
        MsgBox "This customer already exists!" & vbCrLf & _
        "Please type the A/C No. in the Customer's A/C field.", vbCritical, "Wrong Entry"
    Me. ActualAC = Me.ActualAC.OldValue
    Me.AccountNo.SetFocus
End If
End Sub

Thanks a lot, it's working

 

[arnelgp]

you're welcome!

 

[Alhakeem1977]

Did you try stepping through the code? Have you tried setting the focus somewhere else just to see if it will work with other controls?

Or, maybe Arnel is correct, you can't set the focus using the BeforeUpdate event.

Thanks, thaDBguy with the code provided by arnelgp.

Thanks a lot for your attention!

 

[Pat Hartman]

This is what the code should look like:

Private Sub ActualAC_BeforeUpdate(Cancel As Integer)
On Error Resume Next
Dim strLinkCriteria As String
    strLinkCriteria = "[CustTypeID] = " & Me!CustTypeID & " AND " & _
                      "[ActualAC] = '" & Me!ActualAC & "'"

    If DCount("*", "Customerstbl", strLinkCriteria) > 0 Then
        Cancel = True
        Me.ActualAC.Undo
        MsgBox "This customer already exists!" & vbCrLf & _
        "Please type the A/C No. in the Customer's A/C field.", vbCritical, "Wrong Entry"
        Exit Sub
End If
End Sub

The update event gets cancelled and focus stays in the field with the error. Personally, I almost NEVER use .Undo in this way. I think it is annoying to the user to have his entry deleted. It is better for him to see the entry that was bad so that he knows what is going on. The ONLY situation where I use .Undo is if the user is just not authorized to make the change so it doesn't matter what he entered. I'm not going to allow him to make any change.