Auto Numbering In Query Column

Introduction

Find New Auto-Numbers in Query Column Version-2 on this link.

For creating "Running Sum Values in Query-Column" visit the following link:

Running Sum in MS-Access Query.

We know how to create an Auto-number Field in a Table to generate Unique Sequence numbers automatically for the records added to the Table. We know how to create Sequence Numbers for data lines on Reports.

On The Reports.

On Reports, create a TextBox in the Detail Section of the Report, write the expression =1 in the Control Source Property and, change the Running Sum Property Value to Over All or Over Group. If you need sequence numbers for each Group separately, depending on the Sorting and Grouping settings on the Report, then the Over Group option must be set in the Property otherwise set the Over All value, for continuous numbers from the start of the Report to the End.

If you want to create a Running Sum value of a Field, like Quantity or Total Price, then set the Running Sum Property value as explained above. For more details on Running Sum as well as creating Page-wise Totals on Access Reports visit the Page with the Title: MS-Access Report and Page Totals.

In The Query Column.

But, Auto-numbering in the Query Column looks somewhat strange to ask for, unless you want to use the Query result for display purposes or the output created from that should have sequence numbers for some reason. 

Products Category Group-level sequence numbers or for creating Rank List for students based on their obtained marks and so on.

Or after filtering the records in the Query the Auto-number field values gone out of sequence.

Anyway, this requirement was raised by a participant in an MS-Access Users Forum on the Net and nobody (including me) could give a clear-cut solution except for some alternatives. I chipped in with a solution of my own, even though I was not happy with that either.

The Access User who raised the question in the Forum made direct contact by sending an e-mail to me asking for a solution.

This made me think again on that topic and did a few trial runs of a few simple methods. Finally, I could come up with a Function that can do the trick and I am presenting it here so that you can also use it if you really need it.

Need Trial and Error Runs.

It is important to know the usage of the QrySeq() Function in a new Column of Query to create Sequence Numbers. The Function must be called with a few Parameter Values using the value(s) from the Query Column(s) itself. So, before presenting the VBA Code of the Function I will give some details of the Parameters.

Usage of the Function in the Query Column is as shown below:

SRLNO: QrySeq([ORDERID], "[ORDERID]", "QUERY4")

The QrySeq() Function needs three Parameters.

1. The First Parameter must be Unique Values available from any Column in the Query.
2. The second Parameter is the Column Name of the first parameter in Quotes.
3. The third Parameter is the Name of the Query, from which you call the Function.

