subform on another form won't refresh date order

[mackyrm]

I have a form (A), with a subform (A1). A1 is based on a table and is ordered by date.

I have a button that takes me to a new form (B). I can add a new record to B and close this form. Form B is also based on the same table as A1 and I want form A1 to be refreshed to incorporate the addition when form B is closed.

When I close form B, subform 1A is no longer ordered by date (but it has the new record added). I have tried code such as

forms!main!subform.refresh
docmd.close

...on form B's close button, but without success. How can I order the subform A1 by date, when returning to this form, as it does when the form is loaded for the first time?

Any thoughts appreciated!

Macky

 

[wiklendt]

on the command button you use to open the form, use this code instead:

    DoCmd.OpenForm "FormB", , , , , acDialog
    Me.ChildForm.Form.Requery

and yes, as pat said, your forms should be sourced from queries/SQL and the query should have an ORDER BY clause.

 

[mackyrm]

I simply changed the subform source to query and used the 'order by', which immediately rectified this issue, as you both suggested. I'm not sure why I chose to link to table in this instance, probably convenience at the time - costing more time!

Thanks for your help with this.

Macky