Edit Priority Rank List

[Steve]

Hi,

I am creating a projects data base. I would like to be able to reorder the
projects priority ranking without reassigning each ranking to every project.
Based on a similar question posed online and advice from ScottGem (see
below), I have created an update query to add one to the rank list of
projects that have a priority rank of greater than or equal to the edited
value. I am having trouble with this. I have created a form called
frmUpdateRankList that has an unbound combobox and a list box that displays
the current projects sorted by rank order. I would like the user to select a
project from the list and then select a new rank order from the combobox.
This will initiate an afterUpdate event. The code then runs the updatequery
to increase the ranking of all projectes with a priority rank greater than or
equal to the value selected in the combobox. This works ok. I would then like
to get the original priority ranking of the selected project replaced with
the number selected in the combobox. I can not get this to work. Any ideas on
how best to do this? Any help is greatly appreciated.

Scott's Answer
Create an update query that sets the priority field to:
[Priority] + 1
then set the criteria to

= new number

Where new number is the rank of the new number.
This will increment by one each priority.
Hope this helps,
Scott<>

My code:

Private Sub cboNewRankOrder_AfterUpdate()

Dim ListBxCount As Integer
Dim ListBxCounter As Integer
Dim X As Integer
Dim intNewRankOrder As Integer

intNewRankOrder = Me.cboNewRankOrder

'set X = 0
X = 0

'Check to see if an item was selected
ListBxCount = ListBxRankOrder.ListCount - 1
For ListBxCounter = 0 To ListBxCount
If ListBxRankOrder.Selected(ListBxCounter) = True Then
X = X + 1
End If
Next ListBxCounter
If X = 0 Then
MsgBox "You must select a project!"
Exit Sub
End If

DoCmd.SetWarnings False 'suppresses action query warnings
DoCmd.OpenQuery "UpDateQryRankOrder"
DoCmd.SetWarnings True

'set selected listbox item to new sort order
Dim db As Database
Dim rstEvents As DAO.Recordset

ListBxCount = ListBxRankOrder.ListCount - 1
For ListBxCounter = 0 To ListBxCount
If ListBxRankOrder.Selected(ListBxCounter) = True Then
Set db = CurrentDb
Set rstEvents = db.OpenRecordSet("tblProject")
rstEvents.Edit
rstEvents!RankOrder = intNewRankOrder
rstEvents.Update
ListBxRankOrder.Selected(ListBxCounter) = False
End If
Next ListBxCounter

End Sub