September 2018 ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

Last week we learned how to define a User-Defined Data Type and its usage in programs. If you have landed on this page, you may go through the earlier Post: User-Defined Data Type-2, before proceeding further.

User-defined type declaration is done within the [Private/Public] Type … End Type structure. Immediately after the word Type, we give a name to the data type. Actual variables that hold the data values are defined as individual elements, mostly with built-in variable types: String, Integer, Long Integer, Double, or Variant.

Besides built-in variables, we can use other User-Defined Types, consisting of several elements of their own, a child element within a User-Defined Type. That is what we are going to try out here.

Combining Different sets of User-Defined Types.

First, we will declare some User Defined Types separately, for different categories of information, like Home Address, Qualification, and Experience, for Employee records.

We will build an employee record with the above logical related group of information defined separately with its own data elements and place them along with the elements of Type Employee.

The layout of the group of information of Employees, defined separately, before they are organized under a common User Defined Type Employee is shown below.

Qualification

Desc1

Desc2

Experience

Desc1

Desc2

Address and Date of Birth

Address1

Address2

City

State

BirthDate

Employee Details

Employee Name

Designation

Join Date

Monthly Performance Score(1 to 12)

Salary

Declaring the Data Types

First, let us declare the Data Types:

Option Compare Database

Public Type Qualification
    Q_Desc1 As String
    Q_Desc2 As String
End Type

Public Type Experience
     X_Desc1 As String
     X_Desc2 As String
End Type

Public Type BioData
    Address1 As String
    Address2 As String
    City As String
    State As String
    PIN As String
    BirthDate As Date
End Type

Public Type Employee
        E_Name As String * 45
        E_Desig As String * 25
        E_JoinDate As Date
        E_PerfScore(1 To 12) As Double
        E_Salary As Double
End Type

BioData Re-Defined with Qualification and Experience.

The Qualification and Experience Types are defined as child elements inside the BioData Type. After the change, the BioData Type looks like the Code given below.

Public Type BioData Address1 As String Address2 As String City As String State As String PIN As String BirthDate As Date Q_Desc As Qualification X_Exp As Experience End Type

The Qualification Data Type has two elements of String Data Type. The Experience Type also has two elements of the String data type.

The BioData type has Address details and Date of Birth as elements. Besides that Qualification and Experience Data Types are inserted into the BioData Type as its child elements.

Employee Data Type combined with modified BioData Type

Now, we will define the Bio-Data Data Type (along with Qualification and Experience) as a child element of Employee Data Type.

The Employee Data Type with BioData Type as a child element.

Public Type Employee E_Name As String * 45 E_Desig As String * 25 E_JoinDate As Date E_PerfScore(1 To 12) As Double E_Salary As Double B_BioData As BioData

End Type

Got the idea of how all these fit together as Employee records.

Employee types have four elements. The E_Name element is a String type and its length is limited to 45 characters long. The next element is to store the Designation of the employee and can store up to 25 characters. The String length specification is purely arbitrary, you may use it as it is or simply say E_Name As String. Join Date is a Date type.

The next item is an array with 12 elements to store the employee’s monthly performance evaluation score (on a scale of 10) recorded by the management.

We have declared Qualification and Experience data types first, before inserting them as elements of the BioData Type.

BioData Data Type is declared above Employee type before inserting it into the Employee data type.

Cation.

Ensure that you are not placing a Type within itself.

Now, that we are all set to try out this complex data structure and the first question that comes into one’s mind is that, how to address each element to assign values to them.

Sample Test Program for Employee Data Type.

We will write a small program to try out Employee Data Type and assign values to each element of the nested complex data structure. But, it is not as complicated as it sounds. If you find it difficult to follow then try out simpler examples defined on your own level of understanding.

The program code is given below and looks closely at each element as to how it is addressed to assign values to it.

Public Function EmplTypeTest()
Dim Emp As Employee

Emp.E_Name = "John"
Emp.E_Desig = "Manager"
Emp.E_JoinDate = #01/01/2018#
Emp.E_PerfScore(Month(Date) - 1) = 4.5
Emp.E_Salary = 40000
        'BioData
        Emp.B_BioData.Address1 = "115/8"
        Emp.B_BioData.Address2 = "Olencrest,"
        Emp.B_BioData.City = "Columbus"
        Emp.B_BioData.State = "Ohio"
        Emp.B_BioData.PIN = "43536"
        Emp.B_BioData.BirthDate = #9/29/1979#
       
       'Qualifications
            Emp.B_BioData.Q_Desc.Q_Desc1 = "Degree in Computer Science"
            Emp.B_BioData.Q_Desc.Q_Desc2 = "PG Degree in Computer Science"
    
        'Experience
            Emp.B_BioData.X_Exp.X_Desc1 = "From Jan-2010 onwards Working as Project Manager, with XYZ Company."
            Emp.B_BioData.X_Exp.X_Desc2 = "From Mar-2005 to Dec-2009 worked as Team Leader with ABC Enterprise."
Call ListEmp(Emp)
    
End Function

As you can see in the above code we have addressed each element as fully qualified names showing its hierarchical position in the Employee Data Structure. The same code is given below using full object references with With XXXX...End With statements, where XXXX part represents the object hierarchical names.

Public Function EmplTypeTest0()
Dim Emp As Employee

With Emp
  .E_Name = "John"
  .E_Desig = "Manager"
  .E_JoinDate = #01/01/2018#
  .E_PerfScore(Month(Date) - 1) = 4.5
  .E_Salary = 40000
End With

        'BioData
With Emp.B_BioData
        .Address1 = "115/8"
        .Address2 = "Olencrest,"
        .City = "Columbus"
        .State = "Ohio"
        .PIN = "43536"
        .BirthDate = #9/29/1979#
End With

       'Qualifications
With Emp.B_BioData.Q_Desc
            .Q_Desc1 = "Degree in Computer Science"
            .Q_Desc2 = "PG Degree in Computer Science"
End With

        'Experience
With Emp.B_BioData.X_Exp
            .X_Desc1 = "From Jan-2010 onwards Working as Project Manager, with XYZ Company."
            .X_Desc2 = "From Mar-2005 to Dec-2009 worked as Team Leader with ABC Enterprise."
End With

    Call ListEmp(Emp)

End Function

Referencing individual Element Variables of Employee Data Type.

Check the With... Statement and how the Object references are given in proper order and their placement to reach its element variable. The above Code is again modified with With XXXX...End With statements in a nested form only using the Object name nearest to the data variable.

Public Function EmplTypeTestA() Dim Emp As Employee With Emp .E_Name = "John" .E_Desig = "Manager" .E_JoinDate = #01/01/2018# .E_PerfScore(Month(Date) - 1) = 4.5 .E_Salary = 40000 'B_BioData With Emp.B_BioData .Address1 = "115/8" .Address2 = "Olencrest," .City = "Columbus" .State = "Ohio" .PIN = "43536" .BirthDate = #9/29/1979# 'Qualifications With .Q_Desc .Q_Desc1 = "Degree in Computer Science" .Q_Desc2 = "PG Degree in Computer Science" End With 'Experience With .X_Exp .X_Desc1 = "From Jan-2010 onwards Working as Project Manager, with XYZ Company." .X_Desc2 = "From Mar-2005 to Dec-2009 worked as Team Leader with ABC Enterprise." End With

End With ‘ Emp.B_BioData End With ‘ Emp Call ListEmp(Emp) End Function

You can use any of the above three programs or all of them one by one to try out the following Printing program to list the data on the Debug Window.

Public Function ListEmp(ByRef EmpList As Employee)
With EmpList
    Debug.Print "Name: ", , .E_Name
    Debug.Print "Designation: ", , .E_Desig
    Debug.Print "Join Date: ", , .E_JoinDate
    Debug.Print "Performance Score July: ", .E_PerfScore(8)
    Debug.Print "Salary: ", , .E_Salary
    
    Debug.Print "Address1: ", , .B_BioData.Address1
    Debug.Print "Address2: ", , .B_BioData.Address2
    Debug.Print "City: ", , .B_BioData.City
    Debug.Print "State: ", , .B_BioData.State
    Debug.Print "PIN: ", , .B_BioData.PIN
    
    Debug.Print "Qualification1: ", .B_BioData.Q_Desc.Q_Desc1
    Debug.Print "Qualification2: ", .B_BioData.Q_Desc.Q_Desc2
    
    Debug.Print "Experience1: ", , .B_BioData.X_Exp.X_Desc1
    Debug.Print "Experience2: ", , .B_BioData.X_Exp.X_Desc2
    
End With

End Function

Sample Output on Debug Window.

Sample Output displayed on the Debug Window is given below:

Name:                       John                                         
Designation:                Manager                  
Join Date:                  01-01-2018 
Performance Score August:    4.5 
Salary:                      40000
Address1:                   115/8
Address2:                   Olencrest,
City:                       Columbus
State:                      Ohio
PIN:                        43536
Qualification1:             Degree in Computer Science
Qualification2:             PG Degree in Computer Science
Experience1:                From Jan-2010 onwards Working as Project Manager, with XYZ Company.
Experience2:                From Mar-2005 to Dec-2009 worked as Team Leader with ABC Enterprise.

