Printing Ms-Access Report from Excel

Introduction.

Printing an MS Access Report from Excel or from Another Database

In Microsoft Access applications, we often use a Front-End/Back-End design. All the tables are maintained in the Back-End database and linked to the Front-End. We link them because the Front-End frequently needs to retrieve or update data from those tables.

Once linked, external database tables behave just like native tables in the Front-End. You can work with them seamlessly, without noticing any difference.

Note: When linking tables from a network location, always use the full UNC path of the database file, for example:

\\ServerName\FolderName\SubfolderName\DatabaseName.mdb

Instead of a server-mapped drive path, such as:

T:\FolderName\SubfolderName\DatabaseName.mdb

This is important because if the mapped drive letter (e.g., T:\) changes later to K:\, Z:\, or another letter, the links will break. Using the full network address ensures the linked tables remain intact regardless of drive mappings.

Updating a Table not Linked with the Current Database.

When you need to retrieve (or add/update) information in a table that is not linked to the front-end database, we need a VBA program to do that.

A sample procedure is shown below:

Public Function CategoryTable()
Dim wsp As Workspace, db As Database
Dim rst As Recordset

'Set a reference to the active Workspace
Set wsp = DBEngine.Workspaces(0)

'DBEngine.Workspaces(0).Databases(0) is CurrentDB 
'Open a second database in Workspace(0).Databases(1) position 
Set db = wsp.OpenDatabase("C:\mdbs\NWSample.mdb")
 
'Open Categories recordset from Database(1) 
Set rst = db.OpenRecordset("Categories")
 
'Display the CategoryName field value 
MsgBox rst![CategoryName]
 
rst.Close 
db.Close 

'remove objects and release memory
Set rst = Nothing 
Set doc = Nothing 
Set db = Nothing 
Set wsp = Nothing 
End Function

The Databases Workspace.

Working with Databases in the DBEngine Workspaces Collection

When an Access database is opened in the Application Window, it is actually opened within a Workspace in the Workspaces collection, under the Application.DBEngine object.

The default Workspace is Workspace(0).

The first open database inside that workspace is addressable as Workspaces(0).Databases(0).

You can open more than one database within the same workspace and work with its tables or query recordsets. This approach is often better than permanently linking those tables to the Front-End database, especially if you do not need to use them on a day-to-day basis.

⚠️ However, there is an important limitation: you cannot open Forms or Reports from databases opened this way.

In fact, the object reference:

Application.DBEngine.Workspaces(0).Databases(0)

is equivalent to the CurrentDb object. While several databases can be loaded into Workspaces(0), only the current database will be visible in the Access Application Window. Other databases, if opened, will remain in memory until you explicitly close them.

This means you can:

Read and update tables from an external database without linking them together.

Create new tables or queries in those external databases as needed.

But you cannot:

Open forms or reports stored in those databases through this method.

Creating Queries on a non-linked External Table.

What if we want to create a query using data from an external table that is not linked to the front-end database? Surprisingly, Microsoft Access allows you to create queries without permanently linking external tables to the current database. Curious about how this works? You can learn the trick [here].

Up to this point, our discussion has focused on working with external tables and queries. However, the methods we covered so far will not allow you to open a form or report from another database. Normally, this requires opening the database in a separate Access Application Window.

That said, this statement is not entirely accurate—we’ll explore why in next week’s session.

But before diving into the Excel-based procedure, let’s first see how the same task can be handled from the active database itself.

The simple procedure steps are given below:

1. Create a separate Access Application Object.

2. Set its visible property value to Yes, so that we can see the Application Window.

3. Open the required Access Database within that Application Window.

4. Open the required Report in Print-mode (acViewNormal) to send the Report directly to the default printer, or in Print-Preview-mode (acViewPreview) to view the report before sending it to the printer manually.

5. Close the Database first and Quit the Application.

The Sample VBA Code is given below:

' Include the following in Declarations section of module.
Dim appAccess As Access.Application

Public Function PrintReport()
'---------------------------------------------------------
'Original Code Source: Microsoft Access Help Document
'---------------------------------------------------------

    Dim strDB As String

' Initialize string to database path.
    Const strConPathToSamples = "C:\Program Files\Microsoft Office\Office11\Samples\"
    strDB = strConPathToSamples & "Northwind.mdb"

' Create new instance of Microsoft Access Application.
    Set appAccess = CreateObject("Access.Application")
' Make Application Window Visible
    appAccess.Visible = True

' Open database in Microsoft Access window.
    appAccess.OpenCurrentDatabase strDB

' Open Catalog Report in Print Preview
    appAccess.DoCmd.OpenReport "Catalog", acViewPreview
    
' Enable next line of code to Print the Report
    'appAccess.DoCmd.OpenReport "Catalog", acNormal

    'appAccess.DoCmd.Close acReport, "Catalog", acSaveNo
    'appAccess.CloseCurrentDatabase
    'appAccess.Quit
    
End Function

1. Copy and paste the above code into a new Standard Module of your Database.

2. Make changes to the Path of the Database and Report name, if needed.

3. Click somewhere in the middle of the Code and press F5 to run the Program.

In Microsoft Excel.

If you were able to run the code successfully and Print/preview your Report in a separate Access Application Window, then you may proceed to do the same thing from Microsoft Excel.

1. Open Microsoft Excel.

2. Display VBA Window (Developer ->Visual Basic).

3. Insert a Standard Module (Insert -> Module) in the VBA Window.

4. Copy and paste the above Code into the Module and save it.

Before running the code, you must add the Microsoft Access 12.0 Object Library to the Excel Project.

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

1. Select the References option from the Tools Menu.

2. Find Microsoft Access 12.0 Object Library (or whatever version is available on your machine) and put a check mark to select it.

3. Click the OK Command Button to close the Control.

4. Click in the middle of the Code and press F5 to run.

You will see the same result you saw when you ran the Code in Microsoft Access.

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 an Excel Database Directly
12. Create an Excel Word File from Access

Read More

Search for Record Macro Action Access2007

Introduction.

Searching for a record on the Form is normally done with the help of the Find (Ctrl+F) method of Microsoft Access. For the search operations in the Last Name field of the employee's record, on the Employees Form, it is absolutely necessary that the Last Name data field is present in the Form. 

This is where the SearchForRecord Macro Action mainly makes the difference, besides other flexible features.  You can search for the Last Name of an employee even when this field is not present in the Form.  But the data field must be available on the Record Source (Table/Query) on the Form.  The SearchForRecord Macro Action can accept logical comparisons like <, >, AND, OR, and BETWEEN to search and find the required record.  But, the Find method accepts only one of the three options, viz. Whole Field, Any Part of Field & Start of Field to search for a record on any of the available fields on the Form.

Prepare for a Test Run.

Let us try out SearchForRecord Macro Action with the help of data from the Employees Table of the Northwind sample database.

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

2. Design the Form frmEmployees in Columnar Format (see the sample image given below). You can do this quickly with the help of the Form Wizard option from the Forms Group under Create Menu.

   

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