The Query, from where the QrySeq() Function is called should have a column of Unique Values, like Autonumber or Primary Key Field. If this is not readily available, then create a Column by joining two or more existing fields (like NewColumn:([OrderlD] & [ShippName] & [RequiredDate] & [Quantity] from the existing column values and ensure that this will form Unique values in all records and pass this Column value ([NewColumn]) as the first Parameter.

The first Parameter Column Name must be passed to the Function in Quotes ("[NewColumn]") as the second parameter.

The Name of the Query must be passed as the third parameter.

NB: Ensure that you save the Query first, after every change to the design of the Query, before opening it in Normal View, to create the Sequence Numbers correctly.

The QrySeq() Function Code

Now then, the simple rules are in place and it is time to try out the Function.

1. Copy and Paste the following VBA Code into a Standard Module in your Database:
   
   

   Option Compare Database
   Option Explicit
   
   Dim varArray() As Variant, i As Long
   
   Public Function QrySeq(ByVal fldvalue, ByVal fldName As String, ByVal QryName As String) As Long
   '-------------------------------------------------------------------
   'Purpose: Create Sequence Numbers in Query in a new Column
   'Author : a.p.r. pillai
   'Date : Dec. 2009
   'All Rights Reserved by www.msaccesstips.com
   '-------------------------------------------------------------------
   'Parameter values
   '-------------------------------------------------------------------
   '1 : Column Value - must be unique Values from the Query
   '2 : Column Name  - the Field Name from Unique Value Taken
   '3 : Query Name   - Name of the Query this Function is Called from
   '-------------------------------------------------------------------
   'Limitations - Function must be called with a Unique Field Value
   '            - as First Parameter
   '            - Need to Save the Query after change before opening
   '            - in normal View.
   '-------------------------------------------------------------------
   Dim k As Long
   On Error GoTo QrySeq_Err
   
   restart:
   If i = 0 Or DCount("*", QryName) <> i Then
   Dim j As Long, db As Database, rst As Recordset
   
   i = DCount("*", QryName)
   ReDim varArray(1 To i, 1 To 3) As Variant
   Set db = CurrentDb
   Set rst = db.OpenRecordset(QryName, dbOpenDynaset)
   For j = 1 To i
       varArray(j, 1) = rst.Fields(fldName).Value
       varArray(j, 2) = j
       varArray(j, 3) = fldName
       rst.MoveNext
   Next
   rst.Close
   End If
   
   If varArray(1, 3) & varArray(1, 1) <> (fldName & DLookup(fldName, QryName)) Then
       i = 0
       GoTo restart
   End If
   
   For k = 1 To i
   If varArray(k, 1) = fldvalue Then
       QrySeq = varArray(k, 2)
       Exit Function
   End If
   Next
   
   QrySeq_Exit:
   Exit Function
   
   QrySeq_Err:
   MsgBox Err & " : " & Err.Description, , "QrySeqQ"
   Resume QrySeq_Exit
   
   End Function

   The Sample Trial Run

2. Import the Orders Table from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb sample database.
3. Copy and Paste the following SQL String into the SQL Editing View of a New Query and save the Query with the Name: AutoNumberQuery:

   SELECT Orders.*, QrySeq([OrderID],"OrderID","AutoNumberQuery") AS SRLNO
   FROM Orders;
   

4. Select Save from File Menu or click on the Save Toolbar Button.
5. Open the Query in the normal view.

Check the SRLNO Column for Sequence Numbers.

Here, the OrderID in the Orders Table has unique field values and we could easily get away with the Sequence Numbers correctly in SRLNO Column.

Let us pretend for a moment that we don't have a single field with Unique Values in the Query. We must create a Column with Unique Values by joining two or more Columns available in the Query and pass it to the QrySeq() Function.

Let us try such an example with the Orders Table.

1. Copy and Paste the following SQL String into a new Query and Save the Query with the name AutoNumberQuery2.

   SELECT Orders.*, [ShipName] & [RequiredDate] AS NewColumn, QrySeq([NewColumn],"NewColumn","AutoNumberQuery2") AS SRLNO
   FROM Orders;

2. Open the Query in normal View to check whether the Serial Numbers were created correctly or not.

Ensuring Accuracy

When there are hundreds/Thousands of records in the Query it is difficult to check whether the Column Values we have passed to the Function are really unique and the Serial Numbers generated have no duplicates in them by manually checking through the records. Instead, we will take a Count of Serial Numbers appearing more than once in the Records, if any, with the use of a Total Query using AutoNumberQuery2 as the Source.

1. Create a new Query uses the following SQL String and name the new Query as DuplicatesCheckQ:

   SELECT AutoNumberQuery2.SRLNO,
    Count(AutoNumberQuery2.SRLNO) AS CountOfSRLNO
   FROM AutoNumberQuery2
   GROUP BY AutoNumberQuery2.SRLNO
   HAVING (((Count(AutoNumberQuery2.SRLNO))>1));
   

2. Open DuplicatesCheckQ Query in Normal View.

You will find the following result showing SRLNO Column is having the same number appearing more than once in the records indicating that the Unique Column Values we have created for the Function are not really Unique and have duplicates in them.

This can be rectified only by adding more Column Values to the NewColumn expression to eliminate the chance of ending up with duplicates.

This method is only an alternative in the absence of an AutoNumber or Primary Key field Values and not with a 100% percent success rate because when you add more records to the Source Table it is likely that it can fail again. In this case, the only solution is to join more fields to the expression in NewColumn so that we can reduce the chance of failures.

Now, to correct the above Query adds the [Freight] Value Column also to the NewColumn expression. Or Copy and paste the following SQL String into the AutoNumberQuery2 Query overwrites the earlier SQL string in there and save the Query.

SELECT Orders.*,
 [ShipName] & [RequiredDate] & [Freight] AS NewColumn,
 QrySeq([NewColumn],
"NewColumn";,"AutoNumberQuery2") AS SRLNO
FROM Orders;

Open the DuplicatesCheckQ Query again to check for duplicates. If the result is empty, then the Sequence Numbers generated will be correct.

Found Different Method, Share it With me.

If you have a better solution to this, then please share it with me too. I don't need a refined version of the above Code or method, but a different approach to arrive at the same or better result.

Next:

Autonumber with Date and Sequence Number.

Download

Download Demo QryAutoNum.zip

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

Read More

Finding Consecutive Workdays with Query

Introduction.

How can we determine whether date values within a certain range are consecutive or intermittent?

Let’s use a real-world example:

A company hires temporary workers on a daily wage to complete a project within 15 days. Workers are informed that if they work 8 hours a day for 7 consecutive days (no weekends or breaks), they will receive a special incentive in addition to their daily wages.

In practice, employees joined on different dates, and not all could maintain a full 7-day streak. Attendance is logged in a table by date and employee code.

After the project ends, we are tasked with identifying which employees worked 7 straight days to award the incentives.

This is actually quite simple to solve—just three queries are enough.

A Table with Sample Data.

Step 1 – Table Structure

Create a new table in Design View with the following fields:

Field Name	Data Type	Description
emp	Short Text	Employee code (e.g., E001, E002)
workdate	Date/Time	The date the employee worked

---

Step 2 – Primary Key

• Select both fields (emp and workdate) together.

• Right-click → Primary Key.
  This ensures no duplicate date entries are accepted for the same employee.

Solution

1. Query1:

2. Create a Total Query Grouped by emp, take the minimum value from workdate to find the work-start date of each employee, and create a new column, adding 6 days to the work-start date to calculate and store the 7th Day-Date of work for each employee. They must work from the work-start date to this date without a break for special incentive eligibility.

SQL: Query1

SELECT Table1.emp,
 Min(Table1.workdate) AS min_date,
 Min([workdate])+6 AS Day7Date
FROM Table1
GROUP BY Table1.emp;

3. Query2: Join Table1 with Query1 on the emp field and select records using the Day7Date as a criterion on workdate that falls on or before the Day7Date.

   SQL: Query2 

   SELECT Table1.* 
   FROM Query1 INNER JOIN Table1 ON Query1.emp = Table1.emp
   WHERE (((Table1.workdate)<= [Day7Date]));
   

4. Query3: Create a Total Query using Query2 as a source. Group on 'emp' field, take Count of workdate, and filter the output with workdate Count =7.

SQL: Query3 

SELECT Query2.emp,
 Count(Query2.workdate) AS [count]
FROM Query2
GROUP BY Query2.emp
HAVING (((Count(Query2.workdate))=7));

The continuity of dates is validated at the employee level, based on each employee’s individual work start date. All employees don't need to work within the same month or start on a specific date.

However, when the data file contains a large number of records spanning different periods, and only records from a particular period need to be evaluated using this method, it’s important to first filter the data. To do this, create a query using Table1, applying the desired Start Date and End Date as criteria to extract only records within that range. Then, use this filtered query as the source for Query1 and Query2 described earlier, instead of referencing Table1 directly.

As the saying goes, “There’s more than one way to skin a cat,” we can take an alternative approach to solve this problem by slightly differentiating how the earlier queries are defined. The SQL strings for this alternative method are provided below. Copy them and create the queries to try out this method as well.

Query 1_1 SQL

SELECT Table1.emp,
 Min(Table1.workdate) AS min_date,
 [min_date]+0 AS day1,
 [min_date]+1 AS day2,
 [min_date]+2 AS day3,
 [min_date]+3 AS day4,
 [min_date]+4 AS day5,
 [min_date]+5 AS day6,
 [min_date]+6 AS day7
FROM Table1
GROUP BY Table1.emp;

Query 2_1 SQL

SELECT Table1.emp,
 Table1.workdate
FROM Query1_1 INNER JOIN Table1 ON Query1_1.emp = Table1.emp
WHERE (((Table1.workdate) In ([day1],[day2],[day3],[day4],[day5],[day6],[day7])));

Query 3_1 SQL

SELECT Query2_1.emp,
 Count(Query2_1.workdate) AS CountOfworkdate
FROM Query2_1
GROUP BY Query2_1.emp
HAVING (((Count([Query1_2].[workdate]))=7));

I have not tested these methods extensively to eliminate side effects. You may use them at your own risk.

Do you have different ideas? Please share them with me by posting them in the Comments Section.

---

• Percentage on Total Query
• Selecting Office Assistant
• Office Assistant with Check Box
• Msgbox with Options Menu
• Who Changed the Data

Read More

SENDING ALERTS TO WORKSTATIONS

Introduction.

Let us consider a scenario to understand the importance of this method.

Suppose we have three MS Access front-end applications—A, B, and C—linked to a shared table in a back-end database on the network. Users of Applications B and C can update information in this table and complete their tasks only after the main records have been created by users of Application A. Not all records are time-sensitive, but some require urgent attention from users of B and C.

We need a mechanism to inform Application A users to prioritize certain records for updating in the system. Once these records are updated, users of Applications B and C should receive alerts indicating the change in status, prompting them to open their applications and act on their respective tasks. Importantly, Applications B and C do not need to remain open continuously to receive these alert messages.

Functional Diagram.

A Functional Diagram of this arrangement is given below:

Click to Enlarge

The Solution:

1. Create a new table with the name Alert_Param with similar fields given below in the BE Database:
   

   • Create key fields—such as Supplier Code, Invoice Number, and Invoice Date—to establish a link with the main table’s records. These fields, when combined, should be defined as the Primary Key.

   • Add a Text field named WrkStation to store the name of the workstation on the Windows network. This value will be recorded automatically when users of Applications B and C enter the key field values from their respective workstations. It will be used by the system running Application A to send alerts to the appropriate recipients.

   • Include a Logical (Yes/No) field named Updated to indicate that an alert has been sent to the designated workstations, thereby preventing duplicate alerts for the same item. If users of Applications B and C miss a live alert (for example, if their machine was turned off at the time), this flag can serve as a visual cue that the relevant records have already been updated in the main table.

     Note: Management of the parameter table’s contents (adding, editing, and deleting records) will be performed exclusively by users of Applications B and C. Users of Application A will only reference this list to prioritize these cases and complete their part of the process.

   • Finally, design a form named Alert to serve as the interface for working with the parameter table.

   • 

   • Display the Property Sheet of the Wrkstation Field and change the Enabled property to No.

   • Repeat this for the Logical Field with the name Updated. Both these fields are managed and used by the program only.

   • Link the parameter table Alert_Param in BE to all the three Applications (A, B & C).

   • Install the Alert Form in all three Applications.

   • In Application A, open the Alert Form in the design view. Display the Form's Property Sheet and change the following Properties to prevent modifying the parameter data manually:

   • Allow Edits = No

   • Allow Deletions = No

   • Allow Additions = No

   • Allow Design Changes = Design View Only

2. Display the Code Module of the Form (View -> Code), copy and paste the following code in the Module, and save the Form:

   Private Sub Form_BeforeUpdate(Cancel as Integer)
      Me![WrkStation] = Environ("COMPUTERNAME")
   End Sub

3. Create a Query to JOIN the Alert_Param Table with the Main_Table on the Key Fields (Supplier Code, Invoice Number & Invoice Date) in the BE Database to pick the records that match for sending Alerts.

   SELECT DISTINCTROW Alert_Param.*
   FROM Main_Table 
   INNER JOIN Alert_Param ON (Main_Table.Supl_Code = Alert_Param.SuplCode) AND (Main_Table.INVOICE = Alert_Param.INV_NO) AND (Main_Table.INVOICE_DATE = Alert_Param.INV_DATE) 
   WHERE (((Alert_param.Updated)=False));

4. Save the above Query with the name Alert_inQ.

5. Create a second Query as shown below using Alert_inQ as the Source and save it with the name Alert_in_ParamQ.

   SELECT Alert_inQ.WrkStation
   FROM Alert_inQ
   GROUP BY Alert_inQ.WrkStation;

6. Copy and paste the code below into a Global Module of your Project and save it.

Public Function WKAlert()
'--------------------------------------------------------------
'Author : a.p.r. pillai
'Date : 01/03/2008
'All Rights Reservered by msaccesstips.com
'--------------------------------------------------------------
Dim wrkStn() As String, xlnvoice As String, msg() As String
Dim cdb As Database, rst1 As Recordset, rst2 As Recordset
Dim reccount, j As Integer, T As Double, flag As Boolean

On Error GoTo WKAlert_Err

reccount = DCount("* ", "Alert_in_ParamQ")

If reccount > 0 Then
 'check the number of workstations involved
     ReDim wrkStn(l To reccount) As String, msg(l To reccount) As String
    Set cdb = CurrentDb
    Set rst1 = cdb.OpenRecordset("Alert_in_paramQ", dbOpenDynaset)
    For j = 1 To reccount
        wrkStn(j) = rst1![WrkStation]
    Next
    rst1.Close
Else
    Exit Function
End If

Set rst1 = cdb.OpenRecordset("Alert_inQ", dbOpenDynaset)
For j = 1 To reccount
    rst1.MoveFirst
    flag = False
    Do While Not rst1.EOF
    If flag = False Then
        msg(j) = " UPDATED "
        flag = True
    End If
     'add the Supplier Invoice details.
    If rst1![WrkStation] = wrkStn(j) Then
        msg(j) = msg(j) & "Supl.Code: " & rst1![SuplCode] & ", " & "Invoice: " & rst1![Inv_No] & ", " & "Inv.Date: " & rst1![Inv_Date] & ", : "
        rst1.Edit
        rst1![Updated] = True
        rst1.Update

    End If
        rst1.MoveNext
    Loop
    'Use the NET SEND command and format the message
     msg(j) = Left(msg(j), Len(msg(j)) - 2)
    msg(j) = "NET SEND " & wrkStn(j) & msg(j)
    msg(j) = msg(j) & " on " & Now()
Next

For j = 1 To reccount
    Call Shell(msg(j))
' send message through Network
     T = Timer
    Do While Timer < T + 0.5
       DoEvents 'Delay Loop for the next message
   Loop
Next

WKAlert_Exit:
Exit Function

WKAlert_Err:
MsgBox Err.Description, , "WKAlert"
Resume WKAlert_Exit
End Function
 

Note: DOS Command NET SEND works under Windows XP only, later Versions of Windows use the MSG Command. Type MSG /? On the Command prompt, for help and an optional parameter listing.

Consider Options for Sending Alerts to Workstations.

Automated Alert

a) This method is recommended when multiple records are added to the main table in batch mode—for example, at the end of intermediate processing within Application A—based on parameters created by users of Applications B and C. In such cases, call the function WkAlert() at the end of the processing steps.

When using this approach, all updated record references for a particular workstation can be consolidated into a single message (via the relevant queries) and sent together, avoiding repetition for each individual record. This is the most efficient method, as it sends alerts automatically while reducing the total number of messages. The VBA code provided here demonstrates this approach and executes after the records are added to the main table.

b) Alternatively, run the program from the After Update event of an individual record updated in Application A. In this case, the program will reference the parameter table created by Applications B and C. If the updated record in the main table matches an entry in the parameter table, then an alert is sent to the corresponding workstation for that record. The updated flag in the parameter table is set accordingly.

Sending Alerts Manually

If the above automated methods are not practical in certain situations, alerts can be sent manually to the respective workstations by clicking on a command button after updating all, or a selected subset, of the records requested through the parameter table.

Clicking the button will invoke the program and send one consolidated message to each workstation for the group of records belonging to them, based on the parameter table.

While this approach relies on manual user intervention and is therefore less efficient than automation, it is still preferable to sending alerts at the individual record level, as described in item b above.

Download.

Download AlertDemo.zip

• Who Changed the Data
• Progress Counter
• Progress Bar on Form
• Progress Meter
• Keyboard Shortcuts

Read More

Refresh Dependant Combo Box Contents

Introduction.

Creating a combo box on a data entry or editing screen is straightforward. Click the Combo Box tool in the toolbox, follow the wizard to select the required fields from the source table or query, choose the target field on the form, and you’re done. Well, almost—some fine-tuning on the combo box’s property sheet is often needed. You may need to adjust column widths, set the Total List Width, configure the List Rows property, enable 'Limit to List', and handle the On Not in List event to prevent entry of values not defined in the combo box.

In some cases, a form may have multiple combo boxes, with the contents of one depending on the selection made in another. In such situations, it’s more effective to filter the second combo box so that it only displays items related to the current selection in the first, rather than showing all possible items. This way, the user can choose from a shorter, more relevant list instead of scrolling through an unnecessarily long one.

