OrderBy property in VBA

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tezza - 13 Jul 2007 23:27 GMT

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

Reply to this Message

Ken Snell (MVP) - 14 Jul 2007 00:22 GMT

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

Signature

Ken Snell
<MS ACCESS MVP>

>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
[quoted text clipped - 9 lines]
> tia
> tezza

Reply to this Message

Tezza - 15 Jul 2007 16:46 GMT

> 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

Reply to this Message

Marshall Barton - 14 Jul 2007 00:51 GMT

>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
[quoted text clipped - 4 lines]
>
>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?

Signature

Marsh
MVP [MS Access]

Reply to this Message

Tezza - 15 Jul 2007 17:06 GMT

>>I want to set a button on a form that will filter and sort the records.
>>The
[quoted text clipped - 8 lines]
> 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

Reply to this Message

Marshall Barton - 15 Jul 2007 17:58 GMT

>>>I want to set a button on a form that will filter and sort the records.
>>>The
[quoted text clipped - 12 lines]
>= 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.

>> Specific help for these topics is available in VBA Help.
>
[quoted text clipped - 8 lines]
>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

Signature

Marsh
MVP [MS Access]

Reply to this Message

Tezza - 15 Jul 2007 20:26 GMT

>>>>I want to set a button on a form that will filter and sort the records.
>>>>The
[quoted text clipped - 48 lines]
> 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

Reply to this Message

Marshall Barton - 15 Jul 2007 21:42 GMT

>>>>> Forms("frmProject").OrderBy "fldDOB DESC"

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

Me.OrderBy = "fldDOB DESC"

Signature

Marsh
MVP [MS Access]

Reply to this Message

Tezza - 16 Jul 2007 23:58 GMT

>>>>>> Forms("frmProject").OrderBy "fldDOB DESC"
>
> 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

Reply to this Message