Validation- to ensure only the correct format would be accepted:
- Quantity - User can only enter quantity between 1 and 24
- Price - User cannot enter price below £1 or above £50
- Re-Order level – This a set level of stock set by the end user once current levels of stock reach this level which is 3, a warning message will be shown.
- Order Multiple – This is how many items are in stock in packs
A validation has been placed on the quantity field, which means the user can only enter values between 1 and 24.
The validation is working as the user has entered a quantity value above 24 and an error message has appeared.
Another validation placed on the Item Stock table is the re-order level. This means that If a stock in the database reaches the level set which is 3 then an order message shall appear. This was done firstly by:
A lookup was also placed on the item stock table so the user can see what stock comes from which supplier. For the lookup to take place, the field supplier id was right clicked on the item stock table. Then the lookup wizard was found after the tblSupp was chosen. This can be seen in the screen below:
Then which fields were going to be looked up needed to be decided from the supplier table.
From the screen above it can be seen that the supplier ID and the namesupp fields from the tblSupp have been chosen to be looked up.
The lookup was successful the user can now select which supplier the stock comes from.
Order Table 3.1.3:
I then created the order table this consists of the order number, supplier id and the date. This table allows an order to be created which shows all items of stock which need to be ordered. The primary key for the tblOrder is the OrderNumber, the table is shown below:
On the Ordertbl there is a validation on the date field which means every time a order is made it is set to today’s date.
The validation was successful as the date shown is today’s date.
On the Ordertbl There is a lookup so on the order sheet the name of the supplier and the telephone appears after being looked up from the Suppliertbl.
On the supplier id field a lookup wizard was done, the order table will lookup the supplier id, name of supplier and the telephone number.
The lookup was successful as you can click on the supplier id and a dropdown list appears to allow you to select the supplier.
Supplier Table 3.1.4:
I then created the supplier table which consists of the following fields, name of the supplier, address, and postcode and telephone number. The primary key for tblSupp is the SupplierID, the tblSupp can be seen below:
An input mask has been created on the telnumber field so that the user can only enter a maximum on 11 numbers, no less no more. This was done firstly by going on the field then going to general and writing in 11 0’s. This is to make the input mask work:
In the screen below it can be seen the input is working successfully, because when the user enters tries to enter more than 11 digits it stops the user, also means the user cannot enter any text.
Table transaction 3.1.5:
The last table created was the tbltransaction; this table allows stock which is being used to be subtracted from the stock database. It consists of the fields date used, quantity, id number and Tran’s id. The table’s primary key is the TransID and the foreign key is the IDNumber. The table can be seen in the screenshot below:
Validation:
A validation was placed on the quantity field, so when the user subtracts items used on that day it will only take a sensible value between 1 and 12 and not erroneous value for example more than what is in stock.
The validation was created firstly by
Lookup:
A lookup was placed on the id number, so this id number matches the same one from the item stock table. So the stock value taken away is from the correct item of stock.
This was done by clicking on the id number field in the transaction table. Then going to lookup wizard then selecting the table that needs to be looked up which is shown below:
The table being looked up is the Item Stock table
Then the fields within the item stock table needed to be looked up these are shown below:
The fields selected were the IDNumber and the name of the stock.
The lookup is now completed, it can be seen below in the screen shot that the lookup was successful:
3.2 Linking the tables:
The Add Supplier Form:
The add supplier form was created in design view, the form allows the user to add new suppliers into the database.
In Figure 1 the initial add supplier form was incorrect, as there were few fields which were missing. The customisations made can be seen below:
Customisation2: -
- NameSupp changed to a combo box to allow the user to search for a particular supplier
- Edit Supplier button was added, if user needed to make any amendments to current suppliers
-
A Post code field was added
In figure 2 above this is the improved add supplier form where the new customisations have been added.
In the screen below is the design view of how the add supplier form looks like:
Also on the add supplier form a code was added, so each time a new supplier was entered the user would not have to find a new record instead it would start at a new record. For the end user to enter a new suppliers information.
For this to happen firstly we have to enter the design view of the add supplier form. Then go onto form properties then onto the event section. Then the on open field was selected right clicked and then the code builder option was selected. This can be seen in the diagram below:
From the screen above a code has been entered, this code is DoCmd.GoToRecord,, acNewRec. This code means that when the user enters a new supplier each time, it will start with a fresh record and not the previous one put in.
The add supplier form was successful as it can be seen in the screen below that when the suppliers information is entered on the form, it is then added to the supplier database.
The user entered the following information about the supplier, to be added to the supplier database. This can be seen in the screen above.
The form was successful the data the user entered in the form has been added to the supplier database.
The transaction used Query:
The transaction query has been set up in the way it has been said in the design section.
The Customer Orders Form:
