Sorting 2 Fields On A Subform

[Jim Mac Millan]

Hi,

I have a subform with three fields; Date, Time, and Notes. Right now the
records are sorted by entry. It wouldn't be a problem but sometimes I forget
to make note of a conversation and remember to enter it later. When that
happens thing are not shown in chronologic order. I would like them to sort
as follows. I can't seem to grasp how to make this happen.


Record 1
Date: 03/27/2006
Time: 11:01AM
Notes: Make initial contact

Record 2
Date: 03/27/2006
Time: 01:57pm
Notes: Requested a sample

TIA
Jim Mac Millan

 

[Jim Ory]

If using a Query for your subform data; then in the Sort dialog box of the
query, select Ascending or Descending for each control. Use descending to
have the last date at the top of the subform. Left control to right control
would be the sort order in the Query.

• From Access Help: "If you base a new form or report on a table or query
that has a sort order saved with it, the new form or report inherits the sort
order."

 

[Jim Mac Millan]

Hi Jim,

I saw Access Help. The subform is a linked table. I don't see how I
would generate two levels of sorts if it based on that. From what I can see
in table view only one column at a time is only allowed to be sorted. I
believe I need a primary and secondary sort of some kind.

Thanks
Jim Mac Millan

 

[John Vinson]

Hi Jim,

I saw Access Help. The subform is a linked table. I don't see how I
would generate two levels of sorts if it based on that. From what I can see
in table view only one column at a time is only allowed to be sorted. I
believe I need a primary and secondary sort of some kind.

You can base the Subform on a Query, sorted by multiple fields (up to
ten as I recall). Just base it on the Query rather than directly on
the Table. Don't worry, if all you're doing is sorting it will still
be updateable.

You may need to Requery the form if you're entering multiple records
out of sequence.

John W. Vinson[MVP]

 

[Jim Mac Millan]

John,

I setup a subform based on a query as you suggested. The sort works
great. However the new subform will not allow me to add any new records. The
record selectors "add new record button" is grayed out. I must be missing
something.

Thanks
Jim Mac Millan

 

[John Vinson]

John,

I setup a subform based on a query as you suggested. The sort works
great. However the new subform will not allow me to add any new records. The
record selectors "add new record button" is grayed out. I must be missing
something.

Thanks

Please open the Query in SQL view and post the SQL text here. There is
evidently something about the query that makes it non-updateable. If
we see the SQL we can probably fix it!

John W. Vinson[MVP]

 

[Jim Mac Millan]

John,

I have included the query but I don't think I need to use it after all.
I found out that the Order By property in Form design view allows me to sort
the way I want. I put in "Date DESC, Time DESC" and it appears to do what I
want. I guess at this point I want to make sure I am not doing something
wrong by using this method.

Thanks
Jim Mac Millan

SELECT [Batch Control].Batch_No, Leads_Notes.Date, Leads_Notes.Notes, [Leads
List].Owner_First_Name, [Leads List].Owner_Last_Name, [Leads
List].Doing_Business_As, [Leads List].Mailing_Address, [Leads
List].Mailing_Suite, [Leads List].Mailing_City, [Leads List].Mailing_State,
[Leads List].Mailing_Zip
FROM ([Batch Control] INNER JOIN [Leads List] ON [Batch Control].Batch_No =
[Leads List].Link_To_Batch_Control) INNER JOIN Leads_Notes ON [Leads
List].ID = Leads_Notes.Link_To_Leads
WHERE (((Leads_Notes.Date)=[?]))
ORDER BY [Leads List].Doing_Business_As DESC;

 

[John Vinson]

John,

I have included the query but I don't think I need to use it after all.
I found out that the Order By property in Form design view allows me to sort
the way I want. I put in "Date DESC, Time DESC" and it appears to do what I
want. I guess at this point I want to make sure I am not doing something
wrong by using this method.

Well, either will work - sorting in the form or in the query - but the
user can more easily mess up the form sorting (by changing the sort
order using the Records menu option and saving the form). It's harder
for them to get at the query.

If Batch_No is the Primary Key of Batch Control this query *should* be
updateable. Isn't it? When you based your form on the Table, it must
have been just the one table - did you change that in addition to
changing the sort order?

Thanks
Jim Mac Millan

SELECT [Batch Control].Batch_No, Leads_Notes.Date, Leads_Notes.Notes, [Leads
List].Owner_First_Name, [Leads List].Owner_Last_Name, [Leads
List].Doing_Business_As, [Leads List].Mailing_Address, [Leads
List].Mailing_Suite, [Leads List].Mailing_City, [Leads List].Mailing_State,
[Leads List].Mailing_Zip
FROM ([Batch Control] INNER JOIN [Leads List] ON [Batch Control].Batch_No =
[Leads List].Link_To_Batch_Control) INNER JOIN Leads_Notes ON [Leads
List].ID = Leads_Notes.Link_To_Leads
WHERE (((Leads_Notes.Date)=[?]))
ORDER BY [Leads List].Doing_Business_As DESC;

John W. Vinson[MVP]

 

[Jim Mac Millan]

John,

That makes sense and now I understand why it isn't doing what it should.
I think I missed an intermediate link to a third table. I am going to go
back and look at it right now. If I have problems I'll post back but I think
I understand the problem now.

From the beginning I have split the database so I could perform these
kinds of modifications. Out of curiosity and more likely ignorance I ask the
following question; why is it when viewing the front end the tables do not
show their relationships like they do when looking at the back end?

Thanks For All The Help
Jim Mac Millan

John,

I have included the query but I don't think I need to use it after
all.
I found out that the Order By property in Form design view allows me to
sort
the way I want. I put in "Date DESC, Time DESC" and it appears to do what
I
want. I guess at this point I want to make sure I am not doing something
wrong by using this method.

Well, either will work - sorting in the form or in the query - but the
user can more easily mess up the form sorting (by changing the sort
order using the Records menu option and saving the form). It's harder
for them to get at the query.

If Batch_No is the Primary Key of Batch Control this query *should* be
updateable. Isn't it? When you based your form on the Table, it must
have been just the one table - did you change that in addition to
changing the sort order?

Thanks
Jim Mac Millan

SELECT [Batch Control].Batch_No, Leads_Notes.Date, Leads_Notes.Notes,
[Leads
List].Owner_First_Name, [Leads List].Owner_Last_Name, [Leads
List].Doing_Business_As, [Leads List].Mailing_Address, [Leads
List].Mailing_Suite, [Leads List].Mailing_City, [Leads
List].Mailing_State,
[Leads List].Mailing_Zip
FROM ([Batch Control] INNER JOIN [Leads List] ON [Batch Control].Batch_No
=
[Leads List].Link_To_Batch_Control) INNER JOIN Leads_Notes ON [Leads
List].ID = Leads_Notes.Link_To_Leads
WHERE (((Leads_Notes.Date)=[?]))
ORDER BY [Leads List].Doing_Business_As DESC;

John W. Vinson[MVP]

 

[John Vinson]

From the beginning I have split the database so I could perform these
kinds of modifications. Out of curiosity and more likely ignorance I ask the
following question; why is it when viewing the front end the tables do not
show their relationships like they do when looking at the back end?

The relationships exist where the tables exist. There are no tables in
the frontend - only links to tables.

There is a relationships window in the frontend, and it often will
echo the relationships that exist in the backend (I believe that if
you use the Splitter Wizard it does). However, you cannot CREATE
enforced relationships in the frontend (since the tables aren't
there).

John W. Vinson[MVP]