Sort subform data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a typical many-to-one file. I am using a combo box to retrieve data
from both the "one" and the "many" table using subforms of the Combo Box Main
Form. My combo box retrieves the data in both subforms, but the display of
the "many" table elements is not in the sequence I desire - it is in ID
sequence. I have put the Order By in the form properties of the subform, but
that doesn't do anything. Any suggestions?
Thanks
 
I have a typical many-to-one file. I am using a combo box to retrieve data
from both the "one" and the "many" table using subforms of the Combo Box Main
Form. My combo box retrieves the data in both subforms, but the display of
the "many" table elements is not in the sequence I desire - it is in ID
sequence. I have put the Order By in the form properties of the subform, but
that doesn't do anything. Any suggestions?
Thanks
Click to expand...

Base the Subform on a Query with a sort clause.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Thanks John,
I did base the subform on a query as you suggested, and it sequenced the
subform data like I wanted. However, now when I add a record to the subform
through the main form, it does not insert the autoID into the new subform
record - it leaves it blank???? I must have something set wrong?? Any clue
would be appreciated.
 
John,
I should have been more specific in my question and my first response to
your suggestion. I have a combo box which brings up data in my main subform
and all the matching subform records (now in date order thanks to you). The
main subform has a ClientID and the many subform has an ActivityID and
Client ID. When I go to add a record to the many subform, it does contain
all the data I keyed plus it includes the AutoID ActivityID but it doesn't
put in the ClientID, so there is no link back to the main subform. I have
really been going around in circles on this. Am I trying to do something that
can't be done with the combo box and subforms? Perhaps it is not possible to
sort the subform data and add to it also????? Please let me know and I'll
quit wasting my time on this approach.
Thanks
 
Thanks John,
I did base the subform on a query as you suggested, and it sequenced the
subform data like I wanted. However, now when I add a record to the subform
through the main form, it does not insert the autoID into the new subform
record - it leaves it blank???? I must have something set wrong?? Any clue
would be appreciated.
Click to expand...
What's the datatype of the autoID? What's the Recordsource property of
the subform? What are its Master and Child Link Fields - is the
linking field getting set correctly?


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
John,
I should have been more specific in my question and my first response to
your suggestion. I have a combo box which brings up data in my main subform
and all the matching subform records (now in date order thanks to you). The
main subform has a ClientID and the many subform has an ActivityID and
Client ID. When I go to add a record to the many subform, it does contain
all the data I keyed plus it includes the AutoID ActivityID but it doesn't
put in the ClientID, so there is no link back to the main subform. I have
really been going around in circles on this. Am I trying to do something that
can't be done with the combo box and subforms? Perhaps it is not possible to
sort the subform data and add to it also????? Please let me know and I'll
quit wasting my time on this approach.
Click to expand...

If the main form has ClientID as its primary key, and the subform's
table has ClientID as the linking field, then you should be able to
use ClientID as the Master Link Field and Child Link Field properties
of the Subform. This will display just subform records for that
client, and will add the main form's ClientID to any new records on
the subform. You're saying "the main subform" and the "matching
subform" and I'm not at all certain what forms you have!

If this isn't making sense please post:

- The Recordsource queries of both the main and sub forms
- The Master and Child Link Fields of the Subform control
- From the Relationships window, indicate how the mainform and
subform's tables are related

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
John,
I really do appreciate you responding to my problem. I've spent many, many
hours using a trial and error method which hasn't worked for me. Anyway,
here is a more comprehensive description of what I have:
I have the following three forms linked together.
1. Top form only has a Combo Box with the name Combo2 and I have included
ClientId, LastName and FirstName in the box.
2. First Subform has my Client information and ClientId is the AutoNumber.
The LinkChildren Field has ClientId. The LinkMaster has Combo2.
3. Second Subform has my Activity information which is now sorted with a
Query. The LinkChildren Field has ClientId and the LinkMaster has Combo2.
In the Query I made up to sort the Activity information, I did not include
the Client Table to get the ClientId but only used the Activity Table. Is
this my mistake?
Again, I am thankful and appreciative for any help.
 
Since my last response, I did some more testing, and I found that whenever I
add an Activity subrecord to an existing Client record, everything is OK - it
picks up the ClientId in the subrecord. But if I add a new Client Record and
then add an Activity Record to it, the ClientId does not go into the new
Activity record.
 
John,
I really do appreciate you responding to my problem. I've spent many, many
hours using a trial and error method which hasn't worked for me. Anyway,
here is a more comprehensive description of what I have:
I have the following three forms linked together.
1. Top form only has a Combo Box with the name Combo2 and I have included
ClientId, LastName and FirstName in the box.
2. First Subform has my Client information and ClientId is the AutoNumber.
The LinkChildren Field has ClientId. The LinkMaster has Combo2.
3. Second Subform has my Activity information which is now sorted with a
Query. The LinkChildren Field has ClientId and the LinkMaster has Combo2.
In the Query I made up to sort the Activity information, I did not include
the Client Table to get the ClientId but only used the Activity Table. Is
this my mistake?
Again, I am thankful and appreciative for any help.
Click to expand...

I'd suggest a simpler design. The problem you're having is because
your Subform2 is not a subform of the client (sub) form - it's side by
side. Might you consider making your current First Subform into the
main form? You can put an unbound Combo Box on the form to navigate to
a particular client, and make the Second Subform a subform of the
client form directly, using ClientID as the master/child link field.

The alternative would be to put VBA code in the First Subform's
AfterInsert event to requery Combo2 and move it to the newly added
ClientID. I'd have to set up a form to test this, but I'm not happy
with the idea - it seems overly complex to do something that is
normally pretty simple!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top