3. Select the Last Name Textbox and Delete it.

   We will try to search and find records using this field, without placing the field on the Form. We will also try the search method with the First Name field on the Form combined with the Last Name field (not on the form) and learn the usage of AND, OR Logical Operations in the search criterion in the Macro we are going to create.

4. Create two TextBoxes and a Command Button at the Footer of the Form, as shown in the image above.

5. Change the Child Label Caption value of the first Text Box to Last Name and the Textbox Name Property Value to lstName.

6. Similarly, change the second Child Label Caption of the second Text Box to First Name and the Textbox Name Property Value to fstName.

   Before making changes to the Command Button Properties we must create a Macro with the SearchForRecord Action.

7. Save the Form with the name frmEmployees and close it.

Creating a Macro.

1. Select Macro from the Create Menu to open a new Macro design window.

   The Sample Macro image is given below:

   

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

2. Select SearchForRecord from the drop-down list in the Action Column of the Macro.

3. Set Form in the Object Type control, in the property sheet below.

4. Select frmEmployees from the drop-down list of Object Name.

5. Select First in the Record control.

6. Type [Last Name] = "Kotasa" in the Where Condition control.

   NB: You may open the Employees Table to view and select any record value from the Last Name field, preferably after a few records from the beginning of the records. Note down the first name of the employee also so that we can cross-check the correctness of the record found by the search operation when the record changes on the form.  Remember, we have deleted the Last Name Field from the frmEmployees Form.

   Note: Initially, we will try this method with simple constant criteria (easier to understand its usage) in the Where Condition control and search for the last name of an employee Kotasa in the Last Name field, not on the Form.  After that, we will modify the macro to use the values typed on the TextBoxes, which we have created on the Footer of the frmEmployees, as search criteria.  This way it will give us much-needed flexibility in search operations on the Form, by simply changing the search values in the text boxes.

7. Save the Macro with the macro name - macSearch and close.

The Form Design Change.

1. Open frmEmployees in Design View.

2. Click on the Command Button at the Footer of the Form to select it.

3. Display the Property Sheet (F4), if it is not visible.

4. Change the Name Property value to cmdRun and change the Caption value to Search For the Record.

5. Select On Click Event on the Event tab of the Property Sheet and type the macro name macSearch, or select it from the drop-down list.

6. Save the Form and open it in Normal View.  You will see the first record on the form is active now.

7. Click on the Command Button to search the Last Name Kotasa (or whatever the last name you have inserted in the criteria) on the Form.

   You will see the record changes on the Form. Check and confirm that the First Name on the form matches the name you noted down earlier.  We will modify the Macro to make it more flexible.

8. Close the frmEmployees for now.

   The Condition Control Settings

   Now, we will modify the Where Condition control settings on the Macro to take the values we type on the TextBoxes (with the names: lstName and fstName) on the frmEmployees Form, rather than using constant values in the search criteria, as we did in the earlier example. We must create an expression to take the lstName and fstName TextBox values joined with the AND Logical operator to conduct a search operation in the Last Name and First Name fields on the Form.  For this reason, we need to take some extra effort to build the expression correctly so that it works every time.  We must join the Data Field names (Last Name, First Name) and Text Box (lstName, fstName) contents combined with the Logical Operator AND  in the expression.

1. Open the macSearch Macro in Design View.

2. Copy and paste the following expression into the Where Condition control, replacing the existing one.

   ="[Last Name] = '" & [lstName] & "' AND [First Name] = '" & [fstName] & "'"

   The Search Criteria Expressions.

   The expression starts with an = sign, the field name Last Name has a space in the middle and is placed in square brackets, followed by an equal sign for an exact match of value, and the whole segment of the expression is placed in double quotes.  Before closing the second double-quote an open single quote is placed because we have joined the text data from the lstName text box on the form.

   The next segment of the expression is opening with a double-quote, and a closing single quote for the first text data, followed by a space and the AND logical operator followed by the First Name field name in square brackets, followed by a space, = sign, opening single quote for the fstName text value followed by the closing double-quote of the third segment of the expression.  The fstName text box reference, from the Form, is joined with an ampersand followed by an ampersand to join the closing single quote within double quotes.

3. Save and close the macro.

Since we have used the AND Logical operator both the Last Name and First Name field values should match to find a record on the Form.

1. Open the Employees Table and note down the last name and first name of a few records on paper and close the Table.

2. Open the Form frmEmployees.

3. Type the last name and first name of the first record, you have noted down earlier, into their respective text boxes on the footer of the Form.

4. Click the Command Button to run the macSearch and find the record on the form that matches both the last name and first name.  Remember, the last name field is not there on the form.  You may repeat this method with the other record values you noted down earlier if any.

5. You may modify the macro to change the AND Logical Operator to OR.  You may try the macro after entering the search value in any one of the text boxes (lstName or fstName) or values in both text boxes.  If any one of the two or both values matches the record, then it will be returned.

   The modified expression is given below for reference:

   ="[Last Name] = '" & [lstName] & "' OR [First Name] = '" & [fstName] & "'"

• Calculating Time Difference
• Centralized Error-handler & Error- log
• Duplicating fields with Conditional Formatting
• How to use form filter on Report
• Saving Report Pages as separate PDF files

Read More

Exporting and Importing Data in Text Format

Introduction

This is all about exporting data into a Text File and sending it, through the Internet as an E-mail attachment, to a remote location.  At the receiving end, the Text data is converted back into its original form and added to an existing table.  No field delimiters, field names, or any other details are sent along with the actual data lines.  The Source and Target Tables should have identical Structures, and these are known by the sending and receiving end users only.

Let us take a quick look at some sample data and what they look like when converted into Text Format.

Sample MS-Access Table: Export

MS-Access Table: Export text data-image, when exported into Text File: Export.txt.

As you can see from the above text image that each record from Access Table: Export is converted into a continuous stream of characters and written into the Text File in separate lines.  The Text File is saved with the same name as the Table: Export with the file extension .txt (Export.txt).

The first two data fields on the Source Table are Text Fields (size 15 characters each), the third field is Date type and the next five fields are of Numeric Type.

Data Field details are given below:

Seq	Field	Type	Size
1.	LastName	Text	15
2.	FirstName	Text	15
3.	DofB	Date
4.	Height	Number	Integer
5.	Weight	Number	Long Integer
6.	H	Number	Single
7.	W	Number	Double
8.	Dcml	Number	Decimal

The VBA txtExport() Function Code

The VBA program given below reads the MS-Access Table, record by record, converts them into text format, and writes out into an external Text File, with the same name as the Source Table.  The text file:Export.txt is created in the Default Database Folder.  I have created the sample data Table with the name Export for Demo purposes only.  You can use any Table from your database, but see that it doesn’t contain any field types other than text, date, or Number.

