Go to specific record on form open

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using VBA to navigate to a specific record on a form when that form
opens. I have attempted to attach the code to the "On Activate" event,
but after execution, the form goes to the first record, despite its
previous placement on a different record. I'm not able to attach the VBA
to the "On Current" event, because it doesn't work properly there.

The values are:
before On Activate
CurrentRecord = 1
after On Activate
CurrentRecord = 8 (from my code)
after form is loaded
CurrentRecord = 1 (apparently Access does this)

Is there any way to have the VBA code automatically run AFTER the form
is loaded and a record selected? I have a button on my form that's able
to do it, but I want to make it automatic.
 
nlee144(NoSpamTakeSquareRootOfNumber) said:
I am using VBA to navigate to a specific record on a form when that form
opens. I have attempted to attach the code to the "On Activate" event,
but after execution, the form goes to the first record, despite its
previous placement on a different record. I'm not able to attach the VBA
to the "On Current" event, because it doesn't work properly there.

The values are:
before On Activate
CurrentRecord = 1
after On Activate
CurrentRecord = 8 (from my code)
after form is loaded
CurrentRecord = 1 (apparently Access does this)

Is there any way to have the VBA code automatically run AFTER the form
is loaded and a record selected? I have a button on my form that's able
to do it, but I want to make it automatic.
Click to expand...


I've never had a use for the Activate event, so I am inaware
of its nuances. According to Help, Activate is supposed to
occur after Load, but it sounds like that's not what you're
seeing. I've not run into any problems using the Load event
for this kind of thing and suggest that you try that instead
of Activate.
 
Marshall said:
nlee144(NoSpamTakeSquareRootOfNumber) wrote:





I've never had a use for the Activate event, so I am inaware
of its nuances. According to Help, Activate is supposed to
occur after Load, but it sounds like that's not what you're
seeing. I've not run into any problems using the Load event
for this kind of thing and suggest that you try that instead
of Activate.
Click to expand...

It doesn't behave any differently using On Load. That's originally where
I had it, but I tried moving it as far back in the process as possible
to try to get my desired results. Do you know of any way to run code
after Current, or after the form is COMPLETELY done loading and looking
up a record?
 
nlee144(NoSpamTakeSquareRootOfNumber) said:
I am using VBA to navigate to a specific record on a form when that
form opens. I have attempted to attach the code to the "On Activate"
event, but after execution, the form goes to the first record, despite
its previous placement on a different record. I'm not able to attach
the VBA to the "On Current" event, because it doesn't work properly
there.

The values are:
before On Activate
CurrentRecord = 1
after On Activate
CurrentRecord = 8 (from my code)
after form is loaded
CurrentRecord = 1 (apparently Access does this)

Is there any way to have the VBA code automatically run AFTER the form
is loaded and a record selected? I have a button on my form that's
able to do it, but I want to make it automatic.
Click to expand...

If you know which record to go to before the form opens up, then use an
SQL statement to select the
record and change the RecordSource to what every you need.

Me.RecordSource="Select * from Table Where SomeField =" & SomeValue & ";"

Ron
--

Ronald W. Roberts
Roberts Communication
(e-mail address removed)
To reply remove "_at_robcom_dot_com"
 
nlee144(NoSpamTakeSquareRootOfNumber) said:
It doesn't behave any differently using On Load. That's originally where
I had it, but I tried moving it as far back in the process as possible
to try to get my desired results. Do you know of any way to run code
after Current, or after the form is COMPLETELY done loading and looking
up a record?
Click to expand...


You probably have something in your code that repositions
the foem back to the first record, a Requery would do it.

Here's some code I used to do what you want:

Private Sub Form_Load()
With Me.RecordsetClone
.FindFirst "[Reason] Like ""*Sick*"""
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With
End Sub
 
Ronald said:
If you know which record to go to before the form opens up, then use an
SQL statement to select the
record and change the RecordSource to what every you need.

Me.RecordSource="Select * from Table Where SomeField =" & SomeValue & ";"

Ron
Click to expand...

I'm not having trouble getting to the proper record. I'm having trouble
STAYING there. I have a button that is actually set to run the On Load
event, which goes to the proper record. For some reason, after my On
Load event, there is something else happening where the current record
changes. I need to find a way to make some code execute AFTER everything
else has happened. This is what the button does, but I need it to be
automatic.

-Nate
 
nlee144(NoSpamTakeSquareRootOfNumber) said:
I am using VBA to navigate to a specific record on a form when that form
opens. I have attempted to attach the code to the "On Activate" event,
but after execution, the form goes to the first record, despite its
previous placement on a different record. I'm not able to attach the VBA
to the "On Current" event, because it doesn't work properly there.

The values are:
before On Activate
CurrentRecord = 1
after On Activate
CurrentRecord = 8 (from my code)
after form is loaded
CurrentRecord = 1 (apparently Access does this)

Is there any way to have the VBA code automatically run AFTER the form
is loaded and a record selected? I have a button on my form that's able
to do it, but I want to make it automatic.
Click to expand...

I've solved it. The procedure that called the form to open changed some
properties of the form. Apparently, when a calling procedure executes
code on a form after opening it, the form does not execute its On Load
event.

....I'm sure it's in the documentation somewhere.

Nathan
 
nlee144(NoSpamTakeSquareRootOfNumber) said:
I've solved it. The procedure that called the form to open changed some
properties of the form. Apparently, when a calling procedure executes
code on a form after opening it, the form does not execute its On Load
event.
Click to expand...


The Load event has to execute before there is any data for
the form to operate on.

More likely, you've run into a race condition where the code
in the calling form is executing asynchronously from the
called form's load event. The called form's Open event has
completed, but you don't know where it is in processing its
Load event. My bottom line rule is to avoid trying to
"push" stuff into the called form.

In general, to be sure of everything being in sync, the
called form's code should "pull" its data from the calling
form or, if at all possible, from its OpenArgs argument.
The OpenArgs feature is designed specifically for this
purpose.
 
Back
Top