Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2002
    Location
    Maaseik, Belgium
    Posts
    12

    Unanswered: showing next possible number

    hello all:

    I have a small problem. I have created a database that contains all the contracts of the company. Now each contract has a serial number. with this serial number you can find the fysical contract back in the archive.
    But i have one problem. When someone enters a new contract i want them to see the next possible number to give to this contract. This at its own is not so difficult, as where there some rules to the serial....
    the serial number is build up like this:
    A1-001
    A1-002
    ...
    A2-001

    the A is the first letter of the company wich the contract is made for
    the number after this indicates the cabinet it is in, the 3 following numbers define the order in this cabinet.
    This is my challenge: I want the user to type in only the A, acces then has to show the rest of the serial number (in an other field..??) so that the user can complete this. (I can't let acces define and save it, because i can't predifine how many contracts (last 3 nrs) go in a cabinet)
    so if the following files exist:
    A1-001, A1-002, ..., A1-010, A2-001, A2-002

    It has to give A2-003 as the next serial...

    chalenging he... (atleast for me it is)


    thx in advance...

    Davy

  2. #2
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820

    the simple answer is

    access can't do it unless you tell it the cabinate datails
    ie another table that contains a list of all cabinates and the amount of space in them ie
    CABINATE(Identifier, AmountOfSpace, UsedSpace)
    then when adding a contract it looks down the table and takes the first cabinate that UsedSpace doesn't equal AmountOfSpace then increments the UsedSpace values and adds it as the id of the contract

    as for the letter the only reason for the user to type it in is if the company name isn't anywhere in the database
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  3. #3
    Join Date
    Nov 2002
    Location
    Maaseik, Belgium
    Posts
    12
    thx for the reply,

    thats just the problem, you can,t predict the size of teh cabinet, or the amout that goes in it... Whe can set it to a maximum, but what to do if it is full before it reaches this maximum. isnt ther a wa where i let acces look at the 2 different parts of the number?? : example:
    A1-001
    A1-002
    ...
    A2-001

    first let it look wich is the highest in A: in this case it is A2... then get with Dmax the highest record wich starts with A2 and add 1??? could this be a sollution???

    Davy

  4. #4
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    if thats all you want the simplest method is
    Code:
    Sub GenNum()
        Dim sn As String
        DoCmd.GoToRecord , , aclast
        DoCmd.GoToRecord , , acPrevious
        sn = SerialNumber.Value
        DoCmd.GoToRecord , , acNext
        SerialNumber.Value = Left(sn, 3) & (Val(Right(sn, 3)) + 1)
        docmd.close 
    End Sub
    this is to be run on a sorted query that screens by the letter you give and the onclose method appends the serial number to the orginal form
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  5. #5
    Join Date
    Nov 2002
    Location
    Maaseik, Belgium
    Posts
    12

    Thumbs up

    ththing works fine now, they still have to give the number in manually, but access gives a preferred number now.

    thx..

Posting Permissions

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