Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Passing Two Dimensional Array to Function

First of all, I have some good news for you, our website: LEARN MS-ACCESS TIPS AND TRICKS has been selected by https://blog.feedspot.com panellist as one of the Top 5 Microsoft Access Blogs on the Web and awarded the Badge given below.

Microsoft Access Blogs

You can find some of the top ranked Blogs on a variety of subjects, RSS Feeds, Youtube Channels,  top News Websites and others.  Subscribe to Blogs/News Feeds, or topic of any other area of  interest, and get regular updates from www.blog.feedspot.com to your Inbox, as and when it happens, by providing your E-mail address.

Coming back to our VBA lessons, last week we have briefly touched the topic of passing Arrays as Parameter to Function ByRef method.  We were able to work with the singly dimensioned Array in it's original location within the called function, to sort the values in descending order.  For this example we have loaded the array with values for only five elements but the array can have many rows of values.

An Array can be re-dimensioned, for more rows or less rows later on in the program more than once, if we cannot determine the length of the array in advance. In that case you should not specify the number of elements in advance in the initial Dimension Statement.


'These Arrays cannot be redimensioned
'Array has predefined number of elements
Dim Products(1 to 5) as String
Dim Products(5) as String'The number of elements are predefined

'This Array can be redimensioned
'Number of elements required are not known in advance. 
Dim Products() as String
'ReDimension the Array for required number of elements
'Remember the array index numbers will be 0 to 4, total 5 elements
ReDim Products(5) As String
'later on in the program
'all the values assigned to first 5 elements will be lost.
ReDim Products(7) As String 
ReDim Products(Ubound(Products)+2) As String
'To preserve the values already assigned to first 5 elements
ReDim Preserve Products(7) As String

The important point to note here is that the ReDimensioning should take place in the calling program itself, if need arises in the called function, before passing the Array to the called program.  Even though the Array is passed to the called function ByRef and we are able to work with the passed variable directly, we cannot re-dimension the array to increase/decrease the number of elements in the array, from within the called function.

Now, we are aware of the limitation of the array when passed to the called function, we will try to pass a two dimensional array of Sales data to a function and print the values on the Debug Window.  The Sales data array have the following values in a record:

  1. Product Name - Text
  2. Quantity  - Intiger
  3. Unit Price – Double
  4. Total Value  - Double (will be calculated in the called function)

The sales record shows that the data fields have different data types.  To pass each field values separately to the called function we need four different array Variables with different data types (1. String type for Product Name, 2. Integer for Quantity, 3. Unit Price & 4. Total Price with Double precision number) and load each field data into separate variables.

We are going to do it differently here.  We will be using only one Variable to pass all four field values to the called function.  We will define a single Variable as a two dimensional Array of Variant data type, with four rows (each row represents a single record) and four columns (each column is a field).

We are not storing the above column names anywhere in the array and it is assumed that the first column is Product name, next column to the right of the first one is Quantity, next column Unit Price and the last column is Total Price.  Since, the Variant Data Type variable has a peculiar behavior that the data type of a cell changes automatically to match with the data type is assigned to it.

Before writing it as a complete function we will  look at the dimension statement and how the sales values are assigned into each element of the array. 

' variant Variable can hold different data types in each element 
Dim Product(4,4) as Variant 
Product(0, 0) = "Hard Disk": Product(0, 1) = 5: Product(0, 2) = 125.5: Product(0, 3) = 0

Product(1, 0) = "Keyboard": Product(1, 1) = 2: Product(1, 2) = 25.25: Product(1, 3) = 0

Product(2, 0) = "Mouse": Product(2, 1) = 3: Product(2, 2) = 13.75: Product(2, 3) = 0

Product(3, 0) = "DVD Writer": Product(3, 1) = 10: Product(3, 2) = 30: Product(3, 3) = 0

In the above example we have only four records (or four rows or lines of data) in the Table.  There are four fields (four columns) in each record.  Each cell is numbered with two numbers (row index number, column index number), separated by a comma.  Left-side number is the row index number of the column and the number to the right side of comma is the column index number.  Both number range is 0 to 3 (4 rows and 4 columns).  First column (column index 0) is Product Name, 2nd Column  (column index 1) Quantity, 3rd Column (index number 2) Unit Price and the last one is (index number 3) Total Value, which will be calculated and assigned later.

The entire array of these values can be passed to a function as ByRef parameter and we can work with the array directly from within the called function.  If you are new to two dimensional arrays it will be little confusing at first to comprehend the arrangement of values and as how to address each cell to work with it.   This become more difficult when there are calculations involving cells of the same row. 

We have a better way to deal with this problem with User-Defined Variables.  Yes, you heard me correctly we can define our own Variable Type,  besides the built-in variables with default data types.  We will explore this topic further in next week and I am sure you will be happier with this new idea, after struggling with these rows and columns set up.  Believe me this is a very powerful feature once you are familiar with these kind of data arrangements.  You can work with 5 rows, 500 rows or 5000 rows with the same statements in the function.

Public Function ProdList()
Dim Products(4, 4) As Variant
Dim j As Integer, k As Integer, stridx As String
' 0 = Description
' 1 = Quantity
' 2 = Unit Price
' 3 = Total Price to be calculated
'Array elements index numbers are 0 to 3
For j = 0 To 3
 For k = 0 To 3
    stridx = "(" & j & "," & k & ")"
    Select Case k
        Case 0
          Products(j, k) = InputBox("Product Name" & stridx)
        Case 1
          Products(j, k) = InputBox("Quantity" & stridx)
        Case 2
          Products(j, k) = InputBox("Unit Price" & stridx)
        Case 3
          Products(j, k) = 0 'total value will be calculated
    End Select
    Next k
Next j

Call ProdPrint(Products)

End Function

We have defined the Products variable as Variant data type with 4 rows, 4 columns for assigning values of different data types in them.   Next line is setting up three more variables: j & k as control variable for For…Next loops, variable stridx for building a string to display the index numbers of cells when displayed  in the InputBox() function Prompt text.

Two nested For…Next loops are set up to control the Variable index numbers of rows and column values.  The outer loop controls the row number and the inner loop with k control variable  is used for column index number.

Next we have used the Select Case…..End Select statements runs several other statements depending on value in the control variable j. If value in variable k=0 (and j=0) then the Inputbox() function runs below the Case 0 test and gets the Product Name and assigns it to Products(0,0) cell.  When k=1 then the InputBox() gets the value of Quantity and assigns it into Products(0,1) cell. When k=2 gets Unit Price and in the next step assigns Products(0,3)=0. The outer loop with control variable run only once with zero value as row index number.

This action repeats 3 more times  for the outer For…Next loop to control the row index number and each time the inner For…Next loop runs four times to control the column numbers to get values from the User for each cell for the row number in j control variable.

When control comes out of the loop the ProductPrint() Function is called by passing the Products variable as parameter to the function.

Public Function ProdPrint(List As Variant)
Dim j As Integer, k As Integer

'Ubound() function will get the
'total rows in the array - first value in the Dim statement
For j = 0 To UBound(List, 1) - 1
      List(j, 3) = List(j, 1) * List(j, 2)
    For k = 0 To UBound(List, 2) - 1 'get second value in Dim statement
        Debug.Print List(j, k),
    Next k: Debug.Print
Next j

End Function

The ProductPrint() function takes the Products Array's location address (ByRef) method. If you omit ByVal or ByRef key word before the Parameter variable it assumes that Variable List holds location reference of the Products (parameter passed ByRef).

As in the earlier program two integer variables j & k  are defined as control variables for outer and inner For…Next loops.  We need these For…Next loops to control the index numbers (rows & columns) to access each element of the array.  The starting value of the Loop is 0 but to calculate the end value we have used another function Ubound() (get Upper Boundary) value of the Array dimension.  In the first program we have written the control value as 0 to 3.  Here also we could do that but here we have used the Ubound() function to find the row and column numbers. This will calculate the Array size correctly, if the Array size is changed through ReDim statements.

