Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    millsbrandonk is offline Novice
    Windows 10 Access 2007
    Join Date
    Dec 2019
    Location
    Brighton, UK
    Posts
    11

    Alternative for autonumber waiting list where rows can be pulled from the middle and it recounts

    I searched all over, and found soo many defensive people about why you shouldnt change the numbering, etc. However I have not found a solution to this yet.

    I have a waiting list of people. The waiting list is a table that holds information about the people on the waiting list.


    There is currently an AutoNumber field. This field is NOT assigned as a primary key, or any key.

    There is another numeric field on this table as a primary key.

    The people do not get given this autonumber, its simply to use internally so we know who is the next in line.

    Occasionally, people get pulled from the middle of the list based on certain criteria.

    I am looking for any other creative way to either have a simple number field that gets recounted on every row after a change, or a way to reset the autonumbering.

    I need this to be done automatically every time. Would it be better to just have a regular number field, and run some kind of For Each +1 command every time theres a change?
    Im trying to not make this overly complex, and hoping that maybe someone knows a trick to make the autonumber just reset, or another simple solution.


    Thanks in Advance!!!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    There's really no problem here. Use the autonumber field. It counts automatically, passively, without vb.
    Use that field as a reference for the record.

    If you want a numeric ordering, use a report and you can get 1,2,3.. in your list.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Are you saying that if #2 gets seen to before #1 you want your list to be re-ordered/rebuilt so that it doesn't look like
    1
    3
    4
    5
    but instead looks like
    1
    2
    3
    4
    ? Won't that mess things up when the person you associated with a number becomes some other number?
    Last edited by Micron; 03-15-2021 at 09:04 AM. Reason: correction
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Quote Originally Posted by millsbrandonk View Post
    The people do not get given this autonumber, its simply to use internally so we know who is the next in line.
    So, why you need a recounting?

    Quote Originally Posted by millsbrandonk View Post
    I am looking for any other creative way to either have a simple number field that gets recounted on every row after a change, or a way to reset the autonumbering.

    Would it be better to just have a regular number field, and run some kind of For Each +1 command every time theres a change?
    What is this change?

    Anyway, try this query and check the field [Running]:
    Code:
    SELECT *, DCount("*","tblWaitingList","FK=" & [FK] & " and PK<" & [PK])+1 AS Running
    FROM tblWaitingList 
    ORDER BY FK, PK;
    where:
    PK the primary key of the waiting list,
    FK the field of change.

    Result: You get a numbering "on the fly" for each change in the retrieved data set.

    Cheers,
    John

  5. #5
    millsbrandonk is offline Novice
    Windows 10 Access 2007
    Join Date
    Dec 2019
    Location
    Brighton, UK
    Posts
    11
    Quote Originally Posted by Micron View Post
    Are you saying that if #2 gets seen to before #1 you want your list to be re-ordered/rebuilt so that it doesn't look like
    1
    3
    4
    5
    but instead looks like
    1
    2
    3
    4
    ? Won't that mess things up when the person you associated with a number becomes some other number?

    The people do not get given this autonumber, its simply to use internally so we know who is the next in line.



    ranman256
    There's really no problem here. Use the autonumber field. It counts automatically, passively, without vb.
    Use that field as a reference for the record.

    If you want a numeric ordering, use a report and you can get 1,2,3.. in your list.


    I am trying to have it recount when a number(row) has been removed.

    So, why you need a recounting?


    The Recounting is so that when we have lots of people on the list, it dosent look like:
    1
    2
    4
    5
    6
    8
    12
    13
    14
    17

    ---------------------------------------------------



    1 needs to look like-> 1
    2 2
    4 3
    5 4
    6 5
    8 6
    12 7
    13 8
    14 9
    17 10

    Again, These are only for internal staff to use, the people do not get given this number. The people do not get given any number, they give us their name, and we look through the list at criteria, and then pull them out of the list and call them.

    The problem is staff will look at the list of people, and see 1,2,4... and think there is someone missing from the list, and that we have a problem.(which there is someone missing from the list, because they were already contacted based on criteria) but the staff just dosent understand. So its really best to just have 1,2,3,4,5..... so management dosent get calls saying the waitinglist is broke.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The people do not get given this autonumber, its simply to use internally so we know who is the next in line.
    I didn't ask that. Perhaps I should have said "attended to" rather than "seen". Seems that by your latest post, my assumption was correct. Now it remains to be seen if the provided code solves your issue but I don't think so. You need an update query, but to be safe it must be run against an ordered list. That you could do one of two ways:

    If you have an autonumber PK field in the table, that can provide the order. If you don't, I think you need to dispense with the autonumber field that contains your client/patient order. It makes no sense to use an autonumber field anyway because the values are so volatile. Better to use PK autonumber field as the sort order for the update, and just a number field for the client/patient order.

    Another would be to sort the records by client order number and update the first record to 1 regardless of what it is, then +1 to every prior value (e.g. 1+1 for second record, 2+1 for the third and so on). I have no idea if either of those methods would result in what you need but it seems logical. If I was #2 but #3 got taken before me, I remain #2 when the list is re-ordered, and client who was #4 becomes #3.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    millsbrandonk is offline Novice
    Windows 10 Access 2007
    Join Date
    Dec 2019
    Location
    Brighton, UK
    Posts
    11
    Another would be to sort the records by client order number and update the first record to 1 regardless of what it is, then +1 to every prior value (e.g. 1+1 for second record, 2+1 for the third and so on). I have no idea if either of those methods would result in what you need but it seems logical. If I was #2 but #3 got taken before me, I remain #2 when the list is re-ordered, and client who was #4 becomes #3.
    Yes this is exactly what I am thinking needs done.

    Is it possible to use something like this:

    Code:
    Dim Rst As DAO.Recordset
    Set Rst = Currentdb.OpenRecordset("tblname")
    For Each  ? in Rst
    Do
    'All my commands here
    Rst.Next
    Until Rst.EOF

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Close. I would not create a recordset based on a table; rather a query that orders the records by clientNum ascending. MoveLast to get the record count, then MoveFirst and in a counter loop
    For i = 1 to rs.RecordCount

    update rs.Field("yourField") = i

    Do you need help to try that or do you like to tackle things on your own? Regardless, always make a back up of your table first, then experiment with different scenarios.
    EDIT - although you cannot update an autonumber field? When I suggested that, I had in mind not using the autonumber type.
    Might be worth trying the sql approach suggested in post 4?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    millsbrandonk is offline Novice
    Windows 10 Access 2007
    Join Date
    Dec 2019
    Location
    Brighton, UK
    Posts
    11
    A little help would be greatly appreciated.

    I am assuming each line would need to be +1, like this?
    Code:
    update rs.Field("yourField") = i + 1 
    My plan is to use vba, to run the code on form load event., and on any change, I would run this code and then a requery(as theirs other data on the form too)

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Use your own table/field names of course. Don't forget that if you are switching back to a table view to see the results of your test, you must refresh the table - same as you would be doing if you requery the form.
    Code:
    Function ReorderClients()
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim i As Integer
    
    On Error GoTo errHandler
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT tblClientOrder.ClientNum FROM tblClientOrder ORDER BY ClientNum")
    
    If Not (rs.BOF And rs.EOF) Then
       rs.MoveLast
       rs.MoveFirst
       For i = 1 To rs.RecordCount
          With rs
             .Edit
             .Fields(0) = i
             .Update
          End With
       rs.MoveNext
       Next
    End If
    
    exitHere:
    On Error Resume Next
    Set rs = Nothing
    Set db = Nothing
    Exit Function
    
    errHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume exitHere
    
    End Function
    Don't forget - I don't know what field reference you will need. I used (0) because the rs of the sample code only has one field.
    Last edited by Micron; 03-15-2021 at 03:10 PM. Reason: added info
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    If the recount applied on all records and assuming the PK grow while records append, the query become more simpler:
    Code:
    SELECT tblWaitingList.*, 
    DCount("*","tblWaitingList","PK<=" & [PK]) AS Running
    FROM tblWaitingList;
    No VBA,
    No UPDATE,
    No extra field in table.
    Just a refresh.

  12. #12
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Why not use a listbox?
    Loop through your recordset and increment the number

    Code:
    i=1
    do until rs.eof
    MyListbox.AddItem PkField & ";" & i &  ";" & TheirName
    i = i + 1
    rs.movenext
    loop
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  13. #13
    millsbrandonk is offline Novice
    Windows 10 Access 2007
    Join Date
    Dec 2019
    Location
    Brighton, UK
    Posts
    11
    This has worked perfectly!! Thanks soo Much!!!!!

  14. #14
    millsbrandonk is offline Novice
    Windows 10 Access 2007
    Join Date
    Dec 2019
    Location
    Brighton, UK
    Posts
    11
    This has worked perfectly!! Thanks soo Much!!!!!

    Code:
    Function ReorderClients()
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim i As Integer
    
    On Error GoTo errHandler
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT tblClientOrder.ClientNum FROM tblClientOrder ORDER BY ClientNum")
    
    If Not (rs.BOF And rs.EOF) Then
       rs.MoveLast
       rs.MoveFirst
       For i = 1 To rs.RecordCount
          With rs
             .Edit
             .Fields(0) = i
             .Update
          End With
       rs.MoveNext
       Next
    End If
    
    exitHere:
    On Error Resume Next
    Set rs = Nothing
    Set db = Nothing
    Exit Function
    
    errHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume exitHere
    
    End Function

  15. #15
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    deleted as your second post answered my question

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 12-02-2020, 07:35 AM
  2. Create a list with no Autonumber
    By charly.csh in forum Access
    Replies: 8
    Last Post: 09-08-2019, 05:15 PM
  3. Autonumber Coming from middle
    By drunkenneo in forum Programming
    Replies: 6
    Last Post: 10-22-2013, 02:51 PM
  4. Display list of people waiting
    By foxtet in forum Forms
    Replies: 1
    Last Post: 06-21-2011, 12:34 PM
  5. Replies: 3
    Last Post: 05-08-2011, 12:55 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