Auto re-number records based on a query

[Kevin]

I have a form that is used to display active projects that
I'm working on. It is based on a query that filters out
any inactive or complete projects, so that only active
projects are displayed in the form. The query also sorts
the projects in order of importance (rank). I would like
to be able to click on one of the "rank" fields in the
form, and have that field become the new "#1" project, and
have all of the other fields automatically re-numbered to
shift them appropriately down in rank. The form is
displayed in datasheet view.

Example:
Rank Project Description
1 Revise plant drawings
2 Reprogram assembly line
3 Update phone system
4 Design new test fixtures

If I click on the number "3", then I would want it to be
the new #1 project, and then re-number the other projects.

Rank Project Description
1 Update phone system
2 Revise plant drawings
3 Reprogram assembly line
4 Design new test fixtures

Any help would be appreciated.

Thanks,

Kevin

 

[John Smith]

You can do this with some UPDATE queries triggered from your click event :-

Dim SQL as String
If Rank > 1 Then
SQL = "UPDATE ranktable SET rank = 0 WHERE rank = " & rank
CurrentDb.Execute SQL, dbFailOnError
SQL = "UPDATE ranktable SET rank = rank + 1 WHERE rank BETWEEN 1 AND " & rank
CurrentDb.Execute SQL, dbFailOnError
SQL = "UPDATE ranktable SET rank = 1 WHERE rank = 0"
CurrentDb.Execute SQL, dbFailOnError
End If

You will need to insert your own table and column names, and add an error trap.