Ubound(List, 1) gets the number of rows value, which is 4. But the row index numbers starts from 0 in memory so we have used index numbers 0 to 3 in the For…Next loop. The second value 1 in bracket of Ubound() function asks for the numbers of rows in the array.  Since, the row index number starts from 0 we are subtracting 1 from the number of rows value (4-1).

UBound(List, 2) gets the number of columns value.  The second parameter value is optional, if it is omitted it will only get the row value.  If the variable is a singly dimensioned array then the second value is never used.

The statement immediately after the first For…Next loop ‘List(j, 3) = List(j, 1) * List(j, 2)’ calculates the Total Price of each item and assigns it at the right most cell before printing the values of the Sales record item within the next For…Next loop, on the debug window.

The comma at the end of the Debug.Print statement positions the next item on the 14th column on the same line after the item printed earlier.

The empty Debug.Print statement, immediately after the inner Next statement without a comma at the end brings the print control back to the first column of the next line, positions correctly to start printing the next Sales Record.

If we place a semi-colon (;) at the end of the Debug.Print statement the print-head positions to the next character position, without leaving any space between the items printed.

Next week we will explore the User-Defined Variable with mixed data type. We can give appropriate names for each element of the array rather than assuming names as we did in the above examples. I am sure it will be very interesting comparing the difficulty we had in memorising each array elements logical names according to their array position.


Function Parameter Array Passing

Last week we have explored the usage of ByVal (By Value) and ByRef (By Reference),  in the Function Parameter, to pass the value from  a Variable from the calling function or the Location Address of a Variable to the Called Function.  If you have not visited the earlier page the link is given below:

Now, we will learn:

  1. how to pass the location address of a single element of an Array  to the called Function and change it’s value
  2. how to pass the Location Address of an array and sort the values in the array in Descending Order.

First, let us write two small programs for the first example. The first Program Code is given below.

Public Function ArrayArg_Test1()
Dim NumArray(1 To 5) As Integer, j As Integer

'load array with numbers 1 to 5
For j = 1 To 5
   NumArray(j) = j

'pass a single element value
Call ArrayArg_Test2(NumArray(4))

'Print the Array values after change
For j = 1 To 5
  Debug.Print j, NumArray(j)

End Function

In the first line of code we have defined an Array Variable (NumArray()) with five elements to store Integer type values. Variable j is a control variable defined for For...Next Loop.

Inside the For....Next loop the Array is loaded with values 1 to 5, i.e. NumArray(1) = 1, Numarray(2)=2 and so on up to 5 elements.

Next, we call the function ArrayArg_Test2() function with the NumArray(4) element passed as parameter.  The number 4 within brackets is the index number of the element not the value itself.  But, we have the value 4 in that element too.  The called function ArrayArg_Test2() receives the passed value/location depends on the parameter definition there. If we use ByRef  or omit the ByRef specification before the Variable Name and Data Type then the called function takes the passed variable's location address to work with the value stored in the original Variable.  We will go with the second method and will not use the ByRef specification in the next function’s  parameter definition.

Within the next For...Next loop the array contents are printed in the Debug Window.  If any change done by the function ArrayArg_Test2() will show up in the printed list.  Since, we already knew that the array elements 1 to 5 contains the values 1,2,3,4,5 we have not printed those values before calling the second function.

The ArrayArg_Test2() Function VBA Code is given below:

Public Function ArrayArg_Test2(NA As Integer) 'The word ByRef is omited
'multiply NumArray(4) value * 5 = 20
    NA = NA * 5
End Function

The Variable NA is assigned with the NumArray’s 4th element location address.  The ArrayArg_Test2() picks the value from NumArray(4) itself, multiply it by 5 and store the result back into the same location.

This was working with a single element of an Array.  What about passing the full Array’s location address and work with hundreds of elements of this array in the called Function. 

We will use the same array we have used in the above example and sort the values in Descending order by passing the full array to the Sorting function

The modified version of the First Function Code is given below. 

Public Function ArrayArg_Test3()
Dim NumArray(1 To 5) As Integer, j As Integer

For j = 1 To 5
   NumArray(j) = j

'Pass the array to the called function
Call ArrayArg_Test4(NumArray())

'Print the Sorted Array
For j = 1 To 5
  Debug.Print j, NumArray(j)

End Function

Check the function call statement. NumArray() is passed without the element number, as we did in the earlier example. The opening and closing brackets are required along with the array variable name to indicate that the parameter passed is an array not a single variable.

When control is returned from the ArrayArg_Test4() function the sorted list of numbers are printed in the debug window. The value printed at the left side is the array element number and the right side value is array value itself,  sorted in Decending order.

The Data Sorting Program is given below:

Public Function ArrayArg_Test4(apple() As Integer)
Dim j As Integer, k As Integer, Temp As Integer

'Bubble Sort the Array in Descending order
' 1st loop runs maximum array elements minus 1 times

For j = 1 To 5 - 1 ' in place of 5-1 you may use Ubound(apple)-1

   ' inner loop starts with outer loop's current value + 1
   ' and runs to the maximum number of array elements times

      For k = j + 1 To 5 ' replace 5 with Ubound(apple)

     If apple(k) > apple(j) Then 'if second value is greater
        Temp = apple(j) 'copy 1st value to Temp Variable
        apple(j) = apple(k) 'move greater value up
        apple(k) = Temp ' move the smaller value down
     End If
    Next k ' compare next two elements
Next j
End Function

If you want to sort the values in Ascending Order then the only change in this program required is to change Greater Than (>) symbol to Less Than (<) symbol. The number we have loaded into the array was already in Ascending Order.

If you remove the 5-1 constant from the first For...Next loop and replace with Ubound(apple)-1 and replace 5 with Ubound(apple) in the second loop you can use the program to sort the array with any number of elements without change in the Program.

Notice that we have omitted the ByRef specification in the called ArrayArg_Test4()Function Parameter definition. VBA, by default, takes it as ByRef Parameter Variable.


Function Parameter ByVal and ByRef Usage

Before taking up the above subject let us look at some fundamentals on 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.

  1. Make copies of the apples (the number) from the box ourselves and put it 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 add more apples into his box or remove some of them etc.  There will not be any change in the first box contents. 
  2. 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.

In the first method explained above, unlike the physical box, you can make copies of the original value and store them into different Variables.  The original value will not change in the first Variable.  The Function that gets the new Variable with the copy have 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 it's 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 their range of values that we can store in them etc.

Next line Apple_Box1 = 10, 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 value) received from 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 indicating what happens in the function.

We will take a closer look at the next line of statement. This statement have two parts – first part appears left of the = sign and the second part is at 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 as why a function name there. That will be explained in a moment.

The computer always evaluates the expression given at the right side of the equal sign first and arrives at a single value and moves that result into the Variable given at 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 to the left-side and right side of the = sign. As I said earlier the expression at 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 to it, arriving at the single result value of 25 and moves that value into Variable Apple_Box2, replacing earlier value 10.

If the Function name (the function name given at the first line of Code) appears at the left side of 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 Long Integer Type.  In the next line the Variable is loaded with an initial value of 10.  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 is only appears 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:


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.

Usage of 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 Apple_Box1 variable. But, no need to make any change in the expression to do any kind of calculations.  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), 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 it's 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 define 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 it's own address, to hold their maximum range of values. This is different among Programming Languages: VBA, C, C++, C# etc.

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 C language and it's 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 it's value. How we can work with an Array of Variables and Values. We will explore it's methods in the coming weeks.


DIRectory and File Copy Utility

Last week we have seen how to use Dir() DOS Command, it’s ability to read files from the Disk  one by one and display it on the Debug Window.

In continuation of that, we will get familiar with a very useful VBA FileCopy Statement combined with DIR()  Command to read and transfer files from one folder to a different location on the disk.  The files can be of any type, like *.pdf, *.docx, xls or *.* (all files).

