Record order in subform

After upgrading to Access 2003, new records added to a table via a subform do
not appear in the correct order. However, when I open the subform as a form
the records show in the correct order. Table A (the master table) and Table
B (the child table) are linked via Table A's primary key (contact name). In
an attempt to fix the problem I changed the index on Table B from a single
field index on the linked field (duplicates allowed in Table B) to a multiple
field index. The two fields in Table B's index are the contact name field
(linking Table B to Table A) and a field, defined as a text field, with the 4
digit year. The index is defined to sort both fields ascending. New data
entered appears as the first record instead of the last record when displayed
in the subform. There is an event procedure on the invoked on the main form:
Private Sub Form_Current()
DoCmd.GoToControl "Table B Subform"
DoCmd.GoToRecord , , acLast
DoCmd.GoToControl "Table A Contact Name"
End Sub
Thanks in advance for any ideas on correcting this problem.
Rod

Rod,
Tables don't really have any order. To view your records in some particular
order, you need to use a query and sort the records as you need them.

--

Lynn Trapp
Microsoft MVP (Access)
www.ltcomputerdesigns.com


"Rod" wrote in message
...
After upgrading to Access 2003, new records added to a table via a subform
do
not appear in the correct order. However, when I open the subform as a
form
the records show in the correct order. Table A (the master table) and
Table
B (the child table) are linked via Table A's primary key (contact name).
In
an attempt to fix the problem I changed the index on Table B from a single
field index on the linked field (duplicates allowed in Table B) to a
multiple
field index. The two fields in Table B's index are the contact name field
(linking Table B to Table A) and a field, defined as a text field, with
the 4
digit year. The index is defined to sort both fields ascending. New data
entered appears as the first record instead of the last record when
displayed
in the subform. There is an event procedure on the invoked on the main
form:
Private Sub Form_Current()
DoCmd.GoToControl "Table B Subform"
DoCmd.GoToRecord , , acLast
DoCmd.GoToControl "Table A Contact Name"
End Sub
Thanks in advance for any ideas on correcting this problem.
Rod

Rod,
As Lynn points out, you need to order the query that is the record source
for the subform. As to new records, I can't test it at the moment, but I
believe that new records added will not automatically reorder. You will
probably have to requrey the subform to get them into the correct order.

"Rod" wrote:

After upgrading to Access 2003, new records added to a table via a subform do
not appear in the correct order. However, when I open the subform as a form
the records show in the correct order. Table A (the master table) and Table
B (the child table) are linked via Table A's primary key (contact name). In
an attempt to fix the problem I changed the index on Table B from a single
field index on the linked field (duplicates allowed in Table B) to a multiple
field index. The two fields in Table B's index are the contact name field
(linking Table B to Table A) and a field, defined as a text field, with the 4
digit year. The index is defined to sort both fields ascending. New data
entered appears as the first record instead of the last record when displayed
in the subform. There is an event procedure on the invoked on the main form:
Private Sub Form_Current()
DoCmd.GoToControl "Table B Subform"
DoCmd.GoToRecord , , acLast
DoCmd.GoToControl "Table A Contact Name"
End Sub
Thanks in advance for any ideas on correcting this problem.
Rod

Thank you Lynn and Klatuu. I changed the subform source to a query. The
query specified sorting on the primary key and the year. This resolved the
problem.

Thanks again for your assistance.

Rod

"Rod" wrote:

After upgrading to Access 2003, new records added to a table via a subform do
not appear in the correct order. However, when I open the subform as a form
the records show in the correct order. Table A (the master table) and Table
B (the child table) are linked via Table A's primary key (contact name). In
an attempt to fix the problem I changed the index on Table B from a single
field index on the linked field (duplicates allowed in Table B) to a multiple
field index. The two fields in Table B's index are the contact name field
(linking Table B to Table A) and a field, defined as a text field, with the 4
digit year. The index is defined to sort both fields ascending. New data
entered appears as the first record instead of the last record when displayed
in the subform. There is an event procedure on the invoked on the main form:
Private Sub Form_Current()
DoCmd.GoToControl "Table B Subform"
DoCmd.GoToRecord , , acLast
DoCmd.GoToControl "Table A Contact Name"
End Sub
Thanks in advance for any ideas on correcting this problem.
Rod

You are very welcome.

--

Lynn Trapp
Microsoft MVP (Access)
www.ltcomputerdesigns.com


"Rod" wrote in message
...
Thank you Lynn and Klatuu. I changed the subform source to a query. The
query specified sorting on the primary key and the year. This resolved
the
problem.

Thanks again for your assistance.

Rod

"Rod" wrote:

After upgrading to Access 2003, new records added to a table via a
subform do
not appear in the correct order. However, when I open the subform as a
form
the records show in the correct order. Table A (the master table) and
Table
B (the child table) are linked via Table A's primary key (contact name).
In
an attempt to fix the problem I changed the index on Table B from a
single
field index on the linked field (duplicates allowed in Table B) to a
multiple
field index. The two fields in Table B's index are the contact name
field
(linking Table B to Table A) and a field, defined as a text field, with
the 4
digit year. The index is defined to sort both fields ascending. New
data
entered appears as the first record instead of the last record when
displayed
in the subform. There is an event procedure on the invoked on the main
form:
Private Sub Form_Current()
DoCmd.GoToControl "Table B Subform"
DoCmd.GoToRecord , , acLast
DoCmd.GoToControl "Table A Contact Name"
End Sub
Thanks in advance for any ideas on correcting this problem.
Rod