Renumber fields based on a select query

[access22]

My database has a table with the following fields: Meeting, number, Action, Date.

In the table are multiple meetings. When the admin pulls up their meeting (based on a parameter query), they want to be able to automatically renumber their "number" fields in the order they are in. That way, they can put actions occurring on the same date in the order they want. This can't be hard coded in because sometimes they don't want the actions renumbered.

I am relatively new to access, so any help would be appreciated.

Thanks.

 

[OrWolf]

If you are still interested in a possible solution, below is some code that I just created to renumber a survey based on the question numbers already input. This allows the user to renumber a survey and leave gaps. This event procedure will then renumber based on the question number order, but will put them in sequential order with no gaps.

It's still rough, but works.


Private Sub ReNumberBtn_Click()
Dim CurrentID As Long, CurrentQ As Long, NextQ As Long
DoCmd.SetWarnings False
NextQ = 10000

Do
NextQ = NextQ + 1
CurrentID = DLookup("[RAS_ID]", "[RA_SurveyTest]", "[SurveyKey] = " & Me.SurveyKey & " And [QuestionNo] = " _
& DMin("[QuestionNo]", "[RA_SurveyTest]", "[SurveyKey] = " & [SurveyKey]))
CurrentQ = DLookup("[QuestionNo]", "[RA_SurveyTest]", "[SurveyKey] = " & Me.SurveyKey & " And [QuestionNo] = " _
& DMin("[QuestionNo]", "[RA_SurveyTest]", "[SurveyKey] = " & [SurveyKey]))
If CurrentQ > 10000 Then
GoTo ReNumber
End If
DoCmd.RunSQL "UPDATE RA_SurveyTest SET RA_SurveyTest.QuestionNo = " & NextQ _
& " WHERE (((RA_SurveyTest.RAS_ID)=" & CurrentID & &quot);"
Loop

ReNumber:
NextQ = 0
Do
NextQ = NextQ + 1
CurrentID = IIf(IsNull(DLookup("[RAS_ID]", "[RA_SurveyTest]", "[SurveyKey] = " & Me.SurveyKey & " And [QuestionNo] = " _
& (NextQ + 10000))), 0, DLookup("[RAS_ID]", "[RA_SurveyTest]", "[SurveyKey] = " & Me.SurveyKey & " And [QuestionNo] = " _
& (NextQ + 10000)))
If CurrentID = 0 Then
GoTo Finish
End If
DoCmd.RunSQL "UPDATE RA_SurveyTest SET RA_SurveyTest.QuestionNo = " & NextQ _
& " WHERE (((RA_SurveyTest.RAS_ID)=" & CurrentID & &quot);"
Loop

Finish:

DoCmd.SetWarnings True
End Sub