March 2008 ~ LEARN MS-ACCESS TIPS AND TRICKS

Double-Action Command Button

a.p.r. pillaiMarch 28, 2008Command Buttons, msaccess forms 8 comments

Introduction.

Double-Action does not simply mean executing two different procedures with a single click—after all, you could trigger several actions at once if you wanted. The emphasis here is on programming the same command button to perform two distinct actions depending on whether it is being clicked for the first time or for the second time.

In other words, when the button is clicked once, it performs one specific action; when clicked again, it performs a different action.

For clarity, let’s consider a practical example. Suppose a user opens a data editing screen. To protect the data and prevent accidental changes, the screen should initially be in locked mode, making the fields read-only.

When it becomes necessary to edit one or more fields in the current record, the user clicks the command button to unlock the form for editing. After making the required changes, the user clicks the same button again to lock the form back.

Double-Action Command Button – Functional Roadmap

Initial Form State
- When Form opens or when moving to a different record, all fields are locked for editing.
- Purpose: Prevents accidental edits, even if the user forgot to relock the previous record.
New Record Handling
- If the user starts adding a new record, the form opens fully unlocked for data entry.
- The Double-Action button remains disabled in this state (no locking needed for new entry).
First Click Action
- Unlocks the form for editing.
- Allows changes to the current record.
Second Click Action
- Locks the form again to protect data from accidental changes.
Click State Tracking
- The system uses a simple toggle:
  - Odd-numbered clicks = Unlock form.
  - Even-numbered clicks = Lock form.
- Repeated clicking just alternates between locked and unlocked states.

Implementation Steps for the Double-Action Command Button

Open an Existing Project
Open one of your existing MS Access projects that already contains a data editing form.
Add the Command Button
- Switch the form to Design View.
- Insert a Command Button into the Form Footer section.
Set Button Properties
- Click once on the button to select it.
- Open the Property Sheet (View → Properties).
- Change the Name property to: cmdEdit
- Change the Caption property value (very important) to: Edit
- Open the Form’s VBA Module
  - With the form still selected, go to View → Code to open the form’s VBA code module.
- Insert the Code
  Copy and paste the following VBA code into the module:

Private Sub Form_Current()
If Me.NewRecord Then
    With Me
        .cmdEdit.Caption = "Edit"
        .cmdEdit.ForeColor = 0
        .cmdEdit.FontBold = False
        .AllowEdits = True
        .cmdEdit.Enabled = False
    End With
Else
    With Me
        .AllowEdits = False
        .cmdEdit.Caption = "Edit"
        .cmdEdit.ForeColor = 0
        .cmdEdit.FontBold = False
        .cmdEdit.Enabled = True
    End With
End If
End Sub

This VBA code fully implements your functional roadmap:

Form_Current locks or unlocks fields depending on whether the record is new.
cmdEdit_Click toggles between Edit and Lock modes.
Separate LockForm and UnlockForm procedures make it easy to extend for other control types if needed.

If the Caption Value was 'Edit' when the user clicked the button, then the user wants to edit values on the Form.
If the Caption was 'Lock', then the user wants to lock the Form after editing the data.

The following code will do the trick. Copy and paste the VBA Code into the Form's Module and save the Form.

The VBA Code

Private Sub cmdEdit_Click()
Dim cap As String
cap = Me.cmdEdit.Caption
Select Case cap
     Case "Edit"
         With Me
          .AllowEdits = True
          .cmdEdit.Caption = "Lock"
          .cmdEdit.ForeColor = 128
          .cmdEdit.FontBold = True
          .Refresh
         End With
    Case "Lock"
        With Me
          .AllowEdits = False
          .cmdEdit.Caption = "Edit"
          .cmdEdit.ForeColor = 0
          .cmdEdit.FontBold = False
          .Refresh
        End With
End Select
End Sub

Experiment with your design and check whether it works every time correctly as expected.

If any suggestions for improvement or found that it didn't work the way you expected it, then please post them in the comments.

Earlier Post Link References:

SENDING ALERTS TO WORKSTATIONS

a.p.r. pillaiMarch 22, 2008Emails and Alerts, msaccessQuery No comments

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:

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
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
```

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));

Save the above Query with the name Alert_inQ.
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;
```
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

Refresh Dependant Combo Box Contents

a.p.r. pillaiMarch 14, 2008Combo Boxes, msaccessQuery 5 comments

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

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

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

Save and close the VBA Window (File -> Close and Return to Microsoft Access). Save the Order Details Form with the changes.

Open the Form in the normal view. Click and select an item from the new Combo box, say Meat/Poultry.

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.

Filtering Data for different Users

a.p.r. pillaiMarch 07, 2008msaccessQuery, Queries 5 comments

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:

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.
Identifying the User ID and associated Workgroup – This information determines the level of access to grant.
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:

Redefine the SQL of the query OrdersQ so that it filters records based on the current user and the user group membership.
Adjust the query definition before the user gains access to any data.
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:

Admins
MANAGERS
SUPERVISORS
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.