[*] and [**]:- I have marked some fields as you can see above. These fields have their values looked up from another source. I will do this by selecting the data type as LOOKUP WIZARD, then follow the wizard instructions to give the fields the capability to lookup from another table or to have specific data entered by means of a combo box. In the wizard I can define whether I should type in the wanted values of refer the values from another table.
- If I choose to refer the values from another table the wizard asks which table contains the field I want to refer to. I choose the required table and field and click next. The wizard then asks whether I want to display the key column as well or just display the concerned data. Then it will ask me to give a name for my field.
- If I choose to type the wanted values the wizard shows a table with a single column asking me to type the wanted values. I will type all these values and then click next. The wizard then asks me for a name for the field.
The marked fields are explained below.
[*] These fields have their data looked up from the NAME field of the ITEMS table. These are the settings in the LOOKUP tab of the FIELD PROPERTIES:
DISPLAY CONTROL : Combo box
ROW SOURCE TYPE : Table/Query
ROW SOURCE : SELECT ITEMS.ICODE, ITEMS.NAME FROM ITEMS;
BOUND COLUMN : 1
COLUMN COUNT : 2
COLUMN HEADS : No
COLUMN WIDTHS : 0”;1”
LIST ROWS : 8
LIST WIDTH : 1”
LIMIT TO LIST : Yes
[**] These fields have their data looked up from the SUPPLIER field of the SUPPLIERS table. These are the settings in the LOOKUP tab of the FIELD PROPERTIES:
DISPLAY CONTROL : Combo box
ROW SOURCE TYPE : Table/Query
ROW SOURCE : SELECT ITEMS.ICODE, ITEMS.NAME FROM ITEMS;
BOUND COLUMN : 1
COLUMN COUNT : 2
COLUMN HEADS : No
COLUMN WIDTHS : 0”;1”
LIST ROWS : 8
LIST WIDTH : 1”
LIMIT TO LIST : Yes
[***]This field holds the quantity of the item purchased by the customer.
[****]This field holds the unit price of the item.
[*****]This field holds the total price of the item. It calculates the total price of the item by multiplying the quantity field with the unit price field.
Validation rules:
Input Masks:
REPAIRS
This table contains the details of customers who have sent in appliances to be repaired. This table is just required to make sure that the appliance is correctly fixed and if not the customer can give his id and get it properly repaired.
Validation Rules:
Input Masks:
QUERIES
As I had mentioned in ANALYSE, queries form an important part of filtering data. Through the use of queries the system will be able to distinguish certain data from the whole file. Thus this makes the feature very essential in my system as this feature will allow the users to search for data very quickly. It will also make it easier to produce reports for certain data (which I have mentioned later on). I will design these queries in two forms – direct (the query searches for a particular item only) and prompted (where the user is asked what he wants to search for). I am going to make the queries in design mode as it has more options i.e. I can control the fields to be added, criteria etc. These are the queries I plan to use for my system:
FORMS
Forms are also important in my system. This will make the system more user friendly as I can customise the fields to be in different positions. This enables me to group specific fields together so that the database is easier to understand. The next few sheets will display my designs of my forms. I will create the forms using the WIZARD. Here I will choose the required fields, the type of view (columnar), the colour scheme and name of the form. After I make the form in the wizard I will tamper around with the design of the form in design mode. My forms will be in the COLUMNAR format as it looks more professional and user friendly. These are the designs of my forms:
In the MAX field, I will insert the following code in the CODE BUILDER:
Private Sub MAX_LostFocus() ‘also MAX_onChange() and MAX_AfterUpdate()
01 Dim maxz
02 Dim minz
03 Dim pres
04 Dim subtr
05 Dim subtr2
06 Dim perc
07 maxz = MAX.Value
08 minz = MIN.Value
09 pres = PRESENT. Value
10 subtr = pres - minz
11 subtr2 = maxz - minz
12 perc = Int((subtr / subtr2) * 100)
13 If (perc <= 2) Then
14 MsgBox "WARNING! STOCK VERY LOW!!!"
15 STATUS.Value = "WARNING!!!"
16 ElseIf (perc <= 25) Then
17 STATUS.Value = "LOW"
18 Else
19 STATUS.Value = "OK"
20 End If
End Sub
To explain the code, I have numbered each statement. This is the explanation:
01 Creates a variable named maxz
02 Creates a variable named minz
03 Creates a variable named pres
04 Creates a variable named subtr
05 Creates a variable named subtr2
06 Creates a variable named perc
07 Stores the value of the MAX field in the maxz variable
08 Stores the value of the MIN field in the minz variable
09 Stores the value of the PRESENT field in the pres variable
10 Subtracts the value of minz from pres and stores it in the subtr variable
11 Subtracts the value of maxz from minz
12 Turns subtr into a percentage, takes the integer value of it and stores it in the perc variable
13 Opens an IF condition. Checks if perc is lesser than or equal to 2. If the condition is true then lines 13 and 14 are executed and its skips to line 20, otherwise it skips to line 16
14 Displays a dialog box with the message "WARNING! STOCK VERY LOW!!!"
15 Sets the value of the STATUS field to “WARNING!!!”
16 Checks if perc is lesser than or equal to 25. If the condition is true then line 17 is executed and it skips to line 20, otherwise it skips to line 18.
17 Sets the value of the STATUS field to “LOW”.
18 This line is executed if the other two conditions are false.
19 Sets the value of the STATUS field to “OK”.
20 Closes the IF condition
This code is part of my automation of stock control. Should stock start running out, it checks the percentage of stocks left. If this percentage is below 25, the computer considers the stock amount as low. If the percentage is below 2, the computer issues a warning to the user so that he is aware that an item requires restocking. Otherwise the computer considers the stock amount acceptable.
I will add a logo to the forms to make it look professional. Then my next plan is to make a good colour scheme for my forms. This will improve the GUI and make it more presentable. Of course, CODEBUILDER is an important feature as it will help me to program my fields to do some special jobs which could not be done by MS Access alone. Such a case would be storing an addition into both the form and the table, making harder validation rules etc.
RELATIONSHIPS
Relationships between tables are necessary so as to avoid the duplication of data. So I will create relationships between relevant tables. This is a diagram of how my relationship window will look after I am done with creating them:
As you can see, I have 5 instances of the ITEMS table. As you saw in the beginning of this section, I have 5 items that can be stored in the PURCHASE field. I wanted to create a relationship between the ITEMS table and each of the items in the PURCHASE field. In order to carry that out I created 5 instances of the ITEMS table and created a relationship between the ICODE of the ITEMS table and the ICODE(no) of the purchase table.
REPORTSReports are a very important feature of MS Access. This feature creates a summary of the data, something like the pivot table feature in MS Excel. I will create my reports using the WIZARD. These are the reports I plan to design:
These are my plans of the designs of the reports:
MACROS
I will require macros to open queries through the switchboard. I plan to create the macros in design view because, just like the tables, the macros have extra parameters which can be easily configured in the design mode. These are the macros I intend to create:
SWITCHBOARD MANAGER
The switchboard manager is a very important feature of MS Access. This feature is the starting point of my interface – it will be used as a menu. I am going to create the switchboards using the switchboard manager built in within MS Access. The switchboard manager can be accessed by clicking Tools > Database Utilities > Switchboard Manager. The window which pops up shows all the switchboards made. Here I will click New and another pop-up will open asking for a name for the switchboard. After naming it I will choose the switchboard and click Edit. The window that pops-up displays all the items within the switchboard. I can add new items by clicking New and then choosing the options. This is the layout structure that I plan to design:
The following page shows the basic format of my switchboard:
TEST PLAN
Now that my design plans for the system are complete, I will devise a test plan to make sure that my system works the way I expect it to. Thus I will create a TEST PLAN to ensure that my system will not make any errors which are in my control. The following table is the test plan that I will carry out:
USER FEEDBACK
Now my design plans are complete. Before I began my practical work, I decided to have the manager evaluate my progress, so that I would not make a mistake and regret it later. A few days later I went to the manager and asked his opinion after showing him all the designs that I proposed for the system. The manager said it was “well done” but one important thing was missing – the mail merge feature. Also, the manager wanted me to change the structure of the PURCHASE form so that it would look like a receipt with details. So then I started working on the problems. To send letters to the suppliers I need information from both the SUPPLIERS table and the ITEM PROPERTIES TABLE. At first I thought of making a table but after some time it struck me that I could create a query that would link the two tables together. So now I plan to create the query in the following way:
QUERY NAME: MAILMERGE
CRITERIA: (none)
FIELDS SELECTED: ICODE, NAME, ITEM TYPE, MAX, STATUS, SUPPLIER (ITEMS table), SCODE, ADDRESS, STATE, COUNTRY, P/ZCODE, SUPPLIER (SUPPLIERS table).
Then this will be my format for my standard letter:
To:
The manager,
<<POBOX>>
Dear Sir,
Elektronikz has reached its stocking date and is now currently restocking all goods from various countries. In order to replenish our stock levels, we require the normal level (<<MAX>>) of <<NAME>> which is supplied by your company, hopefully reached to us latest by the 7th of April 2003. Please ensure that the goods delivered are in good condition and payment will be made on the 10th of April personally by the manager.
Thank you,
Mr. Nathan Fernandes,
GENERAL MANAGER OF ELEKTRONIX
Now that the mail merge has been handled, I will create the new design of the PURCHASE form. The following is my new planning, and as shown, it looks like a receipt:
Now that my designs have completely been prepared, I will start implementing the new system.
