Sorting Data in subform by Date field

[Guest]

I inserted the following in the "Orderby" property of my subform intending to
have the data display most recent records in the subform from top down. The
form is set to datasheet view.

[Reading Date] Desc Me.OrderByOn = True

What am I doing wrong?

 

[DS NTE]

I did it this way, this code is for a commandbutton on the main form:

Me.sbfrm_Registrering.Form.OrderBy = "AvlestDato Desc"
Me.sbfrm_Registrering.Form.OrderByOn = True

sbfrm_Registrering.Form is the name of the subform,
AvlestDato is the name of the datefield.


HTH
Knut

 

[Van T. Dinh]

I think you need 2 statements rather than the posted code:

Me.OrderBy = "[Reading Date] DESC"
Me.OrderByOn = True

(assuming the code is in the Class Module associated with the Subform.)

 

[Guest]

Nothing happens ....code in the Class Module is above my head though..maybe
this problem is too ...just wanting the new entries on top

I think you need 2 statements rather than the posted code:

Me.OrderBy = "[Reading Date] DESC"
Me.OrderByOn = True

(assuming the code is in the Class Module associated with the Subform.)
--
HTH
Van T. Dinh
MVP (Access)

I inserted the following in the "Orderby" property of my subform intending to
have the data display most recent records in the subform from top down. The
form is set to datasheet view.

[Reading Date] Desc Me.OrderByOn = True

What am I doing wrong?

 

[Van T. Dinh]

Where did you have the code?

Is it in the code window associated with the Main Form or the Subform?

 

[Guest]

I put the code on the Orderby prperty box on the subform

 

[Van T. Dinh]

If you use the OrderBy row in the Properties window of the Subfor, you only
need to enter:

[Reading Date] DESC

assuming [Reading Date] is the Field name in the (Sub)Form's RecordSource.

 

[John Vinson]

I inserted the following in the "Orderby" property of my subform intending to
have the data display most recent records in the subform from top down. The
form is set to datasheet view.

[Reading Date] Desc Me.OrderByOn = True

What am I doing wrong?

I'd suggest instead simply basing the Subform on a query which has the
field sorted the way you like.

John W. Vinson[MVP]

 

[Guest]

I tried that as I have seen that suggestion frequently in here when I was
searching for a solution. The table this is in is a child table linked to
the parent by Parent_ID to Foreignkey. I made the query and switched the
control source for the subform. I received a prompt for the Parent ID...that
through me for a loop too. Thanks for your time.

I inserted the following in the "Orderby" property of my subform intending to
have the data display most recent records in the subform from top down. The
form is set to datasheet view.

[Reading Date] Desc Me.OrderByOn = True

What am I doing wrong?

I'd suggest instead simply basing the Subform on a query which has the
field sorted the way you like.

John W. Vinson[MVP]

 

[John Vinson]

I tried that as I have seen that suggestion frequently in here when I was
searching for a solution. The table this is in is a child table linked to
the parent by Parent_ID to Foreignkey. I made the query and switched the
control source for the subform. I received a prompt for the Parent ID...that
through me for a loop too. Thanks for your time.

The query should (of course) include the foreign key field (which
would not ordinarily be named foreign key). If you can link the table,
you can link the query!

What are the relevant (primary key and linking) fields of the two
tables involved? Did you perhaps try to link [Parent_ID] to a
misspelling such as [Parent ID] or [ParentID]? In the Relationships
window, what field did you use for the join?

John W. Vinson[MVP]

 

[Guest]

It didn't occur to me to link the query to the table. My tables are as
follows:

When I made the child table I copied the Parent_ID then changed it to a
number _ longinteger and use it as a foreign key linked to the Parent_ID
PKey. Of course the Child has it's own autonumber primary key.

Following your thought.....by constructing a query sorted on the date field
to replicate the child table and linked to the child table..I should be able
to base my subform on the query and it will deliver new data into the child
table...correct?

I tried that as I have seen that suggestion frequently in here when I was
searching for a solution. The table this is in is a child table linked to
the parent by Parent_ID to Foreignkey. I made the query and switched the
control source for the subform. I received a prompt for the Parent ID...that
through me for a loop too. Thanks for your time.

The query should (of course) include the foreign key field (which
would not ordinarily be named foreign key). If you can link the table,
you can link the query!

What are the relevant (primary key and linking) fields of the two
tables involved? Did you perhaps try to link [Parent_ID] to a
misspelling such as [Parent ID] or [ParentID]? In the Relationships
window, what field did you use for the join?

John W. Vinson[MVP]

 

[John Vinson]

Following your thought.....by constructing a query sorted on the date field
to replicate the child table and linked to the child table..I should be able
to base my subform on the query and it will deliver new data into the child
table...correct?

WHOA! It seems you have a misconception of what a query is and how it
works.

No. You're not "replicating the child table". A query IS JUST A VIEW
of the data in the table. It has no independent existance; it is not
another table; the records in the query *are* the records in the
table. They'll be sorted differently, or have a subset of the fields
or a subset of the records, but they are *the same records*.

But for this problem, yes: create a Query based on the child table.
Include all of the fields which you wish to see on the subform;
specify a sort on the date field. Use this Query as the recordsource
of the subform (instead of using the table). When you enter data into
the subform, it will update the table directly.


John W. Vinson[MVP]

 

[Guest]

Your explanations have guided me to success on this. It works as does the
little expression:

=DMax("[ReadOn]","[Child Query]","[ParentTable_ID] = " & [ID])

Now, based on your repeated advice to others that I have read in here....I
need to construct another Query adding a calculated field based on the above
expression that will give me nothing but the most recent dates in a field.
If I can get this then I can point all my current reports to this field in
the Query and they will work as before.....I think. Thanks...think this will
work?

 

[John Vinson]

=DMax("[ReadOn]","[Child Query]","[ParentTable_ID] = " & [ID])

Now, based on your repeated advice to others that I have read in here....I
need to construct another Query adding a calculated field based on the above
expression that will give me nothing but the most recent dates in a field.
If I can get this then I can point all my current reports to this field in
the Query and they will work as before.....I think. Thanks...think this will
work?

Absolutely. Just use the DMax() expression as a criterion on the date
field in your query.

John W. Vinson[MVP]