Function Parameter ByVal and ByRef Usage

Function Parameter ByVal and ByRef Usage.

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

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

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

1. Make a copy of the apples (the number) from the box ourselves, and put them into another box and pass it.  The target box's name will be different.  The recipient of the new box can work with his apples, like adding more apples to his box or removing some from it, and so on.  There will not be any change to the number of Apples in the original Box. 
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.

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.

• Useful Report Functions
• Join Split Array Functions
• Join Split Array Functions-2
• Macros and Temporary Variables
• Sub-query in Query Column Expressions

Read More

DIRectory and File Copy Utility

DIRectory and File Copy Utility.

Last week, we explored how to use the Dir() DOS command to read files from the Disk one by one and display their names in the Debug window.

Building on that, we will now create a VBA utility that uses the Dir() command together with the very useful FileCopy statement (note that it is a statement, not a function) to read and transfer files from one folder to another location on the Disk. The files can be of any type—for example, *.pdf, *.docx, *.xls, or *.* (all files).

This utility will read the files from a folder specified in a text box, list them in a list box using the Dir() command, and allow you to copy either all the listed files or only the selected ones to a target folder specified in another text box.

The Utility Form.

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

Right-click to open Large Image in New Tab/Window.

The form design is simple, consisting of two text boxes, one list box, three command buttons, and a label control to display messages from the utility program. You can download this utility form as part of a sample database at the end of this article.

These are the names of the Controls on the Form:

1. Top TextBox: 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, make sure that the same control names are used as described above. The VBA code you will copy into the module references these exact names.

In addition to the main controls, a Label control below the first (source) TextBox provides examples of how to specify the source file path correctly.

Another label control at the bottom of the form displays messages during input validation and error notifications while the VBA code is executing.

An Image of a sample run of the FileCopy Statement is given below:

Right-click to open the large image in a new tab or window.

You can create this user interface using the control names provided above. Once the form is designed with the correct control names, open the VBA window for the form and copy the following code into its VBA module:

The Form Module Code.

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

cmdClose_Click_Exit:
Exit Sub

cmdClose_Click_Error:
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) & ","
    
readnext:
    strfile = Dir() ' read next file
Loop

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

cmdDir_Click_Exit:
Exit Sub

cmdDir_Click_Err:
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
Next

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

End If

'Me.cmdSelected.Requery

'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
Else
    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.List1.SetFocus
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

cmdSelected_Click_Exit:
Exit Sub

allCopy:
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
  Loop
Next
Return

selectCopy:
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
                Loop
   End If
Next
Return


cmdSelected_Click_Err:
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
List1_AfterUpdate_Exit:
Exit Sub

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

You may save the Form as FileCopy.

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

You can copy different sets of files selected from the ListBox to different target folders by first deselecting any previous selections, selecting the desired files, and updating the destination folder address in the TextBox.

Download the Demo Database.

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

Download FileCopy2007.zip
Download (2003) FileCopy.zip

• Disk Operating System Commands in VBA
• Saving Report Pages as Separate PDF Files
• How to Use Form Filter on Report
• Back-tracking Open Forms

Read More

DIR Getting File Names From Folder

Introduction.

Most of us are familiar with the Dir() function from the days of the DOS operating system. Dir() has often been the very first command introduced to anyone learning how to use a personal computer. Under Windows, this command still offers several options for retrieving information from the disk in various ways. For example, you can generate a complete list of folders, subfolders, and files on a hard disk with a single command and even redirect that list to a printer or save it to a text file using the redirection symbol (>).

In this article, however, we will focus only on how the Dir() function is used in VBA to read file names from a folder one by one and display them in the Debug window. When you run this function with a folder path as a parameter, it returns the first file name found in that folder. The question then is: how do we retrieve the remaining file names—one after another—from the same folder?

Using the DIR Command in the Debug Window.

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

1. Open the Microsoft Access VBA Window and then display the 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 a parameter will fetch the first file name from the Current Folder and display it in the debug window. 

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

3. Now, issue the following command without any parameters to get the next file name in the current folder

   ? Dir()
   OR
   ? Dir

