Requery subform and save position in recordset

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dave - 29 Jan 2005 19:33 GMT

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?

Reply to this Message

Marshall Barton - 29 Jan 2005 19:58 GMT

>I have a main form with a subform that contains child records.
>
[quoted text clipped - 11 lines]
>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

Signature

Marsh
MVP [MS Access]

Reply to this Message

Dave - 30 Jan 2005 02:03 GMT

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

Reply to this Message

Van T. Dinh - 30 Jan 2005 03:21 GMT

See comments in-line.

Signature

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.

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

Yes

> Dave

Reply to this Message

Marshall Barton - 30 Jan 2005 04:02 GMT

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.
>
[quoted text clipped - 44 lines]
> End With
> End With

Signature

Marsh
MVP [MS Access]

Reply to this Message

Dave - 30 Jan 2005 05:03 GMT

Thanks guys

> You got it!
>
[quoted text clipped - 62 lines]
>> End With
>> End With

Reply to this Message