locked
Add Field Data to a Table and also Change the QTY of an Item in a table from another table RRS feed

  • Question

  • Hi All, I am in need of some help with my Access Database, I havent done much in Access so I am a bit stuck.. I have two Froms that contain a Subform also, Each form hold data on items that have been ordered and orders.

    The First form is used to order new Items, it contains a list of items (stored in an item table) and then it includes the order details (in a n orders table). I also have a stock table, I want to add a button onto the form that will submit the form and take the delivary date from the order detals table, and then add it to the divlivary date field in the stock table for all of the items that are in the items table that relate to that order. Is there a way to do this? I thought it may be with the append query?

    I also have another form that is used to book in the orders once they arrive. It uses that same tables as above, but this time when the user clicks on the button, there is a field in the items table called recieved, this is yes/no, If yes then I want to add 1 to the qty of the relevent item in the stock table. Im guessing with this you will have to use VBA? I have had some use with VBA in Excel but none with Access. so would need some guidance.

    Are these both possible to do?

    Thanks

    Tom

    Friday, February 17, 2012 1:25 PM

Answers

  • Tom

    Once you download the template and use it to make a new database the database should become "open-source" in that you can make whatever changes you want. Check to see if the file has been set to Read-only in Windows Explorer.

    As I said, your design is not correct. The stcok table must rely on the order table to show what is in stock and what is on order. Inventroy DBs are a bit complex and you must fully understand the relatioships between the tables. Ordered Items are actually in limbo since they are in stock until sent out.


    Bill Mosca
    http://www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    • Marked as answer by Bruce Song Tuesday, March 6, 2012 8:31 AM
    Monday, February 20, 2012 12:06 AM

All replies

  • It sounds like your database design is not correct. I suggest you download one of the database templates from MS Office Templates so you can either use one for your DB or get ideas of how to design your own.

    Bill Mosca
    http://www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Friday, February 17, 2012 6:20 PM
  • It sounds like your database design is not correct. I suggest you download one of the database templates from MS Office Templates so you can either use one for your DB or get ideas of how to design your own.

    Hi Thanks for the link, the closest one to mine is the Product inventory database but annoyingly you cant seet any of it in Design View, so I cant really use it to have a look at how it is built so i can design my own, also it isnt quite what I am after as it doesnt show when you open the stock table the order date if any items have been ordered.

    Thanks

    Tom

    Sunday, February 19, 2012 8:58 PM
  • Tom

    Once you download the template and use it to make a new database the database should become "open-source" in that you can make whatever changes you want. Check to see if the file has been set to Read-only in Windows Explorer.

    As I said, your design is not correct. The stcok table must rely on the order table to show what is in stock and what is on order. Inventroy DBs are a bit complex and you must fully understand the relatioships between the tables. Ordered Items are actually in limbo since they are in stock until sent out.


    Bill Mosca
    http://www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    • Marked as answer by Bruce Song Tuesday, March 6, 2012 8:31 AM
    Monday, February 20, 2012 12:06 AM