Time Value Formatting Beyond Twenty Four Hours

Introduction.

The standard time format hh:nn:ss displays values up to 23 hours, 59 minutes, and 59 seconds (23:59:59). After that, it restarts at 00:00:01 one second past midnight (00:00:00) on the next day. Using the built-in Format() function, it is not possible to display time values beyond 24 hours, such as 45:30:15 (45 hours, 30 minutes, 15 seconds).

So, how do we format time values that exceed twenty-four hours? This requirement often arises in practical applications, such as calculating an employee’s total hours worked in a week (40:30:15), or an IT firm employee’s total internet browsing time for a month (115:47:12) extracted from server logs.

To achieve this, we need a custom function. The following VBA function accepts time values in a suitable format and produces the desired output. You can copy and paste the code into a Standard Module in your project and use it wherever extended time formatting is required. The FormatTime() Function Code.

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
'-------------------------------------------------------------------------
'VALIDATION SECTION

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

On Error GoTo FormatTime_Err

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)
   Else
   'Option 3
      'Assumes Value is Time-Value in decimal format
      v1 = StartDateTime
   End If
End If

'Option 1 to 3
Method1:
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
Method2:
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")

FormatTime_Exit:
Exit Function

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

End Function

Trial Run of Code.

Let’s go through a few examples to understand how to correctly pass parameter values to the FormatTime() function. You can try the following sample runs by typing them directly into the VBA Immediate (Debug) Window.

The FormatTime() function accepts either a Date & Time value or a Time value as its primary parameter. The second parameter is optional. The following rules 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 only.  The difference in time will be calculated by subtracting the first parameter value from the second parameter.

2. The second parameter is optional. If omitted, then the Date value will be ignored when the Date/Time value is passed in the first parameter. The time value will be formatted and displayed.

3. The first parameter can be a time value in decimal format (e.g., 0.999988425925926 ). Omit the second parameter.

4. The first parameter is acceptable as a time value in Seconds (e.g. 86399). Omit the second parameter.

Example-1:

Both Parameter values are Date/Time Values. The second Parameter value should be greater than the 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 is optional and omitted.

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

? FormatTime(SDateTime)

Result: 18:30:15

Example-3:

When the first parameter is passed as a numeric time value (for example, 2.00197916667094), the FormatTime() function interprets it correctly. In this case, the result will be 48:02:51.

This decimal value represents the difference between two Date/Time values (End Date/Time minus Start Date/Time). The result is expressed as a decimal number, where:

• The whole number represents the number of days (e.g., 2 days × 24 hours = 48 hours).

• The fractional part represents the time of day, converted into Hours:Minutes:Seconds.

Such time values often appear as the result of summarizing multiple records from a Table or Query, where the difference between start and end times is accumulated.

StartDT = 2.00197916667094 

? FormatTime(StartDT)

Result: 48:02:51

The whole number 2 on the left side of the decimal point is the number of days and it will be ignored, when the 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 a 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 the internal representation of time value, divide 86400 by 86399 (86399/86400). Result: 0.999988425925926. The time 23:59:59 is stored in computer 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 into our function and try out the result.

? FormatTime(0.999988425925926)

Result: 23:59:59

Example-4:

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

You can input seconds as a time number as the 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, try the following examples.

SD = 86399+86400+86000
   = 258799 seconds

SD = SD/86400
   = 2.9953587962963 time number

? FormatTime(SD)

Result: 71:53:19

Now, let us try passing the time value directly in seconds.
(For example, assume this value represents the total internet browsing time summarized in seconds.)

When a time value is provided in seconds, the FormatTime() function converts it into the proper format of Hours:Minutes:Seconds. This is particularly useful when server logs or other systems record usage duration in raw seconds, and we want to present it in a more readable form, such as 115:47:12.

SD= 258799

? FormatTime(SD)

Result: 71:53:19

If you maintain a library database, you can move this function there so that you don’t need to copy the code into every other database.

A library database is a common repository containing your custom functions, modules, or wizards that can be referenced by other projects. Using this approach allows you to reuse functions across multiple databases without duplicating code. For a detailed discussion on this topic, see the page MS-Access and Reference Library.

We welcome your comments or suggestions for improving the FormatTime() function. Please leave them in the Comments Section at the end of this post.

• Sum, Min, Max, Avg, ParamArray
• Useful Report Functions
• Cardinal Text Format in Access
• Auto-Numbering in Query Column

Read More

Opening Multiple Instances of Form in Memory

Introduction.

1. Over the past few weeks, we have been learning about the dot (.) separator and the exclamation (!) symbol in VBA object references. Now, we will explore some interesting techniques with Forms in VBA, specifically:

   • How to call a function procedure embedded in a form’s module (class module) from the code outside that form?

   • How to open multiple instances of a single Microsoft Access Form in memory, each displaying different information?

   For example, the Screenshot below shows two instances of the Employees Form. The first instance is behind the second, displaying employee details for IDs 4 and 5. Click the image to enlarge for a clearer view.

   

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

   
   

   Calling the Form's Class Module Public Function.

2. How to call a Function Procedure on the Form's Class 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 to call the function procedure of the form from outside.

