LEARN MS-ACCESS TIPS AND TRICKS

Group Account Permissions with VBA

a.p.r. pillaiMarch 17, 2010MS-Access Security, security advanced 1 comment

Group Account Permissions with VBA.

We have learned how to remove all permissions from every object for the Users Group Account. Since all users belong to this default group, they inherit any permissions assigned to it, in addition to those from other group accounts they are members of.

If you haven’t reviewed the earlier articles on Microsoft Access Security using VBA, it’s recommended to go through the following links before continuing.

Nature of Permission Assignment.

Last week, we discussed how to remove all permissions from a specific group account. This week, we’ll learn how to assign different sets of permissions for each type of object—Tables, Queries, Forms, and others—to a particular group account. Once this setup is complete, all users belonging to that group will have restricted access to the designated objects.

For Tables and Queries, Users cannot make design changes, but they can view the Table Structure or Query Design. Users can add, edit, or Delete Records in Tables.

Users can open Run Forms and Reports, but cannot view or make Design Changes.

Macros can be run but cannot be viewed or changed in design.

Automating Object-Level Permission Settings.

The VBA Routine given below runs for an external Database and sets permissions for the given User-Group Account in the active Workgroup Information File.

Remember, usernames, passwords, and user-group accounts are stored in the Workgroup Information File (.mdw), while permission settings are saved within the individual databases. In that sense, Microsoft Access security works like a two-part combination lock-key system.

Copy and paste the following VBA Code into a Standard Module of your Database.

Public Function SetPermission2Grp(ByVal DatabaseName As String, ByVal GroupName As String) As Boolean
'-------------------------------------------------------------------------
'Author : a.p.r. pillai
'Date   : March-2010
'Purpose: To Assign Security Permissions to Objects
'       : for a particular Security Group Account
'       : in an external Database.
'Note   : The Security Group Account must be present
'       : in the active Workgroup Information File
'       :'Remarks: All Rights Reserved by www.msaccesstips.com
'-------------------------------------------------------------------------
Dim wsp As Workspace, db As Database, ctr As Container, doc As Document
Dim ctrName As String, docName As String
Dim L4 As String
Const OBJSFULL = &HD01FE
Const dbOpenRun = 2
Const FrmRptOpenRun = 256
Const MacOpenRun = 8
Const TblQryExcludingModifyAdmin = 244 'All permissions Exluding Modify & Administr
'Assign Permissions to Group Account
On Error GoTo SetPermission2Grp_Err
Set wsp = DBEngine.Workspaces(0)
Set db = wsp.OpenDatabase(DatabaseName)
wsp.Groups.Refresh
For Each ctr In db.Containers
ctrName = ctr.Name
ctr.UserName = GroupName
Select Case ctrName
Case "Databases"
For Each doc In ctr.Documents
         docName = doc.Name
         doc.UserName = GroupName
         Select Case docName
           Case "MSysDb"
             'Set Open/Run Permissions to Database Object
              doc.Permissions = doc.Permissions Or dbOpenRun
         End Select
      Next doc

   Case "Forms"
    'Set Open/Run Permissions to Forms Container
      ctr.Permissions = ctr.Permissions Or FrmRptOpenRun
      ctr.Inherit = True
      For Each doc In ctr.Documents
         docName = doc.Name
         doc.UserName = GroupName
         'Set Open/Run Permissions to Each Form
        doc.Permissions = doc.Permissions Or FrmRptOpenRun
      Next doc

   Case "Reports"
    'Set Open/Run Permissions to Reports Container
      ctr.Permissions = ctr.Permissions Or FrmRptOpenRun
      ctr.Inherit = True
      For Each doc In ctr.Documents
         docName = doc.Name
         doc.UserName = GroupName
         'Set Open/Run Permissions to Each Report
         doc.Permissions = doc.Permissions Or FrmRptOpenRun
      Next doc

   Case "Scripts"
     'Set Open/Run Permissions to Macros Container
      ctr.Permissions = ctr.Permissions Or MacOpenRun
      ctr.Inherit = True
      For Each doc In ctr.Documents
         docName = doc.Name
        doc.UserName = GroupName
         'Set Open/Run Permissions to Each Macro
         doc.Permissions = doc.Permissions Or MacOpenRun
      Next doc

   Case "Tables"
      '1. Assigns Full Permissions to Tables & Queries
      ctr.Permissions = ctr.Permissions Or OBJSFULL
      ctr.Inherit = True
      For Each doc In ctr.Documents
        docName = doc.Name
        doc.UserName = GroupName
         L4 = Left$(docName, 4)
       'Exclude System Objects
        If L4 = "MSys" Or L4 = "~sq_" Then
          GoTo nextloop
        End If
        '2. Remove Modify and Administrator permissions
        doc.Permissions = doc.Permissions Or TblQryExcludingModifyAdmin
nextloop:
      Next doc
  End Select
Next
 ctrSetPermission2Grp = false

SetPermission2Grp_Exit:
Set db = Nothing
Set wsp = Nothing
Exit Function

SetPermission2Grp_Err:
MsgBox Err & ": " & Err.Description, , "SetPermission2Grp"
SetPermission2Grp = True
Resume SetPermission2Grp_Exit
End Function

To test the Program, create a copy of any Database and find a Group Account that is not assigned permissions for the target database.
Press Alt+F11 to open the VBA Editing Window if you have already closed it.
Press Ctrl+G to open the Debug Window.
Type the following Statement in the Debug Window and press the Enter Key to run the Code and assign permissions to the selected Group Account in the Test Database:
SetPermission2Grp "C:\My Documents\TestData.mdb", "FAGRP"
Replace the Pathname of the Database and the Security Group Account Name with your own.
After running the Code, open the Test Database and check the Permission Settings of the Group Account you have specified as the parameter.

