Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2011

    Unanswered: Find highest value , assign to input field

    I have a database that stores our equipment. Each piece of equipment is catagorized, ex: Vehicle, Trailer, Lawn Equipment, Power Tools, etc.... Each item in these categories has a unique ID number we use for inventory purposes. Each category starts with xx1. Example, Trucks are numbered 201, 202, 203,204, and so on. Power tools are numbered 601,602,603 and so on.

    I also have a form for entering new inventory items. The first field you fill out is a drop down box with the item Category's, the next is the inventory number. Well, as it is now, I have to know what the last inventory number is under that item category so that i can manually enter it. Is it possible for the form to autopopulate the inventory id by looking up the highest value currently used and then adding 1.



    SELECT ITEM CATAGORY -> Select Trucks from drop down menu
    ENTER INVENTORY ID -> look up highest INV ID used in the Trucks Category then add 1

    The rest of the form works fine.

    thanks in advance for any help.

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    shortsighted design you've got there matey
    that limits you to n hundred depending on the spacing between things
    in an ideal world youd have an equipment type table which identifies each type of equipment if you wanted to be smarter you could even define subcategories eg
    equipment types
    |    |----Cars
    |    |----Vans
    |    |----Rigid
    |    |----Articulated
    |    |----Power
    |    |    |----Electric
    |    |    |    |----Battery / rechrageable
    |    |    |    |----Mains
    |    |    |----Hydraulic
    and so on
    you store the key from that table in the tools table and define it as a foreign key referencing the equipmenttypes table
    that way round you never have to think up new number ranges for new tool types
    you never run out of room in number ranges

    however If I can;'t convince you then you need to write a function which returns the next avaialble number in a range

    so I'd suggest you look at the dmax function
    ot e the following is air code, untested, untried
    function GetNextID(LowerLimit as integer, UpperLimit as integer)
    GetNextID = dmax("mycolumn", "mytable", "mycolumn between (" & lowerlimit, UpperLimit & ")"
    if isnull GetNextID then 'we didn't find that item so use the lower limit
      GetNextID = LowerLimit
      GetNextID = GetNextID + 1 'otherwise add one to the existing highes value
    'not you probably should add some error checking to stop a new value exceeding the threshold however as its not my design I don't care :)
    end function
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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