How can I set the sort order for a subform from the parent form?

[Dave]

I have a combo box on a parent form that when a selection is made, it
retrieves a set of records that are displayed in a sub form.

I want to set the sort order of the subform from the parent form but I can't
get the syntax correct. It should be something like this:

Me.F_myForm_SUB.Form!OrderBy = "page, datestart"
Me.F_myForm_SUB.Form!OrderByOn = True

But these give me:
Err.Description: Microsoft Office Access can't find the field
'OrderByOn' referred to in your expression.

How do I set a reference to a subform property from the parent form?

 

[Graham Mandeno]

Hi Dave

The "!" syntax can only be used to refer to fields and controls on a form.
OrderBy and OrderByOn are *properties*, so you must use a dot.

 

[Dave]

Thank you Graham

I never quite undersstood when to use the "." and when to use the "!".

Dave

Hi Dave

The "!" syntax can only be used to refer to fields and controls on a form.
OrderBy and OrderByOn are *properties*, so you must use a dot.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a combo box on a parent form that when a selection is made, it
retrieves a set of records that are displayed in a sub form.

I want to set the sort order of the subform from the parent form but I
can't get the syntax correct. It should be something like this:

Me.F_myForm_SUB.Form!OrderBy = "page, datestart"
Me.F_myForm_SUB.Form!OrderByOn = True

But these give me:
Err.Description: Microsoft Office Access can't find the field
'OrderByOn' referred to in your expression.

How do I set a reference to a subform property from the parent form?

 

[Allen Browne]

Dave, your question is quite a common one.

As a guideline:
1. You must use the dot for properties (as Graham explaned).
2. You must use the bang for fields of a recordset.
3. You can use either the bang or the dot for other contexts (e.g. controls
on the form).

The flexibility of #3 is where the one-eyed flamers love to spout their
technobabble about which you should use. Personally, I use the dot because
VBA auto-completes the entry for me so it's faster and it's spelled
correctly. Additionally, if the field/control name is spelled, I get an
error at compile time, which does not happen with the bang. In practice
though there are only a couple of context where it really matters:

a) In the example you gave (referring to a form in a subform control), you
must include the ".Form" bit. Access 2003 seems to be especially susceptible
to this. Explanation:
http://members.iinet.net.au/~allenbrowne/casu-04.html

b) If you are referring to a field in the form's recordset that is *not*
represented by a control on a form, you may find that VBA refuses to compile
with the dot. If you don't strike this, it doesn't matter. If you do, you
can work around it by using the bang. I've struck it enough times now that I
use the bang in this context to avoid the future error.

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

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

Thank you Graham

I never quite undersstood when to use the "." and when to use the "!".

Dave

Hi Dave

The "!" syntax can only be used to refer to fields and controls on a
form. OrderBy and OrderByOn are *properties*, so you must use a dot.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a combo box on a parent form that when a selection is made, it
retrieves a set of records that are displayed in a sub form.

I want to set the sort order of the subform from the parent form but I
can't get the syntax correct. It should be something like this:

Me.F_myForm_SUB.Form!OrderBy = "page, datestart"
Me.F_myForm_SUB.Form!OrderByOn = True

But these give me:
Err.Description: Microsoft Office Access can't find the field
'OrderByOn' referred to in your expression.

How do I set a reference to a subform property from the parent form?