Note: You may run the Program from a Command Button Click after assigning the Database path name and Security Group Account Name in text boxes.

Earlier Post Link References:

Users Group and Permissions

a.p.r. pillaiMarch 08, 2010MS-Access Security, security advanced No comments

Users Group and Permissions.

Making a Database Truly Secure

When you create a new Workgroup Information File (.mdw) As part of implementing Microsoft Access security, there are two essential steps you must complete immediately:

Create a new Administrator User ID (for example, myAdmin, or any name you prefer) and add this user as a member of the Admins Group.
Remove the default Admin user account from the Admins Group.

If you skip this step, your database remains insecure—anyone can open and use it if Microsoft Office is reinstalled.

From this point forward, you must use your new user ID (myAdmin) to log in as the database administrator for any databases associated with this new Workgroup Information File.

Additionally, any databases you create while logged in as myAdmin will have that account automatically designated as the owner of the database and all its objects.

Remember:

The database owner has full authority over all objects, including administrative rights to assign permissions and transfer ownership.
When ownership of a specific object is transferred to another user, that user gains complete control over it—they can run, modify, or delete the object.

Next, let’s look at how to assign object-level access rights to a user group.

You must set object-level access rights for user groups before deploying your project for public use. This process is performed manually through the menu path:
Tools → Security → User and Group Permissions.

This procedure also includes an essential first step, similar to the two priority steps mentioned earlier for the Workgroup Information File. The step is to remove all permissions for all objects assigned to the Users Group Account.

Without completing this step, any permission settings you later assign to objects—such as Forms, Reports, or Tables—will have no effect, and users will retain full access, including the ability to make design changes or modify the objects freely.

This process can be time-consuming when done manually, as it requires reviewing and clearing permissions for every object group. Since this step is necessary for all new projects, I decided to automate it with a VBA-based procedure, which I’m sharing here for you to try.

Automated Removal of Object-level Permissions.

Copy and paste the following Program into a Standard Module and save it.

Public Function DefaultUsersGrp(ByVal DatabasePathName As String) As Boolean
'----------------------------------------------------------------------------
'Author : a.p.r. pillai
'Date     : March-2010
'Purpose: Remove All Permissions from Users Group Security Account
'All Rights Reserved by www.msaccesstips.com
'----------------------------------------------------------------------------
Dim wsp As Workspace, db As Database, ctr As Container
Dim GroupName As String, doc As Document
Dim L4 As String

Const DB_FULLNO = &H60000
Const OBJS_FULLNO = &H2FE01

'Remove All Permissions on Containers & documents
'for USERS Group

On Error GoTo DefaultUsersGrp_Err

Set wsp = DBEngine.Workspaces(0)
Set db = wsp.OpenDatabase(DatabasePathName)

wsp.Groups.Refresh
GroupName = "Users"

Set ctr = db.Containers("Databases")
Set doc = ctr.Documents("MSysDb")
doc.UserName = GroupNamedoc.Permissions = DB_FULLNO

Set ctr = db.Containers("Tables")
GoSub SetPermission

Set ctr = db.Containers("Forms")
GoSub SetPermission

Set ctr = db.Containers("Reports")
GoSub SetPermission

Set ctr = db.Containers("Scripts")
GoSub SetPermission

Set ctr = db.Containers("Modules")
GoSub SetPermission
DefaultUsers_Grp = False

DefaultUsers_Grp_Exit:
   Set db = Nothing
   Set wsp = Nothing
Exit Function

SetPermission:
For Each doc In ctr.Documents
    doc.UserName = GroupName
    If ctr.Name = "Tables" Then
        L4 = Left$(docName, 4)
        If L4 = "MSys" Or L4 = "~sq_" Then
          GoTo nextloopxxx
        End If
    End If
    doc.Permissions = OBJS_FULLNO
nextloopxxx:
NextReturn

DefaultUsersGrp_Err:
MsgBox Err & ": " & Err.Description
DefaultUsersGrp = True
Resume DefaultUsersGrp_Exit
End Function

Points to Note.

Remember, this program is designed to be run on an external database—specifically, the one from which you want to remove all permissions for all objects assigned to the Users Group Account.
Before running the program, open the target database and review the current permission settings of all objects for the Users Group Account. It’s strongly recommended that you test the program on a copy of the original database first, to ensure that your data and structure remain safe.
Open the Database with the above Code if you have closed it.
Press Alt+F11 to display the VBA Editing Window and press Ctrl+G to display the Debug Window.
Type the following and press the Enter Key:

Default UsersGrp "C:\My Documents\myTest.mdb"
Replace the database Pathname in quotes with your own database name.
You may run the Program from a Command Button Click Event Procedure from a Form.
Open your test database and review the current permission settings for the Users Group Account. You’ll notice that all the check marks from the permission options have now disappeared, confirming that the permissions have been successfully removed.

Earlier Post Link References:

Create Security Group Account with VBA

a.p.r. pillaiMarch 01, 2010MS-Access Security, security advanced 1 comment

Create Security Group Account with VBA.

Last week, we learned how to create a Microsoft Access Security User Account (SMITHJOHN) with VBA and how to join him as a member of the default Group Account Users. The Users and Admins Group Accounts are already present in the default Workgroup Information File (Sytem.mdw ) or in the new Workgroup Information File that you create separately.

If Users need to be organized into categories such as Managers, Supervisors, Data Entry Operators, Editors, and others, access rights can be defined at the group level instead of for individual Users. Any number of Users can be assigned to a group. Once the access rights for a group are defined, every User assigned to that group automatically inherits the privileges granted to it.

One User can be joined to more than one Group as well. A sample image of the User and Group Accounts control image is shown below, and we will examine how the User SMITHJOHN's Security credentials appear in the Control.

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

In the User Name control, SMITHJOHN is selected. Under the Member label, the Users Group Account is displayed, indicating that this user currently belongs only to that group. The Users Group Account also appears in the Available Group List. Unlike the Users Group Account, which is the default group, the other Group Accounts listed under the Available Group List have been created manually to organize different categories of Users.

To make the user SMITHJOHN a member of the VEHGRP Group Account, select VEHGRP from the Available Groups list and click the Add >> Command Button. This action copies the group name to the Member list. Once added, the selected user automatically inherits all the Access Privileges assigned to the VEHGRP Group Account.

At the end of last week's main program, CreateUser, the following code segment is doing exactly what we did manually in the above paragraph:

. . .
   With wsp
     Set tempGrp = .Users(UsrName).CreateGroup("Users")
         .Users(UsrName).Groups.Append tempGrp
         .Users.Refresh
   End With

Creating the Group Account.

Create the Group Account VEHGRP by going through the same procedure as creating a new User Account from the Groups Tab on the Control above. So, the VEHGRP Account was created at some point in time earlier.

Here, we will learn:

How to create a Microsoft Access Security Group Account MANAGER with VBA.
How to join the User SMITHJOHN to the MANAGER Group Account with VBA.

NB: User and Group Accounts are not case-sensitive. Here they are given in uppercase for legibility only.

The following Program creates the MANAGER Group Account in the Available Groups List:

Public Function CreateUserGrp()
'---------------------------------------------------------------------
'Creating a Security Group Account
'Author : a.p.r. pillai
'Date   : March-2010
'All Rights Reserved by www.msaccesstips.com
'---------------------------------------------------------------------
Dim newGrp As Group, wsp As Workspace
Dim tempGrp As Group
Dim grpName As String, grpID As String

On Error Resume Next

Set wsp = DBEngine.Workspaces(0)

    grpName = "MANAGER"
    grpID = "MGR13579"

With wsp
    Set newGrp = .CreateGroup(grpName, grpID)
               .Groups.Append newGrp

    If Err = 3390 Then
      MsgBox "Group Name : " & grpName & vbCr & vbCr & "Group PID     : " & grpID & vbCr & vbCr & "Account Name already Exists..! "
      Err.Clear
    End If

      .Groups.Refresh
End With

End Function

Copy and paste the above program into a Standard Module.
Click somewhere in the middle of the Code and press the F5 Key to run the Code and create the MANAGER Group Account.
You may select Tools -> Security -> User and Group Accounts (from the Database Window).
Select SMITHJOHN in the User Name List.
Check for the name MANAGER in the Available Groups List, and you will find it there.

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

Adding the User Account to a Group.

But the User SMITHJOHN is not yet added to the MANAGER Group Account, and this is where we need to add the code segment given at the top of this page to the main program.

The revised VBA Code is given below to create the MANAGER Group Account and to add the User SMITHJOHN to this Group Account.

Public Function CreateUserGrp()
'---------------------------------------------------------------------
'Creating a Security Group Account
'Author : a.p.r. pillai
'Date   : March-2010
'All Rights Reserved by www.msaccesstips.com
'---------------------------------------------------------------------
Dim newGrp As Group, wsp As Workspace
Dim tempGrp As Group
Dim grpName As String, grpID As String

On Error Resume Next

Set wsp = DBEngine.Workspaces(0)

    grpName = "MANAGER"
    grpID = "MGR13579"

With wsp
    Set newGrp = .CreateGroup(grpName, grpID)
               .Groups.Append newGrp
    If Err = 3390 Then
      MsgBox "Group Name : " & grpName & vbCr & vbCr & "Group PID     : " & grpID & vbCr & vbCr & "Account Name already Exists..! "
      Err.Clear
    End If

      .Groups.Refresh
End With

'Add the User SMITHJOHN to the MANAGER Group Account
usrName = "SMITHJOHN"

   With wsp
     Set tempGrp = .Users(usrName).CreateGroup(grpName)
         .Users(usrName).Groups.Append tempGrp
         .Users.Refresh
   End With
End Function

Running the Code a Second Time.

If you run the revised Code again, it will show an Error Message saying that the MANAGER Group Account already exists, because you have already run this code once. But the remaining part of the code will run.

If you open the User and Group Accounts control now and select SMITHJOHN in the User Name Control, you can see that the MANAGER group name is now appearing under the Member List, indicating that the User is a member of the MANAGER Group Account.

See the sample image given below.

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

Creating User-Account with VBA

a.p.r. pillaiFebruary 22, 2010MS-Access Security, security advanced 3 comments

Creating a User Account with VBA.

To create a Microsoft Access user account in the Workgroup Information File '.mdw', navigate to:
Tools → Security → User and Group Accounts → User Name tab.
There, provide a unique User Name and a Personal ID to create a new user account.

We discussed this procedure earlier in the Security Main Menu. The User IDs, Personal IDs, Workgroups, and Passwords are stored in the Workgroup Information File, a database with the '.mdw' extension. The default file used by Microsoft Access is SYSTEM.MDW.

Access privileges for database objects are stored within the database itself. Together, these two components form the combination lock of Access security:

The User ID and Personal ID stored in the Workgroup Information File act as the key, and
The object-level access rights stored within the database serve as the lock.

A user gains access to specific objects only when the correct User ID, Password, and Personal ID are provided.

The Personal ID is a critical part of each user’s profile—it is required if you ever need to recreate a Workgroup Information File after the original one becomes corrupted or lost.

Only Administrators (members of the Admins Group with administrative privileges) have the authority to create and manage User and Group IDs.

Creating a User Account Manually.

First, let’s create a test user account manually. Follow the procedure given below to create a user account step by step.

User Name: JOHNSMITH (maximum 20 characters)

Personal ID: JS123456 (4 to 20 Alphanumeric characters)

Visit the link: Create MS-Access User Account for more details.

