Looking for help?

Tutorial Video

Sample Download

Walk-through page 2 - Creating a Microsoft Access Stock Control/Inventory Management Database

Forms in Microsoft Access
In Microsoft Access, you use forms to view, enter and edit data, and to control the database. When you have set up all the tables and relationships in your database, the Form Wizard is very helpful in setting up forms based on your tables, for data entry, viewing and editing.  You can then make changes to the form produced by the Form Wizard, adding and editing features as required. 

Forms in Access can also be used to display buttons and links to provide access to all the other forms and reports. We always set up a form of this type and call it the Front screen. Setting up a clear top level form like this makes it easy for people to use the system with no database knowledge.

Reports in Microsoft Access
Microsoft Access reports allow you to display information to the user in a convenient way which can be viewed on screen and then printed if required. Normally the information for the report will come from a query. The Report Wizard will help create simple reports.  More detailed reports are beyond the scope of this article.

Security
You might want to make sure that no one who isn’t trusted can tamper with your data. The simplest way of protecting the database is to set a password. 

1. To set or change the password, the database must be opened for exclusive use.  To do this, open up Access then use File, Open and Browse... to select the database.
2. Click the drop-down arrow to the right of the Open button, and select Open Exclusive. This opens the database in a way that allows you to set a password.
3. Go to the File menu, select the Info submenu and click on Set Database Password.
4. Enter the password you require, re-enter to verify and click OK.  The password is now set.

When creating a large stock control database, or one that holds sensitive information, you may require more complex security.  For example, you might want to restrict access to some of the information in the database, or you might want to let some users view the information in the database, but not to change it.  Access allows you to define types of user, and apply levels of security, so that you can specify what actions are available to each type of user.  This is beyond the scope of this article, but it is called 'user-level security' if you want to research it more.

Automation
Access allows you to write custom code in its Visual Basic for Applications language, known as VBA. You can set up a piece of code to run when you perform certain actions or just on command.  This enables you to automate many processes.  For example, you might want your system to adjust your recorded stock levels automatically whenever a delivery is received, or you might want the Customer Reference field to be built automatically from the surname of the customer.  Using VBA to automate features can make a database more powerful and easier to use, but it does require programming knowledge so it’s a more advanced step to take in your database’s development.

So that's all for this tutorial article. If you would like to continue learning about creating stock management databases or inventory control systems in Microsoft Access, please click here for our further article on many-many relationships. These allow you to have multiple parts on a single order, for example. Thanks for reading!

If you decide that building your own stock control or inventory management database is not for you after all, we at Software-Matters are happy to offer advice about alternatives via our free initial consultation. Contact us on 01747 822616 or fill in an enquiry form here and we will get back to you.

If you enjoyed this article or found it useful, why not tell others about it?

Software-Matters is a UK software development team specialising in Microsoft Office and based in Gillingham, Dorset, and close to the county borders of Somerset, Wiltshire and Hampshire. We are near to Bournemouth, Poole, Southampton, Bristol, Bath and Salisbury, although have clients throughout the UK and overseas.