OrderBy property in VBA

  • Thread starter Thread starter Tezza
  • Start date Start date
T

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
 
You must also set the form's OrderByOn property to True after you set the
OrderBy property.
 
Tezza said:
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.
Click to expand...

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?
 
You must also set the form's OrderByOn property to True after you set the
OrderBy property.
Click to expand...

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
 
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.
Click to expand...

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.
Click to expand...

F1 has also been my favourite key :)
Is there a reason why you are using ApplyFilter instead of
setting the form's Filter property?
Click to expand...

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
 
Tezza said:
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.
Click to expand...

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.
Click to expand...


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
 
Marshall Barton said:
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
Click to expand...

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
 
Oh bleep! I just realized that the = sign is missing:

Me.OrderBy = "fldDOB DESC"
 
Tezza said:
Oh bleep! I just realized that the = sign is missing:

Me.OrderBy = "fldDOB DESC"
Click to expand...

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

t
 
Back
Top