LEARN MS-ACCESS TIPS AND TRICKS: Variables

Showing posts with label Variables. Show all posts

User Defined Data Type-2

a.p.r. pillaiAugust 25, 2018Accesstips, Array, Calculation, Data, Variables No comments

User-Defined Data Type 2.

This is the second post on User-Defined Data Types (UDTs).

You can find the link to the first post on this topic [here].

The need for User-Defined Data Types arose when we used two-dimensional arrays of the Variant data type to store different types of values (such as String, Integer, and Double) in each array element. The Variant data type can automatically adjust its type depending on the value assigned to it.

Instead of using a single Variant variable with a multi-dimensional array, we could also use several separate single-dimensional variables of different data types. For most simple data-processing tasks, these approaches are more than sufficient.

However, learning new techniques is always exciting in programming.

The User-Defined Data Type is one such interesting feature in VBA, and in this post, we will explore how to create and use it in our programs.

How to Define a User-Defined Data Type

The steps go something like this:

You can define a new data type that combines multiple built-in variable types—such as Integer, Long, Double, and String—into a single structure. This User-Defined Data Type (UDT) must be declared within a Type ... End Type block at the module level, typically at the beginning of a Standard Module, before any procedures.
The following example defines a UDT named myRecord with two elements: RecordID (of type Long) and Description (of type String):
```
Public Type myRecord

RecID as Long
Description as String

End Type
```
By default, a User-Defined Type (UDT) has Public scope, though explicitly declaring it as Public or Private is optional. If declared as Private, the UDT is accessible only in the same module where it is defined. In that case, variables such as Dim AbcRec As myRecord can be declared and used only inside that module.
When declared with the default Public scope, the UDT becomes available to all modules within the current project, and even to other projects that reference this database.
Let’s begin with a simple example:
```
Type Sales
    Desc As String
    Quantity As Long
    UnitPrice As Double
    TotalPrice As Double
End Type
```
The Data Type name is Sales.

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.

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, the elements of a User-Defined Type are always accessed by specifying the Type name, followed by a dot, and then the element name (for example: mySales.Desc). In this way, the Desc, Quantity, UnitPrice, and TotalPrice elements are treated as individual properties of mySales.
To make the code cleaner and more flexible, we can place these element references inside a With… End With structure. This allows us to enter data directly from the keyboard into each element of the Sales record using the InputBox() function.

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 retrieve information from a single Record and print it out on the Debug Window. Before running the Code, open the Debug Window (Ctrl+G) to view the output.

Arrays of User-Defined Type

Next, we will define an array of mySales variables to store information for five different items. We will then pass this array of user-defined variables—each containing multiple data elements—to the SalesPrint() function as a parameter. The SalesPrint() function will calculate and update the TotalPrice element for each item before printing the array values in the Debug Window.
(Keep the Debug Window open while running the program.)

The sample VBA code for this program 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
Next

Call SalesPrint(mySales())

End Function

Check the Dim statement—it works like any other array definition. We also declare two additional variables: j and strLabel. The variable j serves as the control variable in the For…Next loop, while strLabel is used to construct and store a label such as (1), (2), etc., which appears in the InputBox() prompt. This label helps identify the current record number as we enter data into each record.

We have chosen meaningful names for the array elements—Desc, Quantity, and UnitPrice—instead of using array indexes such as Sales(0,0) for Description or Sales(0,1) for Quantity. The 'MySales(j).TotalPrice' element is initially assigned 0; its value will be calculated and updated in the SalesPrint() function. The array is passed to SalesPrint() as a ByRef parameter.

The SalesPrint() Function.

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 by reference through the PSales variable. Within the function, two local variables are defined: j as an Integer and strLabel as a String. The function begins by printing a record field column heading line in the debug window.

When printing the data, commas are used to separate each item, placing them in 14-column zones on the same line. To allow the Item Description to occupy more space, an empty string is inserted as the second item on the print line. This effectively moves the Quantity value to the 28th column while keeping the layout neat and aligned.

