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

Introduction.

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:

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.
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, 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.
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
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
Next

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.
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 variable. Here 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.
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).
The First-line within the For … Next 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.
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.
The next executable line calculates the Total Price value and assigns it to the TotalPrice element.
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.

Function Parameter ByVal and ByRef Usage

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

Introduction.

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

When we define a variable in VBA or in any other programming language, the computer reserves some memory location and allocates some memory cells (the number of cells allocated depends on the declared variable type, like Integer, Double, String, etc.) to store values passed to it.

In layman's analogy, we can imagine a variable as a box with the name 'Apple' or whatever name we give to the box and uses 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 copies 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 copy of the apples, like adding more apples in his box or removing some of them, etc. There will not be any change in the first box's contents.
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

Introduction.

If you are using Microsoft Access 2007 or a later version of Access then there is something new for you. You can use the SetTempVar action in Macros to define Global Variables. After that, you can use the values in those variables in another macro or in an event procedure, or on the Form or Report. The temporary variables remain in memory till you clear them with the RemoveTempvar macro action or remove all the variables with RemoveAllTempVars macro action. All variables will be cleared from memory when you close the database.

The TempVar Usage in Macros.

Let us try a quick example to understand the usage in 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 will prompt for a value to type.
Type your name and click the OK Command Button.

Your name is stored in the Variable myName. We have used the Function, InputBox() in the expression argument. You can use constant values, functions, or expressions to assign values to the variable myName.
Open a new form in the design view.
Insert a Text Box 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 be displayed in the text box. The above example shows how to define a temporary variable and how to reference it in expressions on a Form. Let us learn how to remove this variable from memory.

Close the Form.
Select Macro from the Create menu to open up a new macro in the design view.
Select RemoveTempvar from the Action list.
Type myName in the Name parameter.
Save the macro with the name macRemoveVar.
Double-Click on the macRemoveVar macro to execute it.
Open the form again to check whether your name still appears on the text box on the form or not.

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

TempVar Usage in Query.

Let us do something better than this and more useful in real-world solutions. Let us calculate the Order-wise Percentage on the sum of Orders Quantity. We have worked on this problem earlier in the blog post: Percentage on Total Query. What we need here mainly is the sum of Orders' Quantity to calculate individual order percentages. In the earlier example, we have used a separate Query to calculate the sum of Orders and linked it with a second Query on Order Number to calculate the percentage.

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 (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 on the macro to run and calculate the total quantity and store the value into the temporary variable TotalQuantity.

We will modify the OrderPercentageQ with the addition of a new column that calculates 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 into 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 to it.

Add() method of 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 a total of 255 temporary variables in this way.

Remove method of TempVars Object:

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

Syntax: TempVars.Remove "Variable Name"

Example: TempVars.Remove "TotalQuantity"

RemoveAll method of TempVars Object:

The RemoveAll() 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 defined in memory has an index number starting from 0 to the total number of such variables in memory –1. Each variable can be referenced by the Item index and can be used to read the Name of the variable or its Value or to assign new values to the variable.

Debug.Print TempVars.Item(0).Name prints the name of the variable.

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.