+ Reply to Thread
Results 1 to 3 of 3

Stock shipment database

  1. #1
    Registered User
    Join Date
    07-20-2015
    Location
    UK
    MS-Off Ver
    2010
    Posts
    17

    Stock shipment database

    Hello,

    I have got a problem and I have been trying all my basic skills to solve, but I have failed…

    I am planning to make a basic shipment database where a product with an unique inventory ID will be transferee between different storages.


    What I have done to date:
    Created a table Hardware, where each tool is described with specific properties.

    The table Hardware is related to the table Inventory were I am planning to keep track of my inventory.

    An inventory entry will be related to a project. So I can have tool 5a for project 1 and 5a for project 2.
    Tools (quantities) will be allocated to certain storages. [Capture1]



    At some point I will have to transfer tools between storages. I have created the manifest table. In the example below tools have to reach storage 3, but on the way to storage 3 they will have to “stop” in storage 2 (manifest ID 2). Then I will transfer tools from storage 2 to reach the final destination storage 3. [Capture2]



    I have then created the table manifested items, where I list in detail what tools will be moved between storages in each manifest. I am using a new table “qty_moved” to create number. [Capture3]



    My problem is how can I update the table “inventory” after each manifest is “released”. I have made an example table in excel where stock is changing after each manifest is released. 1st table is the initial state.
    2nd table is when stock has been moved from stock 1 to stock 2 (manifest ID 3).
    3rd table is when stock has been moved from stock 2 to 3 (manifest ID 4). [Capture4]

    Now is my question. How could I in access transfer change/stock. A macro? If yes what kind of a macro?
    An expression, if yes, what expression?
    Or maybe my table setup is incorrect. Please help


    Flooyd
    Attached Images Attached Images

  2. #2
    Forum Contributor
    Join Date
    04-21-2010
    Location
    Canada
    MS-Off Ver
    Office 2013
    Posts
    120

    Re: Stock shipment database

    I cannot see your attachments, so I will take a stab at this as best I can. First off, are you using forms to view/manipulate the data? Again, this is just a stab at this (hoping to stimulate some thought) but what I would do (similar to some of the stuff I do right now) is possibly use List boxes to denote the various storage areas on a given form. I will leave out the back end logistics of moving the tools as I don't know how that works (time, effort), but your list box could also show a transit flag/date/etc. so that you would know what is in transit, and what is ready to move.

    The main table with the tools should have a field that denotes where that tool is, which can be used to populate each list box (query by location). I personally use VBA, but I believe that there are click macros for the list boxes. The click (or double click) can bring up a message box requesting the direction of movement (up a storage box as in container 1 to container 2, or down a storage box as in container 3 to container 2). The for that record, change the field that denotes the container. To make life easier (especially if the storages are sequentially ordered) this field should just contain the storage number designation.

    As to seeing this live, the end of the macro can requery each of the list boxes, which should update the view. Lastly, as logistics go, if the transit flag is on, then you should not be allowed to move the tool. As to how the transit flag is removed, there are many ways to do this. If it is to be done on this same form, you may want to change the click to move tools to a double click (separate event). the single click to highlight the tool can then activate a button that would 'free up' the tool to be moved to the next box. It could be automated by time, but again, I don't know the logistics of how the tools move, so I cannot help you there.

    Hope this helps, or at least stimulates ideas.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Stock shipment database

    Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
    Also, not all members can upload picture files (Company firewalls and stuff) - and, depending on what browser is being used, some pics dont even show up on the forum

    Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Trying to create a hire stock database
    By sammyjamting in forum Access Tables & Databases
    Replies: 1
    Last Post: 03-11-2016, 11:13 AM
  2. Macro to add and remove stock from database
    By JohnDoh in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-15-2015, 07:27 PM
  3. [SOLVED] Macro to prepare Database from Shipment Details Sheet
    By prkhan56 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 09-12-2014, 03:24 PM
  4. Help with a Stock Control Database
    By markransom in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 11-26-2012, 05:24 PM
  5. Stock Control Database
    By Ramzes in forum Access Tables & Databases
    Replies: 1
    Last Post: 10-06-2011, 08:26 AM
  6. Warehouse database - to track stock and pallet loads
    By warby23 in forum Access Tables & Databases
    Replies: 3
    Last Post: 04-04-2009, 11:38 PM
  7. how do i creat a database to track stock
    By okpoch in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-22-2006, 09:50 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1