Function procedures in a form module are useful for avoiding code duplication. They can be called from subroutines in different parts of the same form, from a command button click, or from other event procedures within the form. A function procedure in a form module can perform calculations, validation checks, update information, or act as a standalone operation—such as the one we will use in our sample Employees Form.

All event procedures in a form module are automatically declared as Private Subroutines, and they begin and end with standard statements, as shown in the sample below. Any custom VBA code that performs specific tasks should be placed within this block:

Private Sub Command8_Click()
.
.
End Sub

A Private subroutine or function is limited in scope to the module in which it is declared and cannot be called from outside that module. Declaring procedures as private is essential to avoid name conflicts with procedures of the same name in other Class Modules or Standard Modules.

To call a function procedure from outside a Form, the Function must be declared as Public in the Form’s module.

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

1. Import the Employees Table from the Northwind sample database.

2. Click on the Employees Table to select it.

3. Click on Create Ribbon.

4. Select the Form option and create a Form for Employees Table, in the format shown above.

5. Save the Form with the name frmEmployees.

6. Open the frmEmployees Form in Design View.  Set the Form Property 'Has Module' Value to Yes.

7. Select the Design Menu and select VBA Code from the Tools button group to open the Form Module.

8. Copy the following VBA code and paste it into the VBA Module of the Form.

   Public Function in Form Class Module.

   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
   
       crit = "ID = " & EmpID
       Set rst = Me.RecordsetClone
       rst.FindFirst crit
       
       If Not rst.NoMatch Then
             
        	Me.Bookmark = rst.Bookmark
           GetFirstName = rst![First Name]
       End If
       
          rst.close
          Set rst = Nothing
       
     Else
       MsgBox "Valid Employee IDs: 1 to " & empCount
   End If
   
   End Function
   

9. Save and Close the Form.

Notice that the starting line of the above function is declared as Public.

The function GetFirstName() accepts EmployeeID as a parameter, locates the corresponding record on the form, and makes that record the current record. If the search is successful, the function returns the employee’s first name to the calling procedure. If the search fails, it displays a warning message indicating that the EmployeeID provided is not within the range of IDs in the Employees table.

Next, we need a program in a Standard Module that calls the GetFirstName() function from the frmEmployees form module. This program will also demonstrate how to create multiple instances of a Microsoft Access form, allowing you to open them in memory and access their properties, methods, or control contents independently.

1. Open VBA Editing Window (Alt+F11).

2. Select the Module option from the Insert Menu and add a new Standard Module.

3. Copy and paste the following VBA Function code into the new Module.

   Call GetFirstName() from the Standard Module.

   Public Function frmInstanceTest()
   
         Dim frm As New Form_frmEmployees '1st Form instance
   
         Dim frm2 As New Form_frmEmployees '2nd instance declaration
   
         Dim Name1 As String, Name2 As String
   
     frm.Visible = True 'make the instance visible in Application Window
     frm2.Visible = True '2nd instance visible
   
     Name1 = frm.GetFirstName(4) 'Call the GetFirstName of Employee ID 4
     
     Name2 = frm2.GetFirstName(5) ''Call the GetFirstName of Employee ID 5
   
   'pause execution of this code to view
   'the Employees Form instances in Application Window.
   
   Stop
   
     MsgBox "Employees " & Name1 & ", " & Name2
     
   End Function

Trial Run of Function frmInstanceTest()

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

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

   The program will pause at the Stop statement, allowing you to view the Access application window, where multiple instances of the frmEmployees form are open in Normal View, with one instance overlapping the other.

2. Press Alt+F11 to display the Application Window, where both instances of the Form are visible, the second form overlapping the first one.

3. Click and hold the Title Bar area at the top and drag it to the right to make part of the form behind visible.

   Observe the employee records on both forms—they are different: one displays Employee ID 4, and the other displays Employee ID 5. Notice the title bar of both forms; they both show the same form name, frmEmployees. Now, return 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 the F5 key one more time to continue executing the remaining lines of code.

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

5. Click the OK button on the MsgBox.

Note: Pay special attention to the Stop statement placed above the MsgBox() function at the end of the code. The Stop statement halts VBA execution at that point. While it is typically used during debugging to trace logical errors or make corrections, here it serves a different purpose: it pauses the program so that we can switch to the Access application window and view both open instances of the frmEmployees form.

If we relied on the MsgBox() function alone, the code would still pause, but the message box would remain on top of the forms. This would prevent us from dragging the front form aside to view the one behind it. By using the Stop statement, we gain the flexibility to examine both instances directly in the application window.

If we don'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. It is not necessary to make the Form instances visible before calling the Function GetFirstName ().

The VBA Code Line by Line.

Let’s take a closer look at each line of code in the frmInstanceTest() function. While brief hints are already included within the code, explaining a few points in detail will make them clearer. We’ll begin with the first two Dim statements.

Dim frm As New Form_frmEmployees

Dim frm2 As New Form_frmEmployees

In the above Dim statement, you can see that the New keyword is 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 themselves open two instances of the frmEmployees in memory.   Form instances opened in this way are not immediately visible in the Application Window.  If we need them to be visible, then make them visible with another statement.

Next, we 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 the frmEmployees Form visible in the Application Window, for information purposes only.

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

Default Instance and Other Instances.

The default instance of a Form is opened in the following manner in programs for accessing their Properties, Methods, and Controls.  These styles of statements are always used to open a form in programs. The default instance of the Form will be automatically visible 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 frm3 object

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

name3 = Forms![frmEmployees].GetFirstName(5) 'target form in memory is the default instance
'OR
name3 = Forms("frmEmployees").GetFirstName(5) 
'OR
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 the Forms collection to address the other two instances.

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

A Shortcut Method.

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 in the Debug Window and press Enter Key:

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

When we execute the above command, it opens an instance of the frmEmployees form in memory and calls the GetFirstName() function with Employee Code 5 as the parameter. The GetFirstName() function searches for the record, finds it, returns the employee’s first name to the calling program, and then closes the form.

Tip: Even after the form is closed, the current record—Employee ID 5 in this case—remains the active record of the closed form’s last session.

You can verify this behavior by typing the following shortcut command in the Debug Window and pressing Enter:

? Forms!frmEmployees!EmployeeID

? form_frmEmployees![First Name]

'Result: Steven

A Fancy Approach.

In the above command, we didn't run the GetFirstName() method, but the current record's First Name field value is printed. If you want to get a little fancy with the command, then try this by typing it in the debug window and pressing the Enter Key:

MsgBox "First Name: " & form_frmEmployees.GetFirstName(8)
'OR
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

• Create 3D Headings on Form
• Reminder Ticker on Form
• Open Forms with Hyperlink in Listbox
• Overlaying Sub-Forms in Real-Time

Read More

Dots and Exclamation Marks Usage with Objects in VBA3

The Final Post and Continued from Last Week's Post.

In Microsoft Access, application objects such as Tables, Queries, Forms, Text Boxes, Labels, and others should be given meaningful names when created. If we don’t, Access assigns default names like Form1, Form2, Text1, Label1, and so on. These defaults provide no real indication of what the object represents. Assigning descriptive names related to their purpose makes it much easier to recognize and remember them later—especially when they are used in calculations, VBA code, or other references.

For example, in VBA, we can directly reference a control like this:

Forms!Employees!Salary

instead of the longer form:

Forms("Employees").Controls("Salary").Value

Last week, we began with a simple example that showed how the exclamation mark (!) symbol can shorten object references compared to using multiple dot separators. Once the form name and control name are known, the expression becomes more concise with the '!' symbol.

This shorthand also applies to Recordset fields. For instance:

rset!LastName

is equivalent to:

rset.Fields("LastName").Value

and both return the contents of the field’s default Value property.

If you are a new visitor to this page and topic, then please visit the earlier two pages and continue from here. The links are given below:

• Dots and Exclamation Marks Usage with Objects in VBA
• Dots and Exclamation Marks Usage with Objects in VBA2

Referencing Form's Controls.

I will repeat the first example here, introduced on the first page of this three-page series, to go further in 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: When it frmSubForm is placed as a subform within frmMain It becomes a control on the main form. Like any other control, it has its own set of properties and contains its own controls. If you generate a list of the controls on the main form, the subform will appear in that list just like a textbox, button, or label.

To see this in action:

1. Open a form that contains a subform.

2. In the Debug Window, type the following command on a single line (adjusting the form name as needed), then press Enter to display the list of control names of the main form:

For Each ctl In Forms!frmMain.Controls: Debug.Print ctl.Name: Next

This will print the names of all controls on the main form, including the subform control itself.

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

The 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 value from the innermost form in the debug window.  Look at the command given below:

? Forms!frmMain!frmSubForm.Form!frmInsideSubForm.Form!Text7

The actual address of Text7 Textbox, all elements are joined with the symbol!, except the '.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 do we need it in the address, and what does it mean? It works without an explicit reference because the system knows that it is a Sub-Form control by default.

When you drag and drop a Sub-Form onto 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 its Property Sheet(F4) and check the Source Object Property setting. You can see that the subform's name is inserted there. This is the control where we set the Link Master Fields and Link Child Fields properties to set the relationship between data on the master form and sub-form.

You can rewrite 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 does the '.Form' part in the above address mean? It means that the Sub-Form Control created by Access is a container control for loading a Form into it, and it will always be a form control, whether you explicitly add the '.Form' part in the address or not.

Loading Table or Query in a Sub-Form Control.

But the interesting part is that you can insert a Table or a Query (not an 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.

   At the top of the list, you will see all Forms. These are followed by the list of Tables, and then the list of Queries. Tables are displayed in the format:

   Table.TableName

   and queries appear in the format:

   Query.QueryName

   This notation indicates the category of object that can be assigned to the Source Object property of the subform 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.

You will find the Table or Query result displayed in the Sub-Form control.

6. Try to print the value of one of the fields displayed 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 Property Value with a Table's name, the system already adds 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
'
'OR
'
? Forms!frmMain!frmSubForm!LastName

• Working with Chart-Object in VBA
• VBA Module Object and Methods
• Hyperlink Opens Objects from another Database
• Overlaying Sub-Forms in Real-time

Read More

Dots and Exclamation Marks Usage with Objects in VBA2

Continued from Last Week's Topic

Last week, we learned a few examples of using the dot (.) separator and the bang (!) symbol with loaded Form and Report objects in memory. In this article, we’ll continue exploring the topic further.

If you haven’t read the earlier article yet, I recommend visiting that page first before proceeding here: [Dots and Exclamation Marks Usage with Objects in VBA].

After working through the previous examples, you might be a little uncertain about which syntax is easiest to use, since we experimented with different ways of referencing forms and controls in VBA. For now, let’s set that aside and approach things from a different angle.

In this section, we’ll focus specifically on the dot (.) separator as it applies to built-in library objects. Unlike with forms and controls, the bang (!) symbol is not valid for referencing these objects. You’ll also see a visual hierarchy of some of the library objects, along with examples of how to access their properties or call their methods directly from the Debug Window.

Object Library View.

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

Tools -->Options-->Editor-->AutoList Members.

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 the Tools Menu.

   • On the Editor Tab, select 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 the Classes window down, find the item with the name 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 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 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 the Enter Key:

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

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

Let us see how many Forms we have in our database by reading the Count Property Value of the 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 the CurrentProject Object's few Property Values assigned by the System. We have used the 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 the dot separator Character only to join each object/property. You cannot use the (!) symbol to address predefined objects, methods, or properties.

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

The TempVars Object.

1. Scroll down to the bottom of the Classes Window.

2. Select the TempVars Class Object. You can see its Methods (.Add(), .Remove() & .RemoveAll()) and Properties in the right side window.

3. Above the TempVars Class, you can see another object named 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.

TempVars.Add "website", "MsAccessTips.com"

We have called the Add() method of the TempVars Collection Object to instantiate the TempVar Class, and assigned 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 a Variant Type, i.e., whatever data type (Integer, Single, Double, Date, String, etc.) you assign 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 the (!) symbol.

'1st method
? TempVars.item(0).value
'Result: MsAccessTips.com
'OR
? TempVars.item("website").value
'Result: MsAccessTips.com
'
'OR
X="website"
? 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.
'TempVars.RemoveAll

The TempVars Variables are Global in nature, which 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 numerical values (like the Exchange Rates), it can be used in Calculations.

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

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

We have used the item collection object in the first three examples.  This is used immediately after the TempVars Collection Object.  When we address text boxes, labels, and other controls on an open Form or Report, we must use the keyword Controls.

You may explore other objects by selecting them in the left-side panel and inspecting their properties, Methods, and Events when you are in doubt about something.

The Data Access Object (DAO).

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

Assume that you are now comfortable with the period (.) and bang (!) symbols in object references. We will explore a few more aspects in the next session before we conclude the discussion on this topic.

• Working with Chart-Object in VBA
• VBA Module Object and Methods
• Hyperlink Opens Objects from another Database
• Overlaying Sub-Forms in Real-time

Read More

Dots and Exclamation Marks Usage with Objects in VBA

Introduction.

Beginner VBA learners are often confused about how to correctly reference controls on a Form or on Subforms. The challenge becomes even greater when a Subform contains another Subform. In such cases, specifying the full address of the control on the innermost Subform correctly is essential if you want to read the control’s contents or set its value directly through VBA.

How do we build the complete object address? Should each element be joined with a dot (.), with an exclamation mark (!), or with a combination of both?

To answer these questions, let’s start with some simple examples of using dots and exclamation marks (or bang ! symbol) in object address specifications.

For demonstration purposes, I’ve created a sample Main Form with two Subforms. The second subform is nested inside the first subform. Below, you can see both the Design View and the Form View images for reference.

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

Sample Form in Normal View

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

Dots and Exclamation Symbols.

General Usages of the dot (.) and bang symbol (!) in object references.

• A dot (.) -  after an object name to access its methods or properties.
• The exclamation mark (!) - after an object name, refers to the sub-object or control of the Top-level Object.

Sample Forms:

Main Form: frmMain

Sub-Form of Main Form: frmSubForm

Inner Sub-Form on frmSubForm:  frmInnerSubForm

Text Control on frmInnerSubForm:  Text7

Another Unbound Text Control on frmInnerSubForm:  Text3

All three forms are designed without linking to any Table (i.e., The Form's Record Source property is empty). All the Text controls on the Form are unbound.

You may design three sample forms with a few unbound text boxes and set them up one inside the other, and open them in the normal view. Open the VBA Editing Window and open the Debug Window (Ctrl+G). Now you are ready to try out the following examples after typing the code directly in the Debug Window and pressing the Enter Key.

Practical Exercises.

First, we will try to read the contents of the Text7 textbox (i.e., 'Innermost Form') from frmInnerSubForm and display the result in the Debug Window.

? Forms!frmMain!frmSubForm.Form!frmInsideSubForm.Form!Text7

Printed Result is: Innermost Form

There was an expression ="Innermost Form" inside the Text7  Text box, which is displayed in the Debug Window.

Forms!frmMain!frmSubForm.Form!frmInsideSubForm.Form!Text3 = "MS Access"

Result: MS Access

The Text3 textbox is in the Innermost Form and its Conttrol Source Property is loaded with the text: MS Access.

Check the opened Form after executing the above statement in the Debug Window.

Let us examine each part of the above command joined by an exclamation mark or a dot.  I call this a command because we directly execute this program statement in the Debug Window, with the? (Print VBA command).

The Forms collection contains all forms currently loaded in memory. Each form opened is indexed in the order it was loaded into memory—the first form opened has index 0, the second has index 1, and so on.

If your main form contains subforms, those subforms are treated as controls of the main form, just like combo boxes, labels, or text boxes. They do not appear as separate entries in the Forms collection.

To display the name of the first form loaded in memory, use the following command in the Immediate (Debug) Window:

? Forms(0).Name

This prints the value of the form’s Name property for the form at index 0 in the collection.

? Forms(0).Name

Result: frmMain - Form's Name property value is printed.

? Forms(0).Width

Result: 21225 (You may get a similar number.)
This value represents the width of the form measured in Twips.

📏 Conversion note:

• 1 Inch = 1440 Twips.

When you set the Width property of a form (whether in Inches, Centimeters, or any other regional measurement unit), VBA automatically converts that value internally into Twips.

If you know the Form's name, then the above command can be given as below:

? Forms("frmMain").Width 

In this form of the command, the Form's name can be given as a string in parentheses.

OR

? Forms!frmMain.Width

In this command, immediately after the Forms Collection name, we have used the symbol (!)  to give the name of the next level of the object of the Forms collection object, not a property or method. The next item, Width, is a property of the frmMain object, so a dot is required, not the other symbol. The bang (!) symbol is given in parentheses for legibility only.

Note: You cannot use the symbol (!) in place of the dot (.) to address the Width or Name, or any other property of the Form. There are over 200 properties for a form. You can display the names of all the properties by typing the following Code, on one line, in the Debug Window and pressing the Enter Key:

For j=0 to Forms(0).Properties.count-1:? j,Forms(0).Properties(j).Name:Next

In the above statement, the Count() Method of the Forms Property is called, takes a Count of the first Form Properties, and prints the Name of each one.

Take a Listing of the Property Names.

OR

For j=0 to Forms("frmMain").Properties.count-1:? j,Forms("frmMain").Properties(j).Name:Next

OR

For j=0 to Forms!frmMain.Properties.count-1:? j,Forms!frmMain.Properties(j).Name:Next

Note the usage of Forms("frmMain") and !frmMain, two different ways to refer to the frmMain object in the open Forms Collection.  In both cases, the form's name is given immediately after the open Forms collection name. If the frmMain form is not open at the time of executing this statement, then it ends up in an Error.  Forms(0) refer to any form that is open first in memory. It will fail only when no form is open at the time of executing this statement.

? Forms.Count

The Count() method of the open Forms Collection Object will print the count of open Forms

We will explore this topic further in the next Post.

• Working with Chart-Object in VBA
• VBA Module Object and Methods
• Hyperlink Opens Objects from another Database
• Overlaying Sub-Forms in Real-time

Read More

Combining Active and Backup Database Records in Union Query

Introduction.

Microsoft Access database has a maximum size limit of 2GB. To maintain performance and avoid reaching this limit, older records (such as previous year’s transactions) can be safely archived into a backup database for future reference. Once these records are backed up, they can be deleted from the active database. Run the Compact & Repair Utility to optimize performance for day-to-day operations.

However, there are situations where older data is required for analysis—for example, preparing budgets, setting sales targets, or comparing trends.  In such cases, we may need to reintegrate archived records with the current master table in the active database to perform meaningful analysis.

The Union Query Solution.

We can merge records of both the active table and backup table (with the same name) in a Union Query.

A simple example is given below:

SELECT Export.* 
FROM Export
UNION ALL SELECT Export.*
FROM Export IN 'G:\NEW FOLDER\DB1.MDB';

From the above example, you can see that the names of the tables exported to an active database, and backup database(DB1.MDB) are the same. No need to link the table to the active database to get access to the data from the backup database.

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

• Union Query
• Cross-Tab Union Queries for Charts
• ChDir and IN Clause of Access Query
• Deleting Folders with DOS Command

Read More

ROUNDUP Function of Excel in Ms-Access

Introduction.

Microsoft Access doesn't have this function built in. An attempt to write the code for this function, and you may use it at your own risk. Before going into the code, take a look at the usage examples of this function in Excel.

ROUNDUP() function in Microsoft Excel.

The Rules.

Rounds a number up, away from 0 (zero).

Syntax: ROUNDUP(number, num_digits). A number is any real number that you want to be rounded up.

Num_digits is the number of digits to which you want to round off the number.

Remarks: ROUNDUP behaves like ROUND, except that it always rounds a number up.

If num_digits is greater than 0 (zero), then the number is rounded up to the specified number of decimal places.

If num_digits is 0, then the number is rounded up to the nearest integer.

If num_digits is less than 0, then the number is rounded up to the left of the decimal point.

Examples: 

=ROUNDUP(3.2,0) Rounds 3.2 up to zero decimal places (4)

=ROUNDUP(76.9,0) Rounds 76.9 up to zero decimal places (77)

=ROUNDUP(3.14159, 3) Rounds 3.14159 up to three decimal places (3.142)

=ROUNDUP(-3.14159, 1) Rounds -3.14159 up to one decimal place (-3.2)

=ROUNDUP(31415.92654, -2) Rounds 31415.92654 up to 2 decimal places to the left of the decimal (31500)

Courtesy: Microsoft Excel Help Documents.

Copy and paste the following VBA Code into a Standard Module of your Database and try it out. The examples given above have successfully passed testing of the code.

The ROUNDUP() Function.

Public Function ROUNDUP(ByVal dblNum As Double, ByVal intprecision As Integer) As Double
'-------------------------------------------------
'ROUNDUP() Function of Excel Redefined in MS-Access
'Author: apr pillai
'Date  : June 2016
'Rights: All Rights Reserved by www.msaccesstips.com
'-------------------------------------------------
On Error GoTo ROUNDUP_Err
Dim sign As Integer
    sign = Sgn(dblNum)
    dblNum = Abs(dblNum)
    dblNum = dblNum * 10 ^ intprecision
ROUNDUP = (Int(dblNum + (1 - IIf((dblNum - Int(dblNum)) <> 0, (dblNum - Int(dblNum)), 1))) / 10 ^ intprecision) * sign

ROUNDUP_Exit:
Exit Function

ROUNDUP_Err:
MsgBox Err & ": " & Err.Description, , "ROUNDUP()"
Resume ROUNDUP_Exit
End Function

Suggestions for improvement of the above VBA Code are welcome.

• Database Daily Backup
• PIE Chart and live data on Form
• Overlaying Sub-Forms in Realtime
• Microsoft Access Form Move-Size Action

Read More

Uploading Comma delimited Text Data into Access Table-2

Introduction.

Last week, we learned how to upload a simple list of names (separated with commas) from a text file into a Microsoft Access Table.

Next, we will enhance the program to include additional fields—Name, Birthdate, Height, and Weight—for each record.

For reference, a sample of the text file layout is shown in the image below.

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

The text file contains a fixed number of items per line, with all four items for a single record. In last week’s example, we used only one column in the Access table output, and the number of items on each line varied.

Since all items are written to a single output column in the Access table, we need to determine how many elements exist in the x_Names array, which is created using the Split() function from a single line of text. We use the UBound() function to get the count of items in the array before processing them.

In this example, we have an output table with a fixed number of fields: Name, Birth Date, Height, and Weight. A sample image of the output table is given below:

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

The VBA Code.

VBA Code that uploads the text file into the Access table is given below:

Public Function NamesList2()
'-----------------------------------------------------
'Utility: Creating Access Table from
'       : comma separated text data.
'Author : a.p.r.pillai
'Date   : May 2016
'Rights : All Rights Reserved by www.msaccesstips.com
'-----------------------------------------------------
Dim db As Database, rst As Recordset, tdef As TableDef
Dim strH As String, fld As Field, j As Integer
Dim x_Names As Variant, tblName As String, fldName As String
Dim txtfile As String

On Error GoTo NamesList2_err
tblName = "NamesList2"
txtfile = "d:\mdbs\Names2.txt" 'Make required changes 

'create the NamesList2 table
Set db = CurrentDb
Set tdef = db.CreateTableDef(tblName)
With tdef
  .Fields.Append .CreateField("Name", dbtext, 50)
  .Fields.Append .CreateField("BirthDate", dbDate)
  .Fields.Append .CreateField("Height", dbInteger)
  .Fields.Append .CreateField("Weight", dbInteger)
End With
db.TableDefs.Append tdef
db.TableDefs.Refresh

'Open the NamesList table to write names with the text file
Set rst = db.OpenRecordset(tblName)

'Open the Names2.txt file to upload data into the table
Open txtfile For Input As #1
'setup a loop to read the data till the end-of-file reached
Do While Not EOF(1)
'read the first line of items separated with commas and
'terminated with carriage return (Enter key)into variable strH
Line Input #1, strH
'extract each item separated with comma and load into the Array variable x_Names
x_Names = Split(strH, ",")

'Read each item from array elements
'and write into the NamesList2 table fields
With rst
    .AddNew
    ![Name] = x_Names(0)
    ![BirthDate] = x_Names(1)
    ![Height] = x_Names(2)
    ![Weight] = x_Names(3)
    .Update

End With
'Repeat till the End-Of-Text File is reached
Loop

NamesList2_Exit:
rst.Close
db.Close
Set rst = Nothing
Set db = Nothing
Exit Function

NamesList2_err:
If Err = 3010 Then 'Table already exists
  'continue executing from the next line onwards
  Resume Next
Else
  MsgBox Err & ": " & Err.Description, "NamesList2()"
  Resume NamesList2_Exit
End If
End Function

How IT Works.

As in the previous example, we first attempt to create a new Access table with four fields. If the table creation process returns error code 3010, it means the table already exists, and the program skips the table creation step and continues execution. Any other error code will cause the program to terminate.

Next, the text file is opened for reading, processing one line at a time. The Split() function breaks each line into individual items and stores them in the x_Names array.

The following step is to add a new record to the Access table. Each item from the array is assigned to its corresponding field before the record in the table is updated. Since each line contains four items in a fixed order, we only need to know the array index numbers to correctly assign them:

• x_Names(0) → Name

• x_Names(1) → Birth Date

• x_Names(2) → Height

• x_Names(3) → Weight

Remember, the Split() function uses a zero-based index, so the array elements are numbered 0, 1, 2, 3 in memory, corresponding to each item in the line. The program snippet below demonstrates this process.

With rst
    .AddNew
    ![Name] = x_Names(0)
    ![BirthDate] = x_Names(1)
    ![Height] = x_Names(2)
    ![Weight] = x_Names(3)
    .Update

End With

We can use the following code in place of the above code snippet:

With rst
    .AddNew
For j = 0 To UBound(x_Names)
    
    .Fields(j).Value = x_Names(j)
    
Next
    .Update
End With

The first code snippet is beginner-friendly and easy to understand. However, it becomes less efficient when there are more items on a line to upload.

The second code snippet is more compact and does not reference field names directly. This has two advantages:

1. If any field names change later, the first snippet may generate an error, whereas the second snippet will continue to work without modification.

2. The second snippet automatically handles any number of items on a line, making it more flexible and scalable for larger datasets.

The first code cannot be used for a different table, but the second code snippet works for any table without change.

• Text search Filter Web Style
• Control Tip Text and Time Delay
• Cardinal Text Format in Access
• Border 2D Heading Text

Read More

Uploading Comma Separated Text Data into Access Table

Introduction.

Converting the contents of an Access table into a comma-delimited text file (CSV format) makes it easier to transport data over the Internet. Such files can be uploaded back into an Access table or imported into virtually any other database system at the receiving end.

We have already explored the data exporting procedure in an earlier example. You can refer to that discussion, which also demonstrates the use of Access’s GetRows() function. This function efficiently copies the entire table contents into a two-dimensional array in memory with a single operation, making it an excellent tool for preparing data for export. 

Microsoft Access already has built-in data Import and Export options.

Here, we will learn how to read a Text File containing the names of Employees, each separated by a comma, and write them into a new Access table. An Image of a sample text file is given below:

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

An Image of the output Access Table, with a single column, is given below for reference.

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

1. First, create a text file using Notepad or any other text editor, and enter some sample data. Each line should contain a few names (people or products) separated by commas. Place each set of items on a new line, as shown in the earlier example.

   ⚠️ Important: Do not put a comma at the end of any line.

2. Save the Text file with the name: Names.txt, in your database folder, or in any folder you like.

3. Open your Access Database to try out the Program given below.

4. Open the VBA Editing Window and insert a new Standard Module.

5. Copy and paste the following VBA Code into the Module and save it:

   Creating a Table From Comma-Separated Text File.

   Public Function NamesList()
   '-----------------------------------------------------
   'Utility: Creating Access Table from
   '       : comma separated text data.
   'Author : a.p.r.pillai
   'Date   : April 2016
   'Rights : All Rights Reserved by www.msaccesstips.com
   '-----------------------------------------------------
   Dim db As Database, rst As Recordset, tdef As TableDef
   Dim strH As String, fld As Field, j As Integer
   Dim x_Names As Variant, tblName As String, fldName As String
   Dim txtfile As String
   
   On Error GoTo NamesList_err
   
   tblName = "NamesList"
   fldName = "Names"
   txtfile = "d:\mdbs\Names.txt" 'Change this line to correctly point where your text file is saved.
   
   'create the NamesList table with a single field: Names
   Set db = CurrentDb
   Set tdef = db.CreateTableDef(tblName)
   With tdef
     .Fields.Append .CreateField(fldName, dbtext, 50)
   End With
   db.TableDefs.Append tdef
   db.TableDefs.Refresh
   
   'Open the NamesList table to write names with the text file
   Set rst = db.OpenRecordset(tblName)
   
   'Open the Names.txt file to read text data
   Open txtfile For Input As #1
   'setup a loop to read the data till the end-of-file reached
   Do While Not EOF(1)
   'read the first line of names, separated with commas and
   'terminated with carriage return (Enter key),into String variable strH
   Line Input #1, strH
   'extract each name separated with comma and load into the Array variable x_Names
   x_Names = Split(strH, ",")
   
   'Read each name from array elements
   'and write into the NamesList table
   With rst
   For j = 0 To UBound(x_Names)
       .AddNew
       !Names = x_Names(j)
       .Update
   Next
   End With
   'Repeat till the End-Of-Text File is reached
   Loop
   close #1
   
   NamesList_Exit:
   rst.Close
   db.Close
   Set rst = Nothing
   Set db = Nothing
   Exit Function
   
   NamesList_err:
   If Err = 3010 Then 'Table already exists
     'continue executing from the next line onwards
     Resume Next
   Else
     MsgBox Err & ": " & Err.Description, "NamesList()"
     Resume NamesList_Exit
   End If
   End Function

6. Find this line in the vba code: txtfile = "d:\mdbs\Names.txt" and make the change in the correct location of your text file. Click somewhere in the middle of the Code and press the F5 Key to run the Code.

   Note: If everything went well, then you will find the Table Names list in the Navigation Pane. If you could not find it, then right-click on the top bar of the Navigation Pane and select Search Bar. Type NamesList in the Search Bar to bring up the table in view. Click on it to open and view the data.

How This Works.

• When the program runs for the first time, it creates the NamesList Table, with a single field: Names. Subsequent runs of the program ignore the table creation action and simply append the data to the existing table from the text file.

• The program reads the first text line (for example, three names separated by commas) into the string variable strH.

  We use the Line Input statement here instead of the Input statement.

  • Line Input reads the entire line of text, stopping only when it encounters a carriage return (the Enter key at the end of the line).

  • Input, on the other hand, treats the comma as a delimiter. So, it would only read the first name and stop at the comma, ignoring the rest of the line.

• The Split() Function will break up the names separately and load them into the Variant Array Variable: x_Names.

• The Array variable x_Names will be automatically dimensioned/re-dimensioned by the Split() Function, for the number of items on the input line, and each item is loaded into the elements of the array.

• In the next step, a new record is added to the Access Table for each item loaded into the array and written to the table from the array elements.

• This process is repeated for all the lines in the text file. When the end of the text file is reached, all files are closed, and the function stops.

Next week, we will learn how to work with text files that have several fields of data in a record.

• Create a Text File from Access
• Textbox and Label Inner Margins
• Memo Field Text Formatting
• Exporting and Importing Data in Text Format

Read More

DIR TREE DOS COMMANDS

Introduction.

The Dir Command, originally part of the Disk Operating System, is also available in Microsoft Access VBA. While it is commonly used to check the presence of files or folders on a disk, you can also use it to generate a complete listing of all folders and files, along with their full pathnames. For example, you can retrieve paths in the format 'C:\Folder\Subfolder\Subfolder\... or C:\Folder\Subfolder\FileName'. Such a listing is useful for reviewing disk usage, organizing files, or maintaining records for future reference.

In 1996–97, during our organization’s migration from Novell NetWare to the Windows NT system, all user departments were instructed to review their server folder structures and remove any unused or unnecessary files and folders before the transition. This requirement led me to take a closer look at the DIR command. By using a combination of optional parameters, I was able to generate a complete listing of all folders and subfolders on our department’s server, accessed through the mapped server drive on a Windows 95 client machine. This listing proved invaluable in reviewing the contents and identifying obsolete folders and files for removal before migration.

You can generate a folder listing using either the DIR command or the TREE command, each producing a different style of output. Personally, I prefer the DIR command. The TREE Command creates a graphical view, displaying the hierarchical structure of folders and subfolders. In contrast, the DIR command presents each folder and subfolder on a single line, separated by backslashes. A sample of both listings.

 styles is shown below for comparison:

Dir Command has several optional parameters to prepare listings in different ways depending on your requirements. Most of the time, we ignore these options because their usage is not common.

DOS Command Help.

You can get a list of all optional parameters with a simple help command parameter (/?). The Usage is as given below. First, let us open the DOS Command Window.

1. Right-click on the Windows Start Button and click on the Run command.
2. Type cmd in the Open control and click OK to open the DOS Command Prompt.
3. If the prompt appears as something like C:\Users\User>, then Type:

   Cd \ then press Enter key to set the prompt to C:\>

   Cd stands for Change Directory command. The \ is the name of the Root Folder. This will set the Disk Drive C root folder as the current.

   Tip: Type the Command Exit and press the Enter key to close the DOS Window, any time you want.

4. Type the following command to display a list of optional parameters of the DIR command:

   DIR /?
   

You can display the details of any DOS command and its usage in this way, by typing the command followed by /?, in the DOS Command prompt.

Display Folders in C: Drive.

Now, let us display the listing of all the folders in the C: drive on the screen.

Warning: Don't say I didn't warn you that this will be a lengthy list, and may take a few minutes to display all of them on the screen.

Tip: You may terminate the listing at any point by pressing Ctrl+C Keys.

Type the following command in the DOS Prompt and press Enter.

C:/>DIR /A:D/S/B/P

Let us take a look at each parameter given with the DIR command.

DIR Command and its few Options.

• /A - Display files with specific Attributes. Specific attributes are given, separated by a colon, like /A:D D - for directories.
• /S - include Sub-folders also in the listing.
• /B - take a Bare-format listing and exclude summary information.
• /P - display the listing Page-wise (Pause the listing when a screen full of information is displayed. Press any key to display the next page).

If you need a listing of a particular folder and its sub-folders only, then include the folder name in the command as given below:

C:/>DIR "\RADIO" /A:D/S/B/P

Saving the Directory Listing to a File.

By default, the output of a DOS command is displayed directly on the screen. If you need a printed copy, the output must first be saved to a text file. This can be done using the output redirection symbol (>) followed by the desired file name. For example, the following command saves the output to a file named FolderList.txt:

C:/>DIR "\RADIO" /A:D/S/B > FolderList.txt

Note: If you are generating a listing of all folders and subfolders/files on a disk, the process may take some time to complete and save all the details to a text file. During this period, it may appear as though the computer has hung. Be patient and wait for the DOS prompt (C:\>) to reappear.

If you wish to terminate the command before it finishes, you can press Ctrl + C.

You may open the text file FolderList.txt in any plain text editor program to take printouts.

The TYPE of Command.

You may use the following DOS command to display the contents of the text file:

Type FolderList.txt | More

Type - Displays the contents of the text file on the screen.

| (Vertical Bar) – This symbol is called the piping symbol. It directs the output of one command to another command for further processing.

For instance, when used with the Type command, the piping symbol passes its output to the More command. The More command displays the output one screen at a time, similar to using the /P parameter with the DIR command. Press any key to view the next page of output.

The TREE Command.

The TREE command displays the folder list in a hierarchical structure.

C:/>TREE | More

Display the folder structure listing page-wise.

C:/>TREE/F | More

/F parameter displays folder names followed by Filenames.

Hope you have enjoyed doing something different and useful.

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

Read More