Auto Numbering In Query Column

Auto Numbering In Query Column.

We know how to create an Auto-number Field in a Table to generate Unique Sequence numbers for the records added to the Table. We know how to insert line numbers sequentially 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 starting with 1 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 Overall All option 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.

However, asking for auto-numbering in a query column might seem unusual—unless the query results are meant for display purposes or the output requires sequence numbers for a specific reason.

Products Category Group-level sequence numbers or for creating a 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 are out of sequence.

This requirement was actually raised by a participant in an online MS Access Users Forum. No one, including myself, was able to suggest a definitive solution, only some alternatives. I offered a solution of my own, even though I wasn’t entirely satisfied with it either.

The Access User who raised the question in the Forum asked for a solution via email.

This prompted me to revisit the topic and experiment with a few simple methods. Eventually, I developed a function that accomplishes the task, and I’m sharing it here so that you can try it out too.

Need Trial and Error Runs.

It is important to understand how to use the QrySeq() function in a new query column to generate sequence numbers. The function must be called with specific parameter values, often derived from the query’s own columns. Before presenting the VBA code for the function, the details of its parameters are explained below.

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

Syntax: Target Column Name: QrySeq([Field Value], "Field Name", "Query Name")

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 which the QrySeq() function is called should include a column of unique values, such as an AutoNumber or a Primary Key field. If such a column is not readily available, you can create one by combining two or more existing fields—for example:

NewColumn: [OrderID] & [ShipName] & [RequiredDate] & [Quantity] 

Ensure that this concatenation produces unique values for all records, and then pass this column ([NewColumn]) as the first parameter to the function.

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.

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 the File Menu or click the Save Toolbar Button.

5. Open the Query in the normal view.

Check the SRLNO Column for Sequence Numbers.

In this case, the OrderID field in the Orders table already contains unique values, so we can generate sequence numbers directly in the SRLNO column without any additional steps.

However, if the query does not contain a single column with unique values, we must create one by combining two or more existing query columns. This newly created column with unique values can then be passed 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 Datasheet View to check whether the Serial Numbers were created correctly.

Ensuring Accuracy.

When a query contains hundreds or thousands of records, it is impractical to manually verify that the column values passed to the QrySeq() function are truly unique and that the generated serial numbers contain no duplicates. Instead, we can use a Total Query to count serial numbers that appear more than once. For this, we use the AutoNumberQuery2 as the source, which allows us to quickly identify any duplicate serial numbers in the dataset.

1. Create a new Query that 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.

The result will show that the SRLNO column contains the same number appearing more than once in the records. This indicates that the column values of the QrySeq() function are not unique and contain duplicates.

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 serves as an alternative when an AutoNumber or Primary Key field is not available, and it does not guarantee 100% accuracy. When additional records are added to the source table, the method may fail again. In such cases, the only solution is to combine more fields in the NewColumn expression to reduce the likelihood of duplicates and ensure uniqueness.

To correct the query above, include the [Freight] column in the NewColumn expression. Alternatively, you can copy and paste the following SQL string into the AutoNumberQuery2 query, overwriting the previous SQL, and then 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 will be correct.

If you know a better solution, please share it with me. I’m not looking for a refinement of the existing code or method, but for a different approach that can achieve the same—or even better—results.

Improved Versions related to this topic:

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

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

Running Sum in MS-Access Query.

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

Finding Consecutive Workdays with Query.

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.

Right-click to open the Large Image in a New Window.

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 a special eligibility incentive.

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 the 'emp' field, take the 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 will 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.

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

NET SEND <message> Windows Command.

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

Assume that we have three MS Access front-end applications—A, B, and C. The main Application A has a back-end Master Table that is shared by the B and C front-end databases on their respective PCs. Users of Applications B and C can update information in this table and complete their tasks and Report generation 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. There is a common parameter table visible to A, B, & C where B & C Users can update the common parameter table to inform A users to prioritize certain Invoices.

Once these records are updated by User A, the System sets a Flag in the parameter table as the work from the A side is completed. In addition, users of Applications B and C will receive desktop 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:

Right-click to open the Large Image in a New Window.

The Solution:

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

   Right-click to open the Large Image in a New Window.

   • 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 workstation name 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.

   • 

     Right-click to open the Large Image in a New Window.

   • 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 VBA.

   • Link the parameter table Alert_Param in BE to all 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 named Alert_inQ.

5. Create a second Query as shown below using Alert_inQ as the Source and save as  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 a command button after updating all or a selected subset of the records requested through the parameter table.

Clicking the button will run 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 Dependent Combo Box Contents

The Dependent Combo Box Contents.

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 items related to the current selection in the first ComboBox, 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.

   

   Right-click to open the Large Image in a New Window.

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 filters the Products in the second ComboBox. 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 length so that they all have the same size. 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. 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; 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 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

Filter the Data Based on User ID.

In a network-based Microsoft Access application, multiple users are likely to update a shared master table simultaneously, either from different locations or data categories. 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 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. 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, it is essential to record the User IDs to be recorded in each record.

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 remove 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 find 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 ShipCountry field is the last in the list, 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 Application's Control Screen (Main Screen).

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 and filter out the records based on the 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.

Right-click to open the Large Image in a New Window.

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:

   

   Right-click to open the Large Image in a New Window.

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: 

   

   Right-click to open the Large Image in a New Window.




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 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, and when finished, the Chart should 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