Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2008
    Posts
    10

    Unanswered: AutoNumber with a Twist

    Hi All,

    I'm creating an Access DB that will store problems raised for devices.
    The issue number must be auto generated when a new record is added.
    But; I need the issue number to start at 1 for each device.

    eg:
    Issue# - Device - Issue_Description
    ---------------------------------------
    1 ------ A ------ Device A problem 1
    1 ------ B ------ Device B problem 1
    1 ------ C ------ Device C problem 1
    2 ------ A ------ Device A problem 2
    2 ------ B ------ Device B problem 2
    2 ------ C ------ Device C problem 2
    3 ------ A ------ Device A problem 3
    3 ------ B ------ Device B problem 3
    3 ------ C ------ Device C problem 3

    Any ideas how to achieve this?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you can't use autonumber for this, you will have to write your own code to generate the number.

    there are plenty of exmaples already in the Access thread
    however as a scheatic what you need to do is

    (if working in a multi user environment where more than one user may be updating the SAME device you need to secure a lock before startign, and release the lock afterwards (as soon as you have written the NEXT value).

    find the current maximum value (use a dlookup, or execute SQL to find the MAX(mycolumnname) for the given device.
    if there are no issue nubers for that device then use 0 as the value
    add one to that value
    write the row
    release the lock if secured by lock
    ...what you probably ought to do is to write that as a function

    ..it will be something like (it may eve be what follows)
    function GetNextIssueNumber(DeviceID as integer) as integer
    GetNextIssueNumber = DLookUp("[Issue#]", "MyTable", "Device ='A'")
    GetNextIssueNumber=GetNextIssueNumber+1
    end sub



    an alternative strategy is to store the issue number in the device header record, pull the value from that, and increment it.. thats not nice, it violates good design but if it floats your boat do it.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2008
    Posts
    10
    Hi Healdem,

    Thanks kindly for the advice.

    The DB connection will be from an ASP ADO web site using DSN connection.
    So i wont need to worry about a secure lock.

    My DB knowledge is fairly basic so please excuse any questions that might be primitive

    Where would i insert the VB code to do this number generation?

    If you can point me to any resource that might help that would be most appreciated.

    Cheers
    Rob

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by rjaric
    Hi Healdem,

    Thanks kindly for the advice.

    The DB connection will be from an ASP ADO web site using DSN connection.
    So i wont need to worry about a secure lock.
    sorry to say.. that's meaningless, it doesnt' matter what mechanism you are using to access the data. what matters is is there a risk that you will have more than one user trying to get the next number for the same record at the same time. if there is a risk you need record locks, if there isn't a risk you don't. defensive development woudl suggest you put locks in, passive developing would suggest you at least put a comment in your code saying why you decided not to implement records. I always find something along the lines of
    Code:
    ' meeting with Fred Smith 22/12/08 @10:30 Fred informed me that we wouldn't need to implement locks because.....
    I'd suggest you implement the code as a function, if its only needed on one place as an in-line function, or possibly in line code. Not having done any ASP I wouldn't know where you would put common functions

    Code:
    function GetNextID(MyValue as integer) as integer
    'this function gets the next available ID for a specified MyValue
    'effectively it generates a sequential ID for a sub table.
    'think of it similar to and order (MyValue) and orderitem (GetNextID)
    GetNextID= dlookup( "ID", "MytableName", "FK=" & myvalue)
    GetNextID = GetNextID+1
    exit function
    you (probably) can extend the function to make it generic

    Quote Originally Posted by rjaric
    My DB knowledge is fairly basic so please excuse any questions that might be primitive

    Where would i insert the VB code to do this number generation?

    If you can point me to any resource that might help that would be most appreciated....
    as this is an ASP question, rather than an Access question, would you prefer it to be moved there.. you have a better chance of getting an accurate answer on ASP problems in the ASP section

    failing that I'd suggest you do a google for other ASP sites
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2008
    Posts
    10
    I would imagine that having the number generated by Access rather than by ASP I could avoid any duplicates if I were to skimp on locks.

    So I wanted the issue number generated by Access (like Autonumber does).
    Also that will minimise code in the ASP pages

    Is this possible?

  6. #6
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    I have used a technique to ensure that order numbers, etc, were maintained in a sequence without any gaps (as might happen when an autonumbered record is deleted. A dedicated table (adapted for the problem at hand) is created with the following fields:

    DeviceID
    IssueNumber
    RecordStatus
    UserID
    AccessDate

    Initialisation records consist of, for each device, an IssueNumber of zero and a RecordStatus of Datum. When a user needs an issue number, they query first for the minimum value of IssueNumber for the Device, where the RecordStatus is FREE. They take this value and use it, changing the value to LOCKED. If there are no FREE records, the maximum value of IssueNumber is incremented and used, again leaving the value is locked. If it is desired to cancel the entry of the issue, the RecordStatus is set to FREE, leaving the IssueNumber available for the next problem for that device. The remaining fields are simply to establish the user's authority over the record (also good for audit purposes).

    The advantages of this method are (1) are full sequence of numbers is maintained and (2) locking of the main data record is avoided.

    I think I have submitted a version of this methodology as a solution in another post. If so, it demonstrates its adaptability.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I would imagine that having the number generated by Access rather than by ASP I could avoid any duplicates if I were to skimp on locks.

    So I wanted the issue number generated by Access (like Autonumber does).
    Also that will minimise code in the ASP pages

    Is this possible?
    its not possible to do automatically, because you want a separate sequence for each device/incident, short of having a separate table per device.. don't even think of going down that route. you also want a sequential number, autonumber doesn't guarantee that when used appropriately.. You cannot rely on autonumber to use sequential numbers with no gaps, you should not try to coerce autonumber to replaqce say a sequential order number, recpipt book or whatever.. its not what Autonumber was designed for. it is designed to guarantee to make a record unique when there is no other obvious mechanism to make a record unique.. it should have no values outside the system, in an ideal world the value should never be exposed to 'mere' humans it should be kept hidden away and used only by the system.

    you don't need any initialisation record.. if dlookup doesn't find a value you should start with a new number
    the dlookup may not be the "right" solution.. you may need to execute a recordset/sql query

    effectively what you want is the MAX(ID) for a given deviceid, in SQL terms
    Code:
    select max(id)  from Mytable where device ID = blah
    if you are in a multi user environment and you think there is the risk of two users requesting the same sequence ID then you must have locking strategy.. there is a risk, however improbable that two or more users may attempt to get a sequence number at the same time... if you don't have record locks then you MUST have an error trapping routine to cater for the, however theoretical, situation.

    if you know that there can never be more than one person trying to grab a sequence number then you can get away with locks, the advantage of having a sequence number in a different table is that you can issue a record lock which only affects the specific master record and reduces the number of lockouts other users experience. if you dow know that to be true document that so if der tag comes and that assumption fails then I'd prefer to be able to point fingers to others when it comes to the necklace party investigating the cause of failure. but in reality for the cost of the record locks I'd run a locking strategy and make the code more bombproof..... personally I don't take kindly to being called out of may bed at odd hours of the night becuase code I designed or spec'd fell over.. not just because the app shouldnt' fall over..... but I also value MY time in bed, or elsewhere,

    from what I understand of the original system Im guessing its unlikely that more than one person will be raising an incident at the same time on the same device, so I suspect locks aren't needed

    the really really ugly way of doing it is to grab a sequence number, attempt to write the value, trap for an error, if the error is "duplicate key" then add one to the sequence number and attempt to re-write, and continue untill the write succeeds.. nasty nasty design but it works
    Last edited by healdem; 12-23-08 at 08:10.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    healdem's "really ugly way" (more or less) is actually my preferred route for all similar things.
    far from ugly - i think it is very pretty.
    i posted a sequential number example in the code bank long ago, but here is the outline of the scam:

    'grab lastused from a one-record table (ID=1)
    varLastused = DLOOKUP("lastused", "sometable", "ID=1")

    'assume only you exist (is true 99.9% of the time)
    strSQL = "UPDATE sometable SET lastused = " & varLastused + 1 & " WHERE lastused = " & varLastUsed & " And ID=1"
    currentdb.execute strSQL

    that UPDATE either works or fails.
    .recordsaffected will tell you (in DAO at least, but i guess something similar exists in other flavours of A)

    if it worked, your sequential number is varLastUsed+1 and you ***KNOW FOR 100% CERTAIN*** that nobody else has taken or will take this number
    if it fails, someone else stole "your" candidate number before your UPDATE executed: repeat DLOOKUP/UPDATE until you get your number.

    heypresto - no locks (or do you want to call it an opportunistic concurrency lock?)
    all the advantages of locks, but nothing to drag healdem from his bed

    izy
    currently using SS 2008R2

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by izyrider
    ......
    heypresto - no locks (or do you want to call it an opportunistic concurrency lock?)
    opportunistic smash and grab?

    Quote Originally Posted by izyrider
    ......
    all the advantages of locks, but nothing to drag healdem from his bed
    izy
    oddly enough I'm all for that......
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740


    i'll readily accept opportunistic grab and confirm but it is not the standard nomenclature.

    izy
    currently using SS 2008R2

Posting Permissions

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