By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,269 Members | 1,506 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,269 IT Pros & Developers. It's quick & easy.

Subform Navigation?

P: n/a
I have a main form with a subform on it.
When I click a button on the main form, I perform some calculations on data
contained in the subform's table (all related records actually).
However, after the data is massaged, I refresh it, which takes me to the
first record on the subform. Instead, I need to return to the record I had
showing on the subform when I clicked the button on the main form.

Example:
Customer= Main form.
Orders = Subform.
If I change a tax percentage on the customer form, I recalculate all orders
for that customer, but end up back at the first record on the subform, not
the one that was showing when I updated.

I cannot requery, as that starts me at the beginning of the recordset as
well.
Is this a bookmark or recordset clone solution?

Thanks.

TA
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Before requerying the subform, save the value of its primary key field, so
you can find the record again later.

This example assumes a subform control named "Orders", with a numeric
primary key field named "SubID":

With Me.[Orders].Form
Dim rs As DAO.Recordset
Dim varID as Variant

If .Dirty Then 'Save any edits in progress.
.Dirty = False
End If

'Store the primary key value before requerying.
varID = ![SubID] 'Will be Null if at a new row.
.Requery

'Find the record again
If Not IsNull(varID) Then
Set rs = .RecordsetClone
.FindFirst "[SubID] = " & varID
If Not .NoMatch Then
.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End With

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Txsg8r" <ti*******@hotmail.com> wrote in message
news:sG*******************@twister.austin.rr.com.. .
I have a main form with a subform on it.
When I click a button on the main form, I perform some calculations on data contained in the subform's table (all related records actually).
However, after the data is massaged, I refresh it, which takes me to the
first record on the subform. Instead, I need to return to the record I had showing on the subform when I clicked the button on the main form.

Example:
Customer= Main form.
Orders = Subform.
If I change a tax percentage on the customer form, I recalculate all orders for that customer, but end up back at the first record on the subform, not
the one that was showing when I updated.

I cannot requery, as that starts me at the beginning of the recordset as
well.
Is this a bookmark or recordset clone solution?

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.