If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Record order in subform
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 |
#3
|
|||
|
|||
Record order in subform
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 |
#4
|
|||
|
|||
Record order in subform
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 |
#5
|
|||
|
|||
Record order in subform
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 |
Thread Tools | |
Display Modes | |
|
|