Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    BigJohn89 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    37

    Automatically assign number to every new record

    Hi everyone,

    I have some basic experience with Access but what I need to do is a bit too much for me



    I need to create form where users can add new records (in this case order numbers) and to every new record Access have to automatically assign number for e.g. from 1 to 60. And user is able to see which number is assign to his order.
    The problem is that when user delete specific record (because order is completed), I don't want it to create new numbers 61,62,..., It has to take a spot of first "not assigned" number in a row.

    I don't know where to start with it. Maybe someone could help me.


    Thank you and Merry Christmas
    J

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Why would you delete records? Deleting records should be a rare event.

    Why does it matter if there are gaps in the number sequence? Autonumber field is not supposed to have meaning to users, they shouldn't even be aware it exists. I have read that autonumber can't even be relied on to always be positive, although I've never seen it generate a negative.

    Managing data to maintain the autonumber sequence without gaps will require some tricky code. First, don't allow users to really delete a record. This means will have to prevent deletion by ribbon button and keyboard button. Run code that deletes data from the record. Then when user needs to input a new record, search table for an existing 'blank' record and open form to that record, if there is not a 'blank' record then open to a truly new record row.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    BigJohn89 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    37
    Hi,

    Maybe I should try to be more clearly so I have 60 empty spots in storage rack. The first employee will have to scan every new order (every order is a new record in this scenario) and Access have to assign number from 1 to 60.
    Now second employee can scan duplicate of this order (in another work spot) and he know which shelf contains goods from his order. When he's done his part he can now delete record (delete order from the list) and make a spot for new one.

    I think numbers can be assigned randomly to each of new records but user have to be able to see them.

  4. #4
    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
    16,716
    J,

    I think you should spend time analyzing and describing clearly what the "business" is. You mention Orders and Goods and Storage rack, but you have focused on assigning numbers 1 through 60.
    We often see people new to database confusing What they are trying to achieve with How it might be done. Spend the time to describe your business --start at 30,000 ft overview and gradually add detail. Keep it in simple English terms --no jargon.
    Access won't magically create a database --it only does what you tell it, so clarity is critical.

    Here are some links to database planning and concepts that may be helpful to you.

    Good luck with your project.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Already described technique could use.

    Code searches table for a 'blank' record and that record is used for the new data.

    Have "Delete" button on form with code that sets field(s) to Null. Will also need code that disables the ribbon delete and keyboard delete. I disable the ribbon completely. Example of code to capture and cancel delete event:

    Code:
    Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
    'suppress default Delete Confirm dialog box.
    Response = acDataErrContinue
    'cancel the automatic delete operation
    Cancel = True
    MsgBox "Must click Remove Test button to delete test from sample." & vbCrLf & _
            "If sample is in closed accounting period, Delete/Remove test not permitted.", , "Delete"
    End Sub
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    BigJohn89 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    37
    Thank you @June7, I will try to use this

    @orange - yes maybe it's a little bit confusing - sorry for that.
    I will try again


    Business overview:
    1. I have two work spots where two employees are preparing goods based on the prepared orders (It can be a hundred orders per day).
    2. First employee has always original order and second employee has duplicate of this order.
    3. Between them I have a storage rack with 60 empty slots (our rack is used to folding goods prepared by two employees.
    4. At the end of production process - both of employees have to move prepared goods to one spot.

    Basic assumptions:
    1. First employee need to scan his order in Access database.
    2. Access need to automatically assign number for this order (from 1 to 60). Then he can move goods to proper shelf.
    3. Second employee can check which number is assigned to his order. Then he can also move goods to proper shelf.
    4. At the end he can delete this order from the list or mark this order as finished (so Access can assign next order for this empty shelf).


    Example:
    1. First employee scan order XYZ.
    2. Access assign number 20 for this order.
    3. First employee leave his goods in shelf No. 20.
    4. Second employee scan duplicate of order XYZ and he can see which shelf is assigned to this order.
    5. Second employee also leave his goods in shelf No. 20.
    6. Now order is finished and ready to be packed.
    7. The person who is responsible for packing can delete this order from the list.
    8. First employee scan next order ABC - Access assign randomly next available number.


    I can handle with linking databases and with a search box. I could try to use code from @June7 but how to create form which can do what I need?

    J
    Last edited by BigJohn89; 12-25-2017 at 07:05 AM.

  7. #7
    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
    16,716
    Thanks for the clarification, WHY are there duplicates? Can you modify the process such that duplicates don't arise?
    Good luck.

  8. #8
    BigJohn89 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    37
    Unfortunately, the production process looks like that. It can't be done differently

  9. #9
    Join Date
    Apr 2017
    Posts
    1,673
    About your business model. Your 2 workers have filled all 60 slots. What now? Unless your model is somewhat unfinished, all work is done forever

    To be serious again, when some good are removed from some slots, those aren't demolished - they are there anyway, only empty! From here another question about your business model - is somehow determined, how many goods you put into slot? What happens, when an employee gets more goods than he can place into slot? What happens, when an employee gets some goods, which were arriving earlier too, and there is a place for some more in same slot?. Or is there the rule - one party, one (two) slots?

    Anyway, those 60 slots will be there all time - some empty, and some filled. So I think you best solution is to have a slots table and a form where all free slots are displayed. And in subform, you can attach goods to free slots - after what this slot will not displayed anymore until it is emptied, and the goods put into this slot are removed from goods selection in subform. And all this will be moot, unless you add a part, where you can remove goods from slots too - otherwise you never know, which slots are free.

    An update: I read rest of entries, and most of questions were clarified later. But my solution will work anyway.

  10. #10
    BigJohn89 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    37
    Ok, I have example database with 10 slots table and form where records are displayed. I also created second input form.
    Now - how can I pass data from txtInput (frmInputData) to free slot? By using If function? If slot_1 is empty then pass data - if not then check slot_2 - if not then check slot_3 etc
    Can you help?
    Attached Files Attached Files

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Suggestions offered require a record for each slot. Not a field for each slot.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    BigJohn89 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    37
    Let's suppose Input text is in the same form. I tried to build click button event:

    Private Sub Polecenie24_Click()
    If IsNull(Me.slot_1.Value) Then
    Me.slot_1 = Me.Tekst22
    Else
    Me.slot_2 = Me.Tekst22
    End If
    End Sub

    Value is added to slot_1 if is empty and to slot_2 when slot_1 it's not available.
    How can I add more If to next slots?


    EDIT
    Or maybe to use the Loop is a better solution? At the beginning code needs to go throught all textboxes and check if there is empty one. The code have to stop on the first empty textbox and insert value from input box.
    Last edited by BigJohn89; 12-27-2017 at 01:38 PM. Reason: Idea

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    If you really want to stick with this table structure, code could be like:

    Code:
    Private Sub Polecenie24_Click()
       Dim x As Integer
       For x = 1 To 60
          If IsNull(Me("slot_" & x)) Then
            Me("slot_" & x) = Me.Tekst22
            Exit For 
          End If
       Next
    End Sub
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    BigJohn89 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    37
    It's working!

    Thank you very much June7

  15. #15
    BigJohn89 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    37
    Hi, I have another issue
    I need counter with a quantity of empty slots.

    I have something like that and it almost works:

    Private Sub Form_AfterUpdate()
    Dim x As Integer
    Dim intC As Integer
    intC = 0
    For x = 1 To 60
    If IsNull(Me("slot_" & x)) Or (Me("slot_" & x)) = "" Then
    intC = intC + 1
    txtCount = intC
    End If
    Next
    End Sub

    But, when I assign number to the last empty slot - counter is still showing "1" slot is left.
    What I'm doing wrong?

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

Similar Threads

  1. Replies: 5
    Last Post: 06-02-2015, 02:51 PM
  2. Replies: 5
    Last Post: 03-21-2015, 01:42 PM
  3. Replies: 7
    Last Post: 04-17-2012, 11:53 AM
  4. Formula to assign a number to a field value?
    By dashingirish in forum Queries
    Replies: 2
    Last Post: 02-23-2012, 01:21 PM
  5. automatically assign numbers
    By gvh in forum Access
    Replies: 3
    Last Post: 12-08-2011, 01:53 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