OrderBy property in VBA

[Tezza]

I want to set a button on a form that will filter and sort the records. The
filter works ok, but I can't get the sort to work. Forgive me, i'm
self-taught.

DoCmd.ApplyFilter , "isnull(fldDateOff) = true"
Forms("frmProject").OrderBy "fldDOB DESC"

I can't find an OrderBy example anywhere.

Any help gratefully received.

tia
tezza

 

[Ken Snell \(MVP\)]

You must also set the form's OrderByOn property to True after you set the
OrderBy property.

 

[Marshall Barton]

I want to set a button on a form that will filter and sort the records. The
filter works ok, but I can't get the sort to work. Forgive me, i'm
self-taught.

DoCmd.ApplyFilter , "isnull(fldDateOff) = true"
Forms("frmProject").OrderBy "fldDOB DESC"

I can't find an OrderBy example anywhere.

You also need to set the form's OrderByOn property to True
before it will use the OrderBy setting.

Specific help for these topics is available in VBA Help.

Is there a reason why you are using ApplyFilter instead of
setting the form's Filter property?

 

[Tezza]

You must also set the form's OrderByOn property to True after you set the

OrderBy property.

Ken, I believe your solution suggests the following would work...

DoCmd.ApplyFilter , "isnull(fldDateOff) = true"
Forms("frmProject").OrderBy "fldDOB DESC"
Forms("frmProject").OrderByOn = True
'MsgBox "OrderByOn property is " & Forms("frmProject").OrderByOn

.... but it produces the error message: 'Invalid use of property',
hightlighting .OrderBy

Is my OrderBy syntax correct (the field name is correct)?

tia
t

 

[Tezza]

I want to set a button on a form that will filter and sort the records.

You also need to set the form's OrderByOn property to True
before it will use the OrderBy setting.

Hi Marsh, see my reply to Ken on this one. I've tried placing the OrderByOn
= True code before and after the OrderBy code but it still does not like my
OrderBy property.

Specific help for these topics is available in VBA Help.

F1 has also been my favourite key

Is there a reason why you are using ApplyFilter instead of
setting the form's Filter property?

Not sure if that would accomplish the task I set myself, which is: Once a
month I take my DB to a meeting. The only records we are interested in at
this time are those with no 'off date' (fldDateOff) and we want to view them
youngest to oldest to match the meeting agenda. Obviously, I am creating a
form button to change these settings. Hope that answers your question.

tia
t

 

[Marshall Barton]

Hi Marsh, see my reply to Ken on this one. I've tried placing the OrderByOn
= True code before and after the OrderBy code but it still does not like my
OrderBy property.

Looks good from this end, but double check the name of the
form and that the form is open (and not a subform). You can
avoid misspelled form names by using Me
Me.OrderBy "fldDOB DESC"

Also check that the field fldDOB is in the form's record
source table/query. (The OrderByOn line should be after the
Order By line.)

This whole approach presumes that you have a good reason for
not sorting the records in the form's record source query.

F1 has also been my favourite key


Not sure if that would accomplish the task I set myself, which is: Once a
month I take my DB to a meeting. The only records we are interested in at
this time are those with no 'off date' (fldDateOff) and we want to view them
youngest to oldest to match the meeting agenda. Obviously, I am creating a
form button to change these settings. Hope that answers your question.

I understand that you want to filter the data, it's how you
are coding it that I am questioning. ApplyFilter does not
have a way for you to specify the form you want to filter.
As long as you are not also filtering a subform, this is
more specific:
Me.Filter = "fldDateOff Is Null"
Me.FillterOn = True

 

[Tezza]

Looks good from this end, but double check the name of the
form and that the form is open (and not a subform). You can
avoid misspelled form names by using Me
Me.OrderBy "fldDOB DESC"

Also check that the field fldDOB is in the form's record
source table/query. (The OrderByOn line should be after the
Order By line.)

This whole approach presumes that you have a good reason for
not sorting the records in the form's record source query.




I understand that you want to filter the data, it's how you
are coding it that I am questioning. ApplyFilter does not
have a way for you to specify the form you want to filter.
As long as you are not also filtering a subform, this is
more specific:
Me.Filter = "fldDateOff Is Null"
Me.FillterOn = True

Ok, I've amended my filter code as you suggested, and it works just fine.
Thanks for that.

As for the OrberBy code; i appear to meet all the necessary conditions but
neither Forms("frmProject").OrderBy "fldDOB DESC" nor Me.OrderBy "fldDOB
DESC" produce the expected result.

I further tested the date of birth field like this...

Me.Filter = "fldDOB = #23/01/1969#"
Me.FilterOn = True

....which worked just fine. Does this show that fldDOB should be available to
the OrderBy code?

I've also tested the OrderBy code using two other fields fldDateOn and
fldSurname, but I still get the 'invalid use of property' error.

Thanks so far. If you've any further suggestions I'd be grateful to hear
them

t
WinXP/Office2003

 

[Marshall Barton]

Oh bleep! I just realized that the = sign is missing:

Me.OrderBy = "fldDOB DESC"

 

[Tezza]

Oh bleep! I just realized that the = sign is missing:

Me.OrderBy = "fldDOB DESC"

oh man, I should have picked that up before posting. Thanks for your
persistence marsh

t