onExit and Current conflict?

[dgunning]

When entering data into the location field of my table, I want the users to
be able to eliminate any leading zeros from the location number. I will fill
them in myself so that "587" will be changed to "00587". I do this by the
following:

Private Sub Location_Exit(Cancel As Integer)
Me.Location = Format(Me.Location, "00000")
End Sub

I also want focus to be transferred to the location field if the user clicks
on the new record button, so I have the following Current event.

Private Sub Form_Current()
Me.Location.SetFocus
End Sub

This combination gives me a big problem though. When I open the form and
try to navigate through the records (using the built-in next and previous
buttons at the bottom of the form), I cannot get off the first record. If I
comment out the current event handler, then I can navigate as expected.

Any ideas on how I can get this form to act the way I want? Thanks for any
help!

dg

 

[Marshall Barton]

When entering data into the location field of my table, I want the users to
be able to eliminate any leading zeros from the location number. I will fill
them in myself so that "587" will be changed to "00587". I do this by the
following:

Private Sub Location_Exit(Cancel As Integer)
Me.Location = Format(Me.Location, "00000")
End Sub

I also want focus to be transferred to the location field if the user clicks
on the new record button, so I have the following Current event.

Private Sub Form_Current()
Me.Location.SetFocus
End Sub

This combination gives me a big problem though. When I open the form and
try to navigate through the records (using the built-in next and previous
buttons at the bottom of the form), I cannot get off the first record. If I
comment out the current event handler, then I can navigate as expected.

Try using:

If Me.NewRecord Then
Me.Location.SetFocus
End If

Is your Location field is a number (Long?) field, then you
can just set the text box's Format property instead of using
code to add leading zeros that will not be displayed. If
the field is a Text field, your code is fine, but it should
be in the AfterUpdate event instead of the Exit event.

 

[Linq Adams via AccessMonster.com]

Had your line

Me.Location = Format(Me.Location, "00000")

been in the proper event, you wouldn't have had this problem:

Private Sub Location_AfterUpdate()
Me.Location = Format(Me.Location, "00000")
End Sub

Besides the problem you've noted, with the code in the OnExit event, Access
would waste time re-formatting "Location" every time you exited the field,
which, as you've found out, is each time you move to a different record.

 

[dgunning]

Thanks to both Marshall and Linq. Moving the code to the after update event
fixed the problem. Figuring out which event to use in a given situation is
something that (obviously) I have some trouble with. I guess that's
something that will come with more experience...