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.