iSeries Date in Imported Data

Introduction.

When working with an IBM iSeries (IBM AS/400) mainframe computer and importing raw data into Microsoft Access for custom report preparation, an issue often arises. The date values in the data records are frequently stored as plain numbers.

These can appear in formats such as mmddyyyy (e.g., 07092011), ddmmyyyy (e.g., 09072011), or yyyymmdd (e.g., 20110709). Such values cannot be used directly as dates in queries or other analysis tasks. They must first be converted into proper date values before they can be utilized effectively.

Let us try one or two conversion examples.

Example-1: mmddyyyy

X = 07092011 or x=”07092011”

Y = July 09, 2011 (internal representation of the actual date number is 40733)

Conversion expression:

Y = DateSerial(Right(x,4),Left(x,2),Mid(x,3,2))

OR

y = DateValue(Left(x,2) & "-" & Mid(x,3,2) & "-" & Right(x,4))

Depending on the input date format (Asian, USA, or ANSI), the order in which you use the Left(), Right(), and Mid() functions within the DateSerial() or DateValue() functions will vary. However, writing these expressions repeatedly in every query is not practical and quickly becomes time-consuming. The simplest solution is to create a user-defined function with the required expression and call that function from the query column—or from anywhere else the numeric date values are used in calculations or comparisons.

The DMY_N2D() Function.

Let us create the following simple Functions that will make our lives easier with these numbers in the long run.

Function: DMY_N2D(ByVal ddmmyyyy as Variant) As Date 

This function converts a date number in ddmmyyyy format into a valid date value. The input can be provided either as a long number (e.g., 09072011 or 9072011) or as text (e.g., "09072011"). The suffix _N2D stands for Number to Date. The first three characters in the function name (DMY) indicate the order of the day, month, and year segments in the input date number.

Public Function DMY_N2D(ByVal ddmmyyyy As Variant) As Date

'------------------------------------------------------------------
'Converts Numbers (in ddmmyyyy format) 09072011 into a Date Number
'Author : a.p.r.pillai
'Date   : Sept. 1999
'Rights : All Rights Reserved by www.msaccesstips.com
'------------------------------------------------------------------
Dim strN2D As String

On Error GoTo DMY_N2D_Err

strN2D = Format(ddmmyyyy, "00000000") ' add 0 at the left side, if 7 digit number

DMY_N2D = DateSerial(Right(strN2D, 4), Mid(strN2D, 3, 2), Left(strN2D, 2))

DMY_N2D_Exit:
Exit Function

DMY_N2D_Err:
MsgBox Err.Description,, "DMY_N2D()"
Resume DMY_N2D_Exit
End Function

The MDY_N2D() Function.

When the input Number is in mmddyyyy format:

Public Function MDY_N2D(ByVal mmddyyyy As Variant) As Date 
'------------------------------------------------------------------ 
'Converts Numbers (in mmddyyyy format) 07092011 into a Date Number 
'Author : a.p.r.pillai 
'Date   : Sept. 1999 
'Rights : All Rights Reserved by www.msaccesstips.com 
'------------------------------------------------------------------
Dim strN2D As String 

On Error GoTo MDY_N2D_Err 

strN2D = Format(mmddyyyy, "00000000") ' add 0 at the left side, if 7 digit number

MDY_N2D = DateSerial(Right(strN2D, 4), Left(strN2D, 2), Mid(strN2D, 3, 2)) 

MDY_N2D_Exit: 
Exit Function 

MDY_N2D_Err: 
MsgBox Err.Description,, "MDY_N2D()" 
Resume MDY_N2D_Exit 
End Function

The YMD_N2D() Function.

When the date number is in yyyymmdd (ANSI) format:

Public Function YMD_N2D(ByVal yyyymmdd As Variant) As Date
'------------------------------------------------------------------
'Converts Numbers (in yyyymmdd format) 20110709 into a Date Number
'Author : a.p.r.pillai
'Date   : Sept. 1999
'Rights : All Rights Reserved by www.msaccesstips.com
'------------------------------------------------------------------
Dim strN2D As String

On Error GoTo YMD_N2D_Err

YMD_N2D = DateSerial(Left(strN2D, 4),Mid(strN2D, 5, 2),Right(strN2D, 2))

DMY_N2D_Exit:
Exit Function

YMD_N2D_Err:
MsgBox Err.Description,, "YMD_N2D()"
Resume YMD_N2D_Exit
End Function

Earlier Post Link References:

• Memo Field Text Formatting
• Sub-Query in Query Column Expression
• GetRows() Function and Exporting Data
• Create Menus with Macros-2
• Create Menus with Macros

---

Read More

Change Query Top Values Property with VBA-2

Continued from Last Week's Topic.

Through last week’s introduction, we have seen various ways the Top Value and other properties change the SQL string of a SELECT Query.  Now we will learn how to redefine the Query for the Top Values and other property changes.

As I mentioned earlier, three types of queries, SELECT, APPEND, and MAKE-TABLE, only have the Top Values property.  SELECT and MAKE-TABLE queries have almost identical SQL strings with DISTINCT, TOP nn, and PERCENT clauses appearing immediately after the SELECT clause at the beginning of the SQL String.

A sample SQL string of a make-table query is given below:

SELECT TOP 15 PERCENT SalesReportQ.* INTO chart
FROM SalesReportQ
ORDER BY SalesReportQ.Total DESC;

Unlike SELECT and MAKE-TABLE Queries, APPEND Queries have the Top Values property settings inserted somewhere in the middle of the SQL string immediately after the SELECT clause. Check the sample SQL of the Append Query given below:

INSERT INTO Table3 ( xID, Field1, Field2 )
SELECT DISTINCT TOP 17 PERCENT Table2.ID, Table2.Field1, Table2.Field2
FROM Table2
ORDER BY Table2.ID DESC;

Our VBA program scans through the SQL String to find the TOP Values property Clauses in the SQL String(wherever they appear), removes the existing settings, and inserts changes as per input from the User.

First, we will create a form for the User to input the Query 'Top Values' property values and click a Command Button to redefine the SQL.

An image of a sample form is given below:

Two text boxes with the names Qry and TopVal, for Query name and for Top values parameters, respectively, and a checkbox with the name Unik for Unique value selection.  The Top Values text box can be set with a number or a percentage value (15%).  If the Unik checkbox is set, then the query suppresses duplicate records based on the selected field values in the Query.

After setting the Query property values in the above controls, the user should click the Command Button to redefine the SQL of the selected query in the Query Name control.  The Command Button's name is cmdRun (with the Caption: Modify Query). When the Command Button is clicked, the cmdRun_Click() Event Procedure is run (the VBA Code is given below) and validates the input values in the controls above and calls the QryTopVal() function (with parameters: query name, Top Values property value, and Checkbox value) to redefine the Query based on the user inputs.

Form Module Code.

Private Sub cmdRun_Click()
Dim strQuery, strTopVal, bool As Boolean
Dim msg As String

On Error GoTo cmdRun_Click_Err

msg = ""
strQuery = Nz(Me![Qry], "")
If Len(strQuery) = 0 Then
   msg = "  *>>  Query Name not found." & vbCr
End If
strTopVal = Nz(Me![TopVal], 0)
If strTopVal = 0 Then
   msg = msg & "  *>>  Top Property Value not given."
End If
bool = Nz(Me![Unik], 0)
If Len(msg) > 0 Then
    msg = "Invalid Parameter Values:" & vbCr & vbCr & msg
    msg = msg & vbCr & vbCr & "Query not changed, Program Aborted...."
    MsgBox msg, , "cmdRun_Click()"
Else
    'Call the QryTopVal() Function to redefine the Query
    QryTopVal strQuery, strTopVal, bool
End If

cmdRun_Click_Exit:
Exit Sub

cmdRun_Click_Err:
MsgBox Err.Description, , "cmdRun_Click()"
Resume cmdRun_Click_Exit
End Sub

Copy and paste the above VBA Code into the Form Module and save the Form. Don't forget to name the Command Button as cmdRun.

The Main Function QryTopVal().

The main function QryTopVal() checks the validity of the Query Type (SELECT,  APPEND, or MAKE-TABLE) and reads the SQL of the query.  Checks for the existence of Top Values and other Property settings, and if they exist, then removes them.  Redefines the query based on the Top Values and other property inputs from the user.

Copy and paste the following VBA Code of QryTopVal() into the Standard Module and save it:

Public Function QryTopVal(ByVal strQryName As String, _
                       ByVal TopValORPercent As String, _
                       Optional ByVal bulUnique As Boolean = False)
'--------------------------------------------------------------------
'Author : a.p.r. pillai
'Date   : Jun 2011
'Remarks: All Rights Reserved by www.msaccesstips.com
'Valid Query Types:
'  0 - SELECT
' 64 - APPEND
' 80 - MAKE TABLE
'--------------------------------------------------------------------
Dim strSQL1 As String, strSQL2 As String, strTopValue
Dim db As Database, qrydef As QueryDef, sql As String
Dim loc, qryType As Integer, locTop
Dim txt(1 To 3) As String, num
Dim J, xt, msg As String

On Error GoTo QryTopVal_Err

txt(1) = "DISTINCT"
txt(2) = "TOP"
txt(3) = "PERCENT"

Set db = CurrentDb
Set qrydef = db.QueryDefs(strQryName)
qryType = qrydef.Type

If qryType = 0 Or qryType = 64 Or qryType = 80 Then
   xt = qrydef.sql

   GoSub ParseSQL

   loc = InStr(1, TopValORPercent, "%")

   If loc > 0 Then
      TopValORPercent = Left(TopValORPercent, Len(TopValORPercent) - 1)
   End If

   If Val(TopValORPercent) = 0 Then
      sql = strSQL1 & strSQL2
   Else
      sql = strSQL1 & IIf(bulUnique, "DISTINCT ", "") & "TOP " & TopValORPercent & IIf(loc > 0, " PERCENT ", "") & strSQL2
   End If

   qrydef.sql = sql
   msg = "Query Definition of " & strQryName & vbCr & vbCr & "Changed successfully."
   MsgBox msg, , "QryTop()"
Else
   msg = strQryName & " - Invalid Query Type" & vbCr & vbCr
   msg = msg & "Valid Query Types: SELECT, APPEND and MAKE-TABLE"
   MsgBox msg, , "QryTop"
End If

QryTopVal_Exit:
Exit Function

ParseSQL:
For J = 1 To UBound(txt)
  xt = Replace(xt, txt(J), "", 1)
Next
  
  locTop = InStr(1, xt, "SELECT")
  num = Val(Mid(xt, locTop + 7))
  num = " " & Format(num) & " "
  strSQL1 = Left(xt, locTop + 7)
  xt = Right(xt, Len(xt) - (locTop + 7))
  xt = Replace(xt, num, "", 1, 1)
  strSQL2 = " " & xt
  locTop = InStr(1, strSQL2, "ORDER BY")
  If locTop = 0 Then
    MsgBox "ORDER BY Clause not found in Query.  Result may not be correct.", , "QryTopVal()"
  End If
Return

QryTopVal_Err:
MsgBox Err & " : " & Err.Description, , "QryTopVal()"
Resume QryTopVal_Exit

End Function

You may try the Code with sample Queries.

• Sub-Query in Query Column Expression
• GetRows() Function and Exporting Data
• Create Menus with Macros-2
• Create Menus with Macros
• Join Split Array Function-2

Read More

Change Query Top Values Property with VBA

Introduction.

We have already learned how to use the Top Values property of Queries (applicable in SELECT, MAKE-TABLE, and APPEND queries) in an earlier post. If you haven’t seen it yet, I recommend taking a look at that article first [link here].

