Results 1 to 5 of 5
  1. #1
    Mickjav is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Location
    Margate, Kent
    Posts
    117

    Position Sequence

    Hi I've been trying to create a sub to compile A personal chart I have it working in it's basic form look at image pos

    Click image for larger version. 

Name:	2020-01-12.png 
Views:	12 
Size:	73.3 KB 
ID:	40658

    The following code complies the above
    Code:
    Private Sub Cmd_Compile_Click()
    Dim R As DAO.Recordset
    Dim I 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
    Set R = CurrentDb.OpenRecordset("SELECT * FROM tblChartCreator WHERE [WeekID]=" & Me![WeekID] & " ORDER BY SortNumber DESC", dbOpenDynaset)
        With R
            Do While Not .EOF
                .Edit
                    !Position = I
                    !InChart = True
                .Update
                I = I + 1
                If I > 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
    But instead of The pos reading

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10

    you will notice at pos 4 and 5 they both have the same votes I.E. 6



    So Instead of the count above I need it to read
    1
    2
    3
    4
    4

    6
    7
    8
    9
    10

    But it should be able to do the following
    1
    2
    3
    4
    4

    6
    7
    7

    9
    10

    Mick
    All open code examples and free projects are only available from:
    https://databasedreams.createaforum.com/index.php

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,853
    Mick,
    You may find something in this Allen Browne example/article.

    Or https://bytes.com/topic/access/insig...-ms-access-sql

    Maybe search "Ms Access Ranking query with ties"

    Good luck.

  3. #3
    Mickjav is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Location
    Margate, Kent
    Posts
    117
    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.
    All open code examples and free projects are only available from:
    https://databasedreams.createaforum.com/index.php

  4. #4
    Mickjav is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Location
    Margate, Kent
    Posts
    117
    Thanks for the help Orange got it working now Need to clean it up a bit lol

    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
                End If
                O = O + 1
                    !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
    Edit: Removed O As not needed mick
    All open code examples and free projects are only available from:
    https://databasedreams.createaforum.com/index.php

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,853

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Forms position next to another
    By LonghronJ in forum Modules
    Replies: 2
    Last Post: 08-21-2018, 04:07 PM
  2. How to position Tab Control
    By mdavid in forum Forms
    Replies: 8
    Last Post: 11-27-2017, 01:48 PM
  3. Add decimal in position
    By BrianF in forum Import/Export Data
    Replies: 4
    Last Post: 04-15-2013, 12:18 PM
  4. Position of scroll bar
    By VictoriaAlbert in forum Access
    Replies: 2
    Last Post: 04-14-2011, 04:29 PM
  5. Set Form Position
    By Yance in forum Programming
    Replies: 3
    Last Post: 11-29-2010, 02:20 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums