[AccessD] When to Requery?

 Arthur,
What I would do is, on the main form drop the ID into a variable, then do a
requery, then reposition on the ID again *.
If you don't do this, the requery will put you on the first record of the
main form whereas you probably want to go back to the existing record and
see the newly added record in the subform.
The subform itself will sort in the order depending on it recordsource.  If
this is linked to a table, then change it to a query and in the query, put
the sort order you require in there.

Max
* AirCode
dim lngID as long, strFind as string, rst as dao.recordsource
Set recordsource = me.recordsourceclone
lngId = me!ID ' change for name of field holding the unique ID of this main
record.
Strfind = "ID=" & lngID
Me.requery
Rst.findfirst strfind
If not rst.nomatch then
Me.bookmark = rst.bookmark
Endif
Set rst = nothing


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
Sent: Thursday, December 06, 2007 2:00 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] When to Requery?

I have a form with a subform (you've heard this one, I bet).

The subform is R/O and there's a button to add a new Item. This pops up a
dialog form that accepts the new data. The form has a button to Save, which
writes the record and then closes the form. I want to requery the subform to
reflect the newly added item at the top of the list.

The subform is sorted by TransactionID DESC, but my various attempts to
requery the subform suggest that I'm doing it in the wrong event. Here is
the scenario in more literal terms:

Main form: Riders_frm
Sub form: Transactions_fsub, containing a button that invokes "New
Transaction", which opens Transactions_New_frm.
User clicks Save on said dialog. Dialog closes, returning the user to the
Main form.

At this point, I wish the sub-form (Transactions_fsub) to refresh or requery
and display the newly-added record at the top of the grid, NOT at the bottom
(which it is currently doing). Yes, the user can re-sort the data but that's
not what I want. I want the Transactions to be sorted (either TransactonID
DESC or TransactionDate DESC -- both will work), but I can't seem to
automate it. Keystrokes are required and that's what I want to get around.

Any suggestions?

TIA,
Arthur
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com