Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Oct 2002
    Location
    Milwaukee, Wisconsin, USA
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Inventory Database (Office 2000)

    After reading some of the other post, what I started with was using the Access database wizard to create my initial database. Well, I was using the auto-number field to be my inventory number for equipment. However I have deleted some records. My question is how can I go about using Access to have my database where it will automatically increase my inventory # every time I have a new entry, and will not allow duplicates? Should it be a text field or a number field? Also I had the ideas of using my company's initials within the inventory number, so that when I get my destructible inventory labels it will have the KSA#000001 for the number, with a corresponding barcode, which will be used for future use?

    Any help or suggestions is appreciated.
    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inventory Database (Office 2000)

    You might want to use an AutoNumber anyway to have a unique number to identify each record.

    If you want to use consecutive inventory numbers, you can do this using code in a form. You have to make sure that your users will only be adding records using this form. They should never open tables or queries directly.

    You can add another field to your table to be used as inventory number; it can be numeric (long integer) or text. Put a unique key on this field, so that duplicates are not allowed.

    Create another table tblInvNum with just one field InventoryNumber of type numeric (long integer). Create one record in it, with the number 0 in the InventoryNumber field.

    In the BeforeInsert event of the form, use code like the following (it uses DAO, so you need to set a reference to the Microsoft DAO ... Object Library in Tools/References...):

    Private Sub Form_BeforeInsert(Cancel As Integer)
    Dim InvNum As Long
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    On Error GoTo Err_Sub

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblInvNum")
    With rst
    InvNum = !InventoryNumber + 1
    .Edit
    !InventoryNumber = InvNum
    .Update
    End With

    ' USE ONE OF THESE:
    ' If InventoryNumber is numeric
    Me.InventoryNumber = InvNum
    ' If it is text
    Me.InventoryNumber = "KSA" & Format(InvNum, "000000")

    Exit_Sub:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    Exit Sub

    Err_Sub:
    MsgBox Err.Description, vbExclamation
    Resume Exit_Sub
    End Sub

    This may seem like a lot of code to accomplish something simple. This way, the chance of conflicts in a multi-user situation are minimal.

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inventory Database (Office 2000)

    Hi Hans

    Great solution

    If the operator adds the inventory, then deletes it, would you put similar code in Form_BeforeDelete to decrement InvNum?

    John

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Inventory Database (Office 2000)

    I've never encountered a need to do this - in general numbers are disposable, and missing gaps aren't a big deal. In some systems, we don't allow deletes in any circumstance, just for auditability. In those cases we put a deleted flag in the record, but we don't reuse identification numbers.
    Wendell

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inventory Database (Office 2000)

    NO! Wendell already commented on this. There is a very good reason *not* to decrement InvNum when an item is deleted:

    Suppose the current value of InvNum is 999. Operator A creates a new inventory item; InvNum is incremented to 1000 and the new item is assigned number 1000. Next, Operator B creates a new item before A has deleted item# 1000; InvNum is incremented to 1001 and Bs item is assigned number 1001. Now Operator A decides that a mistake was made, and deletes item# 1000. If InvNum were to be decremented to 999, the next inventory item to be created would be assigned number 1000 (no conflict there), and the next one after that number 1001 - conflict, since number 1001 has already been given out to B's item! If InvNum were to be decremented to 1000, the conflict would occur immediately when a new item is created...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •