Apply Filter to Table directly

Apply a filter directly to the table.

Normally, we use filter settings on Forms or Reports to extract records based on specific conditions. This can be accomplished in several ways—for example:

Adding a WHERE condition (without the keyword WHERE) in the parameter setting of the OpenForm macro action.

Specifying the ApplyFilter condition in the parameter action in a macro.

Supplying criteria in the DoCmd.OpenForm method: 

DoCmd.OpenForm "myForm", acNormal, , "EmployeeID Between 5 AND 10"

Using Filter by Selection directly on a form.

Setting a Query as the form’s record source.

What many developers overlook, however, is that the Filter property of a table itself can also be used to limit records when the table is opened in Datasheet View.

For instance, if you are reviewing someone else’s project and notice that a table displays only a few records in Datasheet View—while you are told the table has hundreds—you may wonder what happened to the rest. In such cases, the hidden culprit might be an active Table Filter.

Setting a Filter on a Table.

We will now explore how this filtering trick works on a Table, for a change. If you have ever applied a filter condition directly on a Form, then you already know enough to do the same with a table—it works almost identically.

To try this out, we need some ready-made data. The Northwind sample database is an excellent choice, but you may also experiment with any table from your own database.

1. Import the Order Details  Table from the Northwind sample database.

2. Open the Table in Design view.

3. Press F4 to display the Table Property Sheet.

   

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

   
   Table Property Sheet View.

4. Find the Filter Property and type [Order ID] Between 35 AND 45 into the property (in Access 2007). For earlier Access Versions, type [OrderID] between 10249 AND 10251.

5. Set the Filter On Load property value to Yes.

6. Save and close the Table Structure.

7. Open the Table in Datasheet View to see the Filter in action.

The output records displayed will be only those with Order IDs between 35 and 45. If you design a Quick Form or Quick Report from this table, the Record Source property will be set automatically. It will include an SQL SELECT statement containing the WHERE condition derived from the table’s Filter property.

However, keep in mind that if you later modify the filter condition on the table, the change will not be reflected automatically in the Record Source SQL of the Form or Report. Once created, the Form or Report retains the original filter condition unless you manually update it. Automating through VBA.

Like most features in Microsoft Access, you can automate this process with VBA to change the filter criteria at the click of a button. However, there is a small catch, which I’ll explain shortly, so that you understand why it’s important. After all, we’re talking about setting a simple filter condition on the Filter property of a table’s structure.

There are two ways to address the Filter property of a table in VBA.

1. Front-door approach: Accessing the property through the path TableDef.Properties("Filter").

2. Backdoor approach: Accessing it via Containers("Tables").Documents("TableName").Properties("Filter").

As you can see, the second method is more elaborate, which is why I refer to it as the backdoor method. We’ll try out examples of both approaches.

The second approach becomes especially useful when working with Forms or Reports—for example, when generating a list of all Forms or Reports, or when you want to change the Form Name. In an earlier blog post, I demonstrated how to create a user-defined (custom) property to store values, such as the last record you worked on, so that the Form can reopen at that record and allow you to continue seamlessly. [Click here to learn more.]

Example-1:

1. Copy and Paste the following VBA Code into the Database's Standard Module.

   Public Function TableFilter1(ByVal OrderStart As Long, ByVal OrderEnd As Long)
   Dim db As Database
   Dim Tbldef As TableDef
   
   Set db = CurrentDb
   
   Set Tbldef = db.TableDefs("Order Details")
   Tbldef.Properties("Filter").Value = "[Order ID] >=" & OrderStart & " AND [Order ID] <=" & OrderEnd
   Tbldef.Properties("FilterOnLoad").Value = True
   db.TableDefs.Refresh
   
   End Function

2. Run the above sample Code from the Debug Window or from a Command Button Click Event Procedure, like the sample run given below:

TableFilter1 35,45