Select Tools -> Security -> User and Group Accounts
Click New in the Users Tab.
Type JOHNSMITH in the Name control.
Type JS123456 in the Personal ID control.
Click OK to complete the procedure.

**The Users Group.**

By default, all User Accounts are added to the Users Group Members. If a user needs to belong to additional user groups, you must select the desired group name from the Available Groups list and add it to the Member Of list using the control on the right-hand side.

Now, let’s learn how to create a user account using VBA—excluding, for now, the step that assigns the user to a specific group. We’ll explore how to link users to groups later, after we cover the topic: how to create a group account with VBA.

**Create a User Account with VBA.**

The following VBA Code creates a User Account with the User Name: SMITHJOHN, with the Personal ID: SJ78901, and with an initial Password: SMITHJOHN:

Public Function CreateUsers()
'---------------------------------------------------------------------------------
'Author : a.p.r. pillai
'Date   : Feb-2010
'All Rights Reserved by www.msaccesstips.com
'Other Ref: http://www.msaccesstips.com/2006/11/create-msaccess-user-account/
'---------------------------------------------------------------------------------
Dim wsp As Workspace
Dim newUser As User, tempGrp As Group
Dim UsrName As String, PersonalID As String
Dim Password As String

On Error Resume Next

Set wsp = DBEngine.Workspaces(0)

    UsrName = "SMITHJOHN" ' 1 to 20 characters.
    PersonalID = "SJ78901" 'upto 4 to 20 alphaumeric characters
    Password = "SMITHJOHN" 'upto 14 characters except NULL

'Create User Account with personalID
   Set newUser = wsp.CreateUser(UsrName, PersonalID, Password)
   wsp.Users.Append newUser

   If Err = 3390 Then
      MsgBox "User Name : " & UsrName & vbCr & vbCr & "User PID    : " & PersonalID & vbCr & vbCr & "Account Name already Exists..! "
      Err.Clear
      Exit Function
   End If
   wsp.Users.Refresh

'Users Group must be created and joined the
'User to it. When created manually this step
'(for Users Group)is done automatically.
   With wsp
     Set tempGrp = .Users(UsrName).CreateGroup("Users")
         .Users(UsrName).Groups.Append tempGrp
         .Users.Refresh
   End With

End Function

When we create a User ID, we can set a password manually for the User. After opening a new MS-Access Application window, select the Menu Options Tools ->Security ->User and Group Accounts ->, log in without a password, select the Change Logon Password Tab of the Dialog Control, and set a new password.

Copy and paste the above Code into a Standard Module and save it.
Click in the middle of the Code and press F5 to run the Code and create the User Account in the active Workgroup Information File.
Select Tools - ->Security- ->User and Group Accounts and check for the User Name SMITHJOHN on the list.

The User Names are in alphabetical order.

Check for a Particular UserName with VBA.

We can check for the presence of a particular User Name in the active Workgroup Information File with the following VBA Code:

Public Function Check4UserAccount(ByVal strUsrName As String) As Boolean
'------------------------------------------------------------------------
'Author : a.p.r. pillai
'Date   : Feb-2010
'All Rights Reserved by www.msaccesstips.com
'------------------------------------------------------------------------

Dim WS As Workspace, msg As String
Dim UsrName As String, K As User, Flag As Boolean

Set WS = DBEngine.Workspaces(0)

'All the Users belong to the Common Group 'Users'
Flag = False
With WS.Groups("Users")
        For Each K In .Users
                  If K.Name = strUsrName Then
                          Flag = True
                          Exit For
                  End If
        Next
End With

If flag Then
     MsgBox "User Name: " & strUsrName & " Already Exists! "
End If

Check4UserAccount = flag

End Function

Type the following in the Debug Window (Immediate Window) and press the Enter Key to run the above Code:

Check4UserAccount "SMITHJOHN"

If you have not run the first program to create the User Account SMITHJOHN, then to test the second Program, call the above function with the Admin User Name.

Type the following in the Debug Window (Immediate Window) and press Enter Key:

Check4UserAccount "Admin"

Earlier Post Link References:

Microsoft Date Time Picker Control

a.p.r. pillaiFebruary 14, 2010Forms, msaccess controls 4 comments

Microsoft Date Time Picker Control.

In an earlier article, Animated Floating Calendar, we learned how to use the Calendar Control to simplify Date Entry form fields. In that method, a single calendar control was used for multiple date fields by automatically moving it near the selected field with a smooth unfolding animation.

This approach helps to save space on the Form; otherwise, we would need to place separate calendar controls for each date field.

Now, we have an even better alternative — the Microsoft Date and Time Picker (DTPicker), an ActiveX control that closely resembles a Combo Box and is extremely easy to use.

Learn its Simple Usage.

Let us try a simple example to learn how to use this Calendar for a Date Field on a Form.

Import the following Objects from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb sample database:
- Table: Employees
- Form: Employees
Open the Employees Form in Design View.
Select the Company Info Tab.
Select the ActiveX Control option from the Insert Menu.
Select the Microsoft Date and Time Picker Control from the displayed list and click OK to create a control on the Form.
Move the control near the HireDate Field and resize it as shown in the sample design of the Form given below:

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

Few Simple Rules of Date/Time Picker Control.
- Before using the Date and Time Picker control with date fields, it’s important to understand a few simple rules.
  You can set the Control Source property of the Date and Time Picker to a field, such as HireDate, and remove the existing HireDate text box from the form.
  However, note that the HireDate field cannot be blank. If you navigate to a record where the HireDate field is empty, or if you try to add a new record without assigning a date, the control will display the following error message:
  
  “Can’t set the value to NULL when CheckBox property = FALSE.”