Next, the For... Next loop is used to access each record in memory, with the control variable j serving as the array index. The loop runs from 0 to 4, processing all five records.

The first line inside the loop constructs a label in the format (1), (2), and so on, to indicate the sequence of records as they were entered using the InputBox() function.

The next statement uses a With... End With structure on the PSales(j) record. This allows direct access to its elements (Desc, Quantity, etc.) without repeatedly referencing the top-level array name.

Within the With block, the TotalPrice element is calculated and assigned its value. The following line then prints the current record to the Debug Window. This process repeats for each record, ultimately displaying all items in the array.

By now, you should have a good understanding of the usefulness of User-Defined Types. With a little further exploration, you can even save these records from memory into an Access table.

Keep in mind that a User-Defined Type is usually designed for a specific task, and its structure may not be suitable for general-purpose use like built-in variables. For example, elements such as Desc, Quantity, UnitPrice, and so on, may not be relevant outside the context for which the type was created.

There are, however, many interesting ways to leverage User-Defined Types, and we will continue this discussion in next week’s post.

Function Parameter ByVal and ByRef Usage

a.p.r. pillaiJuly 02, 2018Functions, How Tos, msaccess functions, msaccess tips, Variables 1 comment

Function Parameter ByVal and ByRef Usage.

Before taking up the above subject, let us examine some fundamentals of variables for the novices' benefit.

