How to move to chosen record?

[David Rose]

I have a form that displays all records with a certain clientID. Each
client has many jobs, each with a jobID. Initially the form appears with
the first job selected. There is a navigation bar at the bottom of the form
and I can move through the jobs using the navigation bar.

How do I programmatically move to a certain job, say with a jobID = 100?

I have tried cloning the recordset and moving to the correct record, but
predictably, it had no effect on the user interface (since the recordset was
a duplicate).

I tried DoCmd.FindRecord(), but I may have gotten some parameters wrong. I
used:
DoCmd.FindRecord initialJobNumber, acEntire, , acSearchAll, , acCurrent
and got a message that a parameter was incorrect.

Any suggestions?

Thanks.

David

 

[Marshall Barton]

I have a form that displays all records with a certain clientID. Each
client has many jobs, each with a jobID. Initially the form appears with
the first job selected. There is a navigation bar at the bottom of the form
and I can move through the jobs using the navigation bar.

How do I programmatically move to a certain job, say with a jobID = 100?

Here's a standard way using an unbound text box named
txtSearchID for the user entered ID.

With Me.RecordsetClone
If .RecordCount > 0 Then
.FindFirst "JobID = " & txtSearchID
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End If
End With

 

[David Rose]

Marsh,

Here is the code I am using:
OpenArgs contain the job number.
On the line Me.Bookmark = ds.Bookmark execution jumps directly to the
Form_Load() event handler. The code initialJobNumber = "" is not executed
and no exception is thrown.

This is very strange. Any idea what could be happening?

On Error GoTo err_Handler
initialJobNumber = Forms!frmJobTracking.OpenArgs
Dim ds As DAO.Recordset
Set ds = Me.RecordsetClone
If ds.RecordCount > 0 Then
ds.FindFirst "[JobNo] = " & initialJobNumber
If Not ds.NoMatch Then
Me.Bookmark = ds.Bookmark
End If
End If
initialJobNumber = ""

Exit Sub

err_Handler:
MsgBox (Err.Description)

 

[Marshall Barton]

Here is the code I am using:
OpenArgs contain the job number.
On the line Me.Bookmark = ds.Bookmark execution jumps directly to the
Form_Load() event handler. The code initialJobNumber = "" is not executed
and no exception is thrown.

This is very strange. Any idea what could be happening?

On Error GoTo err_Handler
initialJobNumber = Forms!frmJobTracking.OpenArgs
Dim ds As DAO.Recordset
Set ds = Me.RecordsetClone
If ds.RecordCount > 0 Then
ds.FindFirst "[JobNo] = " & initialJobNumber
If Not ds.NoMatch Then
Me.Bookmark = ds.Bookmark
End If
End If
initialJobNumber = ""

Exit Sub

It sounds like you have this code in the form's Open event.
The form's data (and RecordsetClone) is not available until
the load event.

 

[David Rose]

Marsh,

I did have it in the Open event handler, but I have moved it to the Load
event handler and it still exibits the same behavior. If I look at the Me
and ds variables before the bookmark assignment, everything looks ok.
Me.Bookmark(0) = 0 and ds.Bookmark(0) = 4 (which is the record I selected).

It just jumps to the wrong location in the code after the assignment.

David

Here is the code I am using:
OpenArgs contain the job number.
On the line Me.Bookmark = ds.Bookmark execution jumps directly to the
Form_Load() event handler. The code initialJobNumber = "" is not executed
and no exception is thrown.

This is very strange. Any idea what could be happening?

On Error GoTo err_Handler
initialJobNumber = Forms!frmJobTracking.OpenArgs
Dim ds As DAO.Recordset
Set ds = Me.RecordsetClone
If ds.RecordCount > 0 Then
ds.FindFirst "[JobNo] = " & initialJobNumber
If Not ds.NoMatch Then
Me.Bookmark = ds.Bookmark
End If
End If
initialJobNumber = ""

Exit Sub

It sounds like you have this code in the form's Open event.
The form's data (and RecordsetClone) is not available until
the load event.
--
Marsh
MVP [MS Access]

"Marshall Barton" wrote

 