The Top Values property of a Query can only be set manually during design time. Unfortunately, you cannot directly change its value dynamically in VBA. You also cannot expect users to open the query in Design View each time they want a different set of results.

So, how do we handle situations where we need flexibility with these values?

Sample SQL with TOP Property Setting.

When we change the Top Values property manually, Microsoft Access automatically updates the SQL statement of the Query to reflect that change. Keeping this behavior in mind, we can use a little VBA trickery to manipulate the SQL directly, rather than searching for a property setting that doesn’t exist.

Before we move on to that approach, let us first examine what actually happens to the SQL definition of a Query when you set the Top Values property or other related properties.

Here is a sample SQL of a SELECT Query with the Top Values-Property set to the Value 25.

SELECT TOP 25 Orders.OrderID, Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate, Orders.Freight
FROM Orders
ORDER BY Orders.Freight DESC;

As shown in the example, when the Top Values property is set to 25, Access automatically inserts the text TOP 25 immediately after the SELECT clause in the SQL string. This indicates that the Query will return only 25 records. In the ORDER BY clause, the Freight column is sorted in descending order, so the output consists of the 25 records with the highest freight values in the table.

If, instead of a fixed number of records, you want a percentage of the total records—for example, 25%—then the Top Values property must be set to 25% rather than 25. In this case, the SQL text changes accordingly to:

SELECT TOP 25 PERCENT Orders.OrderID, Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate, Orders.Freight

FROM Orders
ORDER BY Orders.Freight DESC;

The next property that affects the record set is the Unique Values property (valid values: Yes or No). When this property is set to Yes, Access suppresses duplicate records in the output shown in the datasheet. However, it only evaluates the fields included in the Query’s column list—other fields from the table that are not selected are ignored when checking for duplicates.

When this property is enabled, Access inserts the keyword DISTINCT in the SQL, immediately after the SELECT clause. The modified SQL will look like this:

SELECT DISTINCT TOP 25 PERCENT Orders.OrderID, Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate, Orders.Freight
FROM Orders
ORDER BY Orders.Freight DESC;

Another property you can set in a query is Unique Records (valid values: Yes or No). When this property is set to Yes, Access suppresses duplicate records in the output. Unlike the Unique Values property, this setting evaluates all fields in the source table, regardless of whether they are included in the query’s column list.

When enabled, the SQL changes by replacing the DISTINCT keyword with DISTINCTROW. This ensures that the uniqueness check is applied across the entire underlying table.

For example:

SELECT DISTINCTROW TOP 25 PERCENT Orders.OrderID, Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate, Orders.Freight
FROM Orders
ORDER BY Orders.Freight DESC;

We will exclude the Unique Records property from our VBA-based solution. As shown in the examples above, depending on the user’s requirements, we can dynamically add or remove any of these three elements—DISTINCT, TOP n, or PERCENT—to control the query output for reports.

Preparing for the VBA-based Solution.

Our methodology for modifying the SQL is straightforward. We will collect the required Query property values from the user through a TextBox and a Checkbox placed on a Form. Once the user provides the input and clicks a Command Button, the SQL of the Query will be redefined accordingly. This will invoke the following actions to redefine the Query:

1. Open the Query Definition and read the existing SQL String.

2. Scan the SQL string and look for the text DISTINCT, TOP nn, and PERCENT.  If found, then remove them from the SQL String.

3. Validate the input given by the User in the Textbox and checkbox, and insert appropriate SQL Clauses in the SQL String.

4. Update the modified SQL in the Query definition.

This article has become too long now.  Explaining the above four steps and introducing the VBA Routines may make it even longer.  We will complete this topic in the next blog post.

Earlier Post Link References:

• GetRows() Function and Exporting Data
• Create Menus with Macros-2
• Create Menus with Macros
• Join Split Array Function-2
• Join Split Array Function

Read More

Creating Animated Command Button with VBA

Introduction.

My very first blog post on this website, published back in September 2006, was about the Command Button Animation method. I have used this technique in almost all of my projects since then. The animated response of command buttons to mouse movements gave the Forms a sense of life, making them feel interactive rather than rigid and boring.

The design involves creating a Command Button, along with a Rectangle control set with a shadow special effect, both placed manually. The animation itself is handled through Mouse_Move() event procedures written in the Form module, which call a common function to perform the animation.

If you haven’t read that post yet, I recommend reviewing it first to get a general understanding before continuing. [Click here to go to the earlier article.]

Now, let’s take this a step further and see how to create animated command buttons automatically, with their corresponding animation procedures written into the Form module, all at design time, with just a single click of a button.

Animated Button Creation Code.

With two VBA routines, we can do this very easily.

1. Open one of your databases.

2. Display the Standard VBA Module.

3. Create a new Standard Module (Insert -> Module).

4. Copy both the following Functions into the Standard Module and save the code:

Command Button Animation Code:

Public Function BAnimate(ByVal strForm As String, ByVal mode As Integer, ByVal lblName As String)
'---------------------------------------------------------------------------
'Author : a.p.r. pillai
'Date   : July 1998
'Remarks: Animates the Command Button
'Rights : All Rights Reserved by www.msaccesstips.com
'---------------------------------------------------------------------------
Dim Frm As Form, l As Long, t As Long

On Error GoTo BAnimate_Err

Set Frm = Forms(strForm)

l = Frm.Controls(lblName & "Box").Left
t = Frm.Controls(lblName & "Box").Top

If (mode = 1) And (Frm.Controls(lblName & "Box").Visible = False) Then
    Frm.Controls(lblName & "Box").Visible = True
        Frm.Controls(lblName).Left = l - (0.0208 * 1440)
        Frm.Controls(lblName).Top = t - (0.0208 * 1440)
        Frm.Controls(lblName).FontWeight = 700