Right-click to open Large Image in New Tab/Window.
This error message indicates that the HireDate field cannot be left blank, and you also cannot clear the date value from the field once it has been set—unless the CheckBox property of the Date and Time Picker control is set to Yes.
Important Settings.
First things first—make sure the Date and Time Picker control you added to the form is selected. Then, open the Property Sheet (by choosing View → Properties or pressing Alt + Enter) and make the following changes:
- Set the Control Source property to HireDate.
- Set the CheckBox property to Yes.
When you navigate to a record, the Date and Time Picker will automatically display the date stored in the HireDate field. To change the existing hire date, simply move the calendar to the desired year and month, then click the required date.
If you move to a record where the HireDate field is NULL, the check mark will disappear, and the field will appear disabled, indicating that it’s empty. However, the control may still display the date from the previously accessed record. If you open the calendar drop-down, that previous date will automatically populate the HireDate field. You can either remove the check-mark to clear the field or select a new date from the calendar to overwrite the value.
If you prefer to adjust the date manually—by incrementing or decrementing the day, month, or year like you would on a digital clock—set the UpDown property to Yes. This changes the calendar’s drop-down into a spin button control, replacing the standard calendar view.

Once enabled, click any date segment (day, month, or year), and use the spin buttons to increase /decrease the value to your desired setting.
You can experiment with the Date and Time Picker control, keeping the points mentioned above in focus, to better understand how the calendar behaves under different settings.

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

Settings for Time Value.
If you want to enter time values instead of dates using the Date and Time Picker control, change its Format property value to 2. This automatically sets the UpDown property to Yes, replacing the drop-down calendar with a spin button control. You can then use the spin buttons to adjust each segment of the time value — hours (hh), minutes (mm), seconds (ss), and AM/PM — individually, just as explained earlier.

Form and Report Open Arguments

a.p.r. pillaiFebruary 05, 2010msaccess reports, Reports 3 comments

Form and Report Open Arguments.

When opening a Report or Form, you can pass several optional parameters as run-time arguments. These arguments help control the behavior of the report or form, such as filtering the output or changing the form’s open mode depending on the user’s profile.

If the current user belongs to a specific User Group with read-only privileges, then the Form can be opened automatically in Read-Only Mode.

If the user does not belong to that group, the form can open in Normal Mode.

The following VBA function, CheckGroup(), checks whether the current user belongs to a specified User Group. Copy the code for this function into a Standard Module.

The CheckGroup() Function.

Public Function CheckGroup(ByVal strUsr As String, grpName As String) As String
'-----------------------------------------------------
'Author : a.p.r. pillai
'Date   : Feb-2010
'URL    : www.msaccesstips.com
'Remarks: All Rights Reserved by www.msaccesstips.com
'-----------------------------------------------------
Dim wsp As Workspace
Dim GrpArray() As Variant, grpcnt As Integer
Dim GrpOut As Variant, j As Integer

Set wsp = DBEngine.Workspaces(0)

grpcnt = wsp.Users(strUsr).Groups.Count - 1
ReDim GrpArray(0 To grpcnt) As Variant

'User may belong to more than one User Group
'Create an Array of Group Names
For j = 0 To grpcnt
    GrpArray(j) = wsp.Users(strUsr).Groups(j).Name
Next

'Compare Admins with the Array List
'if matches then 'Admins' will be output in grpout Array
GrpOut = Filter(GrpArray(), grpName, True)

CheckGroup = GrpOut(0)

End Function

The CheckGroup() function should be called from a Command Button’s Click event procedure (we will implement it shortly). This function checks the current user’s group and returns the User Group name, which can then be used to open the form in a specific mode.

Private Sub cmdOpenForm_Click()
Dim strGrp

strGrp = CheckGroup(CurrentUser, "Admins")

If strGrp = "Admins" Then
    DoCmd.OpenForm "Products", acNormal, , , acFormReadOnly
Else
    DoCmd.OpenForm "Products", acNormal
End If

End Sub

The CheckGroup() program creates a list of workgroups, checks whether the current user belongs to the Admins Group, and returns the result. If the result is Admins, the Products Form opens in Read-Only Mode; otherwise, it opens in Normal Mode.

Creating the Workgroups array is necessary because a single user can belong to multiple workgroups, such as Admins, Users (default), Supervisor, Manager, Editor, or any other group defined in the Workgroup Information File (.mdw).

The Filter() function searches the array for the text "Admins". If it is found, the value is stored in the GrpOut(0) element.

We cannot use the Filter() function inside a form module subroutine, because it conflicts with the form’s Filter property.

Regarding the Open arguments for forms and reports, you can pass the name of a query as a Filter argument or a WHERE condition (without including the word WHERE).

In addition, there is another parameter called OpenArgs, which allows you to pass a value to a form or report. You can then read this value in the class module of the form or report using the same OpenArgs variable, and use it for any purpose for which it was passed.

OpnArgs Example.

We try out a simple example to learn the usage of a OpnArgs parameter. We need a few objects from the C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb sample database.

Import the following from the Northwind.mdb sample database:
- Table: Products
- Table: Categories
- Query: Products by Category
- Report: Products by Category
Open a new Form and create a Combo Box with the Category Name from the Categories Table.

A sample Form image is given below for reference.
Right-click to open Large Image in New Tab/Window.
Select the Combo Box and display its Property Sheet (View -> Properties or press Alt+Enter).
Change the Name Property value to cboCategory.
Create a Command Button and change its Name Property value to cmdOpen and the Caption Property Value to Open Report.
Select Event Procedure in the On Click Event Property and click on the build (...) Button to open the Class Module of the Form with the empty Sub-Routine lines.

Copy and paste the following VBA code, overwriting the existing line, or copy and paste the middle line alone:

Private Sub cmdOPen_Click()
   DoCmd.OpenReport "Products by Category", acViewPreview, , , , Nz(Me!cboCategory, "")
End Sub