Public Function txtExport(ByVal tblName As String)
'-----------------------------------------------------
'Purpose: Export Data into Text Format
'Author : a.p.r.pillai
'Date   : June, 2013
'Remarks: All Rights Reserved by www.msaccesstips.com
'-----------------------------------------------------
'Data Types
'----------
'Text - 10 = Actual size
'Date - 8 = 10 characters
'Integer - 3
'Long Integer - 4
'Single - 6
'Double - 7
'Decimal - 20
'Numeric types 3,4,6,7 or 20 = 12 characters

Dim tblSize() As Variant
Dim db As Database, rst As Recordset
Dim fld As Field, fldCount As Integer
Dim j As Integer, tbldef As TableDef
Dim numSize As Integer, dtSize As Integer
Dim fmt As String, outTxt As String
Dim outFileName As String

On Error GoTo txtExport_Err

numSize = 12
dtSize = 10

'Exported Text Filename is same as Tablename with file-extension .Txt
outFileName = tblName & ".txt"

Set db = CurrentDb
Set tbldef = db.TableDefs(tblName)
fldCount = tbldef.Fields.Count - 1
'A singly dimensioned Array of Variant Type 
'is decalred for number of fields in the Table. 
ReDim tblSize(fldCount) 

'The Array is initialized with text data type 
'for appropriate size to hold text, date & Numeric Values 
For j = 0 To fldCount 
   Set fld = tbldef.Fields(j) 
   Select Case fld.Type 
      Case 3, 4, 6, 7, 20 'Numeric data type 
        tblSize(j) = String(numSize, "0") 
      Case 8 'Date Data type 
        tblSize(j) = String(dtSize, "0") 
      Case 10 'Text data type 
        tblSize(j) = String(fld.Size, "x") 'Actual Text Field-size 
    End Select Next 

'Open the Source Table 
    Set rst = db.OpenRecordset(tblName) 
    'Create and open the output text file in the same folder of the database 
    Open outFileName For Output As #1 
    'Read records till the end of Table and convert them into text format 
    Do While Not rst.EOF 
       For j = 0 To fldCount 
          Set fld = tbldef.Fields(j) 
          Select Case fld.Type 
             Case 3, 4, 6, 7, 20 
                fmt = "00000000.000" 
                'position data right-aligned into the text variable 
                RSet tblSize(j) = Format(rst.Fields(j).Value, fmt) 
             Case 8 
                fmt = "dd/mm/yyyy" 
                'position data right-aligned into the text variable 
                RSet tblSize(j) = Format(rst.Fields(j).Value, fmt) 
             Case 10 
                'position data left-aligned into the text variable 
                LSet tblSize(j) = rst.Fields(j).Value 
          End Select 
       Next 
       outTxt = "" 
       For j = 0 To fldCount 
          'join all text data variables into a single text line 
          outTxt = outTxt & tblSize(j) 
       Next 
          'Write into text file 
       Print #1, outTxt 
       'take next record to export 
   rst.MoveNext 
Loop 

'Close the Text File and other files 
Close #1 
rst.Close 
db.Close 

Set db = Nothing 
Set tbldef = Nothing 
Set fld = Nothing 

txtExport_Exit: 
Exit Function 

txtExport_Err: 
MsgBox Err & " : " & Err.Description, , "txtExport()" 
Resume txtExport_Exit 
End Function

Running the Code

You can Run the Program directly from the Debug Window Command line:

Syntax: txtExport "Table Name"

txtExport "Export"

Or run it from a Command Button Click Event Procedure

Private Sub cmdRun_Click()
txtExport "Export"
End Sub

The Data Format Change.

1. When Text Type Field values are converted into output text the actual size of the field is calculated and the data is left-aligned within the actual size of the text field. If the field value is shorter than the actual size of the field, then the balance character positions are filled with spaces.
2. Date Field value uses 10 characters (dd/mm/yyyy) when converted into text.
3. All Numeric Field Values are converted into a 12 character text type and positioned Right-aligned in the output memory image, filled with zeroes at left positions.

NB: Date and Numeric Data text field sizes (Date=10, Number=12) are selected arbitrarily and can be modified if needed.  The MS-Access Table should have only the above three types of Data (Text, Date & Number) Fields in it.

The Text File created from MS-Access Table can be sent through E-Mail Attachment to the remote location.  The VBA Program given below can be used for converting the Text File back into Access Data and append into the Table with the same structure as the Source Table.

Public Function txtImport(ByVal txtFileName As String)
'txtFileName is same as Tablename with file-extension .txt
'-----------------------------------------------------
'Purpose: Import Text-Data into Table
'Author : a.p.r.pillai
'Date   : June, 2013
'Remarks: All Rights Reserved by www.msaccesstips.com
'-----------------------------------------------------
'Data Types
'----------
'Text - 10 = Actual size
'Date - 8 = 10 characters
'Integer - 3
'Long Integer - 4
'Single - 6
'Double - 7
'Decimal - 20
'Numeric types 3,4,6,7 or 20 = 12 characters

Dim tblSize() As Variant
Dim db As Database, rst As Recordset
Dim fld As Field, fldCount As Integer
Dim j As Integer, tbldef As TableDef
Dim numSize As Integer, dtSize As Integer
Dim fmt As String, outTxt As String
Dim inputFileName As String, I As Integer, k As Integer

On Error GoTo txtImport_Err

numSize = 12
dtSize = 10

inputFileName = txtFileName & ".txt"
Set db = CurrentDb
Set tbldef = db.TableDefs(txtFileName)
fldCount = tbldef.Fields.Count - 1

'A singly dimensioned Array of Variant Type
'is decalred for number of fields in the Table.
ReDim tblSize(fldCount)

'The Array is initialized with text data type
'for appropriate size to hold text, date & Numeric Values
For j = 0 To fldCount
   Set fld = tbldef.Fields(j)
   Select Case fld.Type
      Case 3, 4, 6, 7, 20 'Numeric data type
          tblSize(j) = String(numSize, "0")
      Case 8 'Date data type
          tblSize(j) = String(dtSize, "0")
      Case 10 'Text Field
          tblSize(j) = String(fld.Size, "x") ' Actual Text Field-size
   End Select
Next

'Open the Target Table Export
Set rst = db.OpenRecordset(txtFileName)

'Open the input text file
Open inputFileName For Input As #1

'Read the Text file, convert and add the data to the Target Table
  Do While Not EOF(1)
   'Read text data line
   Input #1, outTxt
   I = 1 'first field value off-set
   'Add a new record into the table
   rst.AddNew

   For j = 0 To fldCount
      Set fld = tbldef.Fields(j)
      'read data size for extracting correct number of characters
      'from the text line
      k = Len(tblSize(j))
     
      Select Case fld.Type 'check the current field type
         Case 3, 4, 6, 7, 20 'if numeric data type then
          'Extract numeric data, convert and write into data field
            rst.Fields(j).Value = Val(Mid(outTxt, I, k))
            I = I + k 'increment to next field off-set
         Case 8 'if it is date then
          'Extract Date data, convert and write into data field
            rst.Fields(j).Value = CDate(Mid(outTxt, I, k))
            I = I + k 'increment to next field off-set
         Case 10 'Text data type
          'Extract Date data, convert and write into data field
            rst.Fields(j).Value = Mid(outTxt, I, k)
            I = I + k 'increment to next field off-set
       End Select
    Next
      rst.Update
      'initialize the variable to read the next line of text data
      outTxt = ""
