Dots and Exclamation Marks Usage with Objects in VBA

Dots and Exclamation Marks Usage with Objects in VBA.

Beginner VBA learners are often confused about how to correctly reference controls in a Form or in 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 the 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. Both the Design View and the Form View images are for reference.

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

Sample Form in Normal View

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

Dots and Exclamation Symbols.

General uses of the dot (.) and exclamation 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 subform, which is inside the SubForm of the Main 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 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 - The 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 other regional measurement units), VBA automatically converts that 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, the symbol (!) refers to the Forms next-level object collection, 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 to get the first form's property count 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 no Form is open when this statement executes, it generates an Error.  Forms(0) refers to the form that is open first in memory. It will fail when no form is open in memory.

? 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

Combining Active and Backup Database Records in a Union Query.

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 from 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';

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

• 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

ROUNDUP Function in Excel and MS Access. 

Microsoft Access doesn't have this function built in. An attempt to write the code for this function; 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 rounded up.

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

Remarks: ROUNDUP behaves like ROUND, except 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 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 Real-Time
• Microsoft Access Form Move-Size Action

Read More

Uploading Comma delimited Text Data into Access Table-2

Uploading Comma-Delimited Text Data into an Access Table - 2.

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 four fields: Name, Birth Date, Height, and Weight. A sample image of the output table is given below:

Right-click to open the large image in a new tab or 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 to upload in a line.

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

Uploading Comma-Separated Text Data into an Access Table.

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 read a Text File having the Employee names, 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 the 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 the large image in New Tab/Window.

1. First, create a Text file in Notepad or in any 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 as 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"; change it to the correct location of your text file. Click in the Code and press the F5 Key to run the Code.

   Note: If everything has been completed successfully, the table name should appear in the Navigation Pane. If it is not visible, right-click the Navigation Pane and select Search Bar. Enter NamesList in the Search Bar to locate the table. Click the table name to open it and view its data. 

How This Works.

• When the program runs for the first time, it creates the NamesList table with a single field, Names. On subsequent runs, the table creation procedure is skipped, and the data from the text file is appended directly to the existing table.

• 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 keep 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 based on 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 a text file having records with several data fields.

• 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

Dir Tree DOS Commands.

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 location 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 a 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 output style. 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 the Command listings is given below:

 styles are 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 uncommon.

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 the Windows Start Button and click Run command.
2. Type cmd in the 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 the Change Directory command. The \ is the name of the Root Folder. This will set the C: Drive root folder as the current directory.

   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 by typing the command followed by /? in the DOS Command prompt.

Display Folders in the C: Drive.

Now, let us display the folder listing 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 runaway listing at any time 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 subfolders 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 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, press Ctrl+C before it finishes.

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

The TYPE 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

Calculating Work Days from Date Range

Calculating Workdays from a Date Range.

How to find the number of workdays (excluding Saturdays and Sundays) from a date range in Microsoft Access?

The logic is straightforward: first, determine the number of whole weeks within the specified date range. Multiplying whole weeks by 5 gives the number of workdays from entire weeks.  From the remaining days, exclude Saturdays and Sundays, if any. Add the remaining days to the total workdays.

The DateDiff() and DateAdd() functions are used to perform date calculations. The Format() function determines the day of the week in its three-character abbreviated form, allowing Saturdays and Sundays to be identified and excluded from the remaining days.

Find the VBA Code segments for the above steps below, and the full VBA Work_Days() Function Code at the end of this Article.

1. Find the number of Whole Weeks between Begin-Date and End-Date:

   WholeWeeks = DateDiff("w", BeginDate, EndDate)

   The WholeWeeks * 5 (7 - Saturdays & Sundays) will give the number of working days in whole weeks. Now, we need to find how many working days to take, if any.

2. Find the date after all the weekdays:
   

   DateCnt = DateAdd("ww", WholeWeeks, BeginDate)

3. Find the number of workdays in the remaining days by checking and excluding Saturdays and Sundays:

   Do While DateCnt <= EndDate
         If Format(DateCnt, "ddd") <> "Sun" And _
           Format(DateCnt, "ddd") <> "Sat" Then
            EndDays = EndDays + 1
         End If
         DateCnt = DateAdd("d", 1, DateCnt)'increment the date by 1
       Loop
   