A few lines of code in the On Click event procedure of the first combo box can achieve this, saving the user time and effort, and making the form more efficient and user-friendly.

Preparing for the Test Run.

We will use three tables from the Northwind.mdb sample database for our test run. This database is a frequent go-to for ready-made data in our examples. If you are unsure where to locate it on your PC, refer to the Page: Saving Data on Form Not in Table for location details.

Note: For new Microsoft Access users, this database offers plenty of sample tutorials and materials to help you learn the basics of database design. Once you outgrow it—or simply want a fresh challenge—feel free to explore the examples and guides on this site. I aim to keep you engaged with practical tips and projects for a long time.

Import the following tables from the Northwind sample database into your current project:

• Categories

• Products

• Order Details

Next, open the Order Details table, select Form from the Insert menu, choose AutoForm: Columnar from the wizard, and click OK. Save the form as Order Details.

Before making any modifications, let’s review the form’s current layout. Open it in Form View. You’ll notice there is already a combo box for the ProductID field.

Click the combo box, scroll through the list, and try reading some of the items. If you’re anything like me, you may find that many product names are unfamiliar, particularly those containing accented characters or diacritical marks. One thing is certain, however: they belong to different Categories (which we’ve already imported) but are all mixed together in a single list.

Change the Form Design

1. We will create an additional combo box to present the data in a more organized way. Let’s proceed with the design changes to the form.

2. If the form is currently open in Normal View, switch to Design View from the View menu.
   If you prefer your form to have a heading, expand the Form Header section, insert a label, change its caption to Order Details, and adjust the font size to make it stand out as a proper heading.

3. Next, select all the controls and labels—except the OrderID control and its label—and drag them downward to create space for a new combo box above the ProductID combo box.

4. To select the controls: click on an empty area of the form, hold down the mouse button, and drag over all the controls except the OrderID field and label. Once you’re sure all the required controls are selected, release the mouse button. Hover the pointer over the selected group until it changes to a hand icon, then click and drag it downward to create space for the new field and label. This new combo box will display data from the Categories table.

5. If the Toolbox is not visible, enable it from the View menu. Ensure the Control Wizards button (in the Toolbox) is activated.

   • Click the Combo Box tool in the Toolbox and draw a combo box above the ProductID combo box, to match the earlier one's size.

   • From the displayed list, select the Categories table and click Next.

   • Click the >> button to select both fields from the Categories table, then click Next twice.

   • On the final screen, ensure that the option 'Remember the value for later use' (in our code) is selected, then click Finish.

   

6. We do not need to create a new field in the Order Details table to store the value selected from this combo box. Instead, the Product Category Code entered by the user in this Unbound combo box will be used to filter the contents of the Products combo box. Only the item chosen from the Products combo box will be stored in the Order Details table.

   1. Select the new combo box, open the Property Sheet (View → Properties), and set the Name property to cboCat.

   2. Adjust the label’s size so it matches the other labels on the form.

   3. Open the form’s code module (View → Code).

   The Form Class Module VBA Code

7. Copy and paste the following code into the VBA Module:

   Private Sub cboCat_Click()
   Dim xsql0 As String, xsql2 As String, xsql As String
   Dim crit As String
   
   xsql0 = "SELECT DISTINCTROW [ProductID], " & "[ProductName] FROM Products WHERE ("
   xsql2 = " ORDER BY [ProductName];"
   crit = "[CategoryID] = " & cboCat & ") "
   xsql = xsql0 & crit & xsql2
   
   Me.ProductID.RowSource = xsql
   Me.ProductID.Requery
   End Sub

