Access Continuous Form - order of records any bright ideas?

[Guest]

I want a user to be able to add a succession of records related to a header
on a continous form. DSo far no problem. But I want the USER to be able to
control where on the form the record is placed - i.e. first second third etc.

For example the user could add three records A, B and C and they would
appear in the order A, B and C (if that was how the order by was set).

But I want my user to be able to say no I want this new record say D to
display between A and C on the form rather than at the end.

The only way I can think of doing it is by adding an extra "Position" field
that the user updates eg 1,2,3,4,5 and then re display the screen. But this
will get vey messy for the uer - what if she wants to put a record between
position 1 and 2?

I suppose anyher way would be to have some sort of "move down" button that
deleted adn readds the record with an order by field like position between
after the next records value - but that sounds complicated with VB.

Any ideas?

Lou

 

[fredg]

I want a user to be able to add a succession of records related to a header
on a continous form. DSo far no problem. But I want the USER to be able to
control where on the form the record is placed - i.e. first second third etc.

For example the user could add three records A, B and C and they would
appear in the order A, B and C (if that was how the order by was set).

But I want my user to be able to say no I want this new record say D to
display between A and C on the form rather than at the end.

The only way I can think of doing it is by adding an extra "Position" field
that the user updates eg 1,2,3,4,5 and then re display the screen. But this
will get vey messy for the uer - what if she wants to put a record between
position 1 and 2?

I suppose anyher way would be to have some sort of "move down" button that
deleted adn readds the record with an order by field like position between
after the next records value - but that sounds complicated with VB.

Any ideas?

Lou

Access tables do not store records in any particular order. If you
wish to display the records on a form in a particular order you must
sort them, according to a value in a field.

If the record count is large, and you wish to control how all the
records are displayed, you will have an impossible job of re-ordering
each record each time a new record is added, or re-numbering existing
records each time you wish to re-order their display.

If you are only concerned with displaying a few, let's say 10 (out of
the many) of the records in a certain order, this is easy.

Your idea of a separate numbering field is correct.
Add a field named "SortOrder" to your table. Set it's datatype to
Number, Field Size Double.

All you need do is, for example, number those few records 1,2,3, ...
10 in the ShowOrder field.
Then number all the rest of the records with the same much higher
number, let's say 200.

Make a query the record source for this form.
Sort on the SortOrder field in the query. The first 10 numbered
records will display in their numerical order, the rest (numbered 200)
will be however Access displays them.

To rearrange the order, just re-number the records, changing the
record numbered 4, for example, to 200 and changing another record to
4.

Note: the record numbers do not need to be consecutive. They only need
to sort in the order you wish to display them.
3,6,7,9 will display in the same order as 1,2,3,4.

Code the SortOrder control's AfterUpdate event:
Me!SortOrder.Requery

The records will be re-arranged each time the SortOrder field is
changed.

If you wish to place a record between 3 and 4 simply number it 3.5.
This re-arranging can be time consuming for a large number of
records, but quite easily do-able if only for a small number, since
you only need to number up to 10 records and all the rest are number
20.

 

[Guest]

fredg

Thanks.

This is an OK way to do it - nice and simple! Thanks very much.

Although instead of creating an extra query I set the forms OrderBy property
to the sort field and then coded the fields after update event with :
Me.Requery.

Rgs Lou