ElseIf (mode = 0) And (Frm.Controls(lblName & "Box").Visible = True) Then
    Frm.Controls(lblName & "Box").Visible = False
        Frm.Controls(lblName).Left = l
        Frm.Controls(lblName).Top = t
        Frm.Controls(lblName).FontWeight = 400
End If

BAnimate_Exit:
Exit Function

BAnimate_Err:
Err.Clear
Resume BAnimate_Exit

End Function

Animated Command Button Creation Code:

Public Function CreateButton(ByVal intSection As Integer, strName As String)
'---------------------------------------------------------------------------
'Author : a.p.r. pillai
'Date   : Aug. 2000
'Remarks: Creates animated command button controls and Programs on a Form
'       : at design time of the form.
'Rights : All Rights Reserved by www.msaccesstips.com
'---------------------------------------------------------------------------
'Program Parameter Values : intSection
'     0 =  Form Detail Section
'     1 =  Form Header Section
'     2 =  Form Footer Section
'                         : strName
'Command Button Name Property Value
'The Name will be prefixed with the text 'cmd'
'Example: Edit will be modified as cmdEdit
'Sample Run : CreateButton 0,"Edit"
'Remarks    : If more than one Form is opened in Design View then the
'           : Command Button will be created on the first Form
'---------------------------------------------------------------------------
Dim Frm As Form, Ctrl As Control, txt As String
Dim vLeft As Long, vTop As Long, vWidth As Long, vHeight As Long
Dim i As Long, dif As Double, x As Long, Y As Long, z
Dim frmViewStatus As Integer, frmsCount As Integer, j As Integer
Dim hfd As String
On Error Resume Next

i = 1440
dif = 0.0208 * i
vLeft = 0.25 * i
vTop = 0.0521 * i
vWidth = 1.25 * i
vHeight = 0.2375 * i

frmsCount = Forms.Count - 1
frmViewStatus = False
For j = 0 To frmsCount
    If Forms(j).CurrentView = 0 Then
      Set Frm = Forms(j)
      frmViewStatus = True
      Exit For
    End If
Next
If frmViewStatus = False Then
   MsgBox "Open a Form in Design View then try again." & vbCr & vbCr & "Program CreateButton() Aborted."
   Exit Function
End If

  Set Ctrl = CreateControl(Frm.Name, acRectangle, intSection, , , vLeft, vTop, (vWidth - dif), (vHeight - dif))
  
  With Ctrl
    .Name = "cmd" & strName & "Box"
    .SpecialEffect = 4
    .BorderWidth = 3
  End With

  Set Ctrl = CreateControl(Frm.Name, acCommandButton, intSection, , strName, vLeft, vTop, vWidth, vHeight)
  
  With Ctrl
    .Name = "cmd" & strName
    .Caption = strName
  End With
   
  x = Frm.Module.CreateEventProc("MouseMove", Ctrl.Name)
  
  txt = "BAnimate me.name, 1, " & Chr(34) & "cmd" & strName & Chr(34)
  Frm.Module.InsertLines x + 1, txt

  hfd = Choose(intSection + 1, "Detail", "FormHeader", "FormFooter")

  z = Frm.Module.ProcBodyLine(hfd & "_MouseMove", vbext_pk_Proc)

  If z = 0 Then
      x = Frm.Module.CreateEventProc("MouseMove", hfd)
  Else
      x = z + 1
  End If

  txt = "BAnimate me.name, 0, " & Chr(34) & "cmd" & strName & Chr(34)
  Frm.Module.InsertLines x + 1, txt


CreateButton_Exit:
Exit Function

CreateButton_Err:
Resume CreateButton_Exit
End Function

Press Ctrl+S to save the programs. Keep the VBA Window open.

Do the Test Run.

Now that you have the main functions for creating and animating Command Buttons, let us do a quick run to see how easy it is.

1. Close all open forms, if any.

2. Open a new Form in Design View.

3. Display the VBA window, if it was closed or minimized (Alt+F11).

4. Display the Debug Window (Immediate Window) - Ctrl+G.

5. Type the following statement in the Debug Window and press Enter Key:

   CreateButton 0, "Edit"

   • The first parameter value (0) represents the section of the form where the command button will be placed.

     • 0 = Detail Section

     • 1 = Form Header

     • 2 = Form Footer

   • The second parameter ("Edit") serves two purposes: it defines the command button’s Name (which will be modified to cmdEdit and assigned to the Name property) as well as its Caption.

     ⚠️ Important: The second parameter must always be a single word. This is essential for correctly creating the MouseMove event procedure in the form’s module. Once the button is created, you can always change its Caption later if needed.

6. Minimize the VBA window to show the Form in Design View.  You can see the Command Button is created in the Detail Section of the Form with the Caption Edit.

7. Change the Form into Form View.

8. Move the Mouse over the Command Button.  The button moves up a little and to the left to show a shadow at the bottom and to the right of the Command Button.

9. Move the mouse out of the button.  The Command Button settles back to the original position, hiding the shadow.

10. Repeat this action in quick succession.  You can see the movement of the button more clearly.

    The VBA Code is Written by the Program.

    If you display the Form's VBA Module, you can see that the following VBA Program lines are written into it:

    Private Sub cmdEdit_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
         BAnimate Me.Name, 1, "cmdEdit"
    
    End Sub
    
    Private Sub Detail_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
        BAnimate Me.Name, 0, "cmdEdit"
    
    End Sub

11. Change the Form mode to Design View again.

12. Click on the Command Button to select it.

13. Drag the Command Button to the right and down to show the Rectangle Control with the special effect shadow underneath it.

    

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

    The Command Button and the Rectangle Control work together to create the animation effect. To move them as a pair, click near the command button and drag the mouse over both controls to select them together. Then, drag the selection to the desired location on the form.

    It is important to do this before creating another command button in the same form section. Otherwise, the new button will be placed directly on top of the previous one. By default, all newly created command buttons are positioned in the same location within their section.