Save the Form with the name Open Argument Demo or any other name you prefer.

Modify the Report Module.
Open the Products by Category Report in Design View.
Display the Class Module (View -> Code)

Copy and Paste the following Code into the Class Module:

Private Sub Report_Open(Cancel As Integer)
Dim strFilter As String

If IsNull([OpenArgs]) Then
   Exit Sub
End If

Report.Title.Caption = Report.Title.Caption & " (" & [OpenArgs] & ")"
strFilter = "CategoryName = '" & [OpenArgs] & "'"
Report.Filter = strFilter
Report.FilterOn = True

End Sub

Save the Report with the Code.
Open the Open Argument Demo Form in Normal View.
Select a Product Category Name (say Beverages) in the Combo Box.
Click on the Open Report Command Button.
The Products by Category Report will open with only Beverage Report items, and the heading label is modified to show the Product Category Name.
Delete the Combo Box's current value and click the Open Report Command Button.

This time, all product categories will appear in the report, and the heading will remain unchanged.

In the Report_Open event procedure, we check whether the OpenArgs variable contains a value. If it is Null, the subroutine terminates immediately.

Trial Run of First Two Programs.

Do the following to try the first two Programs given at the top of this page:

Open the Open Argument Demo Form in Design View.
Create a second Command Button on the Form.
Display the Property Sheet of the Command Button (View -> Properties).
Change the Name Property value to cmdOpenForm, and the Caption Property Value to Open Form.
Display the Class Module (View -> Code).
Copy and paste the second Program from the top into the Module and save the Open Argument Demo Form.
Create a Tabular Type Form for the Products Table and save the Form as Products.
Open the Open Argument Demo Form and click on the Open Form Command Button.
If you have not implemented Microsoft Access Security, you are by default the Admin User, a member of the Admins Group, and the Products Form will open in Read-Only mode.

Indexing and Sorting with VBA

a.p.r. pillaiJanuary 28, 2010msaccessprocess, Process Controls No comments

Indexing and Sorting with VBA.

A table is usually created with a Primary Key or an Index to organize its records in a specific order for viewing or processing. A Primary Key or Index can include one or more fields to ensure that each record has a unique key value, especially when a single field alone cannot guarantee uniqueness.

For example, if you open the Employees table in the Northwind.mdb sample database (located in *C:\Program Files\Microsoft Office\Office11\Samples*), and switch to Design View, you’ll see that the EmployeeID field is defined as the Primary Key.

To create an Index manually and define it as a Primary Key:

Open the Table in Design View.
Click on the left side of the Field Name to select it.
Click on the Indexes Toolbar Button.
You may give any suitable name in the Index Name Field, replacing the PrimaryKey text, if you would like to do so.

If the values in the selected field are not unique, you can include additional fields—up to a maximum of ten—to create a composite key that ensures uniqueness for the Primary Key.

To do this, click and drag over the adjoining fields to select them, or hold down the Ctrl key and click individual fields to select non-adjacent ones.

This process creates a Primary Key Index for the table. You can define multiple indexes in a table, but only one Primary Key can be active at any given time.

Creating an Index with VBA.

We can activate an existing index in a table or create a new one through VBA and use it for data processing.

In this exercise, we’ll learn how to:

Create a new index named myIndex for a table through VBA.
Activate the required index for data processing.
Delete the index once processing is complete.

Before creating a new index, we’ll first check whether it myIndex already exists in the table’s Indexes collection.

If it exists, we’ll activate it.
If not, we’ll create it, activate it, and proceed with processing.

For this example, we’ll use the Orders and Order Details tables from the Northwind.mdb sample database. The Order Details table will be organized in Order-Number Sequence. The total value of all items for each order is calculated and updated in the corresponding record of the Orders table.

The Data Processing Steps

The following are the data processing steps, which we follow in the VBA Routine to update the Orders Table with order-wise Total Value from the Order Details Table:

Open the Orders Table for Update Mode.
Open Orders Details Table for Input.
Check for the Index name myIndex in the Order Details Table. If found, then activate it; otherwise, create myIndex and activate it as the current Index.
Initialize the Total to Zero.
Read the first record from the Order Details Table.
Calculate the Total Value of the item using the Expression: Quantity * ((1-Discount%)*UnitPrice).
Add the Value to the Total.
Read the next record and compare it with the earlier Order Number. If the same, then repeat steps 6 and 7 until the Order Number changes or there are no more records to process from the Order Details Table.
Find the record with the Order Number in the Orders Table.
If found, then edit and update the Total to the TotalValue field in the Orders Table.
Check for the End Of File (EOF) condition of the Order Details Table.
If False, then repeat the Process from Step 4 onwards; otherwise, Close files and stop running.

Prepare for a Trial Run.

To try the above method, Import Orders and Order Details Tables from 'C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb' (Access 2003) or 'C:\Users\User\My Documents\Northwind 2007.accdb' (Access 2007; if not available, then you must create from Local Templates)
Open the Orders Table in Design View.
Add a new Field named Total Value with a Numeric (Double) data Type in the Orders Table.

You may display the Index List of this Table to view its Primary Key Index on the Order ID field.
Save the Orders Table.
Open the VBA Editing Window (Alt+F11).
Create a new Standard Module from the Insert Menu.

Copy and Paste the following VBA Routine and save the Module.

The CreateIndex() Function.

Public Function CreateIndex()
Dim db As Database, fld As Field, tbldef As TableDef
Dim idx As Index, rst As Recordset, PreviousOrderID As Long
Dim CurrentOrderID As LongDim xQuantity As Long, xUnitPrice As Double
Dim xDiscount As Double, Total As Double, rst2 As Recordset

On Error Resume Next

