Need to stop form from going to first record

[Tofudisan]

I've searched the forum archives and haven't found the same scenario
I'm in. There's a few similar problems but the proposals in those
didn't work for me so I'm posting a new plea for help.

I have a small 3-user database. It's a Access 2000 front-end with a
SQL back-end. The application has a main form that handles 99.99% of
the user interaction (the rest is look-up list editing forms). There
are no sub-forms or tabs or anything else remotely complicated about
the form.

Anyway the problem I've run into is that whenever a record is updated
and then saved the form jumps to the first record in the database
instead of "staying put" on the record that was edited.

How do I get it to stop this behavior? I want the user to be able to
edit and update a record and stay on THAT record.

 

[Stuart McCall]

I've searched the forum archives and haven't found the same scenario
I'm in. There's a few similar problems but the proposals in those
didn't work for me so I'm posting a new plea for help.

I have a small 3-user database. It's a Access 2000 front-end with a
SQL back-end. The application has a main form that handles 99.99% of
the user interaction (the rest is look-up list editing forms). There
are no sub-forms or tabs or anything else remotely complicated about
the form.

Anyway the problem I've run into is that whenever a record is updated
and then saved the form jumps to the first record in the database
instead of "staying put" on the record that was edited.

How do I get it to stop this behavior? I want the user to be able to
edit and update a record and stay on THAT record.

Something is causing the form to be ReQuery'd. If the form has a module,
look for code like:

Me.ReQuery

Or a call to a macro that does a ReQuery.

Otherwise, it could be caused by the way you save the record. You'd need to
post that if you're not sure.

 

[Klatuu]

If your form is jumping back to the first record after an update, that means
the form is being requeried. What you need to do is before the query, save
the primary key of the current record. Then after the requery, use the saved
key to return to the record:
This example assumes the primary key is an autonumber(long data type)

Dim lngCurrKey As Long

lngCurrKey = Me.txtPrimeKey

Me.Requery

With Me.RecordsetClone
.FindFirst "[PrimeKeyField] = " & lngCurrKey
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

You will, of course, have to change the code to use your names and data types.

 

[Tofudisan]

Something is causing the form to be ReQuery'd. If the form has a module,
look for code like:

Me.ReQuery

Or a call to a macro that does a ReQuery.

Otherwise, it could be caused by the way you save the record. You'd need to
post that if you're not sure.

The code is an update query via DoCmd.RunSQL. I did have a Me.Requery
but I commented that out and I still got the behavior. Actually tried
that before my post. :-/

Just to be sure I've gone back and verified. Here's a summary of the
code:

Dim sSQL As String

sSQL = "UPDATE tblCampsiteReservations SET "

With Me
'.... Code to build the remainder of the update.  It's long
and cumbersome so I've abridged it.
'.... Suffice it to say that I know it works properly and it's
just a bunch of string concatenation with field values and nothing
else
'.... so it shouldn't be responsible for the problem I've got.

DoCmd.SetWarnings False
DoCmd.RunSQL (sSQL)
DoCmd.SetWarnings True

End With

 

[Tofudisan]

Thanks I'll try this technique out and see if it works for me.

If your form is jumping back to the first record after an update, that means
the form is being requeried. What you need to do is before the query, save
the primary key of the current record. Then after the requery, use the saved
key to return to the record:
This example assumes the primary key is an autonumber(long data type)

Dim lngCurrKey As Long

lngCurrKey = Me.txtPrimeKey

Me.Requery

With Me.RecordsetClone
.FindFirst "[PrimeKeyField] = " & lngCurrKey
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

You will, of course, have to change the code to use your names and data types.
--
Dave Hargis, Microsoft Access MVP

I've searched the forum archives and haven't found the same scenario
I'm in. There's a few similar problems but the proposals in those
didn't work for me so I'm posting a new plea for help.

I have a small 3-user database. It's a Access 2000 front-end with a
SQL back-end. The application has a main form that handles 99.99% of
the user interaction (the rest is look-up list editing forms). There
are no sub-forms or tabs or anything else remotely complicated about
the form.

Anyway the problem I've run into is that whenever a record is updated
and then saved the form jumps to the first record in the database
instead of "staying put" on the record that was edited.

How do I get it to stop this behavior? I want the user to be able to
edit and update a record and stay on THAT record.

 

[Tofudisan]

Using the RecordsetClone.FindFirst worked! There's still a jump in
the save operation but at least the user is back to that record.
Thanks Klatuu! :-D

If your form is jumping back to the first record after an update, that means
the form is being requeried. What you need to do is before the query, save
the primary key of the current record. Then after the requery, use the saved
key to return to the record:
This example assumes the primary key is an autonumber(long data type)

Dim lngCurrKey As Long

lngCurrKey = Me.txtPrimeKey

Me.Requery

With Me.RecordsetClone
.FindFirst "[PrimeKeyField] = " & lngCurrKey
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

You will, of course, have to change the code to use your names and data types.
--
Dave Hargis, Microsoft Access MVP

I've searched the forum archives and haven't found the same scenario
I'm in. There's a few similar problems but the proposals in those
didn't work for me so I'm posting a new plea for help.

I have a small 3-user database. It's a Access 2000 front-end with a
SQL back-end. The application has a main form that handles 99.99% of
the user interaction (the rest is look-up list editing forms). There
are no sub-forms or tabs or anything else remotely complicated about
the form.

