Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2009
    Posts
    2

    Unanswered: Next available location.

    Hi all, I was wondering if somebody could confirm something for me. I am creating a database for an archive room. I also need to keep an inventory log of where each record physically goes in the archive room.
    I was wondering can Access tell the user the next available location so they they know where to put the record? I would like this to happen when they are filling out the form so they know where to put the record.
    Any thoughts would be greatly appreciated.

    Regards
    Will

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you have a list of predetermined locations, then there is nothing stopping you allocating that location to the next record.

    If you think about it, effectively thats what an Autonumber column is. it allocates the next available number to the next new record

    So you need a table containing these storage locations
    then do a SQL serach to find the first available storage location that has nothing in it.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2009
    Posts
    2
    Thanks healdem,
    Thats interesting. I'll try that. I haven't really ventured down that road yet of access programming yet so what level of competency is required fo that you reckon?
    I will have a pre-determined list of locations so hopefully something like that would work nicely..

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    assuming its a multi user system you would need to make sure no one could try to grab the next available location at the same time someone else was.

    to do that you probably need a function that secures a lock (effectivley locks out someone else trying to " grab" the same number).

    If its your first dabble in the stygian depths of VBA then it may be a struggle.. not a problem we've all got to start somewhere, and there are lots of people here who can help.

    The first thing in my mnd is to decide if a pshysical location in the archive room can be used for more than one entry, or is it strictly one location matches blah, or can you have many blahs's in one location.

    ferisntance
    do you have say a pallet/bucket system where a pallet goes in one space, or would you allow the same cusotmer (or even multiple customers) to put different stuff in the same physical location. the rreason for asking
    well if its one object per location, then I'd suggest a table with say a location, and an object ID

    if mulitple things go int he same location you still need your locations table, but you need antoher table whcih associates a locaztion with an object. so you can then have multiple objects assocaoted with a single location.

    problem 2 is if you take in so much crud from the customer that it spanes multiple locations.... again shouldn't be a problem, the association table above handles that by allowing an object to sit in multiple homes.

    when it comes to finding the next available slot (either you wait for the warehosue to tell you where it went, or you grab the location fromt he table.

    so you are looking for the first location that meets whatever criteria. thaose criteria coudl be based on geographicla location or it could be that you have mulitple sized locations ( ie this object is say a Euro pallet so you are lookign for the first available europallet sized free slot.
    it coudl be this object must be stored at floor level so you look for th efirst available europallet sized space on shelf 1.


    to find that space you need
    1) to secure a lock to stop anyone doing the same thing you are doing
    2) find the slot
    3) update the data
    4) release the lock.

    it all depends on how complex your requirement are
    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
  •