Adding functionality to an ordinary Inventory Database

[MRPeters22]

OS: Windows 7 Home 64 bit
Version: Access 2010

Current design:
(note: * indicates a primary key)

tblInventory - *InventoryID (Auto), ItemID (text), CategoryID (text), SupplierID (num), CategoryID (num), InStock (num)
tblSupplier - *SupplierID (Auto), SupplierName (text), PhoneNumber (text), Website (Hyperlink)
tblCategories - *CategoryID (Auto), CategoryName (text)

I have a simple inventory database for medical supplies. My lab is behind technologically in the sense that everything is still done by hand and there's way too much paperwork involved. There's redundancy in almost everything we do. Every time someone wants to order something, they bring the box with them and write down the item name, catalog number, supplier name and quantity to order. The order log is as thick as an encyclopedia and there's no way to track who requested to ordered each item.

Most of my coworkers are afraid of computer usage and up until now I've been using this database by myself.

This quick reference database has up-to-date stock quantities for all items we carry and this helps me keep tabs on our inventory in a more efficient manner. I print out a report list every Friday and carry it around with me as I determine what needs to be ordered. Rinse and repeat weekly.

I've got continuous forms based off of queries. The forms have a couple of buttons (using OnClick with VBA event procedures) to view/sort/manipulate data then to print reports based on this.

I also have a frmSearch where the user can search and further change the view output of the continuous form based on their search txtstring.

For example someone can search for "scalpels" and return only the scalpels in the database in a continuous form layout, then click "Generate Report" to view the report of only scalpels and then print.


NowI want to add some more functionality and more complexity (with VBA?). I would like to expand this to also make the ordering process more efficient.

Goals:

• User login
• tblOrders, qryOrders, frmOrders
• Each user can browse the list of items and click a button to order an item (one item at a time, for now)
• Once the button is clicked (and the user confirms their choice) it will append to a table that summarizes all orders for the day
• I should be able to know who ordered what and when they ordered it.

Future development ideas...

I would love to have orders for the day/week automatically emailed to the supervisor by 5pm. All orders placed after a time cutoff would append on the following day's order. Its purpose is to let the supervisor know ahead of time what to expect as far as invoices.


Any suggestions for some VBA commands for the buttons on the ordering form? I'm imagining a small popup form after OnClick to confirm item and quantity.




Thanks,

-Mark

 

[DCrake]

By the sounds of it everything you have mentioned is doable.
You will need a login form to authenticate the user. Samples are available here.
You will need some sort of authentiation as to who can order and who can't.
In your stock table you need to have max/min/eoq for ordering.
Look as some sample stock systems to get a general gist of how things work.
The email aspect is also doable, again examples exist on this forum.

For each order placed you need to capture the user name, computer name, date & time, etc for traceability.

 

[jdraw]

There are a number of data models related to Inventory, Orders, etc at this site.
http://www.databaseanswers.org/data_models/index.htm
Some of these may give some ideas of how the additional "things" fit together.

There are also sites that give instructions on various aspects of database and Access and vba.

Here is a list of references that is often suggested.
http://www.utteraccess.com/forum/Suggested-Readings-and-t373096.html

http://r937.com/relational.html

http://www.accessmvp.com/strive4peace/

Hope you find these useful.