NB: The above code and sample run are demonstrated using the Order Details table in Access 2007.
If you are working with an earlier version of Access, make the following adjustments:

• Change the field name from [Order ID] to [OrderID] (without the space).

• In the sample run, use:

TableFilter1 10249, 10251

instead of the values 35, 45 for the OrderID range.

If you have not tried out the manual filter method explained above or removed the filter criteria setting of the Filter Property then you will run into problems with the above program reporting an Error message stating that the Filter Property not found.

When you implement the VBA method see that an initial criteria setting is set in the Filter property of the Table.  Without the criteria setting the Filter Property will not be visible in VBA.

Example-2:

Copy and paste the following VBA Code into the Standard VBA Module and run the Code in the same way as Example-1 with a different set of OrderIDs:

Public Function TableFilter2(ByVal OrderStart As Long, ByVal OrderEnd As Long)
Dim db As Database, ctr As Container, doc As Document

Set db = CurrentDb

Set ctr = db.Containers("Tables")
Set doc = ctr.Documents("Order Details")
doc.Properties("Filter").Value = "[Order ID] >=" & OrderStart & " AND [Order ID] <=" & OrderEnd
doc.Properties("FilterOnLoad").Value = True
doc.Properties.Refresh

End Function

Technorati Tags: Microsoft

• Percentage of Report Summary
• Embedded Macros in Access 2007
• Report Design in Access 2007
• Memo Field Text Formatting
• Sub-Query in Query Column Expression

Read More

Dynamic Dlookup in Query Column

Dynamic Dlookup in Query Column.

If we need only a single value in a Query Column from a related Table, we often use the DLookup() function.

Normally, the standard approach is to add the related table to the query design surface.  Establish a relationship between the Primary Key of the main table and the Foreign Key of the other table, and then pull the required field value into the query column.

However, in certain cases, this method can lead to complications. To illustrate, let’s look at an interesting scenario where DLookup() is used in a query column.

Suppose we use the following expression to pick up the Sales Tax Rate for each sold item from the tblTaxRates table, so that the tax value can be calculated on the sales amount:

DLookup("TaxRate2005", "tblTaxRates", "[Product Code] = '" & [ProductCode] & "'")

Here’s the catch: the tax rate changes every year as part of the government’s annual budget revisions. The company maintains a history of these changes by adding a new tax rate field for each year, with the year as a suffix—e.g., TaxRate2005, TaxRate2006, TaxRate2007, and so on.

A sample image of the tblTaxRates table is shown below:

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

In the Query, we assign a consistent column name TaxRate, and insert the DLookup() Function as its expression. For example:

TaxRate: DLookup("TaxRate2005","tblTaxRates","[Product Code]='" & [ProductCode] & "'")

Here, the function may refer to TaxRate2005, TaxRate2006, or TaxRate2007, depending on the year being processed. But the query output column name remains the same, TaxRate.

This consistency is useful when designing reports. On the report, we can place a control bound to the TaxRate field. Since the query always produces a column named TaxRate, the report design does not need to be modified each time the tax year changes.

Check the sample Query result image given below:

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

Applying Different Tax Rates

Now, consider a practical scenario:

• Some orders were placed in December, when the previous year’s tax rate still applied.

• Other orders were placed after the budget, when the new tax rate came into effect.

When preparing invoices, this means:

• Older invoices must use the TaxRate2005 field.

• Newer invoices must use the TaxRate2006 field.

In other words, the first parameter of the DLookup() function needs to change each time:

DLookup("TaxRate2005","","")
DLookup("TaxRate2006","","")

(The table name and criteria are omitted for simplicity.)

Obviously, we cannot expect the user to open the query design and manually update the field name every time they need to print invoices from a different year.

The solution is straightforward:

1. Create a small Form with a Text Box (or, better yet, a Combo Box that lists available tax rate fields).

2. Provide a Command Button on the form.

3. The user enters (or selects) the required tax rate field name and clicks the button.