Set db = CurrentDb
Set rst = db.OpenRecordset("Order Details", dbOpenTable)
'Check for presence of myIndex, if found set as current
rst.Index = "myIndex"
If Err = 3800 Then
'myIndex not found
    Err.Clear
    GoSub myNewIndex
End If

On Error GoTo CreateIndex_Err

Set rst2 = db.OpenRecordset("Orders", dbOpenTable)
rst2.Index = "PrimaryKey"
PreviousOrderID = rst![Order ID]
CurrentOrderID = PreviousOrderID
Do Until rst.EOF
    Total = 0
    Do While CurrentOrderID = PreviousOrderID
        xQuantity = rst![quantity]
        xUnitPrice = rst![unit price]
        xDiscount = rst![discount]

        Total = Total + (xQuantity * ((1 - xDiscount) * xUnitPrice))
        rst.MoveNext
        PreviousOrderID = CurrentOrderID
        If Not rst.EOF Then
            CurrentOrderID = rst![Order ID]
        Else
            Exit Do
        End If
    Loop
    rst2.Seek "=", PreviousOrderID
    If Not rst2.NoMatch Then
        rst2.Edit
        rst2![totalvalue] = Total
        rst2.Update
    End If
    PreviousOrderID = CurrentOrderID
Loop

rst.Close
rst2.Close

'Delete temporary Index
Set tbldef = db.TableDefs("Order details")
tbldef.Indexes.Delete "myIndex"

CreateIndex_Exit:
Exit Function

myNewIndex:
rst.Close
Set tbldef = db.TableDefs("Order Details")
Set idx = tbldef.CreateIndex("myIndex")

Set fld = tbldef.CreateField("Order ID", dbLong)
idx.Fields.Append fld
Set fld = tbldef.CreateField("Product ID", dbLong)
idx.Fields.Append fld
tbldef.Indexes.Append idx
tbldef.Indexes.Refresh
Set rst = db.OpenRecordset("Order Details", dbOpenTable)
rst.Index = "myIndex"
Return

CreateIndex_Err:
MsgBox Err.Description, , "CreateIndex()"
Resume CreateIndex_Exit

End Function

Click somewhere in the middle of the VBA Routine and press F5 or click the Run Command Button to execute the Code and update the Orders Table.

At the beginning of the code, we attempt to activate one of the indexes (myIndex) in the Order Details table. Since myIndex has not yet been created, this action triggers an error. The error is trapped, and control is passed to a subroutine that creates myIndex and adds it to the table’s Indexes collection. The new index is then activated in preparation for data processing.

The next steps calculate Order-wise Total Values and update them in the Orders Table.

At the end of the process, myIndex is deleted from the Indexes Collection of the Order Details Table.

Earlier Post Link References:

Data Upload Controls

a.p.r. pillaiJanuary 13, 2010Process Controls 2 comments

Data Upload Controls.

In some projects, we need to regularly import data from external sources such as dBase, Excel, or flat files like CSV and text. These external files can remain linked to the project, allowing their data to be added to a local Microsoft Access table for reporting purposes.

For example, assume that we have an MS Access application that generates monthly business profitability reports. To prepare these reports, we need to upload new data each month from a LAN location, where the file is updated and replaced from a remote source in one of the supported formats.

Our Application has a mechanism to identify when the existing linked file on the LAN Server is replaced with a new file having fresh data. When a new data file is introduced on the Server and replaces the old one, the System detects the new data and enables the CommandButton. This CommandButton is disabled after uploading the data and remains disabled till new external data file overwrites the old one.

When the MS Access application opens, it compares the previously stored file parameters with those of the current file on the server. If the parameters match, the application assumes that the data has already been uploaded and keeps the DataUpload Command Button disabled. If the parameters do not match, it assumes that new data is available and enables the DataUpload Command Button to allow the upload process.

So, how do we detect new data in the attached file? Depending on the file type, different approaches can be used. One common method is to check the continuity of a control value, such as an Invoice Number, Last Receipt Date, or any other unique identifier that reliably distinguishes new records. These control values from the last uploaded data can be compared with the corresponding values in the attached file. If the values match, we can assume that the data has already been uploaded; otherwise, we can proceed to upload the new records.

To perform this verification, you can design a few queries to extract and filter these key values from both sources, then use a VBA routine to compare them and control the next steps in the upload process.

However, I use a simpler method to check for the new data in the attached file. Before explaining that, we need to consider a few important factors.

The Access Application's back-end Database and the upload data source both must be on the LAN Server. Only one authorized Front-End Database user is permitted to execute the upload process.

Taking these considerations into account, we must design a reliable, controlled method to ensure that data is uploaded correctly and prevent duplication.

External Data Source Files.

I have several applications that upload data from various sources — including IBM AS400 systems, dBase, Excel, and even AS400 report spool files. Over time, I’ve experimented with different methods to detect new information in these files, using queries that compare control data from internal database tables and linked tables.

You might be wondering how I handle the AS400 report spool files, which often have hundreds of pages saved directly to the LAN by the EDP department. These files cannot be linked directly to the database because they don’t follow a proper table structure — except for the detail lines that contain the actual data.

I have developed VBA procedures that will read the spool file line by line and discard unwanted lines, headers, footers, underlines, blank lines, etc., and take only the data lines, cut into text fields in a Table initially before converting each field value into its respective data types and writing it out into a new table.

But the question remains: how can we track the presence of a new report spool file that cannot be directly attached to the database? The solution is quite simple. At the end of each upload operation, I create a control file by copying the first 50 lines of the current spool file. Whenever the application is opened, a small routine compares the first 50 lines of both the current spool file and the control file. If no differences are found, it means the data has already been uploaded into the system. If any variation is detected, the system recognizes it as a new file and prepares to upload the fresh data.

A Common Simple Method is suitable for all Types of Files.