4. Calculate the Total Workdays:

   Work_Days = Wholeweeks * 5 + EndDays

The Whole Calculation is in the Work_Days Function.

The full VBA Code of the Work_Days() Function is given below:

Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer

   Dim WholeWeeks As Variant
   Dim DateCnt As Variant
   Dim EndDays As Integer
         
   On Error GoTo Err_Work_Days

   BegDate = DateValue(BegDate)
   EndDate = DateValue(EndDate)
'Number of whole weeks
   WholeWeeks = DateDiff("w", BegDate, EndDate)
'Next date after whole weeks of 7 days each
   DateCnt = DateAdd("ww", WholeWeeks, BegDate)
   EndDays = 0 'to count number of days except Saturday & Sunday

   Do While DateCnt <= EndDate
      If Format(DateCnt, "ddd") <> "Sun" And _
        Format(DateCnt, "ddd") <> "Sat" Then
         EndDays = EndDays + 1
      End If
      DateCnt = DateAdd("d", 1, DateCnt)'increment the date by 1
    Loop
'Calculate total work days and return the result
   Work_Days = WholeWeeks * 5 + EndDays

Exit Function

Err_Work_Days:

    ' If either BegDate or EndDate is Null, return a zero
    ' to indicate that no workdays passed between the two dates.

    If Err.Number = 94 Then
                Work_Days = 0
    Exit Function
    Else
' If some other error occurs, provide a message.
    MsgBox "Error " & Err.Number & ": " & Err.Description
    End If

End Function

The above VBA Code was taken from the Microsoft Access Help Document.

• Useful Report Functions
• AS400 iSeries Date in Imported Data
• GetRows Function and Exporting Data
• Cardinal Text Format in Access

Read More

Microsoft Access Form Move Size Action

Microsoft Access Form Move Size Action.

We design Access Forms that fits into the existing Application Window Width (to display/edit records), or design popup Forms with specific size without borders or scroll bars (can be moved out of the Application Window area too) or Modal type form (popup type forms with its Modal property value set to True) that must be closed, after taking suggested action on it, before you can work with other forms.

This type of form opens one over the other (when more than one form is open) on the Application Window. You must enable Overlapping Windows by selecting Office Button --> Access Options --> Current Database --> Document Window Options --> Overlapping Windows to open the forms in this way; otherwise, they will be in the Tabbed style in the Application Window.

The Pop-Up Forms.

The pop-up forms will open at the exact location of the Application Window where you have saved it during design time. If you need more details on this topic, visit the Article Link: Positioning pop-up Forms.

We can open a Microsoft Access Form and move it to the Application Window's location in the resized state; if necessary, use the MoveSize Action on the DoCmd Object.

The MoveSize Action.

Here, we will learn the MoveSize Action of the DoCmd Object in VBA.

View the YouTube Demo Video given below for reference. Select the Settings 720p HD option for better quality viewing.

Demo Video

When the Supplier Code is selected on the Supplier List Form, the corresponding Product List is displayed above and to the right of the main Supplier Form. The width of the Product List Form remains unchanged, while its height adjusts dynamically based on the number of items displayed. 

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

We need two tables, two Queries, and the Supplier List Form from the Northwind sample database to build this trick. You need to design a Form for the Product List. A Demo Database Link is given at the end of this Article to download and try out right away.

The list of Tables, Queries, and Forms required to build this database is given below.

Tables:

• Suppliers
• Products

Queries:

• Suppliers Extended
• ProductListQ

SQL Code:

SELECT Products.[Supplier IDs], Right([Product Name],Len([product name])-17) AS Product, Products.[List Price], Products.[Quantity Per Unit]
FROM Products
WHERE (((Products.[Supplier IDs].Value)=[forms]![Supplier List]![id]));

Forms:

• Supplier List
• Product List

Copy and Paste the following VBA Code into the Supplier List Form's VBA Module and save the Form:

Private Sub Company_Click()
Dim frm As Form, ProductForm As String, items As Integer
Dim mainFormHeight As Integer
Dim intHeader As Integer, intFooter As Integer
Dim intH As Integer, frmchild As Form, oneInchTwips As Integer

On Error GoTo Company_Click_Err

ProductForm = "Product List"
oneInchTwips = 1440 'Form's internal value conversion factor

mainFormHeight = Me.WindowHeight

For Each frm In Forms
  If frm.Name = ProductForm Then
    DoCmd.Close acForm, ProductForm
    Exit For
  End If
Next
DoCmd.OpenForm ProductForm
Forms(ProductForm).Refresh
items = DCount("*", "ProductListQ")

Set frmchild = Forms(ProductForm)
'Calc the required height of the chid-form
'based on number of items for selected supplier
intHeader = frmchild.Section(acHeader).Height
intFooter = frmchild.Section(acFooter).Height
'0.272 inch - product item row height
intH = intHeader + items * 0.272 * oneInchTwips + intFooter
intH = intH + oneInchTwips '- one inch margin from bottom
'Move and resize the height of the child form
'4.275 inches to the right from left of the Application Window
'1.25 inches - arbitrary value taken for bottom margin
DoCmd.MoveSize 4.275 * oneInchTwips, mainFormHeight - intH, , (items * 0.272 + 1.25) * oneInchTwips

Company_Click_Exit:
Exit Sub

Company_Click_Err:
MsgBox Err & ": " & Err.Description, , "Company_Click()"
Resume Company_Click_Exit

End Sub

Private Sub Form_Current()
Me.Refresh
End Sub

Note: Don't forget to change the Overlapping Windows option in the Access Options settings mentioned in paragraph two from the top.

1. Open Supplier List Form.
2. Click the Supplier ID Field (with the Company column heading) of any record to open the  Supplier Products List display in the Resized Product List Form, and move it to its specified location.

Download the Demo Database.

Download Demo MoveSize Demo.zip

• Synchronized Floating Popup Form
• Open Forms with Hyperlinks in Listbox
• Animated Floating Calendar
• Memo Field Text Formatting

Read More

PROPER Function of Excel in Microsoft Access

PROPER Function in Excel and Microsoft Access.

We have the UCase() Function in Access to convert all letters in a string to uppercase letters (UPPER() Function in Excel).

? UCase("LeARn mS-access Tips aNd tRicKs")

Result: LEARN MS-ACCESS TIPS AND TRICKS