The files will be read from a Folder and listed in a list-box from the selected folder, specified in a text box.,  with the use of DIR() Command.  All the files in the list or selected ones can be copied to a different location specified in a text box, defined as target location.

The design view image of a Form created for this purpose is given below for reference:


The design is simple with two text boxes, one Listbox,  three Command Buttons and a Label Control to display messages from this Utility Program.  You can download this Utility Form in a sample database at the end of this article.

These are the names of the Controls on the Form:

  1. Top Text box : Source
  2. Text Box 2     :  Target
  3. List Box          :  List1
  4. Top Command Button : cmdDir
  5. Second Command Button : cmdSelected
  6. Last Command Button : cmdClose
  7. Bottom empty Label Name : msg

Note: If you are designing this form yourself then ensure that you give the controls the same names as given above, because the VBA Code that you are going to copy, paste in the VBA Module references all these names in the Code

Besides the above main controls there is a Label Control below the first Source Textbox showing  examples as how to specify Source File Path correctly.

The label control at the bottom of the form shows messages that pops up during validation checks of the inputs and when errors detected, during the execution of the VBA Code.

Image of a sample run of the FileCopy Utility is given below:


You may create this User Interface with the names of the Controls as given above.  After designing the form with the correct names for the controls, display the VBA Window of the Form, copy and paste the following code into the Form’s VBA Module:

Option Compare Database
Option Explicit
Dim strSource1 As String
Dim strSource2 As String, strMsg As String

Private Sub cmdClose_Click()
On Error GoTo cmdClose_Click_Error

If MsgBox("Close File Copy Utility?", vbOKCancel + vbQuestion, "cmdClose_Click()") = vbOK Then
   DoCmd.Close acForm, Me.Name, acSaveYes
End If

Exit Sub

MsgBox Err.Description, , "cmdClose_Click()"
Resume cmdClose_Click_Exit
End Sub

Private Sub cmdDir_Click()
'Author : a.p.r.pillai
'Date   : June 2018
'Purpose: Take directory listing
'Rights : All Rights Reserved by www.msaccesstips.com
Dim strSource As String, strMsg As String
Dim i As Integer, x As String
Dim j As Integer, strfile As String
Dim strList As ListBox, LList As String

On Error GoTo cmdDir_Click_Err
msg.Caption = ""

'Read Source location address
strSource = Nz(Me!Source, "")
If Len(strSource) = 0 Then
    strMsg = "Source Path is empty."
    MsgBox strMsg,vbOKOnly + vbCritical, "cmdDir_Click()"
msg.Caption = strMsg
    Exit Sub
End If

'check for the last back-slash location
'this can be used to split the folder name
'and file name type values separately.

