Walk Through - Creating an MS Access Stock Control Database

< Previous Page

Forms in MS Access
In MS 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 MS 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 MS Access
MS 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 MS Access, use File, Open to select the database.  Click the Open box to the right of the Open button, and select Open Exclusive.
  2. Select Tools, Security, Set Database Password (Access 2003). Or select the Database Tools tab in the Ribbon and click on Set Database Password (Access 2007). Or go to the File menu, select the Info submenu and click on Set Database Password (Access 2010/2013)
  3. Enter the password you require, and re-enter to verify.  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.  MS 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 in MS Access, please click here for our further article on many-to-many relationships. Thanks for reading!

If you decide that building your own stock control 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?Tweet

Software-Matters is a UK software development team based in Gillingham, Dorset, and close to the county borders of Somerset, Wiltshire and Hampshire. Nearby cities include Bournemouth, Poole, Southampton, Bristol, Bath and Salisbury.

Don't just take our word for it...

"I would like to say thanks to yourself and your team for all your hard work.
We are very happy with the quoting system; for the first time ever we are looking at a system in which we have confidence."

Steve Ringsell, CFN Packaging

scroll to top - icon