Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Creating User-Account with VBA

For creating a Microsoft Access User-Account manually in the Workgroup Information File (.mdw) we go through Tools - -> Security - -> User and Group Accounts - ->User Name Option. There, we need to provide a unique User Name and a Personal ID to create a User-Account.

We have seen this procedure in the Security Main Menu. The UserIDs, PersonalIDs, Work-GroupIDs and Passwords of Users are maintained in the Workgroup Information File (a database with .mdw extension and the Default Workgroup Information File is System.mdw).

Access privileges to Database Objects are stored within the database itself. Both these two parts of the Combination-Lock (UserID with PersonalID in Workgroup Information File and access rights to individual objects for each User/Group maintained within database) works together. Individual User gets access to Objects with the right UserID, Password and PersonalID. PersonalId is a very important element of the User-Profile and we need it again, if we ever need to recreate a Workgroup Information File, when the existing one is corrupted or lost.

UserIDs and GroupIDs can be created and managed only by Administrators (a member of the Admins Group with Administrator privileges) of the Database.

First, let us create a test User Account manually. Go through the following procedure for creating a User Account manually and see how it is done?

  • User Name: JOHNSMITH (maximum 20 characters)
  • Personal ID: JS123456 (4 to 20 Alphanumeric characters)

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

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

By default all Users will be members of the Users Group. If the User is going to be part of any other User-Groups then that Group Name must be selected from the Available Groups List and Add it to the Member of list at the right hand side.

Let us see how to create a User-Account with VBA, except the procedure explained for joining the User to a particular Group. We will examine the procedure for joining the User to a Group later, after learning to create a Group 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 manually we can only set a password for the User after opening a new MS-Access Application window and by going through the Menu Tools- ->Security- ->User and Group Accounts - - > and log-in without a password and select Change Logon Password Tab of the Dialog Control and set a new Password.

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

The User-Names are in alphabetical order.

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 Enter Key to run the above Code:

Check4UserAccount "SMITHJOHN"

If you have not run the first program to create the User Account SMITHJON 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"

Share:

Microsoft Date Time Picker Control

We have learned how to use Calendar Control for easy entry of Date Values into Fields on Form through the earlier Article: Animated Floating Calendar. We have used a single Calendar Control for several Date Fields by moving the Calendar Control automatically near the selected Date Field with unfolding animation.

We have used this method to save space on the Form otherwise we need to place several Calendar Controls linked to each date field on the Form.

Now we have a better ActiveX Control . . . Microsoft Date Time Picker - a Calendar Control that almost looks like a Combo Box on Form and easy to use.

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

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

    We must know few simple rules that go with this Control to use it with Date Fields.

    • We can set the Control Source Property of Date Time Picker to HireDate and remove the existing HireDate Field.
    • The HireDate field cannot be blank. When you make a record current with HireDate Field blank or attempt to add a new record the Date Time Picker will show an Error Message: Can't set value to NULL when CheckBox Property=FALSE.

    The Error message indicates that HireDate field cannot be left blank or you cannot remove Date Value from a field and make it blank unless you set the CheckBox Property of the Date and Time Picker Control to Yes.

  7. So, first thing first, ensure that the Date Time Picker Control you have added to the Form is in selected state or click to select it, display the Property Sheet (View - -> Properties or Alt+Enter) of the Calendar Control and change the Control Source Property Value to HireDate and change the CheckBox Property Value to Yes.
    • When you move to a particular record the Calendar will automatically move to the date earlier recorded in the HireDate Field. If you want to change the existing HireDate then move the Calendar to the desired Year and Month and click on the required date.
    • When you move to a record with HireDate Field value NULL the Check-Mark disappears and the HireDate field looks like disabled indicating that it is empty but the field shows the date value of last record accessed. If you click on the drop down control of the Calendar to open it then the date value, shown from last accessed record will be entered into the HireDate field immediately, whether you want it or not. You can remove the CheckMark to empty the field or select correct date from the Calendar to overwrite the wrong value.
    • If you prefer to set Date by incrementing or decrementing individual segment (day/month/year) of the Date value, like you do on a Digital Clock, then you may change the UpDown Property Value to Yes. This will change the Drop-down control of the Calendar to a Spin Button Control and the normal Calendar View will not be available.
    • You may click on the Day Segment of the Date value and use the Spin Button Control of the Date Time Picker to increase or decrease the value to the desired level. Use the same method for Month and Year Values.
  8. You may experiment with the Date Time Picker Control, keeping those points I have mentioned above, to understand the behavior of the Calendar control better.
  9. If you want to enter Time rather than Date into a field from the Date Time Picker then change the Format Property Value to 2. This will automatically change the UpDown Control to Yes and the Spin Button control appear in place of the drop-down control and these can be used to set each segment (hh:mm:ss AM/PM) of the Time Value individually as explained above for date.