[Marshall Barton]

I did have it in the Open event handler, but I have moved it to the Load
event handler and it still exibits the same behavior. If I look at the Me
and ds variables before the bookmark assignment, everything looks ok.
Me.Bookmark(0) = 0 and ds.Bookmark(0) = 4 (which is the record I selected).

It just jumps to the wrong location in the code after the assignment.

Assuming the JobNo field is a numeric type, I don't see
anything wrong. I guess I need more details.

Where/How is InitialJobNumber declared?

Where does it jump to?

Have you placed a breakpoint in the code and single stepped
through it to see what it does after the bookmark is set?

How about something in the Current event doing something
that causes trouble?
--
Marsh
MVP [MS Access]

Here is the code I am using:
OpenArgs contain the job number.
On the line Me.Bookmark = ds.Bookmark execution jumps directly to the
Form_Load() event handler. The code initialJobNumber = "" is not executed
and no exception is thrown.

This is very strange. Any idea what could be happening?

On Error GoTo err_Handler
initialJobNumber = Forms!frmJobTracking.OpenArgs
Dim ds As DAO.Recordset
Set ds = Me.RecordsetClone
If ds.RecordCount > 0 Then
ds.FindFirst "[JobNo] = " & initialJobNumber
If Not ds.NoMatch Then
Me.Bookmark = ds.Bookmark
End If
End If
initialJobNumber = ""

Exit Sub

"Marshall Barton" wrote
It sounds like you have this code in the form's Open event.
The form's data (and RecordsetClone) is not available until
the load event.

David Rose wrote:

I have a form that displays all records with a certain clientID. Each
client has many jobs, each with a jobID. Initially the form appears with
the first job selected. There is a navigation bar at the bottom of the
form and I can move through the jobs using the navigation bar.

How do I programmatically move to a certain job, say with a jobID = 100?

"Marshall Barton" wrote
Here's a standard way using an unbound text box named
txtSearchID for the user entered ID.

With Me.RecordsetClone
If .RecordCount > 0 Then
.FindFirst "JobID = " & txtSearchID
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End If
End With

 

[David Rose]

Hi Marsh,

I have to say that this is driving me nuts. My environment was not too
stable. It kept crashing, sometimes just typing into the editor. Even the
help was locking up and could not be shut down unless everything else is
closed. Now it seems better - I have been closing all the windows that I am
not working on and have not been crashing. I'm running Windows 2003
Server - if you know of any issues relating to the os, please let me know.
I think that I have the latest service packs for Office. I am using Access
2003.

Now things are not jumping to the wrong code. By the way, it was jumping
correctly to the next event (which is not too surprising); it just was not
finishing the code in the function.

So, the bookmark is being set. I can watch it in the debugger and see that
Me.Bookmark(0) goes to Me.Bookmark(4), which is correct. This change is not
reflected in the UI. The first record still displays and I have to use the
arrows on the navigation bar to get to the correct record.

Is there a GoToBookmark() function that I have to call?
Could something be locking the Recordset so that only the navigation bar
works?

Thanks for your help.

David

I did have it in the Open event handler, but I have moved it to the Load
event handler and it still exibits the same behavior. If I look at the Me
and ds variables before the bookmark assignment, everything looks ok.
Me.Bookmark(0) = 0 and ds.Bookmark(0) = 4 (which is the record I selected).

It just jumps to the wrong location in the code after the assignment.

Assuming the JobNo field is a numeric type, I don't see
anything wrong. I guess I need more details.

Where/How is InitialJobNumber declared?

Where does it jump to?

Have you placed a breakpoint in the code and single stepped
through it to see what it does after the bookmark is set?

How about something in the Current event doing something
that causes trouble?
--
Marsh
MVP [MS Access]

David Rose wrote:
Here is the code I am using:
OpenArgs contain the job number.
On the line Me.Bookmark = ds.Bookmark execution jumps directly to the
Form_Load() event handler. The code initialJobNumber = "" is not executed
and no exception is thrown.

This is very strange. Any idea what could be happening?