Loop

'No more text data in the file
Close #1
rst.Close
db.Close
Set db = Nothing
Set tbldef = Nothing
Set fld = Nothing

txtImport_Exit:
Exit Function

txtImport_Err:
MsgBox Err & " : " & Err.Description, , "txtImport()"
Resume txtImport_Exit

End Function

Debug Window Command line Run Syntax:

txtImport "Text File Name"

txtImport "Export"

NB: Text File Name is entered without the file extension (.TXT). Target Table should have the same structure as the Source Table.

• Centralized Error-handler & Error- log
• Duplicating fields with Conditional Formatting
• How to use form filter on Report
• Saving Report Pages as separate PDF files
• Autonumber with Date and Sequence Number

Read More

Finding Difference between Dates in rows of a Column

Introduction.

Your Company has several Customers who place orders for Products regularly, and you maintain the Orders detail data in an MS-Access Table.  The management would like to know the frequency of each customer order so that the company can plan and acquire adequate stock in advance to meet their requirements in time.

We have a table of Orders (tblOrders), of a particular customer, with the following fields and sample data as shown below:

AutoID	OrderNo	OrderDate	OrderValue	Days
1	2012060500	05-06-2012	100000
2	2012070701	15-07-2012	50000
3	2012109000	25-10-2012	150000
4	2012120050	27-12-2012	125000
5	2013028075	14-02-2013	175000

Our task is to find the frequency of orders, in the number of days, from this particular customer.  This can be done by finding the difference between the Order Dates.  The sample data records are organized in such a way that they have a sequence number in the first column.  This is very important for the first method we are going to try out.  It is easy to find the OrderDate in the next record with the help of the Dlookup() Function in an MS-Access Query.

Organizing the Data.

We are going to use only two columns from the tblOrders Table, AutoID & OrderDate, and will create a third column Days by finding the difference between Order Dates. 

Here, the data records are organized (as shown above) in such a way that the output in the Days Column can be found with a simple Query.  The Query-based solution works only when the AutoID field has consecutive values and the OrderDate is arranged in Ascending Order. 

The SQL of the sample MS-Access Query is given below:

SELECT tblOrders.AutoID,
 tblOrders.OrderID,
 tblOrders.OrderDate,
 DateValue(nz(DLookUp("OrderDate","tblOrders","AutoID = " & [AutoID]+1),"31-12-1899")) AS EndDate,
 IIf([EndDate]-[OrderDate]<0,0,[EndDate]-[OrderDate]) AS Days
FROM tblOrders
ORDER BY tblOrders.OrderDate;

The result of the run of the Query is shown below:

AutoID	OrderID	OrderDate	EndDate	Days
1	2012060500	05-06-2012	15-07-2012	40
2	2012070701	15-07-2012	25-10-2012	102
3	2012109000	25-10-2012	27-12-2012	63
4	2012120050	27-12-2012	14-02-2013	49
5	2013028075	14-02-2013	0	0

Even though the MS-Access Query-based solution looks simple and effective, preparing data with consecutive number values is not that easy, because you will be filtering and creating output data from a larger data file and the auto-number values, if exists, will not be consecutive, if they are taken from the main table.  But, you can create auto-numbers in Query Column very easily with a VBA User-defined Function.  You can find the Code and details here.

The VBA-Based Solution.

The VBA-based solution doesn’t need a column with consecutive numbers. But, the OrderDate field must be sorted in Ascending Order.  To prepare the data from our MS-Access Table tblOrders, as input for our VBA Program FrequencyCalc() we need only a SELECT Query with required fields from the tblOrders Table.  The SQL of the sample Query is given below:

Query: tblOrdersQ – OrderDate field value is sorted in ascending order.

SELECT tblOrders.OrderID,
 tblOrders.OrderDate,
 tblOrders.Days
FROM tblOrders
ORDER BY tblOrders.OrderDate;

VBA Code of the FrequencyCalc() Function is given below:

Public Function FrequencyCalc()
'----------------------------------------------------------
'Author: a.p.r.pillai
'Date  : March 2013
'All Rights Reserved by www.msaccesstips.com
'----------------------------------------------------------
Dim db As Database, rst1 As Recordset, rst2 As Recordset
Dim m_diff As Integer

On Error GoTo FrequencyCalc_Error

Set db = CurrentDb
'Open tblOrdersQ's first instance and position on the first record
Set rst1 = db.OpenRecordset("tblOrdersQ", dbOpenDynaset)
'Open tblOrdersQ's second instance and position on the second record
Set rst2 = db.OpenRecordset("tblOrdersQ", dbOpenDynaset)
rst2.MoveNext

'Find difference between dates from first & second instances of OrderDates
'in the same Query.
'update number of days in the second record onwards.
Do While Not rst1.EOF
   m_diff = rst2!OrderDate - rst1!OrderDate
   If Not rst2.EOF Then
     With rst2
        .Edit
        !Days = m_diff
        .Update
      rst1.MoveNext
        .MoveNext
      End With
      If rst2.EOF Then
         Exit Do
      End If
   End If
Loop
rst1.Close
Set rst1 = Nothing
rst2.Close
Set rst2 = Nothing
db.Close
Set db = Nothing

FrequencyCalc_Exit:
Exit Function

FrequencyCalc_Error:
MsgBox Err & " : " & Err.Description, , "FrequencyCalc()"
Resume FrequencyCalc_Exit
End Function

Demo Run Result of VBA Code.

The run result of the Program is given below:

AutoID	OrderID	OrderDate	Days
1	2012060500	05-06-2012
2	2012070701	15-07-2012	40
3	2012109000	25-10-2012	102
4	2012120050	27-12-2012	63
5	2013028075	14-02-2013	49

The VBA procedure updates the frequency of Days in the second record onwards, rather than the first record through the sample Query we have tried earlier with the Dlookup() Function.

Technorati Tags: Utilities

Earlier Post Link References:

• Duplicating fields with Conditional Formatting
• How to use form filter on Report
• Saving Report Pages as separate PDF files
• Autonumber with Date and Sequence Number
• Assigning Module-level Error-trap Routines

Read More

Shifting Focus from one sub-form to the other.

Introduction.

Sample Form with two Sub-Forms:

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

The Focus movement Requirement

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

Last week we saw how to set focus to a particular field in a sub-form from the main form field.  I have not mentioned anything about the relationships between these three forms in the earlier example.  But, here it is important to know before we attempt to leave Focus from a record in the first sub-form and set focus to its corresponding record in the second sub-form.