4. The code behind the button dynamically updates the query, refreshes it, and then opens the Report that depends on it.

This approach lets the user control which TaxRate field the DLookup() uses, without ever touching the query design.

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

The Textbox name is Tax on the form.  Our Dlookup() function in the Order Details Query column will look as given below:

TaxRate:DLookup([Forms]![InvoiceParam]![Tax], _
”tblTaxRates”,”[ProdCode]= '” & [Product Code] & “'”)

In the criteria, the ProdCode field of the tblTaxRate table should match the (Product Code) field of the Products Table linked to the Order Details Table to return the tax rate for each item on the Order Details table.

The sample SQL of the Query.

SELECT [Order Details].[Order ID],
 Products.[Product Code],
 Products.[Product Name],
 [Order Details].Quantity,
 [Order Details].[Unit Price],
 [Quantity]*[Unit Price] AS SaleValue,
 Val(DLookUp([Forms]![InvoiceParam]![Tax],"TaxRates", _
 "[ProdCode]='" & [Product Code] & "'")) AS TaxRate,
 [SaleValue]*[TaxRate] AS SaleTax,
 [SaleValue]+[SaleTax] AS TotalSaleValue
FROM Products INNER JOIN [Order Details] _
ON Products.ID = [Order Details].[Product ID];

The Command Button Click Event Procedure can run the following code to open the Sales Invoice Report after refreshing the change on the Form:

Private Sub cmdRun_Click()
Me.Refresh
DoCmd.OpenReport "SalesInvoice", acViewPreview
End Sub

Technorati Tags: Microsoft

• Embedded Macros in Access 2007
• Report Design in Access 2007
• Memo Field Text Formatting
• Sub-Query in Query Column Expression
• GetRows() Function and Exporting Data

Read More

Creating Watermark on MS-Access Reports

Creating Watermarks in MS-Access Reports.

No matter what kind of database design you create for your company, the ultimate output that external users expect is reports—whether in text format or as charts. Charts can be presented in simple 2D Designs, or in more polished 3D designs with gradient colors. While appearance can make reports more attractive, what truly matters is that the information presented is meaningful and serves its purpose.

Think of it like a song: the lyrics may be the same whether sung by an amateur in the bathroom or by a professional singer, but the audience will always prefer the professional version. The same principle applies to reports.

You can throw together a report in a few minutes. Or you can carefully design it, paying attention to every detail—control placement, sizing, font style, highlighting, headings, footers, and summary lines. This may take hours of refinement, but the result is worth it when you present the report and see your boss’s nod of approval.

Now, speaking of controls and enhancements—why not take it one step further? For instance, you can add a watermark (such as a company logo or name) to the report’s background. This small touch gives your report a professional polish. If you have a light grayscale image of your company logo (in .bmp format), you can easily print it as a watermark to enhance the presentation.

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

Incorporating a Watermark on the Report.

1. Open the Report in Design View.
2. Display the Report's Property Sheet (F4).
3. Look for the Picture Property and select the Property.
4. Click on the Build button (...), at the right end of the property, to browse for the Watermark image on the disk and select it.
5. With the following three property settings of the Report, you can display and print the watermark image in various ways:
   • PictureAlignment = 2 (center)
   • PictureTiling = False 
   • PictureSizeMode = 3 (zoom)

When you load the Watermark picture in the background, with the above property settings, the Report Print Preview looks like the image given below:

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

Print the Report on the Printer or convert it to MS-Access Snapshot format (file extension: .snp) or into PDF.

The above work can be automated to define and assign the Watermark Image at the run-time of the Report, with the VBA Code given below:

The ReportWaterMark() Function.

Public Function ReportWaterMark()
Dim rpt As Report, txtRpt As String
Dim imgPath As String

txtRprt = ""
imgPath = ""