On Error GoTo err_Handler
initialJobNumber = Forms!frmJobTracking.OpenArgs
Dim ds As DAO.Recordset
Set ds = Me.RecordsetClone
If ds.RecordCount > 0 Then
ds.FindFirst "[JobNo] = " & initialJobNumber
If Not ds.NoMatch Then
Me.Bookmark = ds.Bookmark
End If
End If
initialJobNumber = ""

Exit Sub
"Marshall Barton" wrote
It sounds like you have this code in the form's Open event.
The form's data (and RecordsetClone) is not available until
the load event.



David Rose wrote:

I have a form that displays all records with a certain clientID. Each
client has many jobs, each with a jobID. Initially the form

appears
with

the first job selected. There is a navigation bar at the bottom of the
form and I can move through the jobs using the navigation bar.

How do I programmatically move to a certain job, say with a jobID = 100?

"Marshall Barton" wrote
Here's a standard way using an unbound text box named
txtSearchID for the user entered ID.

With Me.RecordsetClone
If .RecordCount > 0 Then
.FindFirst "JobID = " & txtSearchID
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End If
End With

 

[Marshall Barton]

I have to say that this is driving me nuts. My environment was not too
stable. It kept crashing, sometimes just typing into the editor. Even the
help was locking up and could not be shut down unless everything else is
closed. Now it seems better - I have been closing all the windows that I am
not working on and have not been crashing. I'm running Windows 2003
Server - if you know of any issues relating to the os, please let me know.
I think that I have the latest service packs for Office. I am using Access
2003.

Now things are not jumping to the wrong code. By the way, it was jumping
correctly to the next event (which is not too surprising); it just was not
finishing the code in the function.

So, the bookmark is being set. I can watch it in the debugger and see that
Me.Bookmark(0) goes to Me.Bookmark(4), which is correct. This change is not
reflected in the UI. The first record still displays and I have to use the
arrows on the navigation bar to get to the correct record.

Is there a GoToBookmark() function that I have to call?
Could something be locking the Recordset so that only the navigation bar
works?

This is defintely getting weird. And No, AFAIK, there is no
other action required. I don't use a Server system so I'm
not familiar with any issues. All I can suggest at this
point is closing Access, Rebooting the system and making a
backup of the mdb file, before trying snything else.

If any weirdness remains, then I would start worrying about
some kind of corruption.
--
Marsh
MVP [MS Access]

I did have it in the Open event handler, but I have moved it to the Load
event handler and it still exibits the same behavior. If I look at the Me
and ds variables before the bookmark assignment, everything looks ok.
Me.Bookmark(0) = 0 and ds.Bookmark(0) = 4 (which is the record I selected).

It just jumps to the wrong location in the code after the assignment.

"Marshall Barton" wrote
Assuming the JobNo field is a numeric type, I don't see
anything wrong. I guess I need more details.

Where/How is InitialJobNumber declared?

Where does it jump to?

Have you placed a breakpoint in the code and single stepped
through it to see what it does after the bookmark is set?

How about something in the Current event doing something
that causes trouble?

David Rose wrote:
Here is the code I am using:
OpenArgs contain the job number.
On the line Me.Bookmark = ds.Bookmark execution jumps directly to the
Form_Load() event handler. The code initialJobNumber = "" is not
executed
and no exception is thrown.

This is very strange. Any idea what could be happening?

On Error GoTo err_Handler
initialJobNumber = Forms!frmJobTracking.OpenArgs
Dim ds As DAO.Recordset
Set ds = Me.RecordsetClone
If ds.RecordCount > 0 Then
ds.FindFirst "[JobNo] = " & initialJobNumber
If Not ds.NoMatch Then
Me.Bookmark = ds.Bookmark
End If
End If
initialJobNumber = ""

Exit Sub

It sounds like you have this code in the form's Open event.
The form's data (and RecordsetClone) is not available until
the load event.



David Rose wrote:

I have a form that displays all records with a certain clientID. Each
client has many jobs, each with a jobID. Initially the form appears
with
the first job selected. There is a navigation bar at the bottom of
the
form and I can move through the jobs using the navigation bar.

How do I programmatically move to a certain job, say with a jobID =
100?