i = InStrRev(strSource, "\")

'get the folder name part into the variable
strSource1 = Left(strSource, i)

'take file type (*.docx, *.exl, *.txt etc.) value into a separate
'variable temporarily
If Len(strSource) > i Then
    strSource2 = Right(strSource, Len(strSource) - i)
End If

'define Listbox object
Set strList = Me.List1

'Read the first file from the folder
strfile = Dir(strSource, vbHidden)
If Len(strfile) = 0 Then
    strMsg = "No Files of the specified type: '" & strSource2 & "' in this folder."
    MsgBox strMsg, vbCritical + vbOKOnly, "cmdDir()"
    msg.Caption = strMsg
    Exit Sub
End If

j = 0
LList = ""
Do While Len(strfile) > 0
   If Left(strfile, 1) = "~" Then 'ignore backup files, if any
      GoTo readnext:
   End If
    j = j + 1 'File list count
    LList = LList & Chr(34) & strfile & Chr(34) & ","
    strfile = Dir() ' read next file

LList = Left(LList, Len(LList) - 1) ' remove the extra comma at the end of the list
strList.RowSource = LList 'insert the files list into the listbox RowSource property
strList.Requery 'refresh the listbox
msg.Caption = "Total: " & j & " Files found."

Me.Target.Enabled = True

Exit Sub

MsgBox Err.Description, , "cmdDir_Click()"
Resume cmdDir_Click_Exit

End Sub

Private Sub cmdSelected_Click()
'Author : a.p.r.pillai
'Date   : June 2018
'Purpose: Copy Selected/All Files to Target Location
'Rights : All Rights Reserved by www.msaccesstips.com

Dim lstBox As ListBox, ListCount As Integer
Dim strfile As String, j As Integer, t As Double
Dim strTarget As String, strTarget2 As String
Dim chk As String, i As Integer, yn As Integer
Dim k As Integer

On Error GoTo cmdSelected_Click_Err

msg.Caption = ""
'Read Target location address
strTarget = Trim(Nz(Me!Target, ""))

'validate Destination location
If Len(strTarget) = 0 Then
   strMsg = "Enter a Valid Path for Destination!"
   MsgBox strMsg, vbOKOnly + vbCritical, "cmdSelected()"
   msg.Caption = strMsg
   Exit Sub
ElseIf Right(strTarget, 1) <> "\" Then
      strMsg = "Correct the Path as '" & Trim(Me.Target) & "\' and Re-try"
      MsgBox strMsg, vbOKOnly + vbCritical, "cmdSelected()"
      msg.Caption = strMsg
      Exit Sub
End If

'Take a count of files in listbox
Set lstBox = Me.List1
ListCount = lstBox.ListCount - 1

'take a count of selected files, if any, for copying
i = 0
For j = 0 To ListCount
If lstBox.Selected(j) Then
  i = i + 1
End If

'identify user's response for copy
If (i = 0) And (ListCount > 0) Then
       strMsg = "Copy all Files..?"
       Me.cmdSelected.Caption = "Copy All"
       strMsg = "Copy Selected Files..?"
       Me.cmdSelected.Caption = "Copy Marked files"

End If


'get copy option from User
yn = MsgBox(strMsg, vbOKCancel + vbQuestion, "cmdSelected_Click()")

'Run Copy selected option
If (i = 0) And (yn = vbOK) Then
    GoSub allCopy
ElseIf (i > 0) And (yn = vbOK) Then
    GoSub selectCopy
    Exit Sub
End If

'disable Copy button to stop a repeat copy of the same files.
'Remarks: User can make fresh selections from the same list
'To copy them to the same target locatiion.
'Or to a different location by specifying different Path
'in the Destination Text Box
Me.cmdSelected.Enabled = False

'Display copy status
strMsg = "Total " & k & " File(s) Copied." & vbCrLf & "Check the Target Folder for accuracy."
MsgBox strMsg, vbInformation + vbOKOnly, "cmdSelected_Click()"
Me.msg.Caption = strMsg

Exit Sub

k = 0
For j = 0 To ListCount
    strfile = lstBox.ItemData(j)
    strSource2 = strSource1 & strfile
    strTarget2 = strTarget & strfile
    FileCopy strSource2, strTarget2
  'give enough time to copy the file
  'before taking the next file
  k = k + 1
  t = Timer()
  Do While Timer() > (t + 10)
    'do nothing

k = 0
For j = 0 To ListCount
   If lstBox.Selected(j) Then
        strfile = lstBox.ItemData(j)
        strSource2 = strSource1 & strfile
        strTarget2 = strTarget & strfile
            FileCopy strSource2, strTarget2
               'give enough time to copy the file
               'before taking the next file
               k = k + 1
                t = Timer()
                Do While Timer() > (t + 10)
                    'do nothing
   End If

MsgBox Err.Description, , "cmdSelected_Click()"
Me.msg.Caption = Err.Description
Resume cmdSelected_Click_Exit

End Sub

Private Sub List1_AfterUpdate()
On Error GoTo List1_AfterUpdate_Error
Me.cmdSelected.Enabled = True
Exit Sub

MsgBox Err.Description, , "List1_AfterUpdate()"
Resume List1_AfterUpdate_Exit
End Sub

You may save the Form with the name FileCopy.

Note: FileCopy is a VBA Statement not a built-in Function.

You may copy different set of files from the list of files displayed in the List Box to different Target Folders by selecting the files (after de-selecting earlier selections) and after changing Destination Location address in the Text Control.

You may download the sample database with the VBA Code from the Link given below:

Download (2003) FileCopy.zip

Download FileCopy2007.zip

DIR Getting File Names From Folder

We all know Dir() Function from the time of Windows DOS Operating System.  This is the first Command introduced to those who sit on a Personal Computer to learn how to use Computers.  This Command have several options, to get the output from the disk in so many ways, under Windows Operating System.  You can take a full list of Folders, Sub-folders and Files from the hard disk in a single command.  The entire list can be sent to a Printer or save them into text file with the use of redirection symbol (>).

We are not going to use all those options here.  We will see how Dir() Function used in VBA to read file names from a Folder one by one and display them in Debug Window.  Every time we run this function with a Folder Path as parameter it returns the first file name from the folder.  Now, the question is how to get the next few file names or all the files one-by-one from the Folder.

We will try Dir() Function from the Debug Window directly, so that it is easy to understand as how to use this function to get few file names from a folder one after the other.

  1. Open Microsoft Access VBA Window and display Debug Window (Ctrl+G).
  2. Type the following command in the Debug Window and press Enter Key:
    ? Dir("")

    Dir() Function with an empty string as parameter will fetch the first file name from the Current Folder and display it in the debug window. 

    Since, we have not given any specific folder name in the function parameter it looks for files in the active folder on the disk.

  3. Now, issue the following Command without any parameter to get the next file name in the current folder
    ? Dir()
    ? Dir
  4. Each time you run the DIR() command it will get the next file from the folder.
  5. Use a specific Folder Path as parameter, in place of the empty string to get files from that particular folder.
  6. Example:
    ? Dir("D:\Documents\")
    ? Dir("D:\Documents\*.*")

If D:\Documents\ folder doesn't have any files in it then the above command will return and empty string. If you go further and execute the ? Dir command it will end up with an error message.

There is an optional second parameter to the Dir() Command that we have not used in the above examples. Since, this is an DOS Command executed in it's own window we can specify this second parameter to show it's normal window(vbNormal) or hide the execution window (vbHidden) among other options available.

I have written a small function for you to list all the files in a folder in the Debug Window.

Public Function TestDir(ByVal strFolder As String) As String
'Function Usage e.g.: TestDir "D:\Documents\"
Dim j As Integer, strFile As String
'files counter
j = 1
'Run the function with the specified folder in a hidden window
strFile = Dir(strFolder, vbHidden)
'Next steps of Dir() function is nested in a loop
'to read all the files and print in the Debug Window

Do While Len(strFile) > 0
 Debug.Print j & ":" & strFile
 j = j + 1
 strFile = Dir()
End Function

Call the function from the Debug Window by giving the full path of the Folder as parameter.

? TestDir("D:\Documents\")
? TestDir("D:\Documents\*.*")

All the files from the specified folder will be printed with a serial number in the debug window. After reading and printing the last file from the folder the Dir() function executes one more time and end up with an empty string. The Do While condition will prove false and the program stops.

If you need only specific Type of Files to be read and displayed then you may specify the parameter with the file type extension.


? TestDir("D:\Documents\*.xls")

The above example will read only Excel files and print in the Debug window.

I have used the term Function and Command interchangeably. Dir() is referred to as a Function in VBA reference documents and as Command in Disk Operating System documents, both refers to the same operations done in different environments.


Form Recordset and Bookmarks

Bookmarks are stored on individual records of a Recordset, when loaded into memory on a Form.  When a Table or Query linked to a Form is open a unique Id is generated and stored in the Bookmark Property of each record.  When you close the Form this is cleared.  Bookmarks are a two Byte data of String Type.  They are not displayable or printable characters, when printed on screen it simply displays a ? character.

Not all Recordsets have Bookmarks and this can be checked by reading it’s  Bookmarkable Property Value.  If the Bookmarkable Property value is false then this Recordset doesn’t have bookmarks.

If you create a Recordsetclone in VBA from a Form’s (say Form-A) Recordset the Recordsetclone’s bookmark and Form’s Recordset bookmarks will be identical. You can use the StrComp() Function to compare Bookmarks.  Use 0 (zero) as third argument of the function.

But, if you load the same Table on a different Form (say Form-B) at the same time both form’s Recordset bookmarks will not be identical.  When you close and open the Form with same table a second time both session’s bookmarks of records will not be identical.

When an attached Table having no Primary Key is open in a Form that Recordset will not have any bookmarks.

When Form doesn’t have a Record Source Value then addressing Form’s Bookmark Property will trigger an error.  But, when a Table or Query is loaded into the Record Source property of the Form, the Form will have a Bookmark Property only for the Current Record.  You can move the records on the Form and read their bookmarks and save them into different Variables to come back to those records later through VBA.

Let us try a simple example to save the Bookmark of a record on the Form into a variable and use it later to come back to the bookmarked record.

  1. Import the Employees Table from Norwind sample database.
  2. Create a Tabular Form for Employees Table.
  3. On the Footer Section of the Form create two Command Buttons.
  4. Select the first Command Button.
  5. Display it’s Property Sheet (F4).
  6. Change the Name Property value to cmdSave. 
  7. Change the Name Property value of the second Command Button to cmdRestore.
  8. Display the VBA Module of the Employees Form.
  9. Copy and Paste the following Code into the VBA Module:
    Dim bkMark As Variant
    Private Sub cmdRestore_Click()
       Me.Bookmark = bkMark
       MsgBox "Bookmark Restored"
    End Sub
    Private Sub cmdSave_Click()
        bkMark = Me.Bookmark
        MsgBox "Bookmark saved" 
    End Sub
  10. Save and Close the Form.
  11. Open the Form in normal view showing employee records.
  12. Use the record navigation control to move to the 5th record.
  13. Click on the Save Command button to save the Bookmark of the current record in bkMark Variable.
  14. Now, move few records forward on the Form.
  15. Click on the Restore Command Button to quickly make the 5th record current on the Form, by copying the Bookmark from bkMark Variable into the Form’s Bookmark Property.  You can try this out with different records on the Form.

The following Links will show you more tricks on this topic with interesting examples:

  1. Form Bookmarks and Data Editing
  2. Form Bookmarks and Data Editing-2
  3. Form Bookmarks and Data Editing-3
  4. Forms and Custom Properties
  5. Saving Data on Forms not in Table

Activity Dates and Quarterly Reports

There are four Quarters in a Year:

Jan - Mar = 1st Quarter
Apr - Jun = 2nd
Jul - Sep = 3rd
Oct - Dec = 4th

First three months of the year is first quarter, next three months belongs to second Quarter and so on.

Usually, when we prepare a Quarterly Report (for a period of three months based on the table above) we use date-range value to filter the required data for the report.

For example: To prepare Sales Report for the Second Quarter of Sales Year 2017 we will set the date range from April 1st, 2017 to June 30, 2017 as data filtering criteria in a SELECT Query. Probably we may use a Date-Picker control on the parameter entry Form to make it easier to pick and set the date values, rather than typing the date range manually.

If the Report preparation procedure is created on the above fixed pattern then the task can be made easier by creating a small Function and use it on the data filtering Query

GetQrtr() Function Code is given below:

Public Function GetQrtr(ByVal mPeriod As Date) As Integer

Dim mMonth As Integer

On Error GoTo GetQrtr_Err

mMonth = Month(Nz(mPeriod, 0))

If mMonth > 0 Then
    GetQrtr = Choose(mMonth, 1, 1, 1, 2, 2, 2, 3, 3, 3, 4, 4, 4)
    GetQrtr = 0
End If

Exit Function

GetQrtr = 0
Resume GetQrtr_Exit
End Function

The GetQrtr() Function takes the date value as parameter. The Month() Function extracts the month number from date and uses it as parameter for the Choose() Function to pick the correct Quarter Number from it's list.

When the month value is 1,2 or 3 the GetQrtr() function returns 1 to the calling procedure. The Function can be called from a Query, from other Functions, from Form control or from a Report Control by passing date as parameter. When date value passed to the function belongs to April, May, June will returns 2. These months belongs to the Second Quarter of the Year. Dates from next three months returns 3 and so on.

Let us see how we can use this Function in a Sales Query to extract data for Second Quarter 2017 Sales Report. Sample SQL of a data filtering Query is given below:


The GetQrtr() function extracts the Quarter number from all the Sales Record Dates, based on the Values we have lined up in the Choose() Function inside the GetQrtr() Function, compares them with the criteria parameter value 2 and filters the records for that period.

You may setup a Parameter Variable within the Query so that it will prompt for the criteria value, when the Query Runs, and can input the required value directly to filter the data for report.

When Financial Year is from April to March next Year (Jan - Mar become 4th quarter) still the filter criteria will be 1 to extract the data for the fourth quarter. The report heading Labels will indicate that the report is for the fourth quarter of Financial Year 2017-18.


Finding Last Day of Week

How to find the week-end date or first-day date of a week using Current Date as input.  Or find first/last-day date of any week by giving a date value as input.

These kind of calculations may become necessary to find week-end date and use it as criteria in queries, filter data for viewing or printing of Reports for sharing of information.

Wherever you need it you can use the following simple expression to find the week-end date of current week:

LastDayOfWeek = Date()-WeekDay(date())+7

If current date is 14th June, 2017 (or any date between 11 and 17 June 2017) then the value returned in variable LastDayOfWeek = 17th June, 2017.

To find the first-day date of the current week use the following method:

FirstDayOfWeek = date()-WeekDay(date())+1

Assuming current date is 14th June, 2017 (or any date between 11 and 17 June 2017) the first-day date of the week returned in variable FirstDayofWeek = 11th June, 2017.

By giving a specific date as input to the expression to find the last-day date of the week:

dtValue = #06/27/2017#
LastDayOfWeek = dtValue - WeekDay(dtValue)+7
Result: 1st July 2017

If you would like to do it differently then try the following expression:

x = #06/27/2017#
'To find the last-day date of the Week:
LastDayofWeek = DateSerial(Year(Date()),1,1)+DatePart("ww",x)*7-1
Result: 1st July, 2017
'To find the first-day date of the Week.
FirstDayofWeek = DateSerial(Year(Date()),1,1)+DatePart("ww",x)*7-7
Result: 25th June, 2017


Define it as a Function in a VBA Module and call it wherever you want it, with a date value as parameter. Sample Function code is given below:

Public Function WeekLastDay(ByVal dtVal As Date) As Date
'Date value as input
   WeekLastDay = dtVal - WeekDay(dtVal) + 7
End Function


Public Function WeekFirstDay(ByVal dtVal As Date) As Date 
'Date value as input 
    WeekFirstDay = dtVal - WeekDay(dtVal) + 1 
End Function

Time Value Formatting Beyond Twenty Four Hours

Time Format: hh:mm:ss, returns time values up to 23 hours 59 minutes and 59 seconds (23:59:59) and restarts with the value 00:00:01 for next day, 1 second after midnight 00:00:00.  With the built-in Format() Function we cannot format time values beyond 24 hours, like 45 Hours 30 minute and 15 seconds (45:30:15).

Then how do we format time values beyond twenty four hours for applications, like formatting total hours worked by an employee for a week: 40:30:15 or an IT Firm employee's total internet browsing time for the month: 115:47:12, extracted from Server Logs.

We need a function of our own for this purpose.  The following VBA Function: FormatTime() accepts time values in different form, will do the job.  I  have created this function for you and you may Copy Paste the VBA Code into a Standard Module of your project for use.

Public Function FormatTime(ByVal StartDateTime As Variant, Optional ByVal EndDateTime As Variant = CDate(0)) As String
Dim v1 As Double, hh As Integer, nn As Integer, ss As Integer
'Remarks: Time-Format function for time value beyond 23:59:59
'Author : a.p.r.pillai
'Date   : August-2016
'Rights : All Rights Reserved by www.msaccesstips.com
'Input options
'1. StartDatetime & EndDateTime (both date/time values or both Time Values)
'2. StartDateTime = Date/Time Value and EndDateTime value=0
'3. StartDateTime = Time Value in decimal format – e.g. 0.999988425925926) for TimeValue("23:59:59") or less
'4. StartDateTime = Time Value in Seconds – e.g. 86399 for Timevalue("23:59:59")*86400

'If Input Value is in whole seconds (TimeValue*86400)
'for applications like internet Server log time etc.

On Error GoTo FormatTime
If TypeName(StartDateTime) = "Long" Then
   GoTo Method2
End If

If EndDateTime > 0 And EndDateTime < StartDateTime Then
'End-Date is less than Start-Date
   MsgBox "Error: End-Date < Start Date - Invalid", vbCritical, "Format_Time()"
ElseIf (EndDateTime > StartDateTime) And (StartDateTime > 0) Then
'option 1
   If TypeName(StartDateTime) = "Date" And TypeName(EndDateTime) = "Date" Then
     v1 = EndDateTime - StartDateTime
     GoTo Method1
   End If
ElseIf StartDateTime > 0 And EndDateTime = 0 Then
'option 2
'Is it Today's date & Time
   If Int(StartDateTime) = Int(Now()) Then
      'Remove Date number and take only time value
      v1 = StartDateTime - Int(StartDateTime)
   'Option 3
      'Assumes Value is Time-Value in decimal format
      v1 = StartDateTime
   End If
End If

'Option 1 to 3
hh = Int(v1 * 24)
nn = Int((v1 - hh / 24) * 1440)
ss = Round((v1 - (hh / 24 + nn / 1440)) * 86400, 0)
FormatTime = Format(hh, "00:") & Format(nn, "00:") & Format(ss, "00")
Exit Function

'Time Input in Seconds
'Option 4
v1 = StartDateTime
hh = Int(v1 / 3600)
nn = Int((v1 - (hh * 3600#)) / 60)
ss = v1 - (hh * 3600# + nn * 60)
FormatTime = Format(hh, "00:") & Format(nn, "00:") & Format(ss, "00")

Exit Function

MsgBox "Error: " & Err & " - " & Err.Description, , "FormatTime()"
Resume FormatTime_Exit

End Function

Let us try few examples so that you will know how the function parameter values are correctly input to the function.  You may try the following examples by typing them directly on the VBA Debug Window.

The FormatTime() function accepts two Date/Time or Time input parameter values to the function, second parameter is optional.  The following conditions apply when parameter values are passed to the function:

  1. When both parameters are entered both should be either Date/Time Values or both Time Values.  Difference of time will be calculated by subtracting first parameter value from second parameter.
  2. Second parameter is optional. If omitted then Date value will be ignored when Date/Time value is passed in the first parameter. Time value will be formatted and displayed.
  3. First parameter can be time value in decimal format (e.g. 0.999988425925926 ). Omit second parameter.
  4. First parameter is acceptable as time value in Seconds (e.g. 86399). Omit second parameter.

Example-1: Both Parameter values are Date/Time Values. Second Parameter value (Date or Time) should be greater than first parameter value.

StartDT = DateAdd("d",-2,Now()) = 8/27/2016 7:38:22 PM 

EndDT = Now() = 8/29/2016 7:41:13 PM

? FormatTime(StartDT,EndDT)

Result: 48:02:51

Example-2: First parameter Date/Time Value, second parameter optional and omitted.

SDateTime=Now() or DateVallue("08/29/2016")+TimeValue("18:30:15")

? FormatTime(SDateTime)

Result: 18:30:15

Example-3: First parameter Time Value input as a number (2.00197916667094), the decimal value equal to the first example Result: 48:02:51). When we subtract Start-Date/Time Value from End-Date/Time Value you will get the difference as a decimal number, equal to number of days and time value.  This time number can be the result of summary of time values of several records from a Table or Query. The whole number represents number of days (i.e. 2*24=48 hrs.) + the fractional part in hours:minutes:seconds.

StartDT = 2.00197916667094 

? FormatTime(StartDT)

Result: 48:02:51

If the whole number at the left side of the decimal point is equal to the current date number then it will be ignored, when second parameter is omitted.

You can create a time number similar to the one above for testing this function. To do that we will look at few basics on the time value to understand them better.

1 Day = 24 hours. 1 Hour = 60 Minutes. 1 Minute = 60 Seconds. So 1 Day = 24 * 60 * 60 = 86400 Seconds.

1 second before mid-night = 86400-1 = 86399 seconds = 23:59:59

To convert the time 23:59:59 (86399) into internal representation of time value, divide 86400 into 86399 (86399/86400). Result: 0.999988425925926. The time 23:59:59 is stored in computers memory in this form. When combined with the current date number it will be like: 42612.999988425925926 for 08/30/2016 23:59:59

Let us input this time number alone to our function and try out the result.

? FormatTime(0.999988425925926)

Result: 23:59:59


So, if you want to convert a Time number into Seconds then multiply it with 86400. 0.999988425925926 * 86400 = 86399

You can input number of seconds as a time number as first parameter to get it formatted in Hours:Minutes:Seconds.

? FormatTime(86399)

Result: 23:59:59

If you want larger values for testing this function, then try the following examples.

SD = 86399+86400+86000
   = 258799 seconds

SD = SD/86400
   = 2.9953587962963 time number

? FormatTime(SD)

Result: 71:53:19

Let us input the time value in seconds (let us assume that this value is the summary of Internet browsing time value in seconds).

SD= 258799

? FormatTime(SD)

Result: 71:53:19

If you have a Library Database then you can move this Function into that database so that you don't have to copy the code into all your other databases.

A Library database is a common database you have created with all your custom functions or custom wizards that you can attach with your other Projects. This method will enable you to use these functions in your other projects without duplicating codes in all of them. For a detailed discussion on this subject visit the page MS-Access and Reference Library.

Please leave your comments or suggestions for improvement of FormatTime()  function in the Comments Section, at the end of this Post. Thank you.


Opening Multiple Instances of Form in Memory

Last few weeks we have been through learning the usage of dot(.) separator and exclamation symbol(!) in VBA object references.  Now, we will explore some interesting trick with Forms in VBA.  How to Call a Function Procedure embedded in a Form Module (Class Module), from a program outside the Form?

We will explore two different aspects on this particular topic.

  1. How to open several instances of a single Microsoft Access Form in memory, displaying different information on each of them?

    Sample screen shot of two instances of Employees Form is given below for reference.  The first form is behind the second instance of the form, displays employee details of ID: 4 & 5.   Click on the image to enlarge the picture. 

  2. How to call a Function Procedure on the Form Module, from outside the Form?

    Call the Function from a Standard Module,  from the Module of another form or from the VBA Debug Window (Immediate Window).  The target form must be opened in memory in order to call the function procedure of the form from outside.

Function Procedures in a Form module is helpful to avoid duplication of code. It can be called from subroutines in different locations on the same Form, from a command button click or from some other Event Procedures of the Form.  The function procedure on a Form Module can be anything that does some calculation, validation check, updating the information or a stand-alone search operation procedure, like the one we are going to use on our sample Employees Form.

All the Event Procedures on a Form Module are automatically declared as Private Subroutines and they all will start with the beginning  and end Statements, like the sample statements given below.   Our own VBA codes that does something goes within this block of codes:

Private Sub Command8_Click()
End Sub

The scope of Private declared Subroutine/Function stays within that module and cannot be called from outside.  Private declaration is absolutely necessary to avoid procedure name clash with the same name on another Class Module or Standard Module.  Form Function Procedure must be declared as Public in order to call it from outside the Form.

To perform a trial run of the above trick you need the Employees Table and a Form.

  1. Import Employees Table from Northwind.accdb sample database.
  2. Click on the Employees Table to select it.
  3. Click on Create Ribbon.
  4. Click on Form Button, from the Forms group, to create a Form, like the image given above, for Employees Table.
  5. Save the Form with the name frmEmployees.
  6. Open the frmEmployees Form in Design View.
  7. Click on the View Code button, in Tools button group, to open Form Module.
  8. Copy the following VBA Code and Paste them into the VBA Module of the Form.
    Public Function GetFirstName(ByVal EmpID As Integer) As String
    Dim rst As Recordset, crit As String
    Dim empCount As Integer
    'get total count of employees in the Table
    empCount = DCount("*", "Employees")
    'validate employee code
    If EmpID > 0 And EmpID <= empCount Then
    'create search criteria
        crit = "ID = " & EmpID
    'make a clone of the recordset of the Form
        Set rst = Me.RecordsetClone
    'Find the first record that matches the criteria
        rst.FindFirst crit
    'If the record is found then
        If Not rst.NoMatch Then
    'copy the recordset bookmark to the form bookmark
    'this will make the found record current on the form
         Me.Bookmark = rst.Bookmark
    'Return the Employee first name to the calling program
         GetFirstName = rst![First Name]
        End If
    'close the recordset clone and release the used memory
        Set rst = Nothing
    'IF EmployeeID is not in range of 1 to empCount
    'then display a message and exit function
        MsgBox "Valid Employee IDs: 1 to " & empCount
    End If
    End Function
  9. Save and Close the Form.

Have you noticed the starting line of the above Function that is declared as Public?

The Function GetFirstName() accepts Employee ID number as parameter, finds the record and makes that record current on the form. The Function returns the First Name of the Employee to the calling program,  if the search was successful.  If the search operation fails then it gives a warning message, saying that the employee ID code passed to the function is not within the range of ID codes available in the Employees table.

Now, we need another program, in the Standard Module, to run the search function GetFirstName() from the frmEmployees Form Module.  Besides that this program demonstrates as how to create more than one instance of a Microsoft Access Form and open them in memory, to access their properties, methods or control contents.

  1. Open VBA Editing Window (Alt+F11).
  2. Select Module from Insert Menu and add a new Standard Module.
  3. Copy and paste the following VBA Function code into the new Module.
    Public Function frmInstanceTest()
    'Create first instance of the form frmEmployees
    'and opens it in memory(not visible in application window)
          Dim frm As New Form_frmEmployees
    'Create second instance of the Form frmEmployees
    'and opens it in memory(not visible in application window)
          Dim frm2 As New Form_frmEmployees
    'Name1, Name2 string variables
          Dim Name1 As String, Name2 As String
    'Make both instances of the frmEmployees
    'visible in the Application Window
      frm.Visible = True
      frm2.Visible = True
    'Call the GetFirstName() Public Function of
    '1st instance of the frmEmployees with Employee ID: 4
    'Record of ID 4 becomes current on frmEmployees
    'and returns first name to variable Name1
      Name1 = frm.GetFirstName(4)
    'Call the GetFirstName() Public Function of
    '2nd instance of the frmEmployees with Employee ID:5
    'Record ID 5 becomes current on frmEmployees
    'and returns first name to the variable Name2
      Name2 = frm2.GetFirstName(5)
    'pause execution of this code to view
    'the Employees Form instances in Application Window.
    'display the first names retrieved from
    'both instances of the Employees Form
      MsgBox "Employees " & Name1 & ", " & Name2
    End Function

Let us run the code and view the result in Application Window.

  1. Click somewhere within the body of frmInstanceTest() function and press F5 key to run the code.

    The program will pause at the Stop statement and this will facilitate to view the Application window, where the frmEmployees Form instances are open in normal view mode, one overlapping the other.

  2. Press Alt+F11 to display the Application Window displaying both instances of the Form, second form overlapping the first one.
  3. Click and hold on the title bar area of the top form and drag it to the right, to make part of the form behind visible.

    Check the employee records on both forms, they are different, one with employee code 4 and the other is 5.  Check the title area of forms, both are showing frmEmployees title.  Now, let us come back to the program and continue running the code to complete the task.

  4. Press Alt+F11 again to switch back to the VBA Window and press F5 key one more time to continue executing the remaining lines of code.

    The Message Box appears in the Application Window displaying the Employee name Mariya and Steven together.  When you click OK Button on the MsgBox the frmEmployee form instances disappears from the Application Window.

  5. Click OK button on the MsgBox.

Note: I would like to draw your attention to the Stop statement above the MsgBox() function, at the end part of the code. The Stop statement pauses execution of the VBA code on that statement.  Normally, this statement is used in a program for debugging of code, to trace logical errors and corrections.  Here, it is required to pause execution of code so that we can go to the Application Window and view both instances of the frmEmployees Form there.  The MsgBox() will pause the code but we will be able to view the topmost instance of the form only. We cannot drag the top form to the right side while msgBox is displaced.

If we doesn't create a pause in the code execution both instances of the form are closed immediately, when the program ends.  In that case we will not be able to view the forms.  Since, it is a trial run we would like to know what is happening in the program.

Let us take a closer look at each line of code of the frmInstanceTest() function.  Even though hints are given on each line of code, explaining few things here will make them more clear to you.  We will start with the first two Dim Statement.

Dim frm As New Form_frmEmployees
Dim frm2 As New Form_frmEmployees

In the above Dim statement you can see that the New key word followed by the object reference. The object name is our frmEmployees prefixed by the direct Object Class name FORM followed by an underscore character separation (Form_) to the frmEmployees Form name (Form_frmEmployees).  These Dim statements itself  opens two instances of the frmEmployees in memory.   Form instances opened in this way is not immediately visible in the Application Window.  If we need them to be visible then make them visible with another statement.

Next we have declared two String Variables: Name1 & Name2 to hold the names returned by the GetFirstName() method.

Next two statements: frm.Visible=True and frm2.Visible=True, makes both instances of frmEmployees Form visible in the Application Window.

In Next two lines of code we are calling the GetFirstName() method of first and second instances of the frmEmployees to search, find and return the First Names of employee code 4 and 5.

The default instance of a Form openes in the following manner in programs for accessing their Properties, Methods and Controls.  These style of statements are always used to open a form in programs. The default instance will be automatically visible, when it is open, in the Application Window.

Dim frm as Form 'define a Form class object
DoCmd.OpenForm "frmEmployees", vbNormal 'open frmEmployees in Memory
Set frm3 = Forms!frmEmployees ' attach it to the frm object

Assume that we have opened frm & frm2 instances first in memory before the default instance through the above code.  How to address those three instances in a program to do something?  Let us forget about the frm, frm2, frm3 object references for now, we will go by the straight method, like the one given below:

name3 = Forms![frmEmployees].GetFirstName(5) 'target form in memory is the default instance
name3 = Forms("frmEmployees").GetFirstName(5) 
name3 = Forms(2).GetFirstName(5) ' this is the third and default instance

The other two instances in memory cannot be referenced like the first two default methods, using the name of the form. You have to use only the index number of Forms collection to address the other two instances.

name1 = Forms(0).GetFirstName(3)
name2 = Forms(1).GetFirstName(6)

There is a shortcut method you can use to run the GetFirstName() Method of the frmEmployees Form from the debug window (Ctrl+G).  Type the following command on the Debug Window and press Enter Key:

? form_frmEmployees.GetFirstName(5)
'Result: Steven
X = form_frmEmployees.GetFirstName(5)

What happens when we execute the above command?  It opens an instance of the frmEmployees in memory, Calls the Function GetFirstName() with the employee Code 5. The GetFirstName() runs and finds the record and returns the First Name of the employee and closes the form.

Tip: Even after closing the Form, after execution of the above command, the current record of Employee ID 5 remains as current on the closed Form.

You can check this by executing the following shortcut command by typing it in the debug window and pressing Enter Key.

? form_frmEmployees![First Name]
'Result: Steven

In the above command we didn't run the GetFirstName() method but directly printed the First Name from the current record on the form. If you want get little fancy with the command then try this by typing it in debug window and press Enter Key:

MsgBox "First Name: " & form_frmEmployees.GetFirstName(8)
MsgBox "First Name: " & form_frmEmployees![First Name]

Or try the above command from a Command Button Click Event Procedure from another Form's Module, as given below.

Private Sub Command8_Click()
  MsgBox "First Name: " & Form_frmEmployees.GetFirstName(8)

End Sub

Dots and Exclamation Marks Usage with Objects in VBA3

MS-Access Application Objects (Table, Query, Forms, Text box, Label etc.) needs some meaningful names, when we create them. If we don't, then MS-Access assigns default names, like Form1, Form2, Text1, Label1 and so on. These default names doesn't give any clue as what those names represents. We are free to assign appropriate names in relation to what we are building in the Database. That way it is easy to remember those names (if not all of them), when we need them in calculations,  in VBA or in wherever they are referenced. We need these names to address them easily in VBA, like Forms!Employees!Salary rather than the usage  Forms("Employees").Controls("Salary").Value. 

Last week we have started with a simple example, where we can use the symbol ! , to shorten the lengthy object address when dot separators are used, giving you enough insight into what it is all about. When we have the Form’s name and Control name the expression can be written in short form with the symbol ! .  This is true when recordset fields are referenced, like rset!LastName instead of rset.Fields(0).Value.

If you are new on this page then please visit the earlier two pages and continue from here. The links are given below:

I will repeat the first example here, introduced in the first page of this three page series, to go further on this discussion.

? Forms!frmMain!frmSubForm.Form!frmInsideSubForm.Form!Text7
'The above command without the use of the ! Symbol, you must write it in the following manner to get the same result.
? Forms("frmMain").Controls("frmSubForm").Form.Controls("frmInsideSubForm").Form.Text7.value

Note: The frmSubForm when placed as Sub-Form on the frmMain it becomes a control (with it's own controls and properties) of the Main Form and listed among the Controls list. If we take a list of controls of the Main form we can see that the frmSubForm is listed among them.

Open a Form with a Sub-Form  then type the following command in one line, with changes in the Debug Window and press Enter Key to get a listing of control names of main form

for j=0 to forms!frmMain.controls.Count-1:? j, forms!frmMain.controls(j).name:next
'Result of the above command, on my Form.
 0            Label0
 1            Text1
 2            Label2
 3            Text3
 4            Label4
 5            frmSubForm
 6            Label5

frmSubForm is listed as a Control of the frmMain with index number 5

Now, about the example given at the beginning, we have three open Forms: frmMain, frmSubForm & frmInsideSubForm, layered one inside the other. We are trying to print the Text7 Text Box contents, from the innermost form in the debug window.  Look at the above address of  Text7 textbox, all elements are joined with the symbol ! except .Form after the name frmSubForm and frmInsideSubForm. This command will work without the .Form part, try the command given below.

? Forms!frmMain!frmSubForm!frmInsideSubForm!Text7

If the address works without the .form part why we need it in the address and what it means? It works without explicit reference because the system knows that it is a Sub-Form control by default.

When you drag and drop a Sub-Form on to the Main Form Microsoft Access creates a container control on the main form and inserts the Sub-Form into it. To be more specific, if you select the outer edge of the sub-form control you can select this container control. Display it's Property Sheet(F4) check the Source Object Property setting. You can see that the sub-form's name is inserted there. This is the control where we set the Link Master Fields and Link Child Fields properties to set relationship between data on master form and sub-form.

You can re-write this property value with any other form's name  to load another form into it, in real-time. When you do that consider the relationship change, if the new form's source data is not related.

Coming back to the point, i.e. what the .Form part in the above address means? It means that the Sub-Form Control created by Access is a control for loading a Form into it and it will be always a form-control, whether you explicitly add the .Form part in the address or not.

But, the interesting part is that you can insert a Table or a Query (not Action Query) into this control as well.

Try that, if you have a Form with a sub-form, open it in design view.

  1. Click on the outer edge of the Sub-Form to select the Sub-Form control.
  2. Display the Property Sheet (F4) and select the Source Object Property.
  3. Click on the drop-down control to the right of the property to display the available forms, Tables and Queries.

    On top of the list all forms will appear, after that the list of Tables and then the Queries list. All the Tables are listed with Table.TableName format and queries with Query.QueryName format indicating the category of object you can insert into the Source Object Property of the Form control.

  4. Select a Table or Query to insert into the Source Object Property of the Sub-Form control.
  5. Save the Form and open it in Form View.
  6. You will find the Table or Query result is displayed in the Sub-Form control.

  7. Try to print the value of one of the field in display in the debug window.

    Tip: It will print the value of the active record in the sub-form, if selected, otherwise the first record field value.

Is this the command you have typed in the Debug Window?

? Forms!frmMain!frmSubForm.Table!LastName

Then you are wrong, it is not a Table Control, still it is a Form control only. When you set the Source Object Proerty Value with a Table's name the system already added the category name to the object's name (Table.TableName or Query.QueryName) to identify what type of object is loaded into the sub-form control.

So the correct command is:

? Forms!frmMain!frmSubForm.Form!LastName
? Forms!frmMain!frmSubForm!LastName

Dots and Exclamation Marks Usage with Objects in VBA2

Last week we have started with few examples of the usage of dots (.)  and exclamation marks (!) on memory loaded Form/Report Objects.  We will continue exploring this topic further.  If you have not gone through the earlier page,  please visit that page and then continue from here. Earlier article link: Dots and Exclamation Marks usage with Objects in VBA

After going through last week's article, I am sure you are little bit confused, which syntax is the easy one to use because we have tried different syntax for addressing form & control in VBA.

For the time being we will leave that there and try few things differently here so that you will be better informed about the dot separator usage on built-in objects.  The  exclamation (!) symbol is not at all valid for referencing these objects.  You will see the hierarchy set up of some of the library objects visually and how to address them to view their property values or call their methods directly from the debug window. 

Object Library View.

  1. Open your Access Database.
  2. Open VBA Editing Window (Alt+F11).
  3. Open Debug Window (Ctrl+G).
  4. Display Object Browser Window(F2).
    • Select Options from Tools Menu.
    • On the Editor Tab put a check-mark on the Auto List Members item, if it is not already selected.
  5. Select Access from the <All Libraries> Control's drop-down list.
  6. Move the scrollbar of Classes window down, find the item CurrentProject and select it.

    Check the right side window listing of  CurrentProject’s Properties and Methods.

  7. If the Object Browser Window is small then drag the right and bottom edges to make it large enough to display all the Properties and Methods of the CurrentProject Object in the right-side window.
  8. Click and hold on the Title area of the object browser window and drag it to the right, if it overlaps the Debug Window area.

When you select a Class Object or Public Constant definition from the Classes Window (left panel), the selected object members are displayed in the right-side window.

Let us display information stored in some of these objects and how we address those object properties/methods and in which order we specify them?

We will display the full Pathname (.FullName property value) of the active database with the following command, by typing it in the Debug Window and pressing Enter Key:

? Access.CurrentProject.FullName
'Result: G:\New folder\pwtracker.accdb

The .FullName Property Value of CurrentProject Object from Access Library of Objects is displayed.  When you open a database from a particular location the .FullName Property value, the full pathname of the Database,  is set by MS-Access System.  We have joined the object elements in correct sequence separated by dots to specify the .FullName property at the end. The .Name property value will display the database name part only.

Let us see how many forms you have in your database, by taking the .Count Property Value of AllForms Object.

? Access.CurrentProject.AllForms.Count
'Result: 75 – There are 75 user created Forms in my active database.
? Access.CurrentProject.AllForms.item(50).Name
'Result: frmEmployees 
'The 50th index numbered item (i.e. 51st item)in my database is frmEmployees. 
'This Form is not opened in memory, but it is taken from the Database’s .AllForms Collection. 
? Access.CurrentProject.BaseConnectionString
'Result: PROVIDER=Microsoft.ACE.OLEDB.12.0;DATA SOURCE=G:\New folder\pwtracker.accdb;PERSIST SECURITY INFO=FALSE;Jet OLEDB:System database=G:\mdbs\BACAUDIT_1.MDW

In the above examples we have displayed CurrentProject Object's few Property Values assigned by the System. We have used Forms Collection Object to address the open forms in memory, in last week's examples.

Note: You may explore some of the other objects yourself.

From the above few examples you can see that we have used dot separator character only to join each object/property. You cannot use ! symbol to address predefined objects, methods or properties.

When we reference a User-Defined object (it should have a name) we can use ! symbol followed by the object/control Name to access that Control's Value or other controls/properties, eliminating the lengthy syntax. To make this point very clear we will try out one simple example below.

  1. Scroll down to the bottom of the Classes Window.
  2. Select the TempVars Class Object. You can see it's Methods (.Add(), .Remove() & .RemoveAll()) and Properties in the right side window.
  3. Above the TempVars Class you can see another object name TempVar Class, click on that and check the right-side window. You will find only two properties:Name & Value.
  4. Type the next line of Code in the Debug Window and press Enter Key.
TempVars.Add "website", "MsAccessTips.com"

We have called the .Add() method of the TempVars Collection Object to instantiate the TempVar Class, to assign the Name property with the text: website and the Value property with the text: MsAccessTips.com. The new Tempvar Variable website is added to the TempVars Collection with index number 0, because this is the first TempVar variable we have defined in memory so far.

The TempVar data type is Variant Type, i.e. whatever data type (Integer, Single, Double, Date, String etc.) you assigned to it, it will automatically adjust to that data type.

We can display the website variable contents in two different ways.  First method using dot separators, second with ! symbol.

'1st method
? TempVars.item(0).value
'Result: MsAccessTips.com
? TempVars.item("website").value
'Result: MsAccessTips.com
? TempVars.item(X).value
'Result: MsAccessTips.com

'2nd method
'the above lengthy syntax can be shortened
'with ! symbol and the user-defined name:website
'point to note:after ! symbol Name property value should follow.
? TempVars!website
'Result: MsAccessTips.com
'next command removes the website variable from memory.
TempVars.Remove "website"
'the .RemoveAll method clears all the user-defined Temporary Variables from memory.

The TempVars Variables are Global in nature, that means you can call this variable (Tempvars!website) in Queries, Textbox (=TempVars!website) on Forms or on Reports and in expressions like: ="Website Name is: " & TempVars!website. If the Value property is assigned with numerical values (like Exchange Rates or some common factor) it can be used in Calculations.

Tip: Try defining few more TempVar variables assigning different data types: Integer, Double, Date etc. with appropriate Name Property Values.

The Tempvar variable with the name website is our own creation and it is in memory.  Objects (Form/Report) should be loaded into memory with their controls (Textbox, Combobox, Labels, Sub-Form/Sub-Report etc,) to address them with the use of ! symbol followed by the name of the control.

We have used the keyword .item in the first three examples.  This is used immediately after the TempVars Collection Object.  When we address textboxes, labels and other controls on an open Form/Report we must use the key word .Controls.

You may explore other objects by selecting it in the left-side panel and inspecting their properties, methods and events etc. now or later when you are in doubt on something.

Tables & Queries are part of DAO Library. You may select DAO in the top control replacing Access and explore DAO related Classes, their Properties and Methods.

Hope you are now comfortable with the usage of (.) and (!) symbols in object references. We will look into few more things in the next session before we conclude the discussion on this topic.



Your email address:

Delivered by FeedBurner


Infolinks Text Ads

blog.feedspot.com Microsoft Access Blogs

Popular Posts

Search This Blog

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

Featured Post

Passing Two Dimensional Array to Function

First of all, I have some good news for you, our website: LEARN MS-ACCESS TIPS AND TRICKS has been selected by https://blog.feedspot.com pa...


Blog Archive

Recent Posts