14. Right-click on the Detail Section of the Form and select Form Header/Footer to display them on the Form.

Creating a Button on the Header or Footer Section.

You can now try the CreateButton() function with the first parameter set to 1 or 2 to create command buttons in the Form Header or Form Footer sections, respectively.

The second parameter, which specifies the name of the command button, must be unique for each button on the same form.

Below are sample statements demonstrating how to create animated command buttons on the header and footer sections of the form:

CreateButton 1, "PrintPreview"

CreateButton 2, "Exit"

Note: Make sure the Header and Footer sections are visible before attempting to create command buttons on them.

Also, ensure that only one form is open in Design View. If multiple forms are open, the first form found in design mode will be selected for creating the button controls.

During the form design time, running the CreateButton() function from the Debug Window is the most convenient method.

However, if you prefer a different approach, such as launching the program from a Command Button click, that can also be implemented easily.

Button Creation from a Command Button Click.

We can create a Command Button on a form that, when clicked, allows you to input parameter values for the button creation program. This will quickly create the new button on the form currently in Design View and automatically generate its associated Sub-Routines in the form’s module.

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

1. Open a new Form in Design View.

2. Create a Command Button as you normally do.

3. Display its Property Sheet when the Command Button is in the selected state (F4).

4. Change the Name property value to cmdCreate and set the Caption property value to Create Button.

5. Display the Form's VBA Module (Alt+F11).

6. Copy and paste the following Code into the Module and press Ctrl+S to save the Form with the name frmCreateButton:

   Private Sub cmdCreate_Click()
   Dim intSection As Integer, strButtonName As String
   Dim msg As String
   msg = "Form Section (0,1 or 2)" & vbCr
   msg = msg & "0 - Form Details" & vbCr
   msg = msg & "1 - Form Header" & vbCr
   msg = msg & "2 - Form Footer"
   
   intSection = 3
   Do While intSection < 0 Or intSection > 2
       intSection = Nz(InputBox(msg, "cmdCreate_Click()"), 0)
   Loop
   
   strButtonName = ""
   Do While Len(strButtonName) = 0
       strButtonName = InputBox("Command Button Name", "cmdCreate_Click()")
   Loop
   
      If (intSection >= 0 And intSection < 3) And Len(strButtonName) > 0 Then
          CreateButton intSection, strButtonName
      End If
   
   End Sub

   Perform a Demo Run

7. Open a form in Design View and display the Form’s Header/Footer Sections.

8. Open the frmCreateButton in Normal View.

9. Click on the Command Button to enter the parameter values of the program.

10. Input one of the values (0, 1, or 2) suggested by the Prompt to select a Section of the Form and press the Enter Key or click the OK button.

    

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

11. Enter a Name for the Command Button in the second prompt (it should be a single word like PrintPreview) and press Enter Key or Click OK.

12. The Command Button controls are created on the selected Section of the form you kept open in the design view.

If you want to use this method across all your projects, you can transfer the first two functions, BAnimate() and CreateButton(), into a common Library Database. Then, attach this library as a Reference Library File to your projects. (Refer to the blog post Command Button Animation to learn how to do this.)

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

Read More

Data Change Monitoring

Introduction

In a secure environment—databases implemented with Microsoft Access Security—several options are available to protect data from unauthorized changes. With User-level security, you can clearly define who can modify data, view data, or which group of users is restricted from opening a particular form. Unfortunately, these features are not available in Microsoft Access 2007 and later versions, which is surprising given their usefulness.

Regardless of whether a database is secured or not, data integrity remains crucial. Let us highlight a small issue that can arise during data editing or viewing.

Suppose a user needs to search for certain records on a form and update specific field values—for example, a customer’s telephone number, fax number, or other details. When the user moves from one record to another, Access should ideally prompt for confirmation if any changes were made to the record before saving.

There are two common approaches to handling this:

1. Overall Record Check (Simple Method):
   This method checks the Dirty status of the form to determine if any changes were made to the current record. It does not detect which specific fields were modified, only that changes exist. The system can then prompt the user with a warning: if the user agrees, the changes are saved; otherwise, the update is canceled.

2. Field-Level Change Tracking (Advanced Method):
   This method tracks changes made to each individual field in the record. Before updating, it presents the modifications to the user. The record is updated only if the user confirms; otherwise, the changes are canceled. This method provides more granular control and is highly effective in maintaining data accuracy.

Try the first method on a form.

1. Open one of your databases with a Data Editing Form.

2. Open an existing Form in Design View.

3. Display the VBA Module of the Form.

4. Copy and paste the following code into the VBA Module of the Form (you can use this code in any form):

   Private Sub Form_BeforeUpdate(Cancel As Integer)
   Dim msg As String
   
   On Error GoTo Form_BeforeUpdate_Err
   
     If Me.Dirty And Not Me.NewRecord Then
        msg = "Update the changes on Record." & vbCr & vbCr & "Proceed...?"
        If MsgBox(msg, vbYesNo + vbDefaultButton2 + vbQuestion, "Updating Changes") = vbNo Then
            Me.Undo
        End If
     End If
   
   Form_BeforeUpdate_Exit:
   Exit Sub
   
   Form_BeforeUpdate_Err:
   MsgBox Err & " : " & Err.Description, , "Form_BeforeUpdate()"
   Resume Form_BeforeUpdate_Exit:
   End Sub

5. Save the form and open it in Normal View.

6. Make some changes to one or two fields of the current record.

7. Press Ctrl+S to update the changes.

The following message box will pop up:

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

If the user selects Yes, the changes are saved to the record. If the user selects No, the old values are restored.

In this method, the user must be vigilant about the changes they make to the record. Microsoft Access does not provide any indication of what was changed or which specific fields were modified.

A Different Approach.

The second method tracks changes in each field and shows them to the user.  The following steps we have followed to implement this method:

1. In the Form_Load() Event Procedure, the structure of the record set attached to the Form is scanned for Field Names and Data Types.

   • Field Names and Data Types are loaded into two similar Variant Arrays (Rec() and Rec2(), both are two-dimensional arrays), leaving one element of the Array for loading Field Values later.

   • The memo, OLE Object, Hyperlinks, and Attachment fields are exempted from validation checks.

2. In the Form_Current() event procedure, the current record’s field values—excluding Memo, OLE Object, Hyperlink, and Attachment fields—are loaded into the Rec() array. If the user creates a new record, these values are not loaded or checked. After this step, the user may make changes to the record.

3. In the Before_Update() event procedure, the current record’s field values are loaded into a second array Rec2() and compared with the values stored earlier in the Rec() array. If any field values differ, it is assumed that the user has made changes to those fields. The Field Name, Old Value, and New Value for each changed field are formatted into a message and displayed to the user. A sample image of this message is shown below:

   

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

4. At this point, the user can review the changes and reconfirm them before updating the record by selecting Yes in the message box, or choose No to cancel the changes and restore the original values.

You may copy and paste the following code into the VBA Module of any data editing Form and try it out as we did earlier:

The Form's Class Module VBA Code.

Option Compare Database
Option Explicit

Dim Rec() As Variant, Rec2() As Variant, j As Integer
Dim rst As Recordset, fld_count As Integer, i As Integer


Private Sub Form_BeforeUpdate(Cancel As Integer)
'-----------------------------------------------------
'Author : a.p.r.pillai
'Date   : May 2011
'Remarks: All Rights Reserved by www.msaccesstips.com
'-----------------------------------------------------
Dim msg As String

On Error GoTo Form_BeforeUpdate_Err

If Me.Dirty And Not Me.NewRecord Then
   'Load Field Values after changes into the second array
   For i = 0 To fld_count
       If Rec(i, 0) <> "xx" Then
         Rec2(i, 1) = Me.Controls(Rec(i, 0)).Value
       End If
   Next
   
   'Identify fields with changes made
   'and Mark them.
   For i = 0 To fld_count
     If Rec(i, 0) <> "xx" Then  'If Memo/OLE Object/Hyperlink/Attachment field then skip
        If Rec(i, 1) = Rec2(i, 1) Then
           Rec2(i, 2) = False
        Else
           Rec2(i, 2) = True
        End If
     End If
   Next

   msg = ""
   'Take changed field values and format a message string
   For i = 0 To fld_count
      If Rec2(i, 2) = True And Rec(i, 0) <> "xx" Then
         msg = msg & "[" & UCase(Rec(i, 0)) & "]" & vbCr
         msg = msg & "       Old:  " & Rec(i, 1) & vbCr
         msg = msg & "      New:  " & Rec2(i, 1) & vbCr & vbCr
      End If
   Next
   'If not approved by User reverse the change.
   If Len(msg) > 0 Then
      msg = msg & vbCr & "Update the changes..?"
      If MsgBox(msg, vbYesNo + vbDefaultButton2 + vbQuestion, "Update Change") = vbNo Then
           Me.Undo
      End If
   End If
End If

Form_BeforeUpdate_Exit:
Exit Sub

Form_BeforeUpdate_Err:
MsgBox Err & " : " & Err.Description, , "Form_BeforeUpdate()"
Resume Form_BeforeUpdate_Exit

End Sub

Private Sub Form_Current()
'-----------------------------------------------------
'Author : a.p.r.pillai
'Date   : May 2011
'Remarks: All Rights Reserved by www.msaccesstips.com
'-----------------------------------------------------

On Error GoTo Form_Current_Err
'Load the current record value into array
'Before change
For i = 0 To fld_count
    If Rec(i, 0) <> "xx" Then
        Rec(i, 1) = Me.Controls(Rec(i, 0)).Value
    End If
Next

Form_Current_Exit:
Exit Sub

Form_Current_Err:
MsgBox Err & " : " & Err.Description, , "Form_Current()"
Resume Form_Current_Exit

End Sub

Private Sub Form_Load()
'-----------------------------------------------------
'Author : a.p.r.pillai
'Date   : May 2011
'Remarks: All Rights Reserved by www.msaccesstips.com
'-----------------------------------------------------

On Error GoTo Form_Load_Err
Set rst = Me.RecordsetClone
fld_count = rst.Fields.Count - 1

'Redimension the array for Number of fields
ReDim Rec(0 To fld_count, 0 To 2) As Variant
ReDim Rec2(0 To fld_count, 0 To 3) As Variant
'Load field Name and Type into array
'Memo Field type is 12 and will be excluded
'from validation checks
For i = 0 To fld_count
   j = rst.Fields(i).Type
   If j <> 11 And j <> 12 And j <> 101 Then
       Rec(i, 0) = rst.Fields(i).Name
       Rec2(i, 0) = Rec(i, 0)
       Rec2(i, 3) = rst.Fields(i).Type
   Else
       Rec(i, 0) = "xx"
   End If
Next

Form_Load_Exit:
Exit Sub

Form_Load_Err:
MsgBox Err & " : " & Err.Description, , "Form_Load()"
Resume Form_Load_Exit
End Sub

The Code is not extensively tested for logical errors. Use it at your own risk.

• Create Menus with Macros
• Join Split Array Function-2
• Join Split Array Function
• Memo Field and Data Filtering
• Lively Controls on Form

Read More

Continued on Page 2 on Report

Introduction

Normally, MS-Access reports can span several pages, and it is often useful to display the current page number along with the total number of pages. To achieve this, a TextBox is added to the Page Footer section of the report. In the Control Source property of this TextBox, you can enter an expression like the following:

=”Page: “ & [page] & “ of “ & [pages]

Result: Page 1 of 15

OR

="Page: " & [page] & " / " & [pages]

Result: Page: 1 / 15