8. Save and close the VBA Window (File -> Close and Return to Microsoft Access). Save the Order Details Form with the changes.
9. Open the Form in the normal view. Click and select an item from the new Combo box, say Meat/Poultry.
10. Now, click the Products Combo box. The List of items appearing there belongs to the Meat/Poultry Category only. Experiment with other Categories of items also.

How it works.

Although the combo box we created displays only a single column (the description), the Combo Box Wizard actually selected two fields — CategoryID and CategoryName — from the Categories table. If you check the Column Widths property in the property sheets of both combo boxes, you will notice that the width of the first column is set to 0. This hides the CategoryID value while still allowing us to select it by clicking the description. Showing the description is preferable, as it is easier for the user to understand which item is being selected.

When a selection is made in the combo box, the CategoryID corresponding to that item is stored in the control’s value. We then use this CategoryID to filter the related products from the Products table by constructing a SQL string with CategoryID as the criterion. This SQL string becomes the Row Source for the Products combo box.

If you open the Products table and examine its data, you will notice that the category description is also included there. Clicking one of the category fields reveals that it is itself a combo box. This combo box — created during table design — uses the Categories table as its source but does not perform any filtering.

To confirm, open the Products table in Design View, select the CategoryID field, and open the Lookup tab in the property sheet. You will see similar settings to those in our form’s combo box: the Row Source is the Categories table, and the first column (the numeric code) is hidden by setting its width to 0, allowing only the category name to be displayed.

Note: Suggestions for improvements or alternative approaches to achieve the same results are welcome.

• Progress Bar on Form
• Progress Meter
• Keyboard Shortcuts
• Find or Filter Data on Form
• Who is Online

Read More

Filtering Data for different Users

Introduction.

In a network-based Microsoft Access application, multiple users are likely to update a shared master table simultaneously, either from different locations or categories of data. When a user logs into the application, they typically expect to see and work with only the records relevant to their assigned area, rather than navigating through the entire dataset.

To make this possible, it is best practice to store the User ID of the person who created or updated each record, along with a date-time stamp indicating when the change was made. This simple measure offers several long-term benefits.

For example, a user might report that they accidentally modified the wrong record but cannot recall exactly which one.  By using the stored User ID and timestamp, combined with an approximate date and time provided by the user, you can quickly filter or list matching records, helping them identify and correct the error.

I have discussed a more advanced scenario in my earlier article Who Changed the Data, which covers how to track and audit changes to records in detail. The approach we are focusing on here is less about rare troubleshooting cases and more about meeting an everyday operational need—ensuring that each user only works with records assigned to them. For this, having User IDs stored with each record is essential.

Limiting User Access to Records.

To restrict access so that each user sees only their relevant records, we can create a Select Query on the main table, using the User ID as the filter criterion. This query will serve as the source for data entry forms, editing forms, report preparation, and other operations.

In a multi-user network application, this query must be redefined dynamically each time a different user launches their instance of the application on their workstation. By doing so, the user will work only with records that belong to their assigned area, avoiding the overhead of loading and scrolling through the full dataset.

However, certain privileged groups—such as Administrators, Managers, or Supervisors—may require access to the entire dataset for oversight, reporting, or decision-making purposes. In such cases, the query must be redefined to lift the restrictions, providing full data access.

This approach addresses three key requirements:

1. Redefining the query immediately upon application launch. This ensures that the filtering is applied before the user can interact with any forms, reports, or other objects.

2. Identifying the User ID and associated Workgroup – This information determines the level of access to grant.

3. Applying the appropriate filter logic – For standard users, restrict to their User ID; for privileged groups, provide full access.

By combining User ID tracking with dynamic query redefinition, we can ensure both security and efficiency in multi-user Access applications.

Sample Data for Trial Runs.

Since we do not have a dedicated table containing actual User IDs, we will use the Orders table from the Northwind.mdb sample database for this example.

If you are unsure where to locate this database, refer to the article Saving Data on Forms Not in a Table for location details. Once located, import the Orders table into your project.

In the Orders table, the last field in the list is ShipCountry, which contains the country name for each order. For demonstration purposes, we will treat the ShipCountry value as our “User ID.” This will allow us to see the filtering logic in action.

In the accompanying VBA code, we will write the logic exactly as it would be for real User IDs and User Groups. The only difference is that for this example, we are substituting Country Names in place of actual IDs.

If you already have a secured database with a table containing real User IDs, simply replace the table and field names in the SQL string within the VBA code to adapt it to your system.

Changing Filter Query Definition through VBA.

The following VBA code runs from the Form_Unload event procedure of the Startup Screen form.

Its purpose is to:

1. Redefine the SQL of the query OrdersQ so that it filters records based on the current user and the user group membership.

2. Adjust the query definition before the user gains access to any data.

3. Close the Startup Screen and open the Control Screen (Main Screen) of the application.

Private Sub Form_Unload(Cancel As Integer)
Dim xsql As String, xsql0 As String, xfilter As String
Dim usrName As String, grpName As String
Dim i As Integer, j As Integer, usrFlag As Boolean
Dim wsp As Workspace, cdb As Database, QryDef As QueryDef

On Error GoTo Form_Unload_Err

xsql0 = "SELECT Orders.* FROM Orders "

xfilter = "WHERE (((Orders.ShipCountry)= '"

'enable this line with changes to Table and Field Names
'xfilter = "WHERE (((Table.UserID) = '"

usrName = CurrentUser

Set wsp = DBEngine.Workspaces(0)
i = wsp.Users(usrName).Groups.Count

If i = 1 Then ' he has only one Group that is USERS
    GoTo NextStep
End If

'check the User's Group status.
usrFlag = False
For j = 0 To i - 1
    grpName = wsp.Users(usrName).Groups(j).Name
    If usrFlag = False And (grpName = "MANAGERS" Or grpName = "SUPERVISORS" Or grpName = "Admins") Then
       usrFlag = True
       Exit For
    End If
Next

NextStep:

If usrFlag Then ' MANAGERS, SUPERVISORS or member of the Admins Group
    xsql = xsql0 & ";" 
        ' give full data access
Else
   ' xfilter = xfilter & usrName & "'));"
 'enable this line if Workgroups exists
    xfilter = xfilter & "USA" & "'));" 'try with different Country Names

    xsql = xsql0 & xfilter
End If

'change the Query definition
Set cdb = CurrentDb
Set QryDef = cdb.QueryDefs("OrdersQ")
   QryDef.SQL = xsql 
   cdb.QueryDefs.Refresh
   Set cdb = Nothing
   Set QryDef = Nothing

'Open the Main Control Screen
     DoCmd.OpenForm "Control", acNormal

Form_Unload_Exit:  
Exit Sub

Form_Unload_Err:  
MsgBox Err.Description, , "Form_Unload()"  
Resume Form_Unload_Exit
End Sub

Users and Workgroups

We assume that a particular User can belong to one of four groups:

1. Admins
2. MANAGERS
3. SUPERVISORS
4. Users

The Users group is the default workgroup for all users. However, a particular user can also belong to other groups, such as those listed in serial numbers 1 to 3 above, which grant special privileges.

If you run this code on a workstation where Microsoft Access security is not enabled, the default user ID will be Admin, a member of the Admins group. In this case, Microsoft Access will log you in automatically without prompting for a user ID or password. Note that you may not be able to open a secured database with this default Admin user ID because, in a properly secured Database, the Workgroup Administrator will have removed all privileges from the Admin account. Without doing so, the database cannot be considered fully secured. To regain access to the Admin account, you would need to reinstall Microsoft Access.

For more information about Microsoft Access workgroups, application security, and related topics, refer to the MS-Access & Security pages.

In the code example, we check the current user’s group membership. If the user belongs only to the Users group, the query will be redefined to filter records based on their user ID, so that they can access only their own data.

Users who are members of the MANAGERS, SUPERVISORS, or ADMINS groups will have unrestricted access to all data. All others will be treated as part of the Users group (or a similar group created by the Workgroup Administrator) and will only be able to access their own records.

Running the above VBA Code.

You can implement this code in the Form_Unload event procedure of your Startup Screen, or save it in a global module by renaming the subroutine to something like Public Function RedefineQuery().

Be sure to remove the Cancel As Integer parameter declaration if you move the code to a global module.

If stored in a global module, the function can be executed through an AutoExec macro. Create a macro named AutoExec, use the RunCode action, and specify the function name. This macro will run automatically when the database opens, instantly updating the query definition.

Check the Keyboard Shortcuts Page for AutoKeys Macro and its usage.

Earlier Post Link References:

• Progress Meter
• Keyboard Shortcuts
• Find or Filter Data on Form
• Who is Online
• Automated Email Alerts/

Read More

Crosstab Union Queries for Charts

Crosstab Union Queries for Charts.

When preparing charts in Excel, source data is either entered directly into cells or linked to specific worksheet locations where summary figures are available. The arrangement of these values is planned in advance and entered in the required order, depending on the chart type. Charts are then created from this data as a final step, either for printing or presentation purposes.

In most cases, this is a one-time task. However, if updates are needed—such as adding another month’s data to a month-wise chart—you must adjust the physical arrangement of the data and update the chart’s Data Series Range so the new values appear.

If space for all twelve months is allocated in advance but actual data is only available up to March, the remaining nine months in the chart will appear blank.

Preparing Data for the Chart.

When preparing charts in MS Access, we first need to shape the data into a format that can be used directly in the chart. This is done using queries. The process can be automated by running action queries through macros, making it essentially a one-time setup.

Partially prepared summary data can then be further refined with other queries, such as crosstab or union queries, to format it for charting purposes. These queries can automatically transform newly added rows in the summary source table into new columns, ensuring that charts update instantly to reflect the changes.