4. Each time you run the DIR() command, it will get the next file from the same folder.
5. Use a specific Folder Path as the parameter, in place of the empty string, to get files from that particular folder.
6. Example:

   ? Dir("D:\Documents\")
   OR
   ? Dir("D:\Documents\*.*")
   

If D:\Documents\folder doesn't have files, then the above command will return an empty string. If you go further and execute the Dir command again, then 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 a DOS Command executed in its own window, we can specify this second parameter to show its normal window(vbNormal) or hide the execution window (vbHidden), among other options available.

A VBA Wrapper Function for the DIR Function.

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()
Loop
End Function

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

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

All the files from the specified folder will be listed in the Debug window along with a serial number. After reading and printing the last file from the folder, the Dir() function is called one more time and returns an empty string. At this point, the Do While condition evaluates to False, and the program stops.

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

Example:

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

The above example retrieves only Excel files and prints in the Debug window.

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

• Disk Operating System Commands in VBA
• Microsoft DOS Command in VBA
• Microsoft DOS Command in VBA-2
• ChDir and IN Clause of Access Query
• Deleting Folders with DOS Command

Read More

Unsecured Database and Users Log

Unsecured Database and User Log.

When performing data entry or editing important database tables, it is common practice to save a timestamp and user name for each record to mark the event. This is typically implemented using the form's Before Update event procedure. A sample procedure is provided below:

Private Sub Form_BeforeUpdate(Cancel As Integer)
     Me![EditedBy] = CurrentUser
     Me![EditedDt] = Now
End Sub

These fields will be added to the Data Entry/Editing Form from the Table, but will be kept hidden or disabled to prevent manual changes. The familiar =Now() function retrieves the current date and time, while the CurrentUser() Function provides the username of the person currently logged into the database. In this context, we are focusing on the usage of the CurrentUser() function.

The CurrentUser() function returns the correct username in a secured database—that is, a database configured with Microsoft Access security features and shared on a network. When an authorized user opens an instance of the database on their workstation, they must enter their authenticated username and password to gain access. Consequently, the value returned by CurrentUser() will always correctly identify the logged-in user.

The Admin User.

However, if the database is not secured with Microsoft Access Security, the CurrentUser() function will always return the value Admin. Any user who opens an unsecured database on a network is automatically logged in by MS-Access as the Admin user, a member of the Admins group, without being prompted for a user name or password.

We will not delve into Microsoft Access Security issues here (I have already dedicated about nineteen pages to this topic under the “Microsoft Access Security” section on the Main Menu of this site). Instead, we will focus on how to capture the network user name and workstation name correctly when working with an unsecured database shared on a network.

Fortunately, there is a simple trick to capture the network user ID and workstation ID and record these values in your table fields. No lengthy VBA program is required. But first, let’s see where this information is stored and how you can retrieve your own network user ID and workstation ID from your computer’s memory.

Finding Computer Name.

1. Select Run from the Start Menu.
2. Type Cmd and click OK. A DOS window will open up.
3. Type SET and press the ENTER key.

A long list of environmental variables will appear in the DOS (Disk Operating System) window, the core system underlying the Windows Operating System. These variables are loaded into memory when you start your computer or after logging off and back on, and they play an important role in the smooth execution of your day-to-day tasks.

However, for our purpose, we are interested in only two specific values from this list. Look for the following entries:

COMPUTERNAME=your Computer Name

USERNAME=your Network User ID

These two entries may not appear close to each other in the list of Environment Settings — they can be located anywhere within it. If necessary, use the scroll bar to move up or down through the list until you find them. Once you’ve confirmed their presence, type EXIT and press ENTER to close the DOS window.

In Microsoft Access, there is a built-in function called Environ() that allows you to retrieve these values directly from memory and use them anywhere in your database — such as in forms, reports, or VBA procedures.

Example-1: X = ENVIRON("USERNAME")

This will bring the Network User ID of the User from memory.

Example-2: X = ENVIRON("COMPUTERNAME")

This will get the WorkstationId (Computer Name) from the Environment String that we have seen in Memory.

In fact, we can simplify the use of the Environ() function by creating two small, reusable functions and adding them to a global module in our Function Library. Once defined, these functions can be used anywhere in our application — just like the built-in CurrentUser() function — to retrieve the Network User Name and Workstation (Computer) Name directly from memory.

Since CurrentUser is a built-in function, we will use something different that we can memorize easily. We can even use the Parameter Values of the Environ() function as our own function names.

Add the following Functions in the Global Module (Standard Module) of your Function Library Database or in the shared database itself:

Public Function UserName() As String
    UserName = Environ("UserName")
End Function

Public Function ComputerName() As String
    ComputerName = Environ("ComputerName")
End Function

After adding these Functions to the Global Module, you can call them from wherever you want, like:

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error Resume Next
      If Err then
        Cancel = True
      Else
       Me![EditedBy] = UserName
       Me![EditedDt] = Now
      End IF
End Sub

OR

If Msgbox("Hi, " & UserName & ", Shutdown Application..?", vbQuestion+vbDefaultButton2+vbYesNo,"Shut Down")=vbYes then
     docmd.Quit acQuitSaveAll
End If 

Similarly, the ComputerName() Function gets the Workstation ID of the User from Memory.

Monitoring Intrusions.

These are also useful to monitor unauthorized use of third parties, or somebody not entrusted to maintain the open database,  from the Network Drive, by sending an Alert Message to you from the Startup Screen or the Main Switch Board Form's Open Event Procedure, like the example given below:

Private Sub Form_Open(Cancel As Integer)
       Call Shell("NET SEND YourNetworkID from: " & ComputerName & " User: " & UserName & " opened: " & Currentdb.Name & " at: " & Now())
End Sub

Note: NET SEND was a Windows XP Command; its new Version is the MSG Command.

Refer to the following Posts for more advanced techniques:

1. Record the Open/Close events and activity of Databases in a log Text File and send alerts to your machine in a pop-up message box, which you can turn on or off as you need them.

   Database Open/Close Event Alerts.

2. Sending Alerts to other User Workstations with useful information that they need to know about, immediately after processing activities take place on one side of the User Groups.

   Sending Alerts to Workstations.

3. How many users are currently online using a particular database, and how to communicate with them by sending Alerts to their Workstations?

   Who is online?

4. Send E-Mails to remote Users with attachments of important Reports in Snapshot Format.

   Automated E-Mail Alerts

Earlier Post Link References:

• Form Background with Gradient Color
• MS-Access and Reference Library
• InputBox and Simple Menus
• Change Form Modes On User Profile
• Positioning POP-UP Forms

Read More

Cardinal Text Format In Access

Cardinal Text Format In Access.

The other day, a colleague asked me to open Microsoft Word, type the expression =Rand() on a blank line, and press Enter. What happened next felt like magic — something I hadn’t known until that moment. Instantly, Word replaced the expression with a block of sample text, repeating the following sentence fifteen times (arranged in three rows and five columns):

The quick brown fox jumps over the lazy dog.

Open a Word document and try it yourself: type =Rand() on its own line and press Enter. Word will replace the expression with a block of sample text — the sentence below repeated fifteen times (arranged in three rows and five columns). The sentence contains every letter of the alphabet. You can control the output by passing parameters to the function; for example, it =Rand(5,1) prints the sentence five times in one column. It’s a built-in Word feature that looks like a randomizer but isn’t — it’s just a quick way to insert sample text, and it only works when the expression is on a separate line.

There’s another Word feature I wish Access had natively: formatting numeric values as cardinal text. In Word mail merge fields, you can use switches such as \* CardText or \* DollarText — for example, the mail-merge expression { = 9.20 + 5.35 \* CardText } produces fourteen and 55/100 when merged. Adding the \* Caps switch (e.g., { = 9.20 + 5.35 \* DollarText \* Caps }) capitalizes the first letter of each word, which is handy for printing amounts on invoices.

That’s exactly the kind of output I wanted for Access invoices, so I wrote a VBA function to convert numeric values to cardinal text.

The CardText() Function

Copy and paste the following Code into a Global Module of your Database and save it:

Public Function CardText(ByVal inNumber As Double, Optional ByVal precision As Integer = 2) As String
'------------------------------------------------------------------------
'Author : a.p.r. pillai
'Date   : December 2008
'URL    : www.msaccesstips.com
'All Rights Reserved by www.msaccesstips.com
'------------------------------------------------------------------------
Dim ctu, ctt, bmth
Dim strNum As String, j As Integer, k As Integer, fmt As String
Dim h As Integer, xten As Integer, yten As Integer
Dim cardseg(1 To 4) As String, txt As String, d As String, txt2 As String
Dim locn As Integer, xfract As String, xhundred As String
Dim xctu As String, xctt As String, xbmth As String

On Error GoTo CardText_Err

strNum = Trim(Str(inNumber))
locn = InStr(1, strNum, ".")
'Check Decimal Places and rounding
If locn > 0 Then
  xfract = Mid(strNum, locn + 1)
 strNum = Left(strNum, locn - 1)
    If precision > 0 Then
        If Len(xfract) < precision Then 
        	xfract = xfract & String((precision - Len(xfract)), chr(48))
        ElseIf Len(xfract) > precision Then
            xfract = Format(Int(Val(Left(xfract, precision + 1)) / 10 + 0.5), String(precision, "0"))
        End If
        xfract = IIf(Val(xfract) > 0, xfract & "/" & 10 ^ precision, "")
    Else
        strNum = Val(strNum) + Int(Val("." & xfract) + 0.5)
        xfract = ""
    End If
End If

h = Len(strNum)
If h > 12 Then
'if more than 12 digits take only 12 (max. 999 Billion)
'extra value will get truncated from left.
   strNum = Right(strNum, 12)
Else
   strNum = String(12 - h, "0") & strNum
End If

GoSub initSection

txt2 = ""
For j = 1 To 4
    If Val(cardseg(j)) = 0 Then
       GoTo NextStep
    End If
    txt = ""
    For k = 3 To 1 Step -1
      Select Case k
       Case 3
            xten = Val(Mid(cardseg(j), k - 1, 1))
            If xten = 1 Then
                txt = ctu(10 + Val(Mid(cardseg(j), k, 1)))
            Else
                txt = ctt(xten) & ctu(Val(Mid(cardseg(j), k, 1)))
            End If
        Case 1
            yten = Val(Mid(cardseg(j), k, 1))
            xhundred = ctu(yten) & IIf(yten > 0, bmth(1), "") & txt
            Select Case j
                Case 2
                      d = bmth(2)
                Case 3
                    d = bmth(3)
                Case 4
                    d = bmth(4)
            End Select
            txt2 = xhundred & d & txt2
    End Select
   Next
NextStep:
Next

If Len(txt2) = 0 And Len(xfract) > 0 Then
    txt2 = xfract & " only. "
ElseIf Len(txt2) = 0 And Len(xfract) = 0 Then
    txt2 = ""
Else
  txt2 = txt2 & IIf(Len(xfract) > 0, " and " & xfract, "") & " only."
End If

CardText = txt2

CardText_Exit:
Exit Function

initSection:
xctu = ", One, Two, Three, Four, Five, Six, Seven, Eight, Nine, Ten, Eleven, Twelve,"
xctu = xctu & " Thirteen, Fourteen, Fifteen, Sixteen, Seventeen, Eighteen, Nineteen"
ctu = Split(xctu, ",")

xctt = ", Ten, Twenty, Thirty, Fourty, Fifty, Sixty, Seventy, Eighty, Ninety"
ctt = Split(xctt, ",")

xbmth = ", Hundred, Thousand, Million, Billion"
bmth = Split(xbmth, ",")
k = 4
For j = 1 To 10 Step 3
    cardseg(k) = Mid(strNum, j, 3)
    k = k - 1
Next
Return

CardText_Err:
CardText = ""
MsgBox Err.Description, , "CardText()"
Resume CardText_Exit
End Function

The CardText() or the DollarText.

Sample Demo Runs

The Function name CardText() is derived from MS Word Number Format Switch \* CardText. The CardText() Function accepts a maximum value of 10^12-1 or up to 999 billion. For most applications, this will be sufficient. Passing a Value greater than this will get truncated from the left.

The CardText() Function accepts two parameters, and the second parameter is optional. The second parameter controls the number of digits after the decimal point.

By default, the CardText() Function will round off the fractional part, if present, to two decimal places when the second parameter value is omitted.

To try out the Code, you may open the VBA Window (Alt+F11) and open the Immediate Window (Ctrl+G) and type the following statement or a similar one with a different value or Expression:

Example:? CardText(1234.5678,3) will produce the result shown below.

Result: One Thousand Two Hundred Thirty-Four and 568/1000 only.

The first parameter can be a Number or an Expression that evaluates to a Numeric Value. If the second parameter is zero, then the Number is rounded to the next highest Integer.

Example:? CardText(1234.5678,0)

Result: Thousand Two Hundred Thirty-Five only.

To change the output to upper-case or lower-case letters, enclose the CardText() Function in the UCase() or LCase() built-in function, respectively.

Example:? UCase(CardText(1234.5678))

Result: ONE THOUSAND TWO HUNDRED THIRTY-FOUR AND 57/100 ONLY.

To prefix a Currency Description, use the following example:

Example:? "Dollars" & CardText(1234.5678)

Or

="Dollars" & CardText([UnitPrice]) on Forms  or  Reports.

Result: Dollars One Thousand Two Hundred Thirty-Four and 57/100 only.

Try the Function on a Form or Report with the data field Value as input.

The CardText() Function has not been extensively field-tested, and if you find bugs, please let me know. Use it at your own risk.

Any suggestions for improvement are welcome.

CardTextV2.zip

• Seriality Control - Missing Numbers
• Wave Shaped Reminder Ticker
• No Data and Report Error
• Lost Links of External Tables
• Link External Tables with VBA

Read More