in MS ACCESS main form, how can I make the sub-form shows the active record as first one by default ?

Ay I
Ay I used Ask the Experts™
on
I have an MS ACCESS file with a form and sub-form.
The main form shows the main RENTAL CONTRACT , then the sub-form shows the current active LEASE ;
lease has a duration of a year , then is flagged as DISABLED, and a new lease (ACTIVE ONE) is created as a new sub-record ;

The problem is when I navigate the CONTRACTS in main form ; i want to see the current active lease, but rather is shows the records in sub-form by order, so I see the disabled lease first.

How can I make the sub-form shows the active LEASE as first one by default ?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You could modify the subform to sort on the lease date.
Adjust its RecordSource to something like:

Select * From Leases Order By LeaseDate Desc

Open in new window

Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
Couple of options here, the simplest of which as gustav said is just changing the sort order so the active record is always on top.

If however you don't want that or your allowing the user to change the sort order, then you'll need some code to look through the forms recordset and find the active lease.

Let us know if you want to go that route or if the sort order change gustav suggested will work for you.

Jim.
Chief Technology Officer
Commented:
The attached Access file has a form with a datasheet subform in it that shows you how to display your record at any position in the subform visible stack (anywhere from 1st to last).  You don't have to mess with sorting or doing anything to your datasheet list.

See if it does what you want?
RepositionSubformDatasheetRecords.accdb
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

John TsioumprisSoftware & Systems Engineer

Commented:
Normally Disabled is a boolean field so you could just order by LEASE ASCENDING
SELECT * FROM SUBFORM_SOURCE ORDER BY LEASE

Open in new window

Author

Commented:
Mark , your reply seems what I am looking for, but I am checking the code to apply it to me case and see.

Author

Commented:
Mark ,
I tried the follow the solution and used same code in the MS ACCESS file you attached,
However this error came when reaching the line that refers to the Bookmark

Me.frm_subfrm_RENT.Form.Recrodset.Bookmark = rs.Bookmark

Run time : Application-defined or object-defined error
 
See image with the error message
ERROR___.JPG
John TsioumprisSoftware & Systems Engineer

Commented:
Typo
Me.frm_subfrm_RENT.Form.Recordset.Bookmark = rs.Bookmark

Open in new window

in order to to avoid these kind of typos
Form_frm_subfrm_RENT.Recordset.Bookmark = rs.Bookmark

Open in new window

As soon as you enter the first dot (.) you will see Intellisense kick in.

Author

Commented:
I fixed the typo , and it worked fine , Thank you John

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial