Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

User-Defined Data Type-3

Last week we have learned how to define a User-Defined Data Type and it’s 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 which holds 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 Type, consisting of several elements of it’s own, as child element within a User-Defined Type.  That is what we are going to try out here.

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

We will build an employee record with the above logical group of information defined separately with it’s 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 the User Defined Type Employee is shown below.

1.Qualification
Desc1
Desc2
2.Experience
Desc1
Desc2
3.Address and Date of Birth
Address1
Address2
City
State
PIN
BirthDate
4.Employee Details
Employee Name
Designation
Join Date
Monthly Performance Score(1 to 12)
Salary

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

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 have two elements of String Data Type.  The Experience Type also have two elements of String data type.

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

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

The Employee Data Type with BioData Type as 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 how all these fits together as Employee Record.

The Employee type have four elements. The E_Name element is String type and it’s 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 Date type. 

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 BioData Type.

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

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 comes in one’s mind is that how to address each element to assign values into them.

We will write a small program to try out Employee Data Type and assign values into each element of the nested complex data structure.  It is not that 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 look closely at each element as how it is addressed  to assign values into 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 with fully qualified names showing it's 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

Check the With...statement how the Object references are given in proper order of it’s placement to reach it's 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 to 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 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.

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 keyboard, based on the prompt displayed asking for specific values.  In the last statement Call ListEmp2(Emp) will run the following code by passing the employee records array  and prints the output into the Debug Window. Keep the Debug Window Open (Ctrl+G).

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 Type in your projects and explore it’s 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 in it’s elements.  For example, if any 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 entered is not valid to accept in the field.  Like-wise 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 Class Module, 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 remains as part of the package and don’t have to write separate code for it.

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


Share:

1 comment:

  1. Another great article :-) Thank you and I'm very much looking forward to the article about class modules

    ReplyDelete

Comments are subject to moderation before publishing.

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

http://clicky.com/101199826
Blog Directory Visit blogadda.com to discover Indian blogs

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email

Labels

Forms How Tos Functions MS-Access Security Reports msaccess forms Animations Class Module msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Array Queries msaccess reports External Links msaccess tips Accesstips Downloads Objects Property Event Menus and Toolbars Collection Object Controls MsaccessLinks Process Controls WithEvents Art Work msaccess How Tos Combo Boxes Graph Charts List Boxes VBA msaccessQuery Command Buttons Form Command Button Data Dictionary Object Emails and Alerts Query RaiseEvent Report Calculation Custom Wizards DOS Commands Data Type ms-access functions msaccess functions msaccess graphs msaccess reporttricks Custom Functions Item Object Reference msaccessprocess security advanced Access Security Add Field Type Fields Form Instances Key Macros Menus SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Copy Excel Expression Join Methods Microsoft Numbering System Records Recordset Security Split Table Time Difference Utility Workgroup Wrapper Classes database function ms-access msaccess wizards reference text tutorial vba code Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Export External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Tables Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif msaccess msaccess alerts pdf files restore switch toolbar updating upload

Featured Post

Call Function From MouseMove Event Property

Introduction. This is about running a User-Defined Function (say myFunction(Parameter) ) on the Mouse Move Event Property of Text Boxes.  W...

Labels

Blog Archive

Recent Posts