Two related Sub-Forms and two issues to solve.

1. Two related sub-forms need to link together, on the common Main Form. The first sub-form is directly linked to the main form.
2. Setting focus on the Amount text box on the second sub-form, when Tab-key press transfers focus from the last text box (the School-Year) on the first sub-form.

Assume that the current record on the first sub-form (frm_Session) is the Session ID (Primary Key) field value 1.  Since the first sub-form is directly linked to the second sub-form (we will explore this aspect, i.e., establishing direct links between two sub-forms, a little later on this page) frm_Payments, the payment record with the SessionId (Foreign Key) field value 1 is displayed on that form too.  We want to transfer focus from the first sub-form Session record to its corresponding Payment record Amount field in the second sub-form. 

Normally, when you press Tab-Key on the last field of the current record, the insertion point moves to the first field of the next record on the same form (with SessionID value 2). Automatically the Payment record, linked to the Session record, also changes to synchronize with the Session ID value 2, because it is directly linked with the frm_session.  So the control is lost from the first record with SessionID field value 1 and we could not enter the Payment Value in the Amount field of the frm_Payment, for the same SessionID. We don't want this to happen, instead, we want the focus to move on to the frm_Payment's Amount field, to enter the payment value of SessionID 1. If you could not make out the total picture of the problem, then check the second image diagram.

So, the question is how to keep the Focus on the first record itself, when the focus is lost from the last field, and transfer the Focus to its corresponding record's Amount field on the second subform?

Linking Both Sub-Forms Together.

Before going into that, let us learn how to link both sub-forms directly, to synchronize related records on both subforms.

The first sub-form (frm_Session) is directly linked with the Main Form (frm_Students) on the common field Student ID (Primary Key) field, on Students Table, and Student ID (Foreign Key) on Session Table.

The current student record on the main form can have several session records on the frm_Session Form.  Each Session record on the Session sub-form will have one or more Payment records on the frm_Payment sub-form and have SessionID as Foreign Key.

The frm_Payments sub-form is directly linked to the frm_Session sub-form of the common SessionID field value. 

Linking Both Sub-Forms Together.

Two sub-forms cannot be linked together directly because a sub-form cannot be considered a Master Form by another Sub-Form.  When this kind of link (relationships) becomes necessary, like the above sub-forms, we can do it by simply transferring the first sub-form Key-field value in an Unbound Textbox on the Main form, and this Unbound Textbox that holds the SessionID value becomes part of the Main Form.  The Name of this Textbox can be used in the Link Master Field property of the second sub-form to establish a relationship with the first sub-form.

Check the following Design View of the above Forms:

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

You can see an Unbound TextBox with the yellow background, created specifically for linking the second sub-form (frm_Payments) to the first sub-form (frm_Session) through the Unbound Textbox on the frm_Students main Form.  This Textbox Name Property value is set as Session_ID, somewhat different than the actual field names in the sub-forms: SessionID. The child Label Caption I have changed to Session_ID for information.

The Session_ID TextBox’s Control Source Property is set with the following expression, to copy the SessionID value automatically from the current record on the frm_Session sub-form:

=[frm_Session].Form!SessionID

Once this is done, you can link the frm_Payments sub-form with the frm_Session sub-form through the Session_ID Texbox by setting the Link Master Field and Link Child Field properties of the frm_Payments sub-form, as shown below.

Link Master Field = Session_ID (the unbound Textbox name)
Link Child Field = SessionID  (the Payments Form’s Foreign Key field name)

Tackling the Real Problem

If you understood the subform's relationships then we can proceed with the real issue I have pointed out at the beginning of this Article.  We must be able to transfer control from the last field of a record in the first sub-form to its corresponding record in the second sub-form, without changing the current record on the first sub-form.  We need a small VBA program to do that job successfully.

Tip: You can try this out with three simple tables (Students, Session & Payments) with sample fields as shown on the Forms.  You may download a sample database, with the sample tables and forms, from the download link given at the bottom of this Article.

If you have the sample Tables and Forms organized as per the design shown above, you may open the frm_Students in normal view to do a sample run of what we are trying to achieve, without the VBA Code.

Tip:  If you have downloaded the sample database from the link given at the bottom of this article, then open the frm_Session in the design view, press F4 to display the Property Sheet, and Click on the School Year Field.  You will find [Event Procedure] in the On Got Focus event property.  Click on this property and click on the Build (. . .) Button to open the VBA Module.  Highlight and delete the entire code, except the first two lines: Option Compare Database & Option Explicit.  Save and close the form. You can copy and paste the deleted code from this page.

1. Open the frm_Students in Normal View.

2. Click on the first record on the frm_Session form to select the record with SessionID value 1.

3. Check the presence of the related record on the frm_Payments sub-form, with the Session ID value 1.

4. Press Tab Key to move the control to the last field School Year. 

   Note:  What we want at this point is, when you press the Tab Key one more time the control should jump from the current record on the frm_Session sub-form and set focus on its corresponding record with SessionID value 1, on the frm_Payments sub-form. 

5. Now, press Tab Key to move out of the last data Field - School Year from frm_Session to go to Amount Field of frm_Payments.

But, it didn’t happen as we have expected, instead, the cursor moved down to the next record on the frm_Session, with SessionID value 2.  The related records on the frm_Payments also changed to Foreign-Key SessionID value 2 to match with the record on frm_Session.

We can do this task only with the help of the VBA Code.  The steps of our program are given below:

1. When the Focus arrives on the School Year field on frm_Session, save the SessionID value into a memory variable SID.

2. When the Focus is Lost (i.e., when the user presses Tab Key again) from the School Year, the Focus moves to the next record and at this point, the VBA Code searches through the form’s RecordsetClone for the SessionID value in memory Variable SID.

3. When the record is found then the RecordsetClone Bookmark is saved into the bkmk String Variable.

4. Copy the record set Bookmark into the frm_Session’s Bookmark control. 

   These steps reset the focus back to the previous record, from the second record, on the frm_Session sub-form. The frm_Payment record earlier changed, and before executing the code,  returns to the one matching the SessionID on the frm_Session record.

5. Set Focus on the frm_Payments.

   When this happens the frm_Payments field with Tab Index value 0 receives the Focus.  At this point, we can move the Focus to any other field, if needed.  We will try this by setting the focus on the Amount field.

6. Set focus on the Amount field on frm_Payments.

If you have the above Form ready, then copy and paste the VBA Code given below into the frm_Session’s VBA Module.

1. Open the frm_Session form in the design view.

2. Click on the School Year field to select it.

3. Press F4 to display the Property Sheet of the School Year field.

4. Find the On Got Focus event property and click to select it.

5. Select [Event Procedure] from the drop-down control.

6. Click on the Build (. . .) Button to open the Form’s VBA Module (known as Class Module).  You may find the following lines of Code in the Class Module.

   The VBA Code.

   Option Compare Database
   Option Explicit
   
   Private Sub SchoolYear_GotFocus()
   
   End Sub