After experimenting with several methods for different file types, I realized the need for a simple, universal approach that could work for all kinds of files—whether attached to the system or not. I eventually developed such a method, which I’m sharing below for your use, should you find it helpful.

We need a small table with the following Fields:

Field Name	Data Type	Field Size
FileLength	Long Integer
FileDateTime	Date/Time
UserName	Text	25
UploadDate	Date/Time
FilePath	Text	255

The sample table in Datasheet View:

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

When the file contents are uploaded, we record some basic information about the attached file—such as its size (in bytes) and its last modified date and time. In addition, we store the name of the user (if Microsoft Access security is implemented) authorized to run the upload process, along with the date and time of the last upload event.

We can read the attached file size in bytes using the Function: FileLen(PathName), and the File's last modified Date and Time can be obtained with the Function FileDateTime(PathName). After the data upload, these values can be updated in the table above to cross-check the external file to check the presence of new data. If needed, we can set the attached file's Read-Only attribute ON using the Function SetAttr(PathName, vbReadOnly), so that the file can be protected from inadvertent changes. It can be reset to Normal with SetAttr(PathName, vbNormal).

A program must be run immediately after the Main Switchboard Form is opened, and cross-check the file size and the File Date/Time recorded in the table with the attached file's attributes. If they are different, then new data has arrived and enabled the Upload CommandButton; the new data can be uploaded.

However, when the application is in open state, and the attached file is replaced by the provider with a new one, the Upload button will remain disabled because the status-checking routine runs only when the main switchboard is opened. Instead of requiring the user to close and reopen the application, a standard but inconvenient procedure, we can add another command button labeled Refresh. When the user clicks this button, the program can recheck the file attributes and enable the Upload button if a new file is detected.

A sample VBA Routine is given below that reads the information from the table and cross-checks with the attributes of the attached file, enabling/disabling the Upload Command Button.

Alternatively, we can run a Timer-Interval Subroutine (at a 1-hour or longer interval) to check the presence of a new source file when the Access Application is active.

The UploadControl() Function Code.

Public Function UploadControl(ByVal frmName As String)
'------------------------------------------------------
'Author   : a.p.r. pillai
'Date     : January-2010
'Remarks  : Data Upload control Routine
'         : All Rights Reserved by www.msaccesstips.com
'------------------------------------------------------
Dim frm As Form, lnglastFileSize, dtlastModified, txtFilePath
Dim lngExternalFileSize, dtExternalModified, authUser
Dim tblControl As String, cmdCtrl As CommandButton

tblControl = "UploadCtrl"
authUser = "LizzaMinnelli"
Set frm = Forms(frmName)
Set cmdCtrl = frm.Controls("cmdUpload")

'Read last recorded information from the Control Table
lnglastFileSize = DLookup("FileLen", tblControl)
dtlastModified = DLookup("FileDateTime", tblControl)
txtFilePath = DLookup("FilePath", tblControl)

'Get the External File information
lngExternalFileSize = FileLen(txtFilePath)
dtExternalModified = FileDateTime(txtFilePath)

If (lngExternalFileSize <> lnglastFileSize) And (dtlastModified <> dtExternalModified) Then
    If CurrentUser = authUser Then
        cmdCtrl.Enabled = True
    Else
        cmdCtrl.Enabled = False
    End If
End If

End Function

The Main Switch Board, which has a Command Button with the name cmdUpload, should call the above Program through the Form_Current() Event Procedure of the Form, passing the Form Name as Parameter, like the following example:

Private Sub Form_Current()
    UploadControl Me.Name
End Sub

If the uploading authorisation is assigned to a particular User, then the Current User's User ID (retrieved with the function CurrentUser()) can also be checked with the UserName Field Value before enabling the Command Button cmdUpload.

Auto Numbering In Query Column

a.p.r. pillaiJanuary 05, 2010msaccessQuery, Queries 18 comments

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.

The First Parameter must be Unique Values available from any Column in the Query.
The second Parameter is the Column Name of the first parameter in Quotes.
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.

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.

Import the Orders Table from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb sample database.
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;
```
Select Save from the File Menu or click the Save Toolbar Button.
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.

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;

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.

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

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.

Autonumber with Date and Sequence Number.

Download

Download Demo QryAutoNum.zip

Group Account Permissions with VBA.

Nature of Permission Assignment.

Automating Object-Level Permission Settings.

Earlier Post Link References:

Users Group and Permissions.

Making a Database Truly Secure

Automated Removal of Object-level Permissions.

Points to Note.

Earlier Post Link References:

Create Security Group Account with VBA.

Creating the Group Account.

Adding the User Account to a Group.

Running the Code a Second Time.

Creating a User Account with VBA.

Creating a User Account Manually.

The Users Group.

Create a User Account with VBA.

Check for a Particular UserName with VBA.

Earlier Post Link References:

Microsoft Date Time Picker Control.

Learn its Simple Usage.

Few Simple Rules of Date/Time Picker Control.

Important Settings.

Settings for Time Value.

Form and Report Open Arguments.

The CheckGroup() Function.

OpnArgs Example.

Modify the Report Module.

Trial Run of First Two Programs.

Indexing and Sorting with VBA.

Creating an Index with VBA.

The Data Processing Steps

Prepare for a Trial Run.

The CreateIndex() Function.

Earlier Post Link References:

Data Upload Controls.

External Data Source Files.

A Common Simple Method is suitable for all Types of Files.

The UploadControl() Function Code.

Auto Numbering In Query Column.

On The Reports.

In The Query Column.

Need Trial and Error Runs.

The QrySeq() Function Code.

The Sample Trial Run.

Ensuring Accuracy.

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.

Next:

Download

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

Post Feed

Get new posts by email:

Search

USEFUL LINKS

Popular Posts

About Me

Blog Archive

Labels

Statcounter

**The Users Group.**

**Create a User Account with VBA.**