cursor won't do what I ask with SetFocus

[Guest]

I have a continuous form (with no subforms). If a user enters an invalid
length for the Account # in the [txtAcct#] field, I want a message box to pop
up and tell them what is wrong. I then want it to put the cursor in that
field so they have to fix it before going on.

My code pops up the message box when an invalid length in that field. But
once the user hits OKAY to the msgbox, they are placed in the next field
over. I don't want that. I want them to have to fix the length issue in the
field with the problem. I have tried SetFocus, but I must be doing something
wrong since it goes to the next field over in that record. Anyone see what
is wrong???

Private Sub txtAcct__AfterUpdate()
If Len(Me.[txtAcct#].Text) < 6 Or Len(Me.[txtAcct#].Text) > 7 Then
MsgBox "At least 6 and no more than 7 characters"
Me![txtAcct#].SetFocus
End If
End Sub

 

[OldPro]

I have a continuous form (with no subforms). If a user enters an invalid
length for the Account # in the [txtAcct#] field, I want a message box to pop
up and tell them what is wrong. I then want it to put the cursor in that
field so they have to fix it before going on.

My code pops up the message box when an invalid length in that field. But
once the user hits OKAY to the msgbox, they are placed in the next field
over. I don't want that. I want them to have to fix the length issue in the
field with the problem. I have tried SetFocus, but I must be doing something
wrong since it goes to the next field over in that record. Anyone see what
is wrong???

Private Sub txtAcct__AfterUpdate()
If Len(Me.[txtAcct#].Text) < 6 Or Len(Me.[txtAcct#].Text) > 7 Then
MsgBox "At least 6 and no more than 7 characters"
Me![txtAcct#].SetFocus
End If
End Sub

Use the OnExit event. If the data is invalid, set Cancel to true.

 

[Douglas J. Steele]

Try putting the check in the control's BeforeUpdate event, not the
AfterUpdate event.

Private Sub txtAcct__BeforeUpdate(Cancel As Integer)
If Len(Me.[txtAcct#]) < 6 Or Len(Me.[txtAcct#]) > 7 Then
MsgBox "At least 6 and no more than 7 characters"
Cancel = True
End If
End Sub

 

[Guest]

I agree with Doug about the use of BeforeUpdate instead of AfterUpdate.

Part of your problem is that Access does not play well with special
characters in the names of objects (in this case controls, but may also be in
your field names). You can tell this because in the name of the sub Access
has replace # with _ .
Try changing the name of your control to txtAccctNum, and avoid special
characters in field and control names.

Dale

 

[Guest]

Changing to a before update event works like a charm. I am trying to wrap my
head around this - but why does it work better in the before update event?
It's the momemt before Access actually updates the record, so we are catching
it "early" but even in the after update event, can't it evaluate that field
afte the update to determine if it meets the length requirement or not and
set focus back to the field? It's not like the set focus problem is an undo
of the update.

Try putting the check in the control's BeforeUpdate event, not the
AfterUpdate event.

Private Sub txtAcct__BeforeUpdate(Cancel As Integer)
If Len(Me.[txtAcct#]) < 6 Or Len(Me.[txtAcct#]) > 7 Then
MsgBox "At least 6 and no more than 7 characters"
Cancel = True
End If
End Sub

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a continuous form (with no subforms). If a user enters an invalid
length for the Account # in the [txtAcct#] field, I want a message box to
pop
up and tell them what is wrong. I then want it to put the cursor in that
field so they have to fix it before going on.

My code pops up the message box when an invalid length in that field. But
once the user hits OKAY to the msgbox, they are placed in the next field
over. I don't want that. I want them to have to fix the length issue in
the
field with the problem. I have tried SetFocus, but I must be doing
something
wrong since it goes to the next field over in that record. Anyone see
what
is wrong???

Private Sub txtAcct__AfterUpdate()
If Len(Me.[txtAcct#].Text) < 6 Or Len(Me.[txtAcct#].Text) > 7 Then
MsgBox "At least 6 and no more than 7 characters"
Me![txtAcct#].SetFocus
End If
End Sub

 

[Guest]

Thanks Dale. The before update event fixes it just like that! I just don't
get why my problem with Set Focus is solved by switching from the after
update to the before update event. It's not like I am asking it to undo
data, just move the cursor right? Any light you could shed would be greatly
appreciated to furthering my understanding of the things I do in Access.

I agree with Doug about the use of BeforeUpdate instead of AfterUpdate.

Part of your problem is that Access does not play well with special
characters in the names of objects (in this case controls, but may also be in
your field names). You can tell this because in the name of the sub Access
has replace # with _ .
Try changing the name of your control to txtAccctNum, and avoid special
characters in field and control names.

Dale
--
Email address is not valid.
Please reply to newsgroup only.

I have a continuous form (with no subforms). If a user enters an invalid
length for the Account # in the [txtAcct#] field, I want a message box to pop
up and tell them what is wrong. I then want it to put the cursor in that
field so they have to fix it before going on.

My code pops up the message box when an invalid length in that field. But
once the user hits OKAY to the msgbox, they are placed in the next field
over. I don't want that. I want them to have to fix the length issue in the
field with the problem. I have tried SetFocus, but I must be doing something
wrong since it goes to the next field over in that record. Anyone see what
is wrong???

Private Sub txtAcct__AfterUpdate()
If Len(Me.[txtAcct#].Text) < 6 Or Len(Me.[txtAcct#].Text) > 7 Then
MsgBox "At least 6 and no more than 7 characters"
Me![txtAcct#].SetFocus
End If
End Sub

 

[Rick Brandt]

Changing to a before update event works like a charm. I am trying to
wrap my head around this - but why does it work better in the before
update event? It's the momemt before Access actually updates the
record, so we are catching it "early" but even in the after update
event, can't it evaluate that field afte the update to determine if
it meets the length requirement or not and set focus back to the
field? It's not like the set focus problem is an undo of the update.

It's not before Access updates the record. It's before Access updates the
control. By cancelling that event (triggered by trying to leave the control)
the control never loses focus so there is no need to set the focus back.

The logic is that you *prevent* an invalid entry in BeforeUpdate rather than
complaining about a bad value *after* allowing it to be entered.

 

[OldPro]

The program runs its own setfocus after the afterupdate event,
canceling yours.