Report Date is also added in the page footer area like ="Date: " & format(date(), ”dd/mm/yyyy”)

We often find ourselves repeatedly writing these expressions whenever a new report is designed. If you are a VBA enthusiast, you can automate this process by creating small, reusable User-Defined Functions (UDFs) in your application. By calling the function from a Text Box’s Control Source property, you can quickly insert the required information into your report. I have created two such functions for this purpose—if you’d like to take a look at them, click here.

This approach is especially efficient for developers aiming to standardize functionality across multiple Access applications, much like the concept discussed in your blog post on the MS-Access Reference Library.

Returning to the topic of the page indicator, we aim to display page continuity information in the Report’s Page Footer, but with a slight variation. A report may consist of a single page or multiple pages. When the report spans more than one page, the footer on the first page should display: “Continued on Page 2.”

On subsequent pages (page 2, page 3, etc.), the label should update accordingly—for example, “Continued on Page 3”, and so on—up to the second-to-last page. This label should not appear on the final page. If the report consists of only a single page, the label should be omitted entirely.

Using the first example at the beginning of this article, a single-page report will simply print as “Page 1 of 1.”

Try out the Page Footer Setting

1. To try our new page labels, open one of your Reports with a few pages.

2. Create a Text Box, wide enough to display the label, like Continued on Page 99, at the Page Footer of the Report.

3. Write the following expression in the Control Source Property of the Text Box.

4. =IIf([Pages]>1 And [Page]<[Pages],"Continued on Page " & [Page]+1,"")

5. Save the Report and open it in Print Preview.

6. Check the last page of the Report.  This label should not appear there.

7. Try this out on a single-page Report.

NB: [page], [pages] are System Variables and they should be used in the expression without change. &hypen;

Earlier Post Link References:

• Join Split Array Function-2
• Join Split Array Function
• Memo Field and Data Filtering
• Lively Controls on Form
• Create your own Color Palette

Read More

Product Group Sequence with Auto Numbers

Introduction.

How to generate automatic sequence numbers for different Categories of Products in a Form.  See the sample data given below to understand the gravity of the problem more clearly:

CatCod	Category	ProdSeq	Product Description
1	Beverages	1	Coffee
1	Beverages	2	Tea
1	Beverages	3	Pepsi
2	Condiments	1	Aniseed Syrup
2	Condiments	2	Northwoods Cranberry Sauce
2	Condiments	3	Genen Shouyu
2	Condiments	4	Vegie-spread
3	Confections	1	Uncle Bob's Organic Dried Pears
3	Confections	2	Tofu
1	Beverages	4
2	Condiments	5

The first two columns represent the Product Category Code and its Description, respectively. The third column contains the Category-wise Product Sequence Numbers, and the fourth column lists the Product Description. The product serial number is consecutive. The combination of Category Code and Product Sequence Number forms the Primary Key of the table, so duplicate Product Sequence Numbers are not allowed.

The Product file contains multiple products under each category (e.g., 1. Beverages, 2. Condiments, etc.), and each product within a category should have its own unique sequence number. Product sequence numbers must not contain duplicates.

When a new product is added to a category, the form should automatically generate the next sequence number. Manually tracking the last sequence number used for each category during data entry is impractical. However, MS Access can handle this task efficiently and accurately. A simple VBA routine on the form can accomplish this automatically.

An image of a sample Form for the above data is given below:

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

The first field, Category Code (1, 2, 3, etc.), is a lookup field implemented as a combo box linked to the Category Table. On the property sheet, the first column width is set to 0 so that the category description is displayed in the combo box instead of the numeric code.

During data entry, the user first selects a Product Category in the initial field to prepare for adding a new product under that category. When the user presses the Tab key to move out of the Category field, the next sequence number (i.e., the existing Product Sequence Number + 1) is automatically inserted into the ProdSeq field. The user then needs to enter only the Product Description manually. The ProdSeq field can be locked to prevent accidental changes.

The following program runs in the Category_LostFocus() event procedure. It identifies the highest existing Product Sequence Number for the selected category, calculates the next sequence number, and automatically inserts it into the ProdSeq field:

The Category LostFocus Code.

Private Sub Category_LostFocus()
Dim i, s
If Me.NewRecord Then
     i = Me!Category
     s = Nz(DMax("ProdSeq", "Products", "Category = " & i), 0)
     s = s + 1
     Me![ProdSeq] = s
End If 
End Sub

The program works only when the user attempts to create a New Record.

If the Category Code is a Text value, then you need to make a change in the criteria part of the DMax() Function as given below:

s = Nz(DMax("ProdSeq", "Products", "Category = ‘" & i & "’"), 0)

Technorati Tags: Microsoft

• Join Split Array Function
• Memo Field and Data Filtering
• Lively Controls on Form
• Create your own Color Palette
• Colors 24 bits and Binary Conversion

Read More

Invoke Word Mail Merge from Access2007

Introduction

Mail Merge is a powerful feature that is used to create and print form letters in the Microsoft Word Application. For example, employment contracts often contain fixed terms but require variable details, such as the employee's name, address, basic pay, allowances, and contract period. Traditionally, these documents were preprinted with standard terms and included blank spaces or dotted lines where personal details were filled in manually or with a typewriter. With Mail Merge, however, the document can be printed in its complete form, with personal details automatically inserted into the appropriate locations alongside the standard contract terms. This process can be automated and generated for multiple employees within minutes.

All that’s required is to create a table containing employees’ personal details, design a single Mail Merge Word document with the standard terms of the employment contract, link the Access table to the Word document, and insert the personal detail fields wherever needed. Once set up, the document can be merged and printed directly to the printer.

In MS Access 2007, part of this mail merge process can be initiated directly by selecting a table or query to link with the Mail Merge document in Microsoft Word. You can either connect the table/query to an existing Word document or create a new document linked to the selected data source. Let us create a sample document to see how this works in Microsoft Access 2007.

