SubForm Sort Order

[Guest]

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

 

[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.)

 

[Guest]

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

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.

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