Touch up of the Startup Screen Form

In this set, we are going to carry out a final touch on the StartupScreen form in our database. First open the KCBSystem.accdb file.

On the left column, under the form category, right click the StartupScreen and select design view to open  the form in design view. Drag the right boarder of the form toapproximately 15sm (6") in width.

Select the "WelcomMessage" control, drag the control close to the top of the page and adjust the control width the same full width of the form. Also make the height of the control ao approximately 2.5cm (1"). We can select the "Format" tab on the "Form Design Tool" and adjust the format on how the control is displayed in form view. We can also amend the related property in the property sheet for necessary action.

In my case, I am selecting the "Clarendon Extended" font size 20 to my favourite. The light blue colour "Aqua, Accent 5, Lighter 40%" is selected too. On the property sheet, I decided to change the "border style" property from "Solid" to "Transparent", the "Scroll Bars" property from "Vertical" to "None" and the "Tab Stop" property from "Yes" to "No".

The above mentioned steps finally give me a slight new look of the Startup screen like below:

 Back to design view, I will then make then make the address display box smaller and move it down to give some space to insert the first picture that we created in previous step. On the Form Design Tool - Design tab, select the Image icon and a Insert Picture dialogue will open after you click on the form, browse to the previously saved picture in c:\KCBDatabase\Img folder named "KCBLogo.ico". Place the picture in the middle right below the Welcome Message control.

Adjust the rest of the control as per you own preference by means of the property sheet. You can adjust a few controls at the same time by first selecting one of the control and then hold down shift key and select more controls at the same time. In my case, I decide to make all "boarder style" property to "transparent" and all "Tab Stop" property to "No" except the "Show Startup Screen" control.

I also decided to select the label of the "ShowStartupScreen" control which is by default named as "ShowStartupScreen_Label" and change its "Caption" property to "Show Startup Screen during program start up".

Other then the above mentioned, all of us are allow to play around and adjust to whatever layout that is preferred by each individual. I am inserting my final design layout below for reference, but yours can look different. It is fine. Only to note that in this form, all control's "Tab Stop" property are set to "NO" except the "Show Startup Screen" yes/no field. This means that whenever the form is opened, the cursor will be active only on this field, which will make it easy for the user to toggle it on or off.

The first action in Microsoft Access on creating a form from table.

After the previous five (5) posts on how to set up necessary environment for the database that we plan to design.This is the first step in starting to use the Microsoft Access. In this step, we will open our database file called KCBSystem.

In case you are new to read this post, then may be you have to go through my previous 5 posts before here so that you do set up the environment and required file prior to starting our following steps and practise Microsoft Access database programming by building our database system.

We will first open the Microsoft Access (the version I use is 2010). In Access, click the open button and then browse through to c:\KCBDatabase folder and find the KCBSystem.accdb which we created by following the 5 previous posts.

In the opened KCBSystem, there is currently only 1 linked table named tblSettings. If you double click the table, it will open with a single row of record. This table is used to store the system owner information and thus it will have only one row of record. If there is more than one, you can delete the unnecessary records. If no record is found, you can then key in the necessary information.

We are going to create our first FORM in Microsoft Access 2010 by means of Form Wizard. First select the tblSettings table on the left hand column under tables tab.Select "create" on the top menu and then select Form Wizard under the Forms submenu. The Form Wizard dialogue box will open with the tblSettings table selected and all available fields stay in the left-hand pan. Click on the double arrow pointing to the right and all the available fields will be transferredto the right-hand pan.

Click Next to go to layout selection and select the columnar radio button. Click Next again. In the What title do you want for your form? field, replace the default name tblSettings with the title we want to use, "StartupScreen" and then click Finish. The Form Wizard will continue to create the form and display the form opened after creating it. The initial screen showing the opened form in the form view will be as below:

Go to the top left hand corner and select the view into Design view as we are going to modify our first form. The design view of our first form will look like below:

As we are not going to have header for this form, we will delete the label "StartupScreen" in the blue colour header section. after deleting this label, we can then drag the horizontal bar below the header section to close up the form header section.

The next item we want to delete is the ID field together with its label. What we need to do is select the ID field and select delete. When we delete a field, the related label will be deleted at the same time automatically.

Next, we will select the label for the company field and press delete button on the keyboard to delete the company label. Note that even though the label is deleted, the field will stay in the form.

The next step we would like to do is to modify the company field. We will first select the company field. make sure the property sheet is open and you can select the "All" tab in the property sheet to show all fields properties. In this step we will only take note on the first two items. We will note that the Name of this field is "Company" and the Control Source is "Company" too. By the right hand side of the Control Source field, there is a down pointing triangular button which if clicked will show us all the field name available in the under-laying table of our form (in our case, the tblSettings). This means that the form wizard had selected the Company field for the control we selected in the form, and named it Company. (refer the picture below)

We are going to change the name of this control to "WelcomMessage". Note that the change of name in this case is not really important as it does not play any role in the system yet. Normally the name of a control will only be important if we plan to do any behind the scene amendment of status or property. For the time being, we only change the name for indicative purpose.

After changing the name of the control, we are going to change the control source as below:

="WELCOME" & Chr(13) & Chr(10) & [Company]

the equal sign at the beginning of the field means this is now become an equation rather referring to a specific field. The word WELCOME enclosed in double quotation marks means that the WELCOME word is a string and will be displayed intact. "&" is used to join up various character strings into one long string. Chr(13) & Chr(10) are form feed and line feed signal respectively and in this case we are using it to make sure the string feedto the next line before continue. The last Company enclosed in square bracket is referring to the field in the under-laying table. After changing the control source, select the form view on the top left corner to see the effect in form view. You can easily switch between form view and design view to check the effect at any time during programming.

Now we are going to carry out the same thing on showing the address of the system on the Startup Screen. Nevertheless, we will try to use a slight different approach.

Let us delete all the address related controls on the form to make the desktop clear for our task. After deleting the unwanted control, we will then select the Text Box icon in the Form Design Tools -> Design tab and place a text box on the form. Delete the label of the newly created control and name the control SystemAddress. In the control source, key in the following formula:

=IIf(IsNull([Address]),"",[Address]) & IIf(IsNull([Address2]),"",Chr(13) & Chr(10) & [Address2]) & IIf(IsNull([Address3]),"",Chr(13) & Chr(10) & [Address3]) & IIf(IsNull([City]),"",Chr(13) & Chr(10) & [City]) & IIf(IsNull([StateProvince]),Chr(13) & Chr(10) & "",Chr(13) & Chr(10) & [StateProvince]) & IIf(IsNull([ZipPostal]),""," " & [ZipPostal]) & IIf(IsNull([CountryRegion]),"",Chr(13) & Chr(10) & [CountryRegion])

The screen shot of the design view now looks something like below:

  The above formula that I asked to copy to you control seems a bit long, but it is actually very simple repeat of some inbuilt function of the Microsoft Access.

Isnull(???) is a function to check for entry of a control/field. It will return a true if there is any entry and return false if otherwise.

IIF(condition, true, false) is a conditional check.

Other parts of the formula above are understandable as it is actually a long string which join up all the little string by means of the "&" function. I propose you to create another text box and then copy the formula section by section into the text box and switching between design and form view for slowly understand the effect of each section.

Beside the above-mentioned modification, we can also move around all the other controls to the location we want and finally save the form. Below is the last layout view of the form that I have. Yours may be different depending on your personal design. It is fine.

If we save the form now, close and exit the form, by checking on the left column of the Microsoft Access Desktop, we will now be able to take note that beside the tblSettings table that we created previously, now we have another FORM category, and in this category we can find out newly created StartupScreen form.

Up to now, I am quite sure my follower on this blog will be understanding that I am not a Microsoft Access trainer who train you to use the program. Nevertheless I will try my best to share my understanding and experience, and go as basic as possible, especially in this initial stage of building the databases to guide all follower to "practice" the database programming.

Linking First table

After the previous step of preparation for necessary image, we are now going to start the first task in programming our system.

Close the KCBData file and open the KCBSystem with your Microsoft Access. After opening, you will note that the database is still empty. We are going to link the first table as below:

In your Microsoft Access, Select "External Data" menu, followed by selecting the Access button to open the "Get External Data - Access Database" dialogue box. Click the Browse... button and browse to the c:\KCBDatabase\Data folder and select the KCBData.accdb file. Make sure the "Link to data sourxe..." radio button is selected.

By clicking the OK button, a new "Link Tables" dialogue box will open and you will note that there is on the table "tblSettings" which we created previously available in the table tab. Clicking OK on this new dialogue box will close the dialogue box and return to our KCBSystem database with the tblSettings being linked.

Take note that there is a little right-pointing arrow on the side of the table, indicating that this is a linked table.

The above steps of actions are necessary whenever we need to link a table. We can also import the table by selecting the "Import..." radio button in the "Link Tables" dialogue instead of the "Link..." radio button.

By importing, the table is reside in this database and if we copy the database as a file by means of file explorers or save with a different name, the table will follow. By means of linking, we can then assure that even if we save the name of KCBSystem to a different name, the data will stay the same when we open the new file. This is the trick we used to make sure the data will never be amended even if we copy the file "KCBSystem" onto a different computer.

So, all tables in our system will be created in the KCBData file and linked to KCBSystem which act as the front end of the database system. I can then give the KCBSystem file to my college to run with Microsoft Access runtime program with out problem. I can then modify and upgrade my copy of KCBSystem freely. Only when a new function is complete developing, I can then distribute the new KCBSystem for others to replace their old copy. This is then a method of updating the program system without affecting whatever data that had been stored in the KCBData.

Below is a sanpshot of my KCBSystem with a single tlbSettings table linked.

Preparing image to use as LOGO

The is purely a preparing step, not really related to Microsoft Access. As I am sharing my experience in building a professional look database system, hence the present of logo and necessary picture is part of the extra requirement.

You will now be aware that, I am not going to only teach you on how to use Microsoft Access database management program, but to provide my experience in using it to build a usable company wide database system.

In this step, I am going to create a new folder in my C:\KCBdatabase folder and name it C:\KCBdatabase\img. Select a picture or graphic that you want to use as the logo of the company and then save it in the newly created C:\KCBdatabase\img folder. I had downloaded a picture from the internet for such purpose and this is inserted here for you convinient.

you can right-click on this picture and save it to the folder. I use the name KCBLogo.jpg

The next step is to convert this picture into icon. It can be done easily because there are a lot of icon converter in the internet for free conversion nowadays. Go to the web browser and search "Icon Converter". I use Online-convert.com for this task.

After conversion, the icon was downloaded and safe into the same newly created folder. The conversion to icon will make it convenient to use and save memory. Both my jpg file and icon file are save under the same name.

As I download the above image from internet, which I believe to be without any  copy right requirement. It seems to represent the idea that in par with the database that we are going to build. If someone feel it to be otherwise, please let me know and I will change the picture (may be to my own image, even though I am not a good photographer).

tblSettings, the first table in our database

A tblSettings table is created in the KCBData database, this table is having the following structure.

Field Name	Data Type	Data Size	Description
ID	Long Integer	4	Autonumbered Index  Field
Company	Text	255	My Access Database
BusinessPhone	Text	255	+6012-3456789
Address	Text	255	Address Line 1
Address2	Text	255	Address Line 2
Address3	Text	255	Address Line 3
City	Text	255	City
StateProvince	Text	255	State and Province
ZipPostal	Text	255	Post Code
CountryRegion	Text	255	Country
ShowStartupScreen	Yes/No	1	yes
WebPage	Hyperlink	-	key in web address here
Email	Text	255	kcbehnet@gmail.com
BusinessFax	Text	255	+6012-3456790
Build	Text	255	2014.11.001

This table is meant for the storage of company related particular, which in the later stage can be used to display as letter head of screen display information if necessary. Thus additional fields can be added if necessary.

As we know, a table is only used to store raw data, without any calculated fields, thus the structure is very clearly self-explanatory.

In this table, there are two fields that play some roles in our database system. The first of them is the ShowStartupScreen. This is a Yes/No field. It is used as a flag to mark the requirement to show the start-up screen if necessary. I use this to pop up the start-up screen so that the users will be able to note there is major change and need to update the system.

The second field that serve this purpose id the Build field which is used to display the version number.

After creating the table, please open it and key in the information of the company as per necessary. We can of course create a form for such job, but I am not preparing to do it at this moment because I want to pay more attention to those tasks that can help to make this database functional as soon as possible. The information I key into my tblSettings table is listed in the description column in the above table structure.

First Set up of My database environment

There are a lot of advice available in the cloud to explain on how to use and how to program Microsoft Access database. Thus I am not prepare to revamp the cycle. In this blog, I am going to put our foot down to the ground and build the database and make it functional in reasonable timing.

The Microsoft Access that I am using is of 2010 version. It is going towards cloud operation and we should be able to created web database. But one of the drawback is its web database is not compatible with VBA coding. This post a limitation on "automation" requirement. Hence the database that I am going to build as an example is a desktop database. Intranet is common now in manufacturing plant. Thus I am going to build the database base on network operation. Nevertheless, during the initial start up, I will try to set everything based on a single desktop operation and when it comes to a functional stage, then I will guide you through the simple steps to make it functional in network environment.

To clear any doubt, I would like to clarify that I am not in any relation ship with Microsoft. I am also not very clear on the final unseen limitation that Microsoft Access posed. The only thing I can be sure is that I had been using the system and running the program I designed for years and face no difficulty to-date. Thus it should be alright for the way that I use the program. Just in case we do face any limitation if future, then we will work together to rectify it and progress.

During this initial start up stage, I am creating a new folder in the c:\ drive and naming it as c:\KCBDatabase. In this folder, I am creating a sub-folder and naming it as c:\KCBDatabase\Data.

After creating this two folders, I start the Microsoft Access 2010 and created two new blank database. The first blank database is created in the c:\KCBDatabase folder and named as KCBSystem. The second blank database is created in the c:\KCBDatabase\Data folder and named as KCBData.

So, I am planning to create all tables for keeping our raw data in the KCBData database and the KCBSystem database will be the front-end operational system that linked all the tables from KCBData. All other queries, forms, reports and modules will then be created in this KCBSystem as a frond-end operating system.

The next step that we are planning to do is then to create the first table, which will store some specific data of the organization.

Why I started this Blog

I had been working in more than 10 different types of organizations for close to thirty years. Over the past experience, going through various situation of organization (some very poor and getting into receivership whereas some others are very rich and having cash-free operation, even without the necessity to budget its expanses), the voice of setting up a database system to standardize its operation had never been stopped. Even until today, the same request is still there.

Again, it is not that an organization does not manage to invest in good database system, which may even cost up to millions of dollars.Nevertheless, majority of the organization finally ends up putting its comment that the database system is not performing up to their expectation.

Why?

With my experience, there are two major reasons as below:

1. A lot of organizations have specific way of running their operation, hence there is no set standard available and thus a lot of good ERP,MRP or the same category of database systems do not manage to provide a standard that suite everybody.
2. Beside those modules in a database system that had been standardize or partially standardize,the cost to develop a good user-friendly module for specify operation, especially for those specify to a certain manufacturing process is too costly due to limited market.

Hence I had started to develop a database system by using Microsoft Access, starting twenty over years ago. At that moment, a lot of staffs in the organization (in Malaysia) were still not computer literate. It was also during that time that a lot of manufacturing plant start to look into "Standardizing" their operation with proper documentation. Thus a lot of effort had been put in place to make use of spreadsheet program, with its limited ability to "automate" and high flexibility.

After using some "programs" written in spreadsheet programs, we finally "standardized" to a certain extent, and by then, I decided to put some attention to Microsoft Access database system, and start to write my own program.

The same program was developed some twenty years ago and continue to developed on-the-go when I move on from one organization to the other. It was a normal practice that when I join an organization, I will try to make myself familiar with their existing operation. It was also normal that after understanding and familiar with the existing system used, I came to know that there were a lot of grievances on the lack of user-friendliness and the required of double entry from their existing staffs, especially in those organization that has a few different standardize database packages.

For those who know me, I had left the manufacturing organization for more than 10 years and re-enter it around three years ago. To my surprise, the same condition is still exist. Hence I had dug into my long time archive and brought out the same database which I developed and continue its development again.

During development of the system, I have to again searching for some assistant and advice. Thanks to the new technologies of this new era, provided I continue to search, a lot of information is available in the cloud.Is is during the searching process that I found there is still a lot of people calling for advice too. Hence I decide to start this blog and explain my experience in step by step basis.