Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

User Defined Data Type-2


This is the second post on User-Defined Data Type. The Link to the first post on this topic is here.

The topic of User-Defined Data Type came up when we used Two-Dimensional Arrays of Variant data types storing different value types (String, Integer, Double) in each element of the Array.  The Variant data type has the ability to change its data types automatically, as and when a particular type of value is received in the variable or its individual array element.  Instead of using a single Variant Variable with two dimensional Array, we can use four singly dimensioned Variables of different data types, as an alternative method.  Most of the time these methods are more than sufficient for simple data processing tasks.

But, learning something new is always interesting in programming.  The User-Defined Data Type is an interesting VBA feature.  We will explore and learn how to use it in our programs. 

How to Define a User-Defined Data Type

The steps go something like this:

  1. Define a new Data Type, with composite data types,  consisting of existing built-in variable types:  Integer, Long Integer, Double, String, etc.   The User-defined data type must be defined within the Type. . . End Type structure at the Module level.  The data type declaration must be at the beginning of the Standard Module.  The following sample declaration defines the data type myRecord and has two data elements: RecordID as Long Integer type and Description as String type.
    Public Type myRecord
    RecID as Long
    Description as String
    End Type

    The Scope of the Type declaration is Public by default.  Public/Private declaration is Optional.  You can declare it as Private, in that case, the availability of a Variable declaration is (like Dim AbcRec as myRecord) only within the Module, where the Type is declared.  The default scope (Public) enables the availability of the declared type within all Modules of this Project and to other Projects when this database is referenced in other Projects.  Let us start with a simple example:

    Type Sales
        Desc As String
        Quantity As Long
        UnitPrice As Double
        TotalPrice As Double
    End Type

    The dataType name is Sales.

  2. As you can see in the Sales data type we have used built-in data Types String, Long Integer, and Double for different data elements.

  3. Using the User-Defined variable in the program starts with Dimensioning a Variable of Type Sales, like any other variable.   
    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

    Result printed in the Debug Window:

    iPhone 8 Plus  1             75000         75000 

    Unlike built-in Variables, addressing individual variable elements always starts with the <Type Name> followed by the <Element Name>, and both names are separated with a <dot>. The Desc, Quantity, UnitPrice, and TotalPrice elements are addressed as individual Properties of mySales.

  4. Let us make the above code a little bit flexible and clean, by placing the Variable elements within the With…End With structure.  The InputBox() function will allow us to enter data directly from the keyboard, into each element of the Sales Record.

    Public Function typeTest()
    Dim mySales As Sales
    With mySales
       .Desc = InputBox("Item Description: ")
       .Quantity = InputBox("Item Quantity: ")
       .UnitPrice = InputBox("Item Unit Price: ")
       .TotalPrice = .Quantity * .UnitPrice
    End With
    'Print the values on Debug Window
    With mySales
      Debug.Print .Desc, .Quantity, .UnitPrice, .TotalPrice
    End With
    End Function

    The modified code will get information on one Record and print them out on the Debug Window.  Before running the Code open Debug Window (Ctrl+G) to view the output.

    Arrays of User-Defined Type

  5. Next, we will define an Array of mySales Variable to enter information for five different items.  Pass the Array of User-Defined Variable with multiple elements of data to SalesPrint() Function as Parameter. The SalesPrint() function will calculate and update the TotalPrice element, before printing the Array values in the Debug Window. Keep the Debug Window open. The sample VBA Code of the programs is given below:
    Public Function SalesRecord()
    Dim mySales(5) As Sales
    Dim j As Integer, strLabel As String
    For j = 0 To UBound(mySales) - 1
        strLabel = "(" & j + 1 & ") "
        With mySales(j)
           .Desc = InputBox(strLabel & "Item Description:")
           .Quantity = InputBox(strLabel & "Quantity:")
           .UnitPrice = InputBox(strLabel & "UnitPrice:")
           .TotalPrice = 0
        End With
    Call SalesPrint(mySales())
    End Function

    Check the Dim statement, it is like any other array definition.  Dimension two more variables j and strLabel.  Variable j is used as a control variable in the For…Next loop.  strLabel is used to construct and store a label, like (1), (2), etc. to use in the InputBox() prompt.  This is an indicator to identify the current record number when we enter data into each record.

    We have used meaningful names for the Array Elements (Desc, Quantity, UnitPrice rather than using array index numbers like Sales(0,0) for Description or Sales(0,1) for Quantity, etc.). The MySales(j).TotalPrice is assigned 0.  This element's value will be calculated and assigned in the SalesPrint() function.  We will pass this Array as ByRef Parameter to the SalesPrint() Function.

    The SalesPrint() Function.

  6. The SalesPrint() function Code is given below:

    Public Function SalesPrint(ByRef PSales() As Sales) Dim j As Integer, strLabel As String Debug.Print "Description", " ", "Quantity", "UnitPrice", "Total Price" For j = 0 To UBound(PSales) - 1 strLabel = "(" & j + 1 & ") " With PSales(j)

    'calculate TotalPrice

    .TotalPrice = .Quantity * .UnitPrice 'print the values in debug window Debug.Print strLabel & .Desc, " ", .Quantity, .UnitPrice, .TotalPrice End With Next End Function

    The SalesPrint() function receives the Sales Record Array reference in the PSales variableHere also we have defined two local variables, j as Integer and strLabel as String. In the next line, we are printing a header line in the Debug Window in preparation for displaying Sales record details under proper headings.

    When a comma is used to separate each item they are printed on 14 column zones on the same line.  We have used an empty item with space as the second item on the print line to print the Quantity item on the 28th column so that the Item Description can have more than 14 characters long.

  7. Next, we are using a For . . . Next loop control structure is to access each record from memory, using the control variable j’s current cycle value of the loop as an array index number.  This will run from 0 to 4 (5 times).

  8. The First-line within the ForNext loop creates a label to give the sequence number, in the form of (1), (2), and so on to identify the records in the order in which they are entered into memory using the InputBox() function.

  9. The next statement puts the root level name of the User-Defined Type PSales within the With . . . End With Structure in order to address its Properties (.Desc, .Quantity, etc.) easily rather than repeating the upper-level Object name PSales as we did in the calling program.

  10. The next executable line calculates the Total Price value and assigns it to the TotalPrice element.

  11. Next line prints the current record to the Debug Window.  This process repeats within the For . . . Next loop and print all items in the Array. 

By this time I hope you understood the usefulness of this feature.  If you explore a little further with this method you can save these records from memory into an Access Table.  This type of Variable declaration is made for a particular task and the same data type may not be useful for general-purpose tasks as we do with built-in Variables.  Its data elements properties like Desc, Quantity, UnitPrice, etc. may not be useful for other purposes.

There are some interesting ways we can use the User-defined dataType and we will continue this discussion on this topic next week.


No comments:

Post a Comment

Comments subject to moderation before publishing.




Post Feed


Popular Posts

Blog Archive

Powered by Blogger.


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