Share:

Form and Report Open Arguments

While opening a Report or Form we can pass several Optional Values as Run-Time Arguments to control the output on the Report or to change the Form Open Mode depending on the User's Profile.

For example: If the Current User belongs to a particular Users Group, in the Security Workgroups, who has only enough privilege to view the data and not to Add or Edit then we can open the Form in Read-only mode otherwise in Normal Mode, when the Current User attempts to open the Form.

The following Code checks whether the Current User belongs to a particular User Group. The following Code of CheckGroup() Function must be copied into a Standard Module in your Database.

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 must be called from a Command Button Click Event Procedure (we will do this at the end of this Article) as shown below to check and returns the User Group Name to open the Form in a particular 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 an Array of Work Groups of the User and checks whether the User belongs to Admins Group and returns the result. If the result value is Admins then the Products Form is open in Read-Only Mode otherwise in Normal Mode.

The Workgroups Array creation is necessary because a particular User can belong to one or more Workgroups, like Admins, Users (default), Supervisor, Manager, Editor or any other group in the Workgroup Information File(database.mdw), which he/she has been joined to.

The Filter() Function compares the text "Admins" in the array elements and if found it is output into the GrpOut(0) element.

We cannot use the Filter() Function in Form Module Sub-Rotuine, because it will clash with the Form Property name Filter.

Coming back to the Open Arguments of Form and Reports we can pass the name of a Query as Filter Argument or a WHERE condition without the word WHERE. But, there is another parameter known as Open Argument (OpenArgs) through which you can pass a value to the Report or Form and read it back in the Class Module of Report or Form from the same variable OpenArgs and use it for whatever purpose you passed it.

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

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

    A sample Form image is given below for reference.

  3. Select the Combo Box and display its Property Sheet (View - -> Properties or press Alt+Enter).
  4. Change the Name Property value to cboCategory.
  5. Create a Command Button and change its Name Property value to cmdOpen and the Caption Property Value to Open Report.
  6. Select EventProcedure 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.
  7. 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
    
  8. Save the Form with the name Open Argument Demo or any other name you prefer.
  9. Open Products by Category Report in Design View.
  10. Display the Class Module (View - -> Code)
  11. 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
    
  12. Save the Report with the Code.
  13. Open the Open Argument Demo Form in Normal View.
  14. Select a Product Category Name (say Beverages) in the Combo Box.
  15. Click on the Open Report Command Button.

    The Products by Category Report will open with only Beverages items on the Report and the heading label modified to show the Product category Name.

  16. Make the Combo box value empty by deleting the current value and click on the Open Report Command Button.

This time all the Products by Category will appear on the Report and the heading will remain without any change. We are testing the presence of some values in the OpenArgs Variable in the Report Open Event Procedure, if it is Null then terminate the Sub-Routine.

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

  1. Open the Open Argument Demo Form in Design View.
  2. Create a second Command Button on the Form.
  3. Display the Property Sheet of the Command Button (View - -> Properties).
  4. Change the Name Property value to cmdOpenForm and change the Caption Property Value as Open Form.
  5. Display the Class Module (View - -> Code).
  6. Copy and paste the second Program from the top into the Module and save the Open Argument Demo Form.
  7. Create a Tabular type Form for Products Table and name the Form as Products.
  8. Open the Open Argument Demo Form and click on the Open Form Command Button.
  9. 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.
Share:

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads


Blogs Directory

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

Forms How Tos Functions MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Queries External Links msaccess reports msaccess tips Menus and Toolbars Accesstips MsaccessLinks Process Controls Art Work Downloads msaccess How Tos Graph Charts msaccessQuery List Boxes Command Buttons Emails and Alerts Query Combo Boxes Custom Wizards DOS Commands ms-access functions msaccess functions msaccess graphs msaccess reporttricks msaccessprocess security advanced Access Security Array Custom Functions Data Macros Menus Property Report Top Values VBA msaccess email msaccess menus progressmeter Access2007 Auto-Number Command Button Copy Form Join Microsoft Numbering System Records Security Split SubForm Table Utility Variables Workgroup database msaccess wizards Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting Calculation ChDir Color Palette Conditional Formatting Controls Data Filtering Data Type Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export Expression External Field Type Fields Filter Form Instances Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Methods Monitoring Object Reference Objects Octal Numbers Operating System Paste Primary-Key Product Rank Reading Recordset Rich Text Sequence SetFocus Summary Tab-Page Tables Time Difference Union Query User Users Water-Mark Word automatically commands function hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar tutorial updating upload vba code

Featured Post

Function Parameter Array Passing

Last week we have explored the usage of ByVal (By Value) and ByRef (By Reference),  in the Function Parameter, to pass the value from  a Va...

Labels

Blog Archive

Recent Posts