7. Copy and paste the following lines of Code overwriting the existing above lines of code in the Module:
   

   Option Compare Database
   Option Explicit
   'SID is declared as a global variable
   Dim SID As Long
   
   Private Sub SchoolYear_GotFocus()
   'Save the SessionID value in a Global variable
   SID = Me!SessionID
   End Sub
   
   Private Sub SchoolYear_LostFocus()
   '----------------------------------------------
   'This subroutine runs when the Focus is shifted 
   'from the SchoolYear field.
   '----------------------------------------------
   'Author : a.p.r. pillai
   'Date   : Jan/2013
   'All Rights Reserved by www.msaccesstips.com
   '----------------------------------------------
   Dim ctrl As Control, ctrl2 As Control
   Dim bkmk As String, rst As Recordset, j As Long
   Dim rstSID As Long
   
   'The following lines of code prevents shifting the focus
   'to the next record on the frm_Session sub-form, when the focus
   'is lost from the last field of frm_Session, in preparation to set focus
   'on a particular field on the corresponding record on the
   'frm_Payments sub-form.
   Set rst = Me.RecordsetClone 'frm_session's recordset
   rst.MoveFirst
   For j = 1 To rst.RecordCount
   rstSID = rst![SessionID]
   If rstSID = SID Then 'find the record matching the current record on frm_session
      'when match found save the record's recordset bookmark
      bkmk = rst.Bookmark
      'copy the recordset bookmark to the form
      'this will set the focus back on the same record
      'this will also ensure that the SessionID related Payment record
      'will be current on the frm_Payment form
      Me.Bookmark = bkmk
      'set focus on the first field
      Me.SessionID.SetFocus
      'break the loop
      Exit For
   End If
   rst.MoveNext
   Next
   rst.Close
   'transfer control to the frm_Payments Sub-form
   'Now the field with Tabindex number 0 have the default focus
   Set ctrl = Forms![frm_Students].Controls("frm_Payments")
   ctrl.SetFocus
   'Once the focus is shifted on the field with tabindex 0 within frm_Payment sub-form
   'we can move the focus to any other field within that form, if required
   Set ctrl2 = Forms![frm_Students]![frm_Payments].Form.Controls("Amount")
   ctrl2.SetFocus
   
   End Sub
   

8. Save and Close the Form.

   Try out your Forms.

9. Open the frm_Students in the normal view.

10. Click on the first record on the frm_Session Form.

11. Press Tab Key to move the focus to the School Year field and check the corresponding record on the frm_Payments form.

12. Press Tab Key one more time to jump the control onto the frm_Payments sub-form record, with the same Session ID value,  and to set the Focus directly on the Amount field.

Download Demo-Database.

Download Demo SubForm.zip

• How to use form filter on Report
• Saving Report Pages as separate PDF files
• Autonumber with Date and Sequence Number
• Assigning Module-level Error-trap Routines
• Utility for inserting VBA Error Handler code

Read More

Setting Focus on a field inside a Sub-Form

Introduction.

The Main Form (frm_Students) has two Sub-Forms (frm_Sessions and frm_Payment).  A sample image of such a Form is given below:

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

Sub-Form Container and Sub-Form.

Each Sub-Form on the main form is placed within a Sub-Form Container on the main form.  The Sub-Form has other controls (like Textboxes) within it and we cannot set focus directly on any of these controls, from outside the sub-form container.  That doesn’t mean that we cannot address the controls directly to retrieve or set the value into that control through VBA.

The Difference Between Setting Focus and Retrieving Values

Setting focus in the Amount field of frm_Payments directly, when frm_Payments doesn't have the focus on, through VBA is not possible.

Example:

'this statement will not work when frm_Payments doesn't have focus
Forms![frm_Students]![frm_Payments].Form.Amount.SetFocus

The above statement may highlight the Amount field, but the focus will not change to that field.

But, we can retrieve the value directly from the Amount field of frm_Payments, even when the focus is not ON on that form, with the following statement.

Example-2:

'this statement retrieves the Amount field value directly.
m_Amt = Forms![frm_Students]![frm_Payments].Form!Amount

When the frm_Payments sub-form has the focus ON you can address a control (say the Amount field) within that form to set the real focus on it.

It simply means that it takes two-step action to address a control within a sub-form to set focus on:

1. Set focus on the sub-form container first.  Setting the Tab Index Value of the frm_Payment to 0 also works.
2. Set focus on any control within the frm_Payments sub-form.

So, it is a two-step process and the following two VBA statements do the job:

Me.Payments.form.SetFocus
Me.Payments.form.Amount.SetFocus

The following version of the above statements is also valid:

Forms![frm_Students]![frm_Payments].SetFocus
Forms![frm_Students]![frm_Payments].Form.Amount.SetFocus

What Next...

Next, we will see how to jump from the last field of one sub-form record to its corresponding record field on the second sub-form directly.  If you think it is so easy after learning the above two lines, then think twice.

• Saving Report Pages as separate PDF files
• Autonumber with Date and Sequence Number
• Assigning Module-level Error-trap Routines
• Utility for inserting VBA Error Handler code
• Prepare a List of Procedure names from a Module

Read More

Conditional Formatting in Continuous Form

Introduction.

Conditional formatting is a magical option to change the color (background/foreground) of textbox controls on Forms or Reports.  The Color change is possible when one or more field values meet a specific condition.  The specific condition can be tested in the field, where we want to set the color or can be based on values in any other field.

The color change has three different possible choices and their variations:

1. Color changes when the field has focus.
2. Color changes when the field value meets a specific condition.
3. The Color changes depending on conditional values in some other field.

Changing the Background Foreground Color.

We will try out an example, to learn how to change the background/foreground colors of all fields of certain records, which meet a specific condition on a single field value, on a continuous form.

A sample image of a Form with conditional formatting is given below for reference:

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

The records are formatted based on values in the Unit Price field.  The background color is set on records with a Unit Price value greater than $30.  The font is set with Red color on records with Unit Price value falling between 20 and 30.

Design a Continuous Form.

1. Import the Order Details and Products tables from the Northwind sample database.

2. Design a continuous form on the Order Details Table, like the sample image below:

   

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

   Setting up the Conditions.

3. Open the Order Details Form in Design View.

4. Select the OrderID field and open the Conditional Formatting dialog control.

5. Select Expression Is under Condition 1 and type [UnitPrice]>30 in the right-side control.

6. Select Fill/Back Color to display the color palette and select a light color for the background.

7. Click on the Add>> button to display options for a second condition for the same field.

8. Select Expression Is in Condition 2 Control.

9. Type the expression [UnitPrice]>=20 And [UnitPrice]<=30 in the next text control.

10. Select Red Font Color for formatting from the color palette and click OK to close the formatting dialog box.

11. Repeat Steps-4 to Step-10 for ProductID, Quantity, and Discount fields.

12. Select the Unit Price field and display the Conditional Formatting dialog control.

