Requery subform and save position in recordset

[Dave]

I have a main form with a subform that contains child records.

I can open a popup form and create a new child record on the fly. I then
close the popup form and requery the subform with:

Me!mySubForm.Form.Requery

to display the newly created record.

However, the requery action will reposition the subform recordset at the
first record. If I have 100 records in the subform, I must now scroll down
to find the record I was viewing when I added the new record

Is it possible to preserve my place in the recordset after executing a
requery? Is so, how do I do this?

 

[Marshall Barton]

I have a main form with a subform that contains child records.

I can open a popup form and create a new child record on the fly. I then
close the popup form and requery the subform with:

Me!mySubForm.Form.Requery

to display the newly created record.

However, the requery action will reposition the subform recordset at the
first record. If I have 100 records in the subform, I must now scroll down
to find the record I was viewing when I added the new record

Is it possible to preserve my place in the recordset after executing a
requery? Is so, how do I do this?

The popup form needs to park the primary key value of the
added record somewhere safe (a hidden text box on the
mainform or a global variable?). Or was the record you were
looking at already in the subfor? Either way, save the PK
value.

Once the mainform can get its hands on the desired record's
PK value, you can use a little code to find the record in
the subform:

With Me.subformcontrol.Form
If .DIrty Then .Dirty = False
With .RecordsetClone
.FindFIrst "pkfieldname=" & pkvalue
If Not .NoMatch Then
Me.subformcontrol.Form.Bookmark = .Bookmark
End If
End With
End With

 

[Dave]

Thank you Marshall. That works quite nicely.

One question: What exactly does the following line accomplish?

If .Dirty Then .Dirty = False

Does this simply commit any uncommitted changes on the subform?

Dave

---------------------------------

Below is a summary for anyone who may have the same issue:

A "view detail" button on subform launches a popup form that displays the
detail of the current record on the subform.

The "view detail" button also passes the PK of the current subform record to
an invisible textbox on the parent form as:

'On sub form-
Forms!F_NOTE_ReviewBySource!txtNoteID.Value = Me![NoteID]

stDocName = "F_NOTE" 'popup form

stLinkCriteria = "[NoteID]=" & Me![NoteID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

The record detail may then be modified in the popup form after which the
popup form is closed.

Back on the parent form, the "refresh" button (cmdRefresh) is clicked to
update the subform recordset and display any modifications made in the
popup form.

The subform recordset is refreshed and the pointer is set to the record that
was edited in the popup form by referencing the PK value stored in the
invisible textbox (txtNoteID) as:

'On parent form-
Me!F_NOTE_ReviewBySource_SUB.Form.Requery

With Me.F_NOTE_ReviewBySource_SUB.Form
If .Dirty Then .Dirty = False
With .RecordsetClone
.FindFirst "noteid=" & txtNoteID.Value
If Not .NoMatch Then
Me.F_NOTE_ReviewBySource_SUB.Form.Bookmark = .Bookmark
End If
End With
End With

 

[Van T. Dinh]

See comments in-line.

--
HTH
Van T. Dinh
MVP (Access)

Thank you Marshall. That works quite nicely.

One question: What exactly does the following line accomplish?

If .Dirty Then .Dirty = False

If the Record has been edited (dirty = True) then save the Record.

 

[Marshall Barton]

You got it!

You don't want to be triggering all kinds of events to save
the record while you're in the midst of doing all this other
stuff.

I'm pretty sure that you can remove it in this case, because
the Requery should take care of that issue. Replace the
Dirty check with the requery:

'On parent form-
With Me.F_NOTE_ReviewBySource_SUB.Form
.Requery
With .RecordsetClone
. . .

Thank you Marshall. That works quite nicely.

One question: What exactly does the following line accomplish?

If .Dirty Then .Dirty = False

Does this simply commit any uncommitted changes on the subform?
---------------------------------

Below is a summary for anyone who may have the same issue:

A "view detail" button on subform launches a popup form that displays the
detail of the current record on the subform.

The "view detail" button also passes the PK of the current subform record to
an invisible textbox on the parent form as:

'On sub form-
Forms!F_NOTE_ReviewBySource!txtNoteID.Value = Me![NoteID]

stDocName = "F_NOTE" 'popup form

stLinkCriteria = "[NoteID]=" & Me![NoteID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

The record detail may then be modified in the popup form after which the
popup form is closed.

Back on the parent form, the "refresh" button (cmdRefresh) is clicked to
update the subform recordset and display any modifications made in the
popup form.

The subform recordset is refreshed and the pointer is set to the record that
was edited in the popup form by referencing the PK value stored in the
invisible textbox (txtNoteID) as:

'On parent form-
Me!F_NOTE_ReviewBySource_SUB.Form.Requery

With Me.F_NOTE_ReviewBySource_SUB.Form
If .Dirty Then .Dirty = False
With .RecordsetClone
.FindFirst "noteid=" & txtNoteID.Value
If Not .NoMatch Then
Me.F_NOTE_ReviewBySource_SUB.Form.Bookmark = .Bookmark
End If
End With
End With

 

[Dave]

Thanks guys

You got it!

You don't want to be triggering all kinds of events to save
the record while you're in the midst of doing all this other
stuff.

I'm pretty sure that you can remove it in this case, because
the Requery should take care of that issue. Replace the
Dirty check with the requery:

'On parent form-
With Me.F_NOTE_ReviewBySource_SUB.Form
.Requery
With .RecordsetClone
. . .

Thank you Marshall. That works quite nicely.

One question: What exactly does the following line accomplish?

If .Dirty Then .Dirty = False

Does this simply commit any uncommitted changes on the subform?
---------------------------------

Below is a summary for anyone who may have the same issue:

A "view detail" button on subform launches a popup form that displays the
detail of the current record on the subform.

The "view detail" button also passes the PK of the current subform record
to
an invisible textbox on the parent form as:

'On sub form-
Forms!F_NOTE_ReviewBySource!txtNoteID.Value = Me![NoteID]

stDocName = "F_NOTE" 'popup form

stLinkCriteria = "[NoteID]=" & Me![NoteID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

The record detail may then be modified in the popup form after which the
popup form is closed.

Back on the parent form, the "refresh" button (cmdRefresh) is clicked to
update the subform recordset and display any modifications made in the
popup form.

The subform recordset is refreshed and the pointer is set to the record
that
was edited in the popup form by referencing the PK value stored in the
invisible textbox (txtNoteID) as:

'On parent form-
Me!F_NOTE_ReviewBySource_SUB.Form.Requery

With Me.F_NOTE_ReviewBySource_SUB.Form
If .Dirty Then .Dirty = False
With .RecordsetClone
.FindFirst "noteid=" & txtNoteID.Value
If Not .NoMatch Then
Me.F_NOTE_ReviewBySource_SUB.Form.Bookmark = .Bookmark
End If
End With
End With