SubForm Sort Order

D

DEI

Hello. I am trying to get a subform to sort on (2) fields, by Date, and then
by Time. The form is linked to the master form by and ID#, etc. I cannot
get this to work. I have tried:

-Saving a filter with the underlying table.
-Changing the OrderBy property of the subform to Date,Time - this works and
sorts the data properly when I view the subform alone, but when the subform
appears on the Master form, it still sorts by order of records in the table.

Thanks in advance. This is very frustrating, especially given the otherwise
advanced elements of the database.

DEI
 
A

Allen Browne

Suggestions:

1. If you really have fields named Date and Time, rename them and change
every query, form, report, macro or code that refers to the old name. Date
and Time are reserved words (for the system date and time), so the reference
is ambiguous.

2. Create a query that sorts the records as you want. Then open the subform
in design view, and change its RecordSource property to this query.

3. A less desirable solution is to programmatically set the OrderBy and
OrderByOn properties of the subform in its Open event. Example:
Private Sub Form_Open(Cancel As Integer)
Me.OrderBy = "[MyDateField], [MyTimeField]"
Me.OrderByOn = True
End Sub

(It might also make your life easier to use one field to store the date and
time, rather than 2 fields.)
 
D

DEI

Thank you Allen, I had not thought of storing the date and time in the same
field. That is something to consider. I just used 'Date' and 'Time' to
illustrate my point; those are not the titles of the fields.

I thought there would be a better than creating a query, etc. That worked
fine, though. Thanks.

DEI

Allen Browne said:
Suggestions:

1. If you really have fields named Date and Time, rename them and change
every query, form, report, macro or code that refers to the old name. Date
and Time are reserved words (for the system date and time), so the reference
is ambiguous.

2. Create a query that sorts the records as you want. Then open the subform
in design view, and change its RecordSource property to this query.

3. A less desirable solution is to programmatically set the OrderBy and
OrderByOn properties of the subform in its Open event. Example:
Private Sub Form_Open(Cancel As Integer)
Me.OrderBy = "[MyDateField], [MyTimeField]"
Me.OrderByOn = True
End Sub

(It might also make your life easier to use one field to store the date and
time, rather than 2 fields.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

DEI said:
Hello. I am trying to get a subform to sort on (2) fields, by Date, and
then
by Time. The form is linked to the master form by and ID#, etc. I cannot
get this to work. I have tried:

-Saving a filter with the underlying table.
-Changing the OrderBy property of the subform to Date,Time - this works
and
sorts the data properly when I view the subform alone, but when the
subform
appears on the Master form, it still sorts by order of records in the
table.

Thanks in advance. This is very frustrating, especially given the
otherwise
advanced elements of the database.

DEI
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top