13. Select Field Value Is in the Condition 1 control.

14. Select the same background color selected, for other fields for formatting, from the Color Palettes.

15. Click on the Add>> Button to add a new condition for the same field.

16. Select 'Field Value Is' in Condition 2 Control.

17. Select Between from the drop-down list in the next control.

18. Type 20 in the next control and type 30 in the last control.

19. Select the Red Font Color for formatting from the color palettes and click OK to close the conditional formatting dialog box.

    Test Run your Creation.

20. Save the Form and open it in the normal view.

You should see the form view similar to the sample image shown above.  Scroll the form down to view more records with conditional formatting.

• Autonumber with Date and Sequence Number
• Assigning Module-level Error-trap Routines
• Utility for inserting VBA Error Handler code
• Prepare a List of Procedure names from a Module
• VBA Module Object and Methods

Read More

Autonumber with Date and Sequence Number-2

Introduction.

Perhaps you may be wondering why we need something different when there is an Autonumber feature already built into Microsoft Access?  Well, the built-in feature may not be suitable for all situations, like patients' Unique Registration Numbers in hospitals, with the change of date and sequence numbers. We have created a function earlier for generating auto-numbers with date and sequence numbers, and you can have that from the first link given below. You may visit the other related links for some different approaches for generating Auto-numbers in Queries, too.

• Autonumber with Date and Sequence Number
• Product Sequence with Auto numbers.
• Autonumbering in the Query Column.

We are now going to take a different approach to generate auto-numbers with the date and sequence numbers. Let us take a re-look at the last method we have created with date & sequence numbers, and how we are going to reformat the same thing in the new method with a lesser number of digits as follows:

Sample Data Image.

Sample Dates: 30-10-2012 and 31-10-2012

The Auto-number generated for patient registration looks like the following: the format used in the earlier article (first link above):

Format: yyyymmdd-999

Autonumber-with-Date method-1

Saved Number	Display with an Input mask
20121030001	20121030-001
20121030002	20121030-002
20121030003	20121030-003
20121030004	20121030-004
20121030005	20121030-005
20121031001	20121031-001
20121031002	20121031-002
20121031003	20121031-003

The dash in the number is inserted using the input mask for better readability in the display control. In the above example, it uses eight digits for displaying the date part and three digits for serial numbers. This method requires a total of 11 digits for the auto-number.

In the following new method, we are going to create date-wise changing auto-numbers, which take only eight digits, like the example shown below:

Sample Date: 30-10-2012 and 31-10-2012

New display format: yyddd-999

The first two digits (yy) store the year (12), the next three digits (ddd) are the day number from 1st January (is 001) onwards. October 30th, 2012, is the 304th day from January 1st, 2012.

Autonumber-with-Date method-2

Saved Number	Display with an Input mask
12304001	12304-001
12304002	12304-002
12304003	12304-003
12304004	12304-004
12304005	12304-005
12305001	12305-001
12305002	12305-002
12305003	12305-003

The sequence numbers reset to 001 when the date changes.  The new method's result is somewhat compact in size and takes only eight digits to store the auto-number in place of eleven digits in the earlier method.

DaysAsOnMonth() Function.

Copy and paste the following VBA Code into a Standard Module of your Database:

Public Function DaysAsOnMonth(ByVal dt As Date) As Long
Dim i As Integer, j As Integer, tdays As Long, d As Long

On Error GoTo DaysAsOnMonth_Err
i = Month(dt)
d = DatePart("d", dt)

For j = 1 To i - 1
tdays = tdays + Choose(j, 31, 28 + IIf(Year(dt) / 4 = Int(Year(dt) / 4), 1, 0), 31, 30, 31, 30, 31, 31, 30, 31, 30, 31)
Next
If (Year(dt) Mod 400) = 0 And Month(dt) > 2 Then
  tdays = tdays - 1
End If
tdays = Val(Right(Year(dt), 2)) * 10 ^ 3 + tdays
tdays = tdays + d
DaysAsOnMonth = tdays

DaysAsOnMonth_Exit:
Exit Function

DaysAsOnMonth_Err:
MsgBox Err & " : " & Err.Description, , "DaysAsOnMonth()"
Resume DaysAsOnMonth_Exit

End Function

The AutoNumber() Function.

Public Function AutoNumber(ByVal strField As String, ByVal strTable As String) As String
Dim dmval As String, dt1 As String, dt2 As String, Seq As Integer, dv As String

On Error GoTo AutoNumber_Err

'get the highest existing value from the table
dmval = Nz(DMax(strField, strTable), 0)

'if returned value is 0 then the table is new and empty
'create autonumber with current date and sequence 001
If Val(dmval) = 0 Then
   dv = Format(DaysAsOnMonth(Date) * 10 ^ 3 + 1)
   AutoNumber = dv
   Exit Function
End If

'format the number as an 11 digit number
dv = Format(dmval, "00000000")
'take the 3 digit sequence number separately
Seq = Val(Right(dv, 3))
'take the date value separately
dt1 = Left(dv, 5)
'get today's date
dt2 = Format(DaysAsOnMonth(Date))
'compare the latest date taken from the table
'with today's date
If dt1 = dt2 Then 'if both dates are same
   Seq = Seq + 1 'increment the sequence number
   'add the sequence number to the date and return
   AutoNumber = Format(Val(dt1) * 10 ^ 3 + Seq)
   Exit Function
Else 'the dates are different
   'take today's date and start the sequence with 1
   AutoNumber = Format(Val(dt2) * 10 ^ 3 + 1)
End If

AutoNumber_Exit:
Exit Function

AutoNumber_Err:
MsgBox Err & " : " & Err.Description, , "AutoNumber()"
Resume AutoNumber_Exit

End Function

How it works.

The first function, DaysOfMonth(), is called from the AutoNumber() Function to calculate the number of days from January 1st to the date passed as a parameter to the function.  The input date 30-10-2012 will return the result value 304, i.e., 31+29+31+30+31+30+31+31+30+30 = 304.

The trial run procedure for the new method is already published in an earlier article. I will take you to the exact point in that Article, from where you can continue reading and prepare yourself for the demo. All that you should do is to change the Function name Autonum(), appearing in those sample run lines, to AutoNumber().

Click to continue...

Download the Demo Database and Modify.

DateAndSeq.zip

1. Auto-Numbering in Query Column
2. Product Group Sequence with Auto-Numbers.
3. Preparing Rank List.
4. Auto-Number with Date and Sequence Number.
5. Auto-Number with Date and Sequence Number-2.

Read More

Command Button Color Change on Mouse Button-down Action

Introduction.

After working with the Command Button and Message Box Controls of MS-Access I really got bored with their styles. So, I set out to do something about it and created some animation styles for the command buttons and used Office Assistant (valid only for Access2003 and earlier versions) for Message Boxes. The Links to those Articles and free downloads links are given below:

Command Button Styles

1. Command Button Animation
2. Command Button Animation-2
3. Colorful Command Button

   Message Box Styles (Access2003 or earlier versions only)