Do While txtRpt = "" Or imgPath = ""
  If txtRpt = "" Then
   txtRpt = Nz(InputBox("Give Report Name:", "Report Water Mark"), "")
  End If
  If imgPath = "" Then
   imgPath = Nz(InputBox("Watermark Image PathName:", "Report Water Mark"), "")
  End If
Loop

DoCmd.OpenReport txtRpt, acViewDesign

Set rpt = Reports(txtRpt)
With rpt
    .Picture = imgPath
    .PictureAlignment = 2
    .PictureTiling = False
    .PictureSizeMode = 3
End With
DoCmd.Close acReport, txtRpt, acSaveYes
Set rpt = Nothing

DoCmd.OpenReport txtRpt, acViewPreview

End Function

Copy the code, paste it into a Standard VBA Module, and save the Code.  You can run this program from a Command Button Click Event Procedure like:

Private Sub cmdWMark_Click()
    ReportWaterMark
End Sub

When you run the main program, it will prompt you for two inputs: the report name and the location of the watermark image (including the file name). Be sure to note down this information before running the program. You only need to run the program once for a given report; the image will remain as the background picture till you run it again to replace the image with a different one.

Try the following property settings to see how they appear in the Report Print Preview:

.PictureAlignment = 0
    .PictureTiling = True
    .PictureSizeMode = 0

.PictureAlignment = 2
    .PictureTiling = False
    .PictureSizeMode = 1

Technorati Tags: Microsoft

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

Read More

iSeries Date in Imported Data

AS400 iSeries Date in Imported Data.

When working with an IBM iSeries (IBM AS/400) mainframe computer and importing raw data into Microsoft Access custom report preparations, 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 the 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.

With 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 TextBoxes named Qry and TopVal, for Query name and Top values parameters, respectively, and a CheckBox named Unik for Unique value selection.  The Top Values TextBox can be set to a number or a percentage (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 controls, the user should click the Command Button to Redefine the selected query SQL in the Query Name control.  The Command Button's name is cmdRun (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 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 Query SQL.  Checks for the existence of Top Values and other Property settings; if they exist, then removes them.  Redefines the query based on the Top Values and other property inputs.

Copy and paste the following VBA code for 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

Change Query Top Values Property with VBA.

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 manually change the Top Values property, 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 SELECT Query with the Top Values Property value set to 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 evaluates only 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 enters the input and clicks a Command Button, the SQL will be redefined. 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 to check for the key 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

Creating an Animated Command Button with VBA.

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 consists of a Command Button control and a Rectangle control configured with the Shadow special effect. The CommandButton is manually positioned over the Rectangle control to create the desired visual appearance. The animation is implemented in the Mouse_Move() event procedures in the Form Module, which invoke a common function to perform the Animation.

If you haven’t read the earlier 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 the 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 a little up and to the left to display a shadow at the bottom and to the right of the CommandButton.

9. Move the mouse out from the button.  The CommandButton goes back to its original position, hiding the shadow.

10. Repeat this action in quick succession.  You can see the button movement 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 CommandButton alone 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 value set to 1 or 2 to create CommandButtons 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 View is 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.

A CommandButton Click will prompt for the required parameter values and quickly create the new Animated CommandButton on the form, currently in Design View. It automatically generates 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 CommandButton is in 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 the Command Button to enter the program parameter values.

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 CommandButton in the second prompt (it should be a single word, eg.  PrintPreview) and press the 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

Data Change Monitoring.

In a secure environment using Microsoft Access User-Level Security, several options are available to protect data from unauthorized modifications. User-level security enables you to define precisely which users can modify data, view data, or be restricted from opening specific forms. Unfortunately, these security features are not available in Microsoft Access 2007 and later versions, despite their significant practical value.

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

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 Form's Dirty status 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 Array element 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 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

Continued on Page 2 of the report.

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 Report Page Footer section. 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, 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 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 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 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

Product Group Sequence with Auto Numbers.

How to generate automatic sequence numbers for different Product Categories 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