When we define a variable in VBA or any other programming language, the computer allocates some memory cells (let us skip the technical aspects and stay with the basics to store values passed to it.

In a layman's analogy, we can imagine a variable as a box with the name 'Apple' or whatever name we give to the box, and use that name to pick the value stored in it. Assume that you have put 5 in the Apple box. We can give these apples to someone in two ways.

Make a copy of the apples (the number) from the box ourselves, and put them into another box and pass it. The target box's name will be different. The recipient of the new box can work with his apples, like adding more apples to his box or removing some from it, and so on. There will not be any change to the number of Apples in the original Box.
We can tell the other person (or Function) which area of the room (location) you have kept the original box of apples, get the box contents from there, and work with it. In this room (or within the function body), there may be other boxes (Variables) with different names and contents.

Making Copies of Original Values.

In the first method explained above, unlike the physical box, you can make copies of the original value and store them in different Variables. The original value will not change in the first Variable. The Function that gets the new Variable with the copy has no access to the first Variable. He can do whatever he wants to do with the copy he has.

In the second case you can tell the location of the Apple_Box1 to the other Function so that it can go there and find its contents and do whatever the Function wants to do with them (add, subtract, multiply, etc., or use it as part of other calculations) or whatever operations you would like to do with them.

To prove the first point above, let us take a closer look at the next two example functions Test_1A() (the calling function) and Test_1B() (the called function with the copy of the value).

Method-1 Examples:

Public Function Test_1A()

'Define two variables(boxes)to hold values of
'Long Integer type
Dim Apple_Box1 As Long, ApplesReceived As Long

'Put an initial value of 10

'into the variable

Apple_Box1 = 10

'sending a copy of Apple_Box1 Value to Apple_Box2

'Whatever value changes happened in Apple_Box2 in Test_1B()
'Received back into the third box:ApplesReceived

ApplesReceived = Test_1B(Apple_Box1)

'call funcction Test_1B() with the value
'Display the result in MsgBox.
MsgBox "Apple_Box1: " & Apple_Box1 & vbLf & "Apple_Box2 contents: " & ApplesReceived

End Function

In the above program, we are defining two Variables Apple_Box1 and ApplesReceived both to store Long Integer type values. Here, we are not going to bother about what is Long Integer or Short Integer and the range of values that we can store in them, etc.

Next line Apple_Box1 = 10, the value 10 is stored in Apple_Box1.

The next three lines are remarks explaining what we are doing in the next line.

We are calling Test_1B() Function and passing the value of Apple_Box1 to the function to do something with the value received in a Variable (ByVal Apple_Box2). The ByVal before Apple_Box2 given in Test_1B() function states that take a copy of the value from Apple_Box1. The ‘As Long’ appearing after the closing parenthesis indicates that the second function does some calculations with the received value and returns the result to the first function. The new value received is stored in ApplesReceived Variable.

Next line MsgBox() function displays the Apple_Box1 contents and the result value (after modification done to the original copy of the value) received from Test_1B() function.

The Test_1B Function.

Public Function Test_1B(ByVal Apple_Box2 As Long) As Long

'Take Note of the Key-Word 'ByVal' (says take a Copy of the Passed Value)
'Return the value back into the first function Test_1A
'After adding 15 to the original value of 10
'copied into Apple_Box2

Test_1B = Apple_Box2 + 15

End Function

There is only one executable statement in the above function. Immediately after the function definition, four lines of remarks indicate what happens in the function.

We will take a closer look at the next line of the statement. This statement has two parts – the first part appears left of the = sign and the second part is on the right side of the equal sign.

In this expression, the left side of the equal sign will be a Variable or a Function name. By now you will be asking yourself why a function name is there. That will be explained in a moment.

The computer always evaluates the expression given on the right side of the equal sign first and arrives at a single value and moves that result in the Variable given on the left side of the equal sign. Any existing value in the variable will be lost. You can write that expression in two lines to arrive at the same result as below:

Apple_Box2 = Apple_Box2 + 15

Test_1B = Apple_Box2

In the first expression, you can see that we have used the Apple_Box2 variable on the left side and right side of the = sign. As I said earlier the expression on the right side of the equal sign is evaluated first. So it takes the existing value of 10 from Apple_Box2 for calculations and Adds 15, arriving at the single result value of 25, and moves that value into Variable Apple_Box2, replacing the earlier value of 10.

If the Function name (the function name given in the first line of Code) appears on the left side of the equal sign, then the meaning of the statement is that the final result of the expression must be returned to the Calling Function. Here, the Function Name acts as a Variable with the Data Type (As Long) specified immediately after the closing brackets on the first line.

This function name appears on the calling statement in the calling function to the right side of the = sign and a Variable Name on the left side of the = sign that saves the received result value (ApplesReceived = Test_1B(Apple_Box1).

Method-2 Examples:

In this method, we have defined only one variable Apple_Box1 as a Long Integer Type. In the next line, the Variable is loaded with an initial value of 10. The next two lines are remarks explaining what is happening in the next line that calls the second function Test_1D().

Compare this statement with the statement that calls Test_1B. Unlike the statement that calls Test_1B() the Function Name Test_1D and the function parameter, Variable Apple_Box1 only appear here. The opening and closing brackets are omitted from the function name. The parameter variable is the second item. Test_1D() function is not returning any value back into the calling function Test_1C. Therefore, we don't need to write this line of code in the form of an expression as we did in Test_1A Function. But, you cannot write the statement as:

Test_1D(Apple_Box1).,

Once we use the parenthesis (normally used with the function name) around the parameter variable, then VBA assumes that some value is returned from the called function and you are forced to write it like we did it in Function Test_1A:

x = Test_1D(Apple_Box1) 'Expression
'OR use Call statement 
Call Test_1D(Apple_Box1)

There will not be any value in the variable x because no value is returned from the called function.

If you feel comfortable with this method, then you may do so. You will be defining one more variable for this purpose and your program takes up more memory.

The usage of the Call statement requires the parenthesis around the parameter variable. If no parameters to pass to the called function even then you should use the opening and closing parenthesis at the end of Function Name like Call Test_1D().

When control returns from Test_1D() the next line displays the changed value in Apple_Box1 Variable.

Public Function Test_1C()
Dim Apple_Box1 As Long

'put 10 into Variable
Apple_Box1 = 10

'here Test_1D function takes the
'location address of Apple_Box1

Test_1D Apple_Box1 'compare this statement with Test_1A function

MsgBox "Apple_Box1: " & Apple_Box1 & vbLf & "Apple_Box2 contents: " & ApplesReceived

End Function

Test_1D() function takes the location address of the parameter variable passed to it. It works directly with the value stored in the variable Apple_Box1's own location.

Public Function Test_1D(ByRef Apple_Box2 As Long)

Apple_Box2 = Apple_Box2 + 15

End Function

Test_1D() takes the location address (this is the memory location, number of the variable) of variable Apple_Box1 into variable Apple_Box2, defined in the parameter area of Test_1D() function.

Now, look at the expression:

Apple_Box2 = Apple_Box2 + 15

Apple_Box2 contains the location address, not the contents of the Apple_Box1 variable. But, no need to make any change in the expression to do any kind of calculations. The computer uses the location address to pick the value from there and use the value in calculations.

Even though we have used ByRef to the Parameter Variable, to accept the location address of the variable passed to it (always a number irrespective of different variable types), the rest of the parameter definition is like any other variable with variable Type (as Long) specification. Compare Test_1D() function definition with the Test_1B(). Test_1D doesn't have the As Long at the end of the line because it is not returning any value from the function but it changes the original value at its location directly.

You may omit the usage ByRef from the Function declaration. By default, VBA assumes that the Function Parameter declaration is ByRef (By Reference) if you have not explicitly defined the parameter as ByVal like:

Public Function Test_1D(Apple_Box2 As Long)

Each Variable Type, like Byte, Integer, Long (integer), Double, String, etc., gets allocated with enough memory cells, besides its own address, to hold their maximum range of values. This is different between Programming Languages: VBA, C, C++, C#, etc.

In Conclusion.

We don't have to bother about going too deep into all those things, but it does no harm to have a basic understanding of them.

If you get in touch with the C language and its variants, like the examples given above, you need to deal with these things, sooner or later.

Our discussion here was on a single variable and its value. How we can work with an Array of Variables and Values. We will explore its methods in the coming weeks.

Macros and Temporary Variables

a.p.r. pillaiApril 03, 2011Macros, Variables 1 comment

Macros and Temporary Variables.

If you are using Microsoft Access 2007 or later, there is a useful new feature: the SetTempVar action in Macros, which allows you to define global variables. Once defined, these variables can be used across other macros, event procedures, forms, or reports. The temporary variables remain in memory until you explicitly clear them using the RemoveTempVar action (for a single variable) or the RemoveAllTempVars action (to clear all). All temporary variables are automatically removed from memory when you close the database.

TempVar Usage in Macros.

Let us try a quick example to understand the usage in a macro:

Select Macros from the Create Menu.
Select SetTempVar Action in the first row.
Type myName in the Name argument.
Type the expression Inputbox(“Type your Name”) in the expression argument.
Save the Macro with a name (say macDefineVar).
Right-click on the macro and select Run from the shortcut menu (or Double-click) to execute the Macro. The InputBox Function will run and prompt for a value.
Type your name and click the OK CommandButton.

Your name is stored in the Variable myName. We have used the Function, InputBox() in the expression argument. You can use constants, functions, or expressions to assign values to the variable myName.
Open a new form in the design view.
Insert a TextBox in the details section of the Form.
Type the expression = 'Tempvars!myName' in the Control Source property.
Change the form from Design View to Form View.
Your name will now appear in the text box. The example demonstrates how to define a temporary variable and reference it in expressions in a form. Next, let us see how to remove this variable from memory.
Close the Form.
Select Macro from the Create menu to open a new macro in the design view.
Select RemoveTempVar from the Action list.
Type myName in the Name parameter.
Save the macro named macRemoveVar.
Double-click on the macRemoveVar macro to execute it.
Open the Form again to check whether your name still appears in the TextBox.

The text box will be empty, indicating that the variable myName does not exist in memory. The Remove TempVar action needs a variable name as a parameter.

TempVar Usage in Query.

Let us take this a step further and build something more practical for real-world scenarios. This time, we will calculate the order-wise percentage based on the total Order quantity. We explored a similar problem earlier in the blog post 'Percentage in Total_Query'.

The requirement here is to find the quantity summary to calculate the percentage of each individual order. In the earlier example, we accomplished this by creating a separate query to calculate the Total order quantity and then linking it with a second query, grouped by Order Number, to compute the percentages.

Here we will initialize a Temporary Variable with the sum of Quantity and use the Variable name in the percentage calculation expression.

Import the Order Details table from the Northwind sample database.
Select Query Design from the Create menu; don't select any table or query from the displayed list.

Change the Query in SQL view; copy and paste the following SQL string and save the Query with the name OrderPercentageQ:

SELECT [Order Details].OrderID, First([Order Details].UnitPrice) AS UnitPrice, Sum([Order Details].Quantity) AS Quantity
FROM [Order Details]
GROUP BY [Order Details].OrderID;

Open the macro macDefineVar in the design view.
Change the variable name, myName, to TotalQuantity (the myName variable will remain in memory).
Change the expression InputBox(“Enter your Name”) to DSum(“Quantity”,”[Order Details]”). Do not add the = symbol at the beginning of the expression. Save the macro with the change.
Double-click the macro to run, then calculate the total quantity and store it in the temporary variable TotalQuantity.

We will add a new column in the OrderPercentageQ to calculate the order-wise percentage of Total orders.
Open a new Query in SQL View.

Copy and paste the following SQL String into the SQL editing window of the new Query and save it with the name OrderPercentageQ2:

SELECT [Order Details].OrderID, First([Order Details].UnitPrice) AS UnitPrice, Sum([Order Details].Quantity) AS Qty, Sum([quantity])/[tempvars]![totalQuantity]*100 AS Percentage
FROM [Order Details]
GROUP BY [Order Details].OrderID;

Open the Query in Design View and check how we have written the expression in the last column to calculate the percentage using the temporary variable [tempvars]![totalQuantity].
Change the Query View to Datasheet View to display the Order-wise percentage of Total Quantity.

The TempVar Usage in VBA.

We can work with the Temporary Variable (TempVars Object) in VBA.

With the Add method, we can define a Temporary Variable and assign an initial value.

Add() method of the TempVars Object:

Syntax: TempVars.Add "Variable Name", "Initial Value"

Example-1: TempVars.Add "TotalQuantity", DSum("Quantity", "[Order Details]")

Example-2: TempVars!TotalQuantity = DSum("Quantity", "[Order Details]")

You can define up to 255 TempVars variables.

Remove method of TempVars Object:

The Remove() method erases only one variable and frees the memory.

Syntax: TempVars.Remove "Variable Name"

Example: TempVars.Remove "TotalQuantity"

RemoveAll method of TempVars Object:

The RemoveAll() method removes all the temporary variables defined with the Add() method.

Syntax: TempVars.RemoveAll

Example: TempVars.RemoveAll

The Count property gives the count of all temporary variables defined in memory:

Example: Debug.Print TempVars.Count returns the count of temporary variables defined in memory

TempVars Item Indexes.

Each temporary variable stored in memory is assigned an index number, starting from 0 up to (total number of variables – 1). A variable can be referenced by its Item index, which allows you to read its Name or Value, or even assign a new value to it. The Debug.Print TempVars.Item(0).Name prints the variable Name.

SumofQuantity = TempVars.Item(0).Value

You can also use this reference to modify the existing value in the temporary variable.

TempVars.Item(0).Value = TempVars.Item(0).Value + 1

You should not use a subscript beyond the existing number of temporary variables in memory; otherwise, an error will occur. If you have defined 5 variables, then the valid index numbers are 0 to 4.