Input Values through Keyboard.

If you would like to try out an array example, then copy-paste the following two programs into a Standard Module and run the first code.

Public Function EmplTypeTestB()
Dim Emp(1 To 3) As Employee
Dim j As Integer, strlabel As String

For j = 1 To 3

With Emp(j)
strlabel = "( " & j & " )"
    .E_Name = InputBox(strlabel & "Name:")
    .E_Desig = InputBox(strlabel & "Designation:")
    .E_JoinDate = InputBox(strlabel & "Join Date:")
    .E_PerfScore(Month(Date) - 1) = InputBox(strlabel & "Performance Score:")
    .E_Salary = InputBox(strlabel & "Salary:")
    
   'B_BioData
    With Emp(j).B_BioData
        .Address1 = InputBox(strlabel & "Address1:")
        .Address2 = InputBox(strlabel & "Address2:")
        .City = InputBox(strlabel & "City:")
        .State = InputBox(strlabel & "State:")
        .PIN = InputBox(strlabel & "PIN:")
        .BirthDate = InputBox(strlabel & "Birth Date:")
       
       'Qualifications
        With .Q_Desc
            .Q_Desc1 = InputBox(strlabel & "Qualification-1:")
            .Q_Desc2 = InputBox(strlabel & "Qualification-2:")
        End With
    
        'Experience
        With .X_Exp
            .X_Desc1 = InputBox(strlabel & "Experience-1:")
            .X_Desc2 = InputBox(strlabel & "Experience-2:")
        End With
    End With
End With
Next

    
    Call ListEmp2(Emp)

End Function

The Inputbox() Function will allow you to type details of three employees directly from the keyboard, based on the prompt displayed asking for specific values. In the last statement Call, ListEmp2(Emp) will run the following code with the employee records array and print the output in the Debug Window. Keep the Debug Window Open (Ctrl+G).

Public Function typeTest()
Dim mySales As Sales

   mySales.Desc = "iPhone 8 Plus"
   mySales.Quantity = 1
   mySales.UnitPrice = 75000#
   mySales.TotalPrice = mySales.Quantity * mySales.UnitPrice

Debug.Print mySales.Desc, mySales.Quantity, mySales.UnitPrice, mySales.TotalPrice

End Function

Printing Program.

Public Function ListEmp2(ByRef EmpList() As Employee)
Dim j As Integer, strlabel As String
Dim lower As Integer
Dim upper As Integer

lower = LBound(EmpList)
upper = UBound(EmpList)

For j = lower To upper
With EmpList(j)
Debug.Print
    Debug.Print "=== Employee: " & .E_Name & "  Listing ==="
    Debug.Print "Name: ", , .E_Name
    Debug.Print "Designation: ", , .E_Desig
    Debug.Print "Join Date: ", , .E_JoinDate
    Debug.Print "Performance Score " & MonthName(Month(Date) - 1) & ": ", .E_PerfScore(8)
    Debug.Print "Salary: ", , .E_Salary
    
    Debug.Print "Address1: ", , .B_BioData.Address1
    Debug.Print "Address2: ", , .B_BioData.Address2
    Debug.Print "City: ", , .B_BioData.City
    Debug.Print "State: ", , .B_BioData.State
    Debug.Print "PIN: ", , .B_BioData.PIN
    
    Debug.Print "Qualification1: ", .B_BioData.Q_Desc.Q_Desc1
    Debug.Print "Qualification2: ", .B_BioData.Q_Desc.Q_Desc2
    
    Debug.Print "Experience1: ", , .B_BioData.X_Exp.X_Desc1
    Debug.Print "Experience2: ", , .B_BioData.X_Exp.X_Desc2
    
End With

Next
End Function

I hope you will try out the User Defined Types in your projects and explore its strength and weaknesses further.

The main problem with the User Defined Type is that it doesn’t have any feature to validate the data passed to it before accepting its elements. For example, if a future date is entered into the Date of Birth element there is no built-in code to validate and inform the user that the date value entered is not valid to accept in the field. Likewise, if a negative value is entered into the Salary field it simply accepts it. Wherever the validation check is required, we have to write separate code to do that, whenever we use the User Defined Type (UDT) all the time.

A better option is to use Class Modules. We can define individual elements in the Class Module, and run validation checks on each item, wherever necessary, before accepting the data into the element. Write Functions or Subroutines to operate on the data for common tasks and call the Functions from user programs. All these remain as part of the package and don’t have to write in separate code for it.

We will learn how to use Class Modules to define structured data and use them in programs.