"Marshall Barton" wrote
Here's a standard way using an unbound text box named
txtSearchID for the user entered ID.

With Me.RecordsetClone
If .RecordCount > 0 Then
.FindFirst "JobID = " & txtSearchID
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End If
End With

 

[David Rose]

Marsh,

I rebuilt Access and that seemed to help. Anyway, it crashes a lot less. I
probably should do a full uninstall/install.

This application that I am working on is very poorly written and the focus
is jumping all over the place, but the setting of the bookmark seems to be
working properly. Probably because of the focus jumping from the subform to
the main form and back again, the only way I could get the correct record to
show is to set a filter (so only one job for a client is visible). It would
have been nice to be able to use the navigation bar to move through the
jobs...

Thanks for your help.

David

I have to say that this is driving me nuts. My environment was not too
stable. It kept crashing, sometimes just typing into the editor. Even the
help was locking up and could not be shut down unless everything else is
closed. Now it seems better - I have been closing all the windows that I am
not working on and have not been crashing. I'm running Windows 2003
Server - if you know of any issues relating to the os, please let me know.
I think that I have the latest service packs for Office. I am using Access
2003.

Now things are not jumping to the wrong code. By the way, it was jumping
correctly to the next event (which is not too surprising); it just was not
finishing the code in the function.

So, the bookmark is being set. I can watch it in the debugger and see that
Me.Bookmark(0) goes to Me.Bookmark(4), which is correct. This change is not
reflected in the UI. The first record still displays and I have to use the
arrows on the navigation bar to get to the correct record.

Is there a GoToBookmark() function that I have to call?
Could something be locking the Recordset so that only the navigation bar
works?

This is defintely getting weird. And No, AFAIK, there is no
other action required. I don't use a Server system so I'm
not familiar with any issues. All I can suggest at this
point is closing Access, Rebooting the system and making a
backup of the mdb file, before trying snything else.

If any weirdness remains, then I would start worrying about
some kind of corruption.
--
Marsh
MVP [MS Access]

David Rose wrote:
I did have it in the Open event handler, but I have moved it to the Load
event handler and it still exibits the same behavior. If I look at

the
Me

and ds variables before the bookmark assignment, everything looks ok.
Me.Bookmark(0) = 0 and ds.Bookmark(0) = 4 (which is the record I selected).

It just jumps to the wrong location in the code after the assignment.
"Marshall Barton" wrote
Assuming the JobNo field is a numeric type, I don't see
anything wrong. I guess I need more details.

Where/How is InitialJobNumber declared?

Where does it jump to?

Have you placed a breakpoint in the code and single stepped
through it to see what it does after the bookmark is set?

How about something in the Current event doing something
that causes trouble?


David Rose wrote:
Here is the code I am using:
OpenArgs contain the job number.
On the line Me.Bookmark = ds.Bookmark execution jumps directly to the
Form_Load() event handler. The code initialJobNumber = "" is not
executed
and no exception is thrown.

This is very strange. Any idea what could be happening?

On Error GoTo err_Handler
initialJobNumber = Forms!frmJobTracking.OpenArgs
Dim ds As DAO.Recordset
Set ds = Me.RecordsetClone
If ds.RecordCount > 0 Then
ds.FindFirst "[JobNo] = " & initialJobNumber
If Not ds.NoMatch Then
Me.Bookmark = ds.Bookmark
End If
End If
initialJobNumber = ""

Exit Sub

It sounds like you have this code in the form's Open event.
The form's data (and RecordsetClone) is not available until
the load event.



David Rose wrote:

I have a form that displays all records with a certain clientID. Each
client has many jobs, each with a jobID. Initially the form appears
with
the first job selected. There is a navigation bar at the bottom of
the
form and I can move through the jobs using the navigation bar.

How do I programmatically move to a certain job, say with a jobID =
100?

"Marshall Barton" wrote
Here's a standard way using an unbound text box named
txtSearchID for the user entered ID.

With Me.RecordsetClone
If .RecordCount > 0 Then
.FindFirst "JobID = " & txtSearchID
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End If
End With