re-sort continuous form after insertion

[Thomas Spellman]

What is the simplest way to have a continuous form re-sorted on the text in
a dropdown after every insertion? The dropdown is bound to an integer
field, so it must be the display text, not the value.

T

 

[Allen Browne]

Create a query that includes the form's source table as well as the combo's
RowSource table. Use the query as the RecordSource for your form, and you
can then sort by the text field from the lookup table.

Traps:
1. If the combo is bound to a field that can be left blank, double-click the
line joining the 2 tables in query design view. Access pops up a dialog
offering 3 options. Choose the one that says,
All options from the main table, and any matches from the combo's table.
(Technically this is called an outer join.)

2. If you get a silly message about not being able to set the value when you
try to insert a new record in your form, open the lookup table in design
view and remove the Default Value from all fields. (Access wrongly tries to
insert this value into the lookup table when you are in fact creating a new
record in the main table.)