4. Message Box using the Office Assistant
5. MsgBox with Options Menu
6. MsgBox with Checkbox Menu

Some New Trick on Command Button.

Here is a new trick for you to use on Command Button Control. Command Button will change the color on the mouse button down the action and will restore the original color on the mouse-up action.  You can do this with a Label control, with the same dimension as the Command Button, filled with the color you like, and by placing it behind the Command Button Control.  You need two lines of VBA Code on the Form Module as well.

Design the New Animation Style.

Let us go through with this simple design and enjoy the new animation style of the Command Button Control.

1. Open a new Form.

2. Create a Command Button Control on the Detail Section of the Form.

3. Display the Command Button’s Property Sheet (F4).

4. Change the Name Property Value to cmdClose.

5. Change the Caption Property Value to Close.

6. Create a Label Control with some caption text near the Command Button.

7. Select the Command Button and the Label Control together.

8. Right-click on it and highlight the Size option on the Shortcut Menu and select To Tallest.

9. Repeat step 8 above and select To Widest.

10. Click on the Label Control alone and display its Property Sheet (F4).

11. Remove the text from the Caption property.

12. Change the Special Effect Property value to Raised.

13. Select the Back Color Property and click on the Build ( . . .) button to display the Color Palette.

14. Select the color you like to fill the background of the label control.

15. Move the Label control behind the Command Button control and place it completely hidden.

16. If the label control is overlapping the Command Button then display the Shortcut Menu by right-clicking on it and selecting Send to Back from the Position group.

17. Click on the Command Button and display the property sheet (F4).

18. Click on the Event Tab and select the Mouse down Event, select [Event Procedure] from the drop-down control and click on the build (. . .) button to open the Form’s VBA Module Window.

19. Copy and Paste the following Code into the Form Module, over-writing the existing two empty lines that appeared there:

    Private Sub cmdClose_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
      Me.cmdClose.BackStyle = 0 'transparent
    End Sub

20. Repeat step 18 for Mouse Up [Event Procedure] and copy and paste the following Code into the Form Module:

    Private Sub cmdClose_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single) 
        Me.cmdClose.BackStyle = 1 'Normal 
    End Sub

Test Run.

Now, it is time for the test run of our design. 

1. Save the Form with the name frmButton.

2. Open frmButton in Form View.

3. Click on the Command Button and hold the mouse button down.  You will see the Command Button’s Color changes to the background fill color you have selected for the label control and Command Button caption-text Close appearing on it.

4. Release the mouse button.  The original state of the Command Button is restored.

The trick works by changing the Back Style of the Command button to transparent, forcing it to display the color of the Label control behind it and changing to a Normal view when the mouse button is released. 

You can learn another trick from an earlier Article on Transparent Command Button, here.

1. Command Button Animation
2. Double Action Command Button
3. Colorful Command Buttons
4. Transparent Command Button
5. Command Button Animation-2
6. Creating Animated Command Button with VBA
7. Command Button Color Change on Mouse Move

Read More

Time-bound Form Mode Change

Introduction.

Sometimes, you may want to allow data entry or editing only during specific time periods, and at all other times keep the form locked in a read-only mode. This requirement usually arises in time-bound workflows, where users are permitted to update records only within fixed working slots.

A similar request was once raised in a Microsoft Access discussion forum, and I decided to explore a practical solution.

In the following example, we will enable data entry and editing on a form only during these time periods:

• 06:00 – 07:00 Hours

• 11:00 – 13:00 Hours

• 17:00 – 19:00 Hours

At any other time, the form will remain restricted to data view only. The Data Entry Control Function.

The function written for this task is given below. Copy it into the Standard Module:

Public Function Data_Entry(ByVal frmName As String)
Dim T1S, T1E, T2S, T2E, T3S, T3E
Dim frm As Form

Set frm = Forms(frmName)

D = Date
T1S = TimeValue("06:00:00")
T1E = TimeValue("07:00:00")

T2S = TimeValue("11:00:00")
T2E = TimeValue("13:00:00")

T3S = TimeValue("17:00:00")
T3E = TimeValue("19:00:00")

Select Case time
      Case T1S To T1E, T2S To T2E, T3S To T3E
          With frm
            If .AllowAdditions = False Then
               .AllowAdditions = True
               .AllowEdits = True
               .lblMsg.Visible = False
'change .subFrmName to match the control (window) name of the sub-form
               .subFrmName.Enabled = True
            End If
          End With
          frm.Refresh
      Case Else
          With frm
            If .AllowAdditions = True Then
                .AllowAdditions = False
                .AllowEdits = False
                .lblMsg.Visible = True
 'change the next line to set focus on any field on the main form
                .EmployeeID.SetFocus
'change .subFrmName to match the control (window) name of the sub-form
                .subFrmName.Enabled = False
            End If
          End With
          frm.Refresh
End Select

Set frm = Nothing
End Function

NB: You must make changes wherever applicable to point the code to correct control names on your Form, which I have marked with comments.

Some Changes to the Form.

1. Open your Form in Design View.

2. Add a Label control on the main Form where you want to display 'Entry not allowed', change the Name property value to lblMsg, and write the message in the Caption property.

3. Display the Form Property Sheet.

4. To implement this feature, we can take advantage of the Form’s Timer event.

   1. Set the Timer Interval property of the form to 60000 (i.e., 60,000 milliseconds = 1 minute).

      • This means Access will automatically check the current system time once every minute.

      • If you want more frequent checks, increase the interval in 1000 millisecond increments (1,000 = 1 second).

   2. In the Form_Timer event procedure, we can write a simple VBA routine to check whether the current time falls within the allowed edit periods. If it does, the form will be set to data entry/edit mode. Otherwise, it will automatically switch to view-only mode by disabling edits.

   This way, the form will self-adjust every minute, ensuring that users can only enter or edit data during the specified hours. Select the On-Timer() Event, select [Event Procedure] from the drop-down control, and click on the build (...) Button to open the VBA module.

5. Copy and paste the following lines of Code, replacing the existing two lines displayed there:

   Code:

   Private Sub Form_Timer()
      Data_Entry Me.Name
   End Sub

6. Save and Close the Form.

Tracking the Time for Form Mode Change.

The Timer setting ensures that the program checks every minute to determine whether the current time falls within the allowed time slots specified in the code. If the condition is true, both the main form and its subform will be enabled for data entry and editing; otherwise, they will remain locked in read-only mode.

When the form is opened, however, there will be an initial delay of one minute before Access performs the first check. To avoid this delay, you can call the Data_Entry() function from the Form_Current event procedure. This ensures that the time-check routine runs immediately upon opening the form, rather than waiting for the first timer tick.

Earlier Post Link References:

• Prepare a List of Procedure names from a Module
• VBA Module Object and Methods
• Writing VBA Code with VBA
• Control Setfocus on Tab-Page Click
• Adding Data directly into an external Database

Read More