Anyway the problem I've run into is that whenever a record is updated
and then saved the form jumps to the first record in the database
instead of "staying put" on the record that was edited.

How do I get it to stop this behavior? I want the user to be able to
edit and update a record and stay on THAT record.

 

[Stuart McCall]

Something is causing the form to be ReQuery'd. If the form has a module,
look for code like:

Me.ReQuery

Or a call to a macro that does a ReQuery.

Otherwise, it could be caused by the way you save the record. You'd need
to
post that if you're not sure.

The code is an update query via DoCmd.RunSQL. I did have a Me.Requery
but I commented that out and I still got the behavior. Actually tried
that before my post. :-/

Just to be sure I've gone back and verified. Here's a summary of the
code:

Dim sSQL As String

sSQL = "UPDATE tblCampsiteReservations SET "

With Me
'.... Code to build the remainder of the update.  It's long
and cumbersome so I've abridged it.
'.... Suffice it to say that I know it works properly and it's
just a bunch of string concatenation with field values and nothing
else
'.... so it shouldn't be responsible for the problem I've got.

DoCmd.SetWarnings False
DoCmd.RunSQL (sSQL)
DoCmd.SetWarnings True

End With

Is your form bound to tblCampsiteReservations? If so then you don't need to
run an update query. Let the form do the update. Any data manipulation that
you need to do before the record is saved can be done in the form's
BeforeUpdate event.

 

[Tofudisan]

The form is based off a query of the table. I've coded all the adds,
updates, and deletes to use either SQL or stored procedures so that I
can validate everything before I let it get into the back-end
database.

However I can quickly flip it over to being based off the table
directly if needed.

The code is an update query via DoCmd.RunSQL. I did have a Me.Requery
but I commented that out and I still got the behavior. Actually tried
that before my post. :-/

Just to be sure I've gone back and verified. Here's a summary of the
code:

[/QUOTE]

Dim sSQL As String[/QUOTE]
[QUOTE]
sSQL = "UPDATE tblCampsiteReservations SET "[/QUOTE]
[QUOTE]
With Me
'.... Code to build the remainder of the update.  It's long
and cumbersome so I've abridged it.
'.... Suffice it to say that I know it works properly and it's
just a bunch of string concatenation with field values and nothing
else
'.... so it shouldn't be responsible for the problem I've got.[/QUOTE]
[QUOTE]
DoCmd.SetWarnings False
DoCmd.RunSQL (sSQL)
DoCmd.SetWarnings True[/QUOTE]
[QUOTE]
End With[/QUOTE]
[QUOTE]

Is your form bound to tblCampsiteReservations? If so then you don't need to
run an update query. Let the form do the update. Any data manipulation that
you need to do before the record is saved can be done in the form's
BeforeUpdate event.

 

[Stuart McCall]

The form is based off a query of the table. I've coded all the adds,
updates, and deletes to use either SQL or stored procedures so that I
can validate everything before I let it get into the back-end
database.

However I can quickly flip it over to being based off the table
directly if needed.

If you're going to manage all updates yourself, you really ought to be using
an unbound form. The reason I say that is that a bound Access form does
automatic updates for you IF you let it. There are two events you should
look at: BeforeInsert and BeforeUpdate. The first one fires just before a
new record is automatically inserted into the dataset. The second fires just
before a record that has been edited is inserted. Both events allow you to
cancel the auto-update by setting a variable called Cancel to True. Access
passes this variable to your procedure in the parameter list. So something
like:

If <ValidationFailedSomehow> Then
MsgBox "Cannot update the database because ... blah blah"
Cancel = True
End If

When this code executes, the user will be unable to 'move away' from the
current record until the problem is fixed (or they undo their changes).

Using a form in that manner is natural for Access and doesn't require any
further SQL or stored procedures in order to update your data.

Of course if you are using stored procedures for performance or security
reasons, then you may need to stick with that. If you do, make your form
unbound (clear its RecordSource property). If you leave it bound, your
record is being saved twice - once by your code and once by the form.

 

[yator]

Klatuu,
Using the RecordsetClone.FindFirst method you described above, What
command would I use to return to the bookmark after my Requery?
thanks

If your form is jumping back to the first record after an update, that means
the form is being requeried. What you need to do is before the query, save
the primary key of the current record. Then after the requery, use the saved
key to return to the record:
This example assumes the primary key is an autonumber(long data type)

Dim lngCurrKey As Long

lngCurrKey = Me.txtPrimeKey

Me.Requery

With Me.RecordsetClone
.FindFirst "[PrimeKeyField] = " & lngCurrKey
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

You will, of course, have to change the code to use your names and data types.
--
Dave Hargis, Microsoft Access MVP

I've searched the forum archives and haven't found the same scenario
I'm in. There's a few similar problems but the proposals in those
didn't work for me so I'm posting a new plea for help.

I have a small 3-user database. It's a Access 2000 front-end with a
SQL back-end. The application has a main form that handles 99.99% of
the user interaction (the rest is look-up list editing forms). There
are no sub-forms or tabs or anything else remotely complicated about
the form.

Anyway the problem I've run into is that whenever a record is updated
and then saved the form jumps to the first record in the database
instead of "staying put" on the record that was edited.

How do I get it to stop this behavior? I want the user to be able to
edit and update a record and stay on THAT record.

 

[Linq Adams via AccessMonster.com]

That would be the code Dave posted above!

With Me.RecordsetClone
.FindFirst "[PrimeKeyField] = " & lngCurrKey
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com