LCase() Function (LOWER() in LCase ("LeARn ms-access Tips and tricks")

? LCase("LeARn mS-access Tips aNd tRicKs")

Result: learn ms-access tips and tricks

The Built-in Excel Function PROPER() formats the first letter of each Word in Capital letters and the rest of the word in lowercase letters. 

I don’t say there is no Function in Access to do that job, but its name is not PROPER.

Microsoft Access Function StrConv() can do what the PROPER() Function does in Excel.

Its usage is as given below:

? StrConv("LeARn mS-access Tips aNd tRicKs",3)

Result: Learn Ms-access Tips And Tricks

The STRCONV() Function.

The STRCONV() Function needs two parameters:

1. The Text value to be converted
2. The conversion type parameter is an integer value.

   Here, parameter value 3 is for Proper-case conversion.

I know what you will ask: can we do other conversions with this Function?

Yes, STRCONV(Text,1) for UCase() Function and STRCONV(Text,2) for LCase() Function

There are other uses for the STRCONV() function, with different parameters. If you are interested in exploring further, then type STRCONV in the search control of Microsoft Access Help Documents and get the details.

If you find using the uppercase function StrConv() complicated, we can define an Access equivalent of Excel’s PROPER() function.

Here is the VBA Code:

Public Function PROPER(ByVal strText As String) As String
PROPER = StrConv(strText, 3)
End Function

Copy and Paste the above VBA Code into a Global VBA Module of your Project. If you have a Library Database, copy and paste the code into it and save.

I will not be surprised if you name the function as PCase() in line with the other text conversion functions LCase() and UCase().

1. Roundup Function of Excel in MS-Access
2. Proper Function of Excel in Microsoft Access
3. Appending Data from Excel to Access
4. Writing Excel Data Directly into Access
5. Printing MS-Access Report from Excel
6. Copy-Paste Data From Excel to Access 2007
7. Microsoft Excel Power in MS-Access
8. Rounding Function MROUND of Excel
9. MS-Access Live Data in Excel
10. Access Live Data in Excel-2
11. Opening Excel Database Directly
12. Create Excel Word File from Access

Read More

RUNSQL Action in MACRO and VBA

RUNSQL Action in Macros and VBA.

Microsoft Access beginners are often confused about the difference between the RunSQL action in a Macro and the 'DoCmd.RunSQL' method in Visual Basic for Applications (VBA). Regardless of where it is used—in a macro or VBA—you must provide the SQL statement of an Action Query or a Data Definition Query. These are the only types of queries supported.

If you’re unsure which queries fall under these categories, refer to the following list:

Action Query Types.

Action Query Types

Query Type	Statement
Append	INSERT INTO
Delete	DELETE
Make-Table	SELECT ... INTO
Update	UPDATE

Data Definition Queries.

Data-Definition Query Types

Query Type	Statement
Create a table	CREATE TABLE
Alter a table	ALTER TABLE
Delete a table	DROP TABLE
Create an Index	CREATE INDEX
Delete an Index	DROP INDEX

Using the SQL Statement of any other Query type in the RUNSQL Action will result in errors. In Macro, the length of an SQL statement is 256 characters or fewer.

The 'DoCmd.RunSql' method of VBA can execute an SQL statement with a maximum length of 32768 characters or less.

Note: You are not allowed to give an existing Query Name as a parameter to this command. But in VBA, you can load the SQL Statement of a predefined query into a String Variable and use it as the parameter to the DoCmd.RUNSQL command. 

Example-1:

Public Function DelRecs()
'Existing Delete Query’s SQL is used in this example
Dim db As Database, qryDef As QueryDef
Dim strSQL As String

'Read and save the SQL statement from 'Query48'
'and load into strSQL string variable
Set db = CurrentDb
Set qryDef = db.QueryDefs("Query48")
strSQL = qryDef.SQL

'Execute the SQL statement after
'disabling warning messages
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

'Using DoCmd.OpenQuery Command to run a Delete Query
DoCmd.SetWarnings False
DoCmd.OpenQuery "Query48", acViewNormal
DoCmd.SetWarnings True

End Function

The RunSQL action in a Macro allows you to modify or delete records across multiple records in one step. Before executing an Action Query, Microsoft Access displays a warning message appropriate to the action (such as update or delete) and waits for the user’s response to either proceed or cancel.

Once you have thoroughly tested the Action Query, you can instruct Microsoft Access to temporarily suppress warning messages during the RunSQL execution. After the query completes, you should re-enable the warning messages so that Access can continue to alert you about unexpected errors or issues as they occur.

The SetWarnings action in a Macro (or the DoCmd.SetWarnings method in VBA) is used to set or reset the System warning messages. This is particularly useful when data processing for a report involves one or more Action Query steps within a Macro.

In the Macro design example shown below, the first action is SetWarnings, with its parameter set to No, which suppresses the warning messages when the RunSQL executes the Action Queries in the next step. The third action is another SetWarnings, this time with its parameter set to Yes, which enables System warning messages. This ensures that Microsoft Access can once again handle and report any unexpected errors as they occur.

Right-click to open the large image in New Tab/Window.

Example-2:

Create a Table with the Data-definition SQL

Warning: Double-check that you are not using any existing table name for this sample run.

Public Function DataDefQuery()
Dim strSQL As String

strSQL = "CREATE TABLE Books  (Title TEXT(50) NOT NULL, Author TEXT(50) NOT NULL, PublishDate DATE, Price CURRENCY)"

DoCmd.RunSQL strSQL

End Function

The OpenQuery Action in Macro and DoCmd.OpenQuery Method of VBA uses the name of a predefined Query (of any type) to open it in any of the following Views:

• Design View
• Datasheet View
• Print Preview
• Pivot Table
• Pivot Chart

• Percentage on Total Query
• Union Query
• Crosstab Union Queries for Charts
• Filtering Data for Different Users

Read More