Once a chart is created from such queries, it continues to work without further adjustments—your chart is always ready with updated values.

Next, we will explore how crosstab and union queries can be used to shape summary data for bar charts and line charts. For this example, we will assume that the Profitability Summary data for Branch 1, shown below, was prepared using action queries such as Make Table and Append queries.

Table Name: Profitability

Location	PYear	PMth	Revenue	Expenses
Branch1	2008	1	$25,000	$5,000
Branch1	2008	2	$35,000	$7,500
Branch1	2008	3	$15,000	$4,000
Branch1	2008	4	$40,000	$15,000

The summary data above is reformatted for the chart below using a combination of Select, Crosstab, and Union queries. In this setup, each Crosstab query extracts and structures the Revenue, Expenses, and Income values. These are then merged in a final Union query, which serves as the direct data source for the chart. This approach ensures the chart updates automatically whenever the underlying summary data changes.

Click to Enlarge

Step 1 – Create the Summary_inQ Query

The first step is to format the Year and Month values, calculate the last date of the month, and use this date on the chart’s X-axis.

We will create a Select query named Summary_inQ based on the Profitability table.

1. Open a new query in Design View.

2. Do not select any tables or queries from the displayed list.

3. From the View menu, choose SQL View.

4. Copy and paste the following SQL into the SQL editor:

   sql
   
   SELECT 
   Profitability.*, 
   DateValue([pyear] & "-" & [pmth] & "-01") AS dt, 
   DateAdd("m",1,[dt])-1 AS MonthLastDate
   FROM Profitability;

5. Save the query as Summary_inQ.

6. The Summary_inQ Query in DataSheet View is given below:

   

Tip: I like to include the letter Q in query names, or words like Cross or Union, to make them easy to identify when mixed in with table names.

When new data (e.g., for May, June, and later months) is added to the Profitability table, they will automatically appear in the Summary_inQ and the other Crosstab Queries.

Step 2 – Create the Crosstab Queries.

We will now create three Crosstab queries — one each for Revenue, Expenses, and Income.

Follow these steps for each Crosstab query:

1. Open a new query in Design View.

2. Do not select any tables or queries from the displayed list.

3. Switch to SQL View.

4. Copy and paste the SQL string for the query you are creating.

5. Save the query with the name shown.

1. Revenue Crosstab – CrossRevenueQ

sql
TRANSFORM Sum(Summary_inQ.Revenue) AS SumOfRevenue
SELECT "1Revenue" AS [Desc]
FROM Summary_inQ
GROUP BY "1Revenue"
PIVOT Summary_inQ.MonthLastDate;

2. Expenses Crosstab – CrossExpensesQ

sql
TRANSFORM Sum(Summary_inQ.Expenses) AS TotalExpenses
SELECT "2Expenses" AS Category
FROM Summary_inQ
GROUP BY "2Expenses"
PIVOT Summary_inQ.MonthLastDate;

3. Income Crosstab – CrossIncomeQ

sql
TRANSFORM Sum([Revenue]-[Expenses]) AS Expr1
SELECT "3Income" AS [Desc]
FROM Summary_inQ
GROUP BY "3Income"
PIVOT Summary_inQ.MonthLastDate;

Note on naming:
In the SELECT clause of each SQL statement, you will see a number prefixed to the category name:

• 1Revenue

• 2Expenses

• 3Income

This ensures that when the queries are combined in a Union query, the results are sorted in the logical order (Revenue → Expenses → Income) instead of alphabetical order, which would place Expenses first, Income in the middle, and Revenue last.

Step 3 – Create the Union Query

Finally, we will combine all three Crosstab queries into a single Union query, which will serve as the chart’s source.

1. Open a new query in Design View.

2. Do not select any tables or queries from the list.

3. Switch to SQL View.

4. Copy and paste the following SQL string into the SQL editor:

sql
SELECT * FROM CrossRevenueQ
UNION ALL
SELECT * FROM CrossExpensesQ
UNION ALL 
SELECT * FROM CrossIncomeQ;

5. Save the query with the suggested name — for example: UnionSummaryQ.

6. UnionSummaryQ Query output in Datasheet View is given below: 

   




Designing the Chart.

Now, all that is left to do is to design a Chart using the UnionSummaryQ Query. Instead of repeating the same procedure that I have already explained earlier, you can go to the Next Page and continue there from Step 4 onwards. Change the Property Value Row Source = BarChartQ to Row Source = UnionSummaryQ and continue with creating the Chart and formatting it as explained there. Make changes to the Titles and other formatting options to make the finished Chart look like the sample given above.

1. MS-Access and Graph Charts
2. MS-Access and Graph Charts-2
3. Working With Chart Objects in VBA
4. Column Chart and VBA
5. Pie Chart Object and VBA
6. Missing Lines in Line-Chart
7. Pie Chart and Live Data on Form
8. Scaling Chart Object
9. Cross-Tab Union Queries for Chart

Read More