Thanks Orange but don't think they fit what I'm doing as they seem to rely on the count I have a value which I add for each Item
I have got it working but when there is more than one duplicate vote I get an extra records
Thanks
New code below
Code:
Private Sub Cmd_Compile_Click()
Dim R As DAO.Recordset
Dim I As Integer, C As Integer, O As Integer
Dim D As Long, L As Long
On Error GoTo HandleErr
'Create Recordset for the chart sort number 10 to 1 Order
I = 1
L = 0
O = 1
Set R = CurrentDb.OpenRecordset("SELECT * FROM tblChartCreator WHERE [WeekID]=" & Me![WeekID] & " ORDER BY SortNumber DESC", dbOpenDynaset)
With R
Do While Not .EOF
If I > 1 Then
.MovePrevious
L = !SortNumber
C = !Position
.MoveNext
End If
.Edit
If L = !SortNumber Then
!Position = C
Else
!Position = I
O = O + 1
End If
!InChart = True
.Update
I = I + 1
If O = Me![txt_Entries] Then Exit Do
.MoveNext
Loop
End With
Me.Sub_frmChartCreatorDetails.Requery
R.Close
Set R = Nothing
HandleExit:
Exit Sub
HandleErr:
Select Case Err.Number
Case Else
MsgBox Err.Number & vbCrLf & Err.Description
Resume HandleExit
Resume
End Select
End Sub
I need to store the pos as this is just a compiler once compiled I would post it to the main charts
I know I don't really need to store the position but I would perfer to save it.