Prepare for a Demo Run.

1. Open Microsoft Access 2007.

2. Open the sample database Northwind2007.accdb, or import the Employees table from this database into any database that you wish to open to try this out.

3. Click on the Employees Table in the navigation pane to select it.

4. Select External Data -> Export -> More -> Merge it with Microsoft Word.

   Microsoft Word Mail Merge Wizard opens up and gives you two choices: either to open an existing Word document or to create a new one and link the Employees Table with the document. 

   

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

   Depending on what option you have selected, the Employee Table is linked to that document.

5. Select the Word Menu Mailings -> Insert Merge Fields to display the linked Employees Table field list. See the image given below.

   

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

6. Now, you must place the attached Table's data fields in appropriate locations on the Document's body to insert their contents there.  I have placed the name and address of the attached Employees Table on the Document.  The sample Document image is given below:

   

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

7. Place the insertion point where you want the field content to appear, then select the field from the Mailings -> Insert Merge Fields Menu.

8. Repeat this action to place other fields in appropriate places in the document.

9. You can preview the Document by selecting Mailings -> Preview Results at any preparation stage of the document.  The sample preview of the above test fields is given below:

   

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

If the Employees table has 50 records, then 50 copies of this Document will be printed when you select the option Mailings -> Finish & Merge -> Print Documents; i.e., one document for each employee with their respective personal details merged in.

Technorati Tags: Microsoft

• Memo Field and Data Filtering
• Lively Controls on Form
• Create your own Color Palette
• Colors 24 bits and Binary Conversion
• Hexadecimal Numbering System

Read More

Copy Paste Data from Excel to Access2007

Introduction.

Microsoft Office Applications already provide built-in methods to transfer data between them. This can be implemented by importing or exporting data, directly linking to the source while keeping the original data intact in the parent application, or by simply copying data to the clipboard and pasting it into another program. These options have long been available.

In earlier versions of Access, you first needed to create a table with matching field types before pasting or appending data. Access 2007 has made this process much simpler. For example, you no longer need to create a table beforehand when pasting data from Excel. Instead, Access 2007 prompts you to confirm whether the copied data includes header rows. If you select Yes, Access automatically creates a new table (using the worksheet name) and pastes the data into it, assigning the correct field types.

Let us find out how?

1. Open Microsoft Excel and create a small database with the sample data given below:

   

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

2. Open Microsoft Access 2007 and open an existing .accdb database or create a new one.

3. Make the Excel database window active.

4. Highlight the Excel database range, including the header row.

5. Select Copy from the Home Menu, to transfer the data into the Clipboard.

6. Make the Access 2007 database window active.

7. Right-click on the Navigation Pane of Tables and select Paste from the shortcut menu.  The following message box is displayed:

   

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

8. If you have included the header line of the data while coping it, then you may click on the Yes Command Button; otherwise, select No.

A new Table will be created with the Worksheet name.  The header cell values will be used as field names.  The field data type (Text, Date, Number, etc.) will be correctly defined depending on the data type that you have copied from Excel.

If you have selected No, then the data will still be pasted into a new table, but the field names will be F1, F2, F3, etc.

Technorati Tags: Microsoft

1. Roundup Excel Function in MS-Access
2. Proper Excel Function 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 Excel, Word Files from Access

Read More

User-level Access Security and Access2007

Introduction.

In versions of Microsoft Access before Access 2007, User-level and Object-level Security were reliable methods for protecting applications. I secured all my Access applications with User- and Group-level security, which proved highly effective when the applications were shared over a network. Because of this, I never needed to lock VBA modules with a password or convert the applications into a compiled form (MDE format) to prevent tampering. User-level security provided sufficient control to assign users precisely the level of access intended for them within the application.

If you wish to continue using this feature in Access 2007, you must avoid converting earlier databases (with the .mdb extension) to the newer .accdb format. Once converted, all User-level security settings are removed and cannot be reinstated. However, you can still maintain and use User-level security in Access 2007 as long as your databases remain in Access 2003 or earlier formats (with the .mdb extension).

Database Objects and Permissions

With User-level Security, you can control what users can do and what they should not do.  Check the following table to get some idea as to how to set permissions on each object type and what they do:

Permission	Applies to these objects	Result
Open/Run	Entire database, forms, reports, macros	Users can open or run the object, including procedures in code modules.
Open Exclusive	Entire database	Users can open a Database and lock out other users.
Read Design	Tables, queries, forms, macros, code modules	Users can open the listed objects in the Design view. Note: Whenever you grant access to the data in a table or query by assigning another permission, such as Read Data or Update Data, you also grant Read Design permissions because the design must be visible to correctly present and view the data.
Modify Design	Tables, queries, forms, macros, code modules	Users can change the design of the listed objects.
Administer	The entire database, tables, queries, forms, macros, and code modules	Users can assign permissions to the listed objects, even when the user or group does not own the object.
Read Data	Tables, queries	Users can read the data in a table or query. To grant user permissions to read queries, you must also give those user permissions to read the parent tables or queries. This setting implies Read Design permission, which means that users can read your table or query design in addition to the data.
Update Data	Tables, queries	Users can update the data in a table or query. Users must have permission to update the parent table or queries. This setting implies both Read Design and Read Data permissions.
Insert Data	Tables, queries	Users can insert data into a table or query. For queries, users must have permission to insert data into the parent tables or queries. This setting implies both Read Data and Read Design permissions.
Delete Data	Tables, queries	Users can delete data from a table or query. For queries, users must have permission to delete data from the parent Tables or Queries. This setting implies both Read Data and Read Design permissions.

Technorati Tags: Microsoft Security

Earlier Post Link References:

• Create your own Color Palette
• Colors 24 bits and Binary Conversion
• Hexadecimal Numbering System
• Octal Numbering System
• Learn Binary Numbering System-2

Read More