Requery to same record?

[Shael]

I have a form with a drop down list with values for Salesman. Whenever I
select a different Salesman from the drop down list, I retrieve the remaining
fields from the corresponding record in a table for that particular salesman
and populate the remainder of the form.

A few of the form fields are editable. I have a command button beside an
editable field. Behind the On Click event of the command button I have coded
an SQL Update statement to update the record.

The problem is that when I click the command button, the table update
occurs, but the form redisplays the fields from the first record in the
table. I would like the form to update the table based on the contents of the
editable field and display the same (updated) record in the form.

On the On Click event for the command button I have included a Me.Requery
command.

How can I ensure that once this button is pressed, the form will not revert
to the first record in the table, but stay on the current, updated record?

Thanks.

 

[Ken Snell \(MVP\)]

You need to save the primary key field's value before you do the requery,
then find that value in the requeried data and move the form to that record:

Dim varPrimaryKey As Variant
varPrimaryKey = Me.PrimaryKeyField.Value
Me.Requery
With Me.RecordsetClone
.FindFirst "PrimaryKeyField=" & varPrimaryKey
If .NoMatch = False Then Me.Bookmark = .Bookmark
End With

 

[George Nicholson]

No way to keep the form on the record afaik, but you can move back to it.

Me.requery
me.bookmark = me.recordset.lastmodified

Could also use Find on a RecordsetClone and then sync the bookmarks.

HTH,

 

[Shael]

Thanks for this. It works. But for a half-second, the form displays the first
record before finding and displaying the updated record. It's definitely
visible to the user. Anyway of hiding this processing to the end user?

 

[Ken Snell \(MVP\)]

Sure - Application.Echo turns off and on the screen updating:

Dim varPrimaryKey As Variant
varPrimaryKey = Me.PrimaryKeyField.Value
Application.Echo False
Me.Requery
With Me.RecordsetClone
.FindFirst "PrimaryKeyField=" & varPrimaryKey
If .NoMatch = False Then Me.Bookmark = .Bookmark
End With
Application.Echo True

--

Ken Snell
<MS ACCESS MVP>

 

[Shael]

Great! Thanks.

Sure - Application.Echo turns off and on the screen updating:

Dim varPrimaryKey As Variant
varPrimaryKey = Me.PrimaryKeyField.Value
Application.Echo False
Me.Requery
With Me.RecordsetClone
.FindFirst "PrimaryKeyField=" & varPrimaryKey
If .NoMatch = False Then Me.Bookmark = .Bookmark
End With
Application.Echo True