Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

MICROSOFT ACCESS HOW TOS

Share:

DISPLAY PATH AND FILE INFO

Displaying File Path and File Attributes

Copy and paste the following code into the Standard Module of your project. Replace the text file reference: C:\mytext.txt with one of your own file on disk.

Sub ShowFileAccessInfo2() 
Dim fs, d, f, s  
On Error Goto ShowFileAccessInfo2_Err

Set fs = CreateObject("Scripting.FileSystemObject") 
Set f = fs.Getfile("C:\mytext.txt")  
s = UCase(f.Path) & vbCrLf 
s = s & "Created: " & f.DateCreated & vbCrLf 
s = s & "Last Accessed: " & f.DateLastAccessed & vbCrLf 
s = s & "Last Modified: " & f.DateLastModified & vbCrLf 
s = s & "File Size : " & f.Size & " Bytes."  

MsgBox s, 0, "File Access Info"  

ShowFileAccessInfo2_Exit: 
Exit Sub  

ShowFileAccessInfo2_Err: 
MsgBox Err.Description,,"ShowFileAccessInfo2" 
Resume ShowFileAccessInfo2_Exit  
End Sub 

You may run the program directly from the Debug Window to test it.

Courtesy: Microsoft Access Help Documents.

Go to >> HOW TOs Main Page

Share:

RENAME FILE USING FILESYSTEMOBJECT

Renaming a file and displaying Drive & File Information

Copy and paste the following code into a Standard Module in your Project.

Sub ShowFileAccessInfo() 
Dim fs, f, fn  
On Error GoTo ShowFileAccessInfo_Err
Set fs = CreateObject("Scripting.FileSystemObject") 
Set f = fs.Getfile("C:\mytext.txt") 
fn = f.Name & " on Drive " & UCase(f.Drive) & vbCrLf  
'renames the file named c:\mytext.txt as yourtext.txt  

f.Name = "yourtext.txt" 
fn = fn & "New Name: " & f.Name & vbCrLf 
fn = fn & "Created: " & f.DateCreated & vbCrLf 
fn = fn & "Last Accessed: " & f.DateLastAccessed & vbCrLf 
fn = fn & "Last Modified: " & f.DateLastModified  

MsgBox fn, 0, "File Access Info"  

ShowFileAccessInfo_Exit: 
Exit Sub
  
ShowFileAccessInfo_Err: 
MsgBox Err.Description, , "ShowFileAccessInfo" 
Resume ShowFileAccessInfo_Exit  
End Sub 

Change the program lines wherever the sample text file reference: c:\mytext.txt is appearing in the code with one of your own text file pathname.

Next >> Display Path and File Info.

Share:

CREATE TEXT FILE FROM MSACCESS

Creating Text File from Microsoft Access

The FileSystemObject Object provides access to the computer's file system.

The following code illustrates how the FileSystemObject is used to return a TextStream object that can be read from or written to:

Syntax : Scripting.FileSystemObject

Example:

Sub CreateTextFile()
Dim fs As Object, txt
On Error goto CreateTextFile_Err
Set fs = CreateObject("Scripting.FileSystemObject")
Set txt = fs.CreateTextFile("C:\mytest.txt", True)
txt.writeline ("This is a test.")
txt.Close

CreateTextFile_Exit:
Exit Sub

CreateTextFile_Err:
Msgbox Err.Description,,"CreateTextFile"
Resume CreateTextFile_Exit
End Sub 

In the code shown above, the CreateObject function returns the FileSystemObject (fs). The CreateTextFile method then creates the file as a TextStream object txt, and the WriteLine method writes a line of text to the created text file. The Close method flushes the buffer and closes the file.


Reading From Text File

Reading Text File using FileSystemObject Example:

Sub ReadTextFile()
Dim fs As Object, txt, txtline

On Error Goto ReadTextFile_Err

Set fs = CreateObject("Scripting.FileSystemObject")
Set txt = fs.opentextfile("C:\mytest.txt")
txtline = txt.readline
txt.Close

MsgBox "C:\mytest.txt File contents : " & txtline

ReadTextFile_Exit:
Exit Sub

ReadTextFile_Err:
Msgbox Err.Description,,"ReadTextFile"
Resume ReadTextFile_Exit
End Sub 

Next >> Rename File

Share:

CREATE EXCEL WORD FILE FROM ACCESS

Introduction

Create Excel File or Word Document from Microsoft Access and write information into them. Every application that supports Automation provides at least one type of object. For example, a word processing application may provide an Application object, a Document object and a Toolbar object. To create an ActiveX object, assign the object returned by CreateObject to an object variable.


Create an MS-Word File

The first example creates a Word File and writes some text into it and saves with a name.

Public Sub CreateWordDoc() 
Dim WordObj As Object
  
On Error goto CreateWordDoc_Err

Set WordObj = CreateObject("word.application")
With WordObj
   .Application.Visible = True
   .Application.Documents.Add "Normal", , 0, True
   .ActiveDocument.Content = "THIS IS MY TEST DOCUMENT."
   .Application.ActiveDocument.SaveAs "C:\myDocument2.doc"
   .Application.Quit
End With
Set WordObj = Nothing

CreateWordDoc_Exit:
Exit Sub

CreateWordDoc_Err:
msgbox Err.Description,,"CreateWordDoc"
Resume CreateWordDoc_Exit
End Sub 

Creating an MS-Excel File

The Next example creates an Excel Worksheet and writes a line of text in Column A, Row 1 and saves it with a Name. This code starts the application creating the object, in this case, a Microsoft Excel spreadsheet. Once an object is created, you reference it in code using the object variable you defined. You access properties and methods of the new object using the object variable, ExcelSheet and other Microsoft Excel objects, including the Application object and the Cells collection.

Public Sub CreateExcelSheet()
Dim ExcelSheet As Object

On Error goto CreateExcelSheet_Err

Set ExcelSheet = CreateObject("Excel.Sheet")

With ExcelSheet
   .Application.Visible = True
   .Application.Cells(1, 1).Value = "This is Column A, row 1"
   .SaveAs "C:\TEST.XLS"
   .Application.Quit
End With

Set ExcelSheet = Nothing

CreateExcelSheet_Exit:
Exit Sub
CreateExcelSheet_Err:
Msgbox Err.Description,,"CreateExcelSheet"
Resume CreateExcelSheet_Exit
End Sub 

Next >> Create Text File from Access.

Share:

IMPORT OBJECTS WITH VBCODE

Introduction.

Normally, Tables, Queries or other objects from another database can be imported manually by selecting Import from Get External Data option from File menu. But this can be achieved through VBA Code too and this question, HOW TO? is raised in Microsoft Access User's Forums and I thought it is useful to those who look for this solution. Hence, I present the Code here for importing Tables, Queries and Forms separately.


Importing All Tables.

The next method imports all Tables from a Source database into the active database except the Microsoft Access System Tables.

Public Function TableImport() 
'----------------------------------------------------------------- 
'Function to Import Microsoft Access Tables from another Database 
'Author : a.p.r. pillai 
'Date : 02/12/2006 
'----------------------------------------------------------------- 
Dim wrkSpace As Workspace, db As Database, tbldef 
Dim strFile As String 
Dim ObjFilter As String  
'if conflict with existing object name then ignore 
' and import next object 
On Error Resume Next  
Set wrkSpace = DBEngine.Workspaces(0)  
'Check for Table Definitions in the Source database 
'and import all of them except System Tables.  
Set db = wrkSpace.OpenDatabase("c:\tmp\Sourcedb.mdb") 
For Each tbldef In db.TableDefs 
strFile = tbldef.Name  
'Filter out Microsoft Access System Tables. 
ObjFilter = left(strFile, 4) 
If ObjFilter   "MSys" Then   
    DoCmd.TransferDatabase acImport, "Microsoft Access", "c:\tmp\Sourcedb.mdb", acTable, strFile, strFile, False
End If  
Next  
End Function 

Importing All Queries.

Next Function Imports all the Queries from the Source database into the current database.

Public Function QueryImport() 
'------------------------------------------------------------------ 
'Function to Import Microsoft Access Queries from another Database 
'Author : a.p.r. pillai 
'Date : 02/12/2006 
'------------------------------------------------------------------ 
Dim wrkSpace As Workspace, db As Database, QryDef 
Dim strFile As String  
'if conflict with existing object name then ignore 
'and import next object 
On Error Resume Next  
Set wrkSpace = DBEngine.Workspaces(0) 
'Check for Query Definitions in the Source database 
'and import all of them. 
Set db = wrkSpace.OpenDatabase("c:\tmp\Sourcedb.mdb") 
For Each QryDef In db.QueryDefs
 strFile = QryDef.Name
 DoCmd.TransferDatabase acImport, "Microsoft Access", "c:\tmp\Sourcedb.mdb", acQuery, strFile, strFile, False 
Next  
End Function 

Importing All Forms.

The ImportForms() Function Imports all the Forms from an external Microsoft Access database into the current Database.

Public Function ImportForms() 
'---------------------------------------------------------------- 
'Function to Import Microsoft Access Forms from another Database 
'Author : a.p.r. pillai 
'Date : 02/12/2006 
'---------------------------------------------------------------- 
Dim FRM As Variant, wrkSpace As Workspace 
Dim db As Database, strForm As String 
Dim ctr As Container  
'if conflict with existing object name then ignore 
'and import next object 
On Error Resume Next  
Set wrkSpace = DBEngine.Workspaces(0) 
Set db = wrkSpace.OpenDatabase("c:\tmp\Sourcedb.mdb") 
Set ctr = db.Containers("Forms") 
For Each FRM In ctr.Documents 
strForm = FRM.Name
 DoCmd.TransferDatabase acImport, "Microsoft Access", "c:\tmp\Sourcedb.mdb", acForm, strForm, strForm, False 
Next  
End Function 

Exporting All Forms

The ExportForms() Function Exports all the Forms into an external Microsoft Access database.

Public Function ExportForms() 
'---------------------------------------------------------------- 
'Function to Export Microsoft Access Forms into another Database 
'Author : a.p.r. pillai 
'Date : 02/12/2006 
'---------------------------------------------------------------- 
Dim cdb As Database 
Dim ctr As Container, doc, strFile As String  
'if conflict with existing object name then ignore 
'and import next object 
On Error Resume Next  
'Export all Forms from the current database into  
'the Target database 
Set cdb = CurrentDb 
Set ctr = cdb.Containers("Forms")  
For Each doc In ctr.Documents 
strFile = doc.Name 
  DoCmd.TransferDatabase acExport, "Microsoft Access", "c:\tmp\Targetdb.mdb", acForm, strFile, strFile, False 
Next  
End Function 

With little modifications to these Codes, they can be used for transferring objects between two external databases.

Next >> Create Excel File from Access.

Share:

SHARE PREVIOUS VERSION DATABASE

Share a previous-version secured database across several versions of Microsoft Access.

With one exception, the issues involved when sharing a secured database across more than one version of Microsoft Access are the same as the issues for sharing an unsecured database across more than one version.

The one exception concerns how to handle the workgroup information file that is used with the secured database. You have two choices:

  1. Tell users who will be upgrading to Microsoft Access 2000 to join the appropriate workgroup information file with the oldest version of Microsoft Access that will be sharing the secured database.

    Microsoft Access 2000 can use workgroup information files that have been created with previous versions, but previous versions can only use workgroup information files that have been created with Microsoft Access 2000 or a previous version.

    Important: If users will be sharing a secured database from Microsoft Access 95 or 97, you should compact the current workgroup information file with Microsoft Access 2000 before using it. Compacting the file by using Microsoft Access 2000 does not change the file format, so the file can continue to be used by any Microsoft Access 95 or 97 users who are not upgrading.

  2. If the shared database is Microsoft Access version 2.0, convert the workgroup information file that will be used with the secured database and then tell only users who are upgrading to Microsoft Access 2000 to join the converted workgroup information file. All users who are not upgrading from version 2.0, must continue to use the workgroup information file produced with that version.
Share:

CONVERT OLDVERSION WORKGROUP FILE

Convert a workgroup information file from a previous version of Microsoft Access.

To take advantage of security and performance improvements, you should re-create the Workgroup Information File as described below:

  1. Create a new workgroup information file, making sure to enter the exact, case-sensitive name, company name, and workgroup ID that was used to create the original file. Failure to re-enter the exact entries that were used to create the original file will create an invalid Admins group.
  2. Re-create any group accounts, making sure to enter the exact, case-sensitive user name and PID for each user.
  3. Tell other Microsoft Access 2000 users in the workgroup to use the workgroup Administrator to join the new workgroup information file.

    Click Next to see how to Share a previous-version secured database across several versions of Microsoft Access.

    Goto Main

Share:

CONVERT MSACCESS OLD VERSIONS

Convert a Microsoft Access 95 or 97 secured database.

When upgrading from Microsoft Access 95 or 97, you need to convert your secured database, but you don't need to convert the Workgroup Information File to use it with Microsoft Access 2000. However, you should compact the Workgroup Information File before using it.

  1. Convert the secured database.
  2. Compact the secured database.
  3. Exit Microsoft Access.
  4. Before compacting the Workgroup Information File (typically named System.mdw) that was used with the secured database, temporarily join another Workgroup Information File.
  5. Start Microsoft Access without opening a database.
  6. Compact the Workgroup Information File that was used with the secured database.
  7. Tell users to join the compacted workgroup information file before opening the secured database.
Share:

RUN PERMISSIONS PROPERTY

RunPermissions Property

You can use the RunPermissions property in a multi-user environment with a secure workgroup to override the existing user permissions. This allows you to view a query or run an append, delete, make-table or update query that you will be able to run. For example, as a user, you have read-only permission for queries, while the owner of the queries has read/write permissions. If the owner sets the RunPermissions property to specify the owner's permissions, you can run an append query to add records to a table.

Setting:

The RunPermissions property uses the following settings:

  1. Setting Description
  2. Owner's: All users are allowed the owner's permissions to view or run the query.
  3. User's (Default): Users have only their own permissions to view or run the query.

You can set this property by using the query's property sheet.

You can also set the RunPermissions property in SQL view of the Query window by using the WITH OWNERACCESS OPTION declaration in the SQL statement.

Share:

DEFAULT PERMISSIONS FOR NEW QUERY

Change default permissions for all new queries.

You can change the default permissions that allow others to view data returned from queries or in the case of action queries, to run the queries, even if they are otherwise restricted from viewing the underlying table or query.

Changing the default permissions affects only new queries.

  1. On the Tools menu, click Options
  2. Click the Table/Queries tab.
  3. Click the Run Permissions option you want to use.

If you select Owner's:, all users have the query owner's permission to view or run the query.

Only the query owner can save changes to the query.

Only the query owner can change the ownership of the query.

If you select User's, the permissions that are defined for that classification of users are in effect instead and any user with Administer permissions for the query can save changes to the query, or change its ownership.

Click Next to see how to set Run Permissions Property of Queries.

Goto Main

Share:

PERMIT TO VIEW RUN QUERY

Permit others to view or run my query but not change data or query design.

In a secure workgroup, you can assign others permission to view the data your query returns, or in the case of an action query, to run the query, even if they are otherwise restricted from viewing the query's underlying table or query.

  1. Open the query in Design view. Select the query by clicking anywhere in query Design view outside the design grid and the field lists.
  2. Click Properties on the toolbar to display the query's property sheet.
  3. Set the Run Permissions property to Owner's.

The following are true for this setting:

  • All users have the query owner's permission to view or run the query.
  • Only the query owner can save changes to the query.
  • Only the query owner can change the ownership of the query.

Note: You can also set default permissions for all new queries. Click Options on Tools menu. Click the Tables/Queries tab, and then click the Run Permissions option you want to use.

Share:

TRANSFER OWNERSHIP OF DATABASE

Transfer ownership of an entire database to another administrator

Start Microsoft Access by using a secure workgroup that contains the user account that you want to own the database and its objects.

  1. Log on by using that account.
  2. Create a new blank database.
  3. Import all of the objects from the database that has the ownership you want to change into the new database.

Note: To import a database, you must have Open/Run permission for the database and Read Design permission for its objects. To import Tables you must also have Read Data permission. If you have permissions for some tables, queries, forms, reports and macros but not others, Microsoft Access imports only those objects for which you have permissions.

Share:

VIEW OR TRANSFER OWNERSHIP

View or transfer ownership of individual Tables, Queries, Forms, Reports and Macros in a secure database.

If you have Administer permission for a Table, Query, Form, Report or Macro, you can change the ownership of the object to another user or group.

  1. Open the database.
  2. On the Tools menu, click Security, and then click User and Group Permissions.
  3. On the Change Owner tab, Microsoft Access displays a list of the tables, queries, forms, reports and macros that are currently displayed in the Database window and the current owner of those objects.
  4. Click an object type in the Object Type box, or use the existing object type.
  5. From the Object list, click one or more objects with ownership that you want to change. To select more than one object, either hold down CTRL and click the objects, or drag through the ones you want to select.
  6. In the New Owner box, click the user or group account that you want to be the new owner of the object or objects.
  7. Click the Change Owner button.

Note: If you change ownership of a table, query, form, report or macro to a group account, all users who belong to the group automatically receive the permissions associated with ownership of the object.

Share:

ASSIGN DEFAULT PERMISSIONS

Assign default permissions for new Tables, Queries, Forms, Reports and Macros.

  1. Open the database that contains the Tables, Queries, Forms, Reports and Macros.
  2. On the Tools menu, click Security and then click User and Group Permissions.
  3. On the Permissions tab, click Users or Groups, and then click the user or group that has the permissions you want to assign in the User/Group Name box.
  4. Click the type of object in the Object Type box and click in the Object Name list.

    The selection varies depending on the type of object you've selected.

  5. Select the default permissions that you want to assign for that object type and then click Apply.
  6. Repeat steps 4 and 5 to assign default permissions for additional object types for the current user or group.
  7. Repeat steps 3 through 5 for any additional users or groups, and then click OK when you have finished.


Notes: Default permissions can be assigned only by an administrator account (a member of the Admins group in the workgroup in which the database that contains the object was created) or by the owner of the database. Some permissions automatically imply the selection of others. For example, the Modify Data permission for a table automatically implies the Read Data and Read Design permissions because you need these to modify the data in a table. Modify Design and Read Data imply Read Design. For macros, Read Design implies Open/Run.

Organizing user accounts into groups makes it easier to manage security. For example, rather than assigning permissions to each user for each object in your database, you can assign permissions to a few groups, and then add users to the appropriate group. When users log on to Microsoft Access, they inherit the permissions on any group to which they belong.

Click Next to see how to View or transfer ownership of objects.

Share:

ASSIGN OR REMOVE PERMISSIONS

Assign or remove permissions for a database and existing Tables, Queries, Forms, Reports and Macros

Open the database that contains the Tables, Queries, Forms, Reports and Macros that you want to secure. The workgroup information file in use when you log on must contain the user or group accounts that you want to assign permissions for at this time; however, you can assign permissions to groups and add users to those groups later.

  1. On the Tools menu, point on Security and then click User and Group Permissions.
  2. On the Permissions tab, click Users or Groups and then click the user or group that has the permissions you want to assign in the User/Group Name box.
  3. Click the type of object in the Object Type box, and then click the name of the object to assign permissions for in the Object Name box.
  4. Tip: You can select multiple objects in the Object Name box by dragging through the objects you want to select, or by holding down CTRL and clicking the objects you want.
  5. Under Permissions, select the permissions you want to assign, or clear the permissions you want to remove for the group or user, and then click Apply. Repeat steps 3 and 4 to assign or remove permissions for additional objects for the current user or group.
  6. Repeat steps 2 to 4 for any additional users or groups, and then click OK when you have finished.

Notes: Some permissions automatically imply the selection of others. For example, the Modify Data permission for a table automatically implies the Read Data and Read Design permissions because you need these to modify the data in a table. Modify Design and Read Data imply Read Design. For macros, Read Design implies Open/Run.

When you edit an object and save it, it retains its assigned permissions. However, if a object is saved with a new name by using the Save As command on the File menu, or by cutting and pasting, importing, or exporting the object, the associated permissions are lost and you have to reassign them. This is because you are creating a new object that is assigned the default permissions defined for that object type.

Hidden objects aren't displayed in the Object Name box unless you select Hidden objects on the View tab of the Options dialog box (Tools menu).

Click Next to view how to Assign default permission for new tables, queries, forms, reports and macros.

Share:

CLEAR SECURITY PASSWORD

Clear a security account password

To complete this procedure, you must be logged on as a member of the Admins group.

Start Microsoft Access by using the workgroup information file in which the user account ( and its password) is stored.

You can find out which workgroup information file is current or change workgroups by using the Workgroup Administrator.

  1. Open a database.
  2. On the Tools menu, point to Security, and then click User and Group Accounts.
  3. On the Users tab, enter the user account name in the Name box.
  4. Click Clear Password.
  5. Repeat steps 3 & 4 to clear any additional passwords, and then click OK when you have finished.

Create or Change Security Password

Click Next to see how to Assign or remove permissions for Objects.

Goto Main

Share:

CREATE OR CHANGE SECURITY PASSWORD

Create or change a security account password

A security account password is created to make sure that no other user can log on using that user name. By default, Microsoft Access assigns a blank password to the Admin user account, and to any new user accounts you create in your workgroup.

Start Microsoft Access by using the workgroup the user account is stored in, and log on using the name of the account for which you want to create or change the password.

You can find out which workgroup is current or change workgroups by using the Workgroup Administrator.

  1. Open a database
  2. On the Tools menu, point to Security, and then click User and Group Accounts.
  3. On the Change Logon Password tab, leave the Old Password box blank if a password hasn't been defined previously for this account. Otherwise, type the current password in the Old Password box.
  4. Type the new password in the New Password box.
  5. A password can range from 1 to 20 characters, and can include any characters except ASCII character 0 (Null). Passwords are case-sensitive.
  6. Retype the password in the Verify box, and then click OK.

Caution: You can't recover your password if you forget it, so be sure to store it in a safe place. If you forget your password, a user logged on with an administrator account (a member of the Admins group in the workgroup in which the account and password were created) must clear the password before you can log on.

Click Next to see how to Clear a security account password.

Goto Main

Share:

DELETE MSACCESS SECURITY GROUP ACCOUNT

Delete a security group account

To complete this procedure, you must be logged on as a member of the Admins group.

Note: The Admins and Users group accounts can't be deleted.

  1. Start Microsoft Access by using the workgroup that contains the account you want to delete. You can find out which workgroup is current or change workgroups by using the Workgroup Administrator.
  2. Open a database.
  3. On the Tools menu, point to Security, and then click User And Group Accounts.
  4. On the Groups tab, enter the group you want to delete in the Name box, and then click Delete.
  5. Click Yes to delete the group account.

Repeat steps 4 and 5 if you want to delete additional group accounts.

Click Next to see how to Create or change a security account password.

Delete MS-Access Security User Account.

Goto Main

Share:

DELETE MSACCESS SECURITY USER ACCOUNT

Delete a Microsoft Access security user account

To complete this procedure, you must be logged on as a member of the Admins group.

Note: The Admin user account can't be deleted.

  1. Open a database.
  2. On the Tools menu, point to Security, and then click User And Group Accounts.
  3. On the Users tab, enter a user in the Name box, and then click Delete.
  4. Click Yes to delete the user account.

Repeat steps 3 and 4, if you want to delete additional user accounts, and then click OK when you have finished.

Click Next to see how to Delete a Security Group Account.

Goto Main

Share:

REMOVE USERS FROM SECURITY GROUPS

Remove Users from Microsoft Access Security Groups

To complete this procedure, you must be logged on as a member of the Admins group.

Notes : You can't remove users from the default Users group. Microsoft Access automatically adds all users to the Users group. To remove any user account from the Users group, you must delete the account.

There must be at least one user in the predefined Admins group at all times.

  1. Start Microsoft Access by using the workgroup containing the user and group accounts.
  2. You can find out which workgroup is current or change workgroups by using the Workgroup Administrator.
  3. Open a database.
  4. On the Tools menu, point to Security, and then click User And Group Accounts.
  5. On the Users tab, enter the user you want to remove in the Name box.
  6. In the Member Of box, click the group you want to remove the user from, and then click Remove.
  7. Repeat step 5 to remove this user from any other groups. Repeat steps 4 and 5 to remove other users from groups.
Share:

ADD USERS TO SECURITY GROUPS

Add users to security groups

To complete this procedure, you must be logged on as a member of the Admins group.

  1. Open a database.
  2. On the Tools menu, point to Security and then click on User and Group Accounts.
  3. On the Users tab, enter in the Name box the user name you want to add to a group.
  4. In the Available Groups box, click the group you want to add the user to, and then click Add. The selected group is displayed in the Member of list.

Repeat step 4 if you want to add this user to any other groups.

Repeat steps 3 and 4 to add other users to groups.

Share:

MSACCESS SECURITY GROUP ACCOUNT

Create a security group account

As part of securing a database, you can create group accounts in your Microsoft access workgroup that you use to assign a common set of permissions to multiple users.

To complete this procedure, you must be logged on as a member of the Admins group. Start Microsoft Access by using the workgroup in which you want to use the account.

Important : The accounts you create for users must be stored in the workgroup information file that those users will use. If you are using a different workgroup to create the database, change your workgroup before creating the accounts. You can change workgroups by using the Workgroup administrator.

  1. Open a database.
  2. On the Tools menu, point to Security, and then click User and Group Accounts.
  3. On the Groups tab, click New.
  4. In the New User/Group dialog box, type the name of the new account and a personal ID (PID).

    Group names can range from 1 to 20 characters, and can include alphabetic characters, accented characters, numbers, spaces and symbols, with the following exceptions:

    • The characters ' \ [ ] " | <> + = ; , . ? *
    • Leading spaces
    • Control characters (ASCII 10 through ASCII 31)

    Caution: Be sure to write down the exact account name and PID, including whether letters are uppercase or lowercase and keep them in a secure place. If you have to re-create an account that has been deleted or created in a different workgroup, you must supply the same name and PID entries. If you forget or lose these entries, you can't recover them.

  5. Click OK to create the new group account.

Note: A user account name cannot be same as an existing group account name.

To create more Group Accounts repeat steps 3 to 5 above.

Click Next to see how to add users to security groups.

Go to Main

Share:

CREATE MSACCESS USER ACCOUNT


Create a security user account

  1. Start Microsoft Access and Open a Database. On the Tools menu point to Security, and then click User and Group Accounts.
  2. Click New button on the Users tab of User and Group Accounts dialog box and enter a User-Name and a unique Personal ID (PID) in the New User/Group dialog box and then click OK.

    User name can range from 1 to 20 characters, and can include alphabetic characters, accented characters, numbers, spaces and symbols, with the following exceptions:

    • The characters "\ [ ] : | < > + = ; , . ? *
    • Leading spaces
    • Control characters (ASCII 10 through ASCII 31)

    Caution: Be sure to write down the exact account name and PID, including whether letters are uppercase or lowercase, and keep them in a secure place.

    If you ever have to re-create an account that has been deleted or created in a different workgroup, you must supply the same name and PID entries.

    If you forget or lose these entries, you can't recover them.

    Notes: The PID entered is not a password.

    Microsoft Access uses the PID and the user name as seeds for an encryption algorithm to generate a secure identifier for the user account. The new name will appear in the user name box.

  3. Click on the Admins group name in "Available Group" List and then click on Add>> button to join yourself to the Admins Group.

    Notes: The procedure is same for creating user account for others, but you need to join them into Group Accounts that you create to join users to different workgroup, like Data Entry Group, Supervisors Group, Managers Group or whatever group to share your database and each group with specific access rights.

  4. Now that you have created your own Administrator account, exit Microsoft Access then start it again.
  5. This time, log on with your new Administrator account.

    You have not yet set a password for your new Administrator account so leave password box empty on the log on dialog box.

  6. Select Tools menu, point to Security, select User and Group Account. Select change Log on password tab. Type a new password in the new password box.
  7. Verify the password. Leave the old password box empty.

We have removed the default Admin user from the Admins Group as part of the safety measure. It is equally important that you remove all permissions of all Objects for Users Group by selecting the object type (Database, Tables, Queries, Forms, Reports, Macros) and de-selecting the permissions check-boxes after highlighting the objects in the Object Name List. This step is very important because all users are the members of the Users Group (Users Group itself cannot be removed like we removed Admins Group for Admin user account) even if we remove the permissions of all objects for that particular user account he will inherit the Group level permissions from the Users Group and user level or other group level permission setting will have no effect.

Create Group Accounts and assign permissions for Objects at User Group Level and Add users to the Group (Like Data Entry Group, Supervisor Group, Manager Group etc.) and not necessary to assign permissions at user level every time you add a new user, if already assigned them at the Group Level. Only you need to add the user to specific Group or Groups.

  • Set Open/Run only permissions to Forms, Reports, Macros for User Groups.
  • You may assign Ownership for those Tables that get over-written while running Make-Table Queries, as part of the data processing tasks, they can safely over-written without access-right problems.

Notes:

The workgroup information file contains only the user name, Workgroup Names, Personal IDs and passwords..

The permissions setting information is stored along with the database.

When you create a new database see that you remove all permissions of the Users Group Account before assigning permissions for your Users or User Groups.

Click Next to see how to create a security User Group Account.

Goto Main

Share:

MICROSOFT ACCESS SECURITY

Securing Your Database

Microsoft Access allows you to implement security on your database objects as you need it. By default, security is completely invisible to both the designers and the users of an Access database. As per your requirements, you can secure individual objects, so that most users can't modify a particular object. If you are extremely concerned about security, you can use Access to remove all but a few ways to retrieve data from your tables. In networked applications, a well-designed security system can help make the application more maintainable by eliminating many sources of potential disaster.


Security Concepts

To understand Access security, you'll need to grasp four basic security concepts: users and groups have permissions on objects.

  • An Access user represents a single person who uses an Access application. Users are distinguished by their user name, password, and a unique secret identifier called the Personal Identifier (PID). To use a secured Access application, a user has to type in her user name and password to be able to get to any objects.
  • An Access group is a collection of users. You can use groups to represent parts of your organization, such as Development and Accounting, or simply security levels, such as High and Low. Often you'll find that assigning users to groups, and permissions directly to those groups, makes a security system more maintainable.
  • An access permission is the right to perform a single operation on an object. For example, a user can be granted read data permission on a table, allowing that user to retrieve data from that table. Both users and groups can be assigned permissions.
  • An access security object is any one of the main database container objects (Table, Query, Form, Report, Macro or Module) or a database itself.

Because both users and groups can have permission, you may have to check several places to determine a user's actual permissions. The user's actual permissions are the least restrictive combination of the user's own permissions (called explicit permissions) and the permissions of all groups the user belongs to (called implicit permissions). So if Mary does not have permission to open the Accounting form, but she's a member of the Supervisors group that does have permission to open that form, she will be able to open the form.


Creating a Microsoft Access Workgroup Information File

When you install Microsoft Access, the Setup program automatically creates a Microsoft Access workgroup information file System.mdw that is identified by the name and organization information you specify. Because this information is often easy to determine, it's possible for unauthorized users to create another version of this workgroup information file and consequently assume the irrevocable permissions of an administrator account (a member of the Admins group) in the workgroup defined by that workgroup information file. To prevent this, create a new workgroup information file, and specify a workgroup ID (WID). Only someone who knows the WID will be able to create a copy of the workgroup information file.

Procedures explained on this Document assumes that you have Microsoft Access 2000 installed in your machine. Other versions of Microsoft Access also use the same procedures but the location of Workgroup Administrator (Wrkgadm.exe) and default workgroup information file (system.mdw) may differ.

  1. Exit Microsoft Access (Access2000 or earlier versions)
  2. To start the Workgroup Administrator, open the language folder (C:\Program Files\Microsoft Office\Office\1033 is for US English), then double-click Wrkgadm.exe. Workgroup Administrator image is given below:
  3. Select the Create Option to create a new Workgroup Information File.
  4. Select the Join... Option to join a Workgroup Information File you have created earlier.

Alternatively, you can use Microsoft Access Workgroup Administrator shortcut in the \Program Files\Microsoft Office\Office folder.


To run Workgroup Administrator in Microsoft Office 2003:

  1. Start Microsoft Access 2003
  2. Select Tools menu, point on Security, click Workgroup Administrator option.
  3. In the Workgroup Administrator dialog box, click Create.
  4. In the Workgroup Owner Information dialog box, type your name and organization, and then type any combination of up to 20 characters for the workgroup ID.

    Caution: Be sure to write down the exact name, organization, and workgroup ID , including whether letters are uppercase or lowercase (for all three entries) , and keep them in a secure place. If you have to re-create the workgroup information file (if your existing workgroup information file is corrupted or lost by accidentally deleting it) , you must supply exactly the same name, organization, and workgroup ID. If you forget or lose these entries, you can't recover them and might lose access to your databases.

  5. Type a new name for the new workgroup information file, and then click OK.

By default, the workgroup information file is saved in the language folder (C:\Program Files\Microsoft Office\Office\1033, for U.S. English). To save in a different location, type a new path or click Browse to specify the new path). The new workgroup information file is used the next time you start Microsoft Access. Any user and group accounts or passwords that you create are saved in the new workgroup information file.

To have others join the workgroup defined by your new Workgroup Information File, copy the file to a shared folder and then have each user run the Workgroup Administrator (wrkgadm.exe) as explained above on their own PC to join the common workgroup information file.

To Join a Microsoft Access Workgroup using Workgroup Administrator.

  1. Follow steps 1 & 2 as explained above, depending on the Access Version (Access2000 and earlier or Access2003).
  2. In the Workgroup Administrator dialog box, click Join.
  3. li>Type the path and name of the Workgroup Information File that defines the Microsoft Access workgroup you want to join and click OK, or click the Browse button to find the Workgroup Information File on disk, click Open then click OK to close the dialog control.

Next time you start Microsoft Access, it uses the User and Group Accounts and Passwords stored in the workgroup information file for the workgroup you have joined.


Log on to a Microsoft Access workgroup

Activate the Logon dialog box

Until you activate the logon procedure for a workgroup, Microsoft Access automatically logs on all users at startup using the predefined Admin user account and the log on dialog box is not displayed.

You need to set a password for the default Admin user account to activate the logon dialog box so that your users can enter their user name and password to open and work with your secured databases.

  1. Start Microsoft Access.
  2. On the Tools menu, point to Security, and then click User and Group Accounts.
  3. Click the Users tab, and make sure that the predefined Admin user account is highlighted in the Name box.
  4. Click the Change Logon Password tab click the New Password box, and type the new password. Don't type anything in the Old Password box.

    To maintain the security of your password, Microsoft Access displays asterisks (*) as you type. Passwords can be from 1 to 20 characters, and can include any characters except ASCII character 0 (null). Passwords are case-sensitive.

  5. Verify the password by typing it again in the Verify box, and then click OK.

The Logon dialog box is displayed the next time any member of the workgroup that you joined starts Microsoft Access and opens a database. If no user accounts are currently defined for that workgroup, the Admin user is the only valid account at this point.

Note: When you secure a database, you create User Accounts in a Microsoft Access workgroup, and then assign permissions for Databases, Tables, Queries, Forms, Reports and Macros to those Accounts and to any Group Accounts to which they belong. Users log on to Microsoft Access by typing a User-name and password in the Log on Dialog Box. When Users log on to Microsoft Access by using their Accounts, they have only the permissions associated with those accounts.

Keep the following points in mind while implementing MS-Access Security:

  1. The Members of the Admins Group have full Permissions to all objects of the Database and have full authority to give or take away permissions to other Users or User Groups.
  2. The Owner of the Database (the User who created the database) have full authority (like members of the Admins Group) to give permissions or to give ownership of objects to other Users or Groups.
  3. Create an Administrator account for yourself. Click show how.
  4. Remove the default user Admin from the Admins Group.

    Caution: Before going through the procedure in step 4 above you must create a new Administratoraccount(as a member of the Admins group) for yourself otherwise you will be shutting yourself out of your workgroup information file.

  5. Remove all permissions on all objects for the Users group.

By default all users are the members of the Users group. Even if you implement Security at User Account or and other Group Account level it will have no effect if the Users group carries full permissions and the Users will inherit the permissions from the Users group.

  1. Create a security user account
  2. Create a security group account
  3. Add users to security groups
  4. Remove users from security groups
  5. Delete a security user account
  6. Delete a security group account
  7. Create or change a security account password
  8. Clear a security account password
  9. Assign or remove permissions
  10. Assign default permissions for new tables, queries, forms, reports and macros.
  11. View or transfer ownership of Objects
  12. Transfer ownership of an entire database to another administrator
  13. Permit others to view or run my query but not change data or query design.
  14. Change default permissions for all new queries.
  15. RunPermissions Property
  16. Convert a Microsoft Access 95 or 97 secured database.
  17. Convert a workgroup information file from a previous version of Microsoft Access.
  18. Share a previous-version secured database across several versions of Microsoft Access
Share:

REMINDER TICKER FORM

Introduction


This is an image of the Main Switchboard Screen, where a Reminder Ticker is active and scrolls with a continuous stream of information.The Automotive Sales & Service Company enters into Vehicle Service Contracts with Corporate Customers for various periods and maintains the data in an MS-Access Database. Every month a few Vehicle Contracts are due for renewal and the Staff concerned have to contact those Customers and check whether they would like to renew their Maintenance Contract with the Company or not.

The Reminder Ticker displays the Customer Code, Vehicle Model, Chassis Number, Vehicle Description and Expiry Date (not yet moved into the visible area of the Ticker).

The Input Data for the Ticker is extracted from the Vehicle Maintenance Contract Table based on the Expiry Date falling within the current month, with the help of a Query. Customer Code, Vehicle Model Number, Chassis Number, Vehicle Description & Expiry Date Values of each contract record is concatenated into a Variant Variable (String Variable may limit the length of the String to 255 characters) and used for the Ticker with the help of Timer.



The VBA Code

The VB Code that does this trick is given below:

Option Compare Database 
Option Explicit 
'Global Declaration 
Dim strTxt  
Private Sub Form_Open(Cancel As Integer) 
Dim db As Database, rst As Recordset 
Dim rstcount As Integer, currMonth As Integer, marqMonth  
On Error GoTo Form_Open_Err  
currMonth = Month(Date)  
' Expiry_Marque is a parameter Table which holds  
' the Start-Date & End-Date of Current Month and uses to pick  
'the Contract Expiry Cases falls within this period.  

marqMonth = Month(DLookup("ExpDateTo", "Expiry_Marque"))  
If currMonth   marqMonth Then  
' when the month is changed the parameter table is  
' updated with changed period. 
' i.e. Start-Date and End-Date of the Current Month  
DoCmd.SetWarnings False 
DoCmd.OpenQuery "Expiry_Marque_Updt", acViewNormal 
DoCmd.SetWarnings True 
End If  
'checks whether any contract expiry cases are there 
'during the month.  

rstcount = dCount("*", "Expiry_MarqueQ")  
If Nz(rstcount, 0) = 0 Then
  strTxt = String(60, " ") & "*" NO CONTRACT EXPIRY CASES FOR "
  strTxt = strTxt & Format(Date, "mmmm yyyy") & " **"  
GoTo Form_Open_Exit
End If  
' builds the String strTxt with ticker data.  
Set db = CurrentDb 
Set rst = db.OpenRecordset("Expiry_MarqueQ", dbOpenDynaset)
  strTxt = String(60, " ") & "Expiry Cases:"
  Do While Not rst.EOF  
     With rst
 strTxt = strTxt & " ** {" & rst.AbsolutePosition + 1 & "}. CUST: ["
        strTxt = strTxt & ![CUST_COD] & "] MODEL :[" & ![MODL_COD]
        strTxt = strTxt & "]  CHAS :[" & ![CHASSIS] & "](" & ![DESC]   
        strTxt = strTxt & ") EXP.: " & ![EXP_DATE]
 End With
  rst.MoveNext
  Loop
  rst.Close  
'A Text Box on the Form is set with the Total Number 
'of Contracts getting expired.
  Me![mVehl] = rstcount & " Vehicles."  
' the Timer is invoked and the time to refresh  
' the control is set with quarter of a 
' second. This value may be modified.
Me.TimerInterval = 250 
Set rst = Nothing 
Set db = Nothing  
Form_Open_Exit: 
Exit Sub  
Form_Open_Err: MsgBox Err.Description, ,"Form_Open" 
Resume Form_Open_Exit 
End Sub   

Private Sub Form_Timer() 
Dim x  
On Error GoTo Form_Timer_Error  
x = Left(strTxt, 1)  
strTxt = Right(strTxt, Len(strTxt) - 1)  
strTxt = strTxt & x  
' Create a Label with the Name lblmarq  
' on your Form to scroll the values 
' The value 200 used in the Left Function may be 
' modified based on the length of the 
' Label. Format the Label with a fixed width font 
' like Courier New so that you can correctly determine 
' how many characters can be displayed on the legth 
' of the Label at one time and change the value accordingly.  

lblmarq.Caption = Left(strTxt, 200)  

Form_Timer_Exit: 
Exit Sub  
Form_Timer_Error: 
MsgBox Err.Description, , "Form_Timer_Error" 
Resume Form_Timer_Exit  
End Sub 


Ticker Active/Inactive States

The code starts running immediately after opening the form and runs continuously, till you close the form.

When the Form become inactive, after you open some other Form over it, the Ticker is deactivated. If  nobody is watching there is no point in keep running the Program. When the Form become active again the ticker starts running again. To add this capability to the reminder ticker copy and paste the following Code also into the Form Module:

Private Sub Form_Deactivate()
     Me.TimerInterval = 0
End Sub

Private Sub Form_Activate() 
Me.TimerInterval = 250 
End Sub


Download

You can download a demo sample database from the download link given below:



Download Demo Database


Share:

FILE BROWSER IN MSACCESS

Introduction


SEARCHING FOR OTHER FILES FROM MSACCESS

We can search for Microsoft Access database files on disk by selecting File - - > Open option from the main menu.  But, if we want to search, select and open some other file from disk then how do we do that?  If we are able to do that then what do we do with those File(s) in Access? Well, we can create a Hyperlink to the file and store it in a data field or use FileCopy() function to make a copy of it into a different location.



Demo Run Preview

Anyway let us get to work with the first part. But before that, a preview of the Run of our Project is shown below:


Designing a Demo Form

open a Database from your Computer or from the Network Drive.

Let us design a small Form with the Common Dialog Control, a Textbox and a Command Button with few lines of VBA Code for our project.  The design will look like the image given below. 

Tip: You can download a demo database from the bottom of this page.

The rectangular shaped object at the left side is the Common Dialog control inserted from the ActiveX control’s group. when the User clicks on the Browse. . . button the dialog box like the first image above opens up.

Now let us get to work.

  1. Open a new Form and Create a Text-Box Control, wide enough to hold the Path and File name selected from the disk.
    • Change the Caption of the child-label to File Path Name.
    • Select the Textbox control, display the property sheet (F4) and change the Name property value to lbldb.
  2. Create a Command Button as shown in the above Design, display its Property Sheet and change the following property values as shown below:
    • Name = cmdBrowse
    • Caption = Browse. . .
  3. Now it is time to bring in the real hero element of our design: the Microsoft Common Dialog Control, to do that follow the procedure given below:
    • Select ActiveX Control from Insert Menu
    • You will find a List of ActiveX Controls opens up, scroll down and select the Microsoft Common Dialog Control and Click OK. If you didn't met any trouble on the way out after clicking the OK button you will find a square shaped control sitting on your Form. If your MS-Office installation is not properly done it is likely that you may end with a message like 'this ActiveX DLL is not registered, re-install it' or something similar.

    Display the Property Sheet of the Common Dialog Control and change the Name property to cmDialog1. You can place it anywhere at your convenience, it will not be visible when you activate your Form.

  4. Click on the Command Button to select it.
  5. Display the Property Sheet (F4).
  6. Click on the On Click Event property and select [Event Procedure] from the drop-down control.
  7. Click on the build (. . .) button to open the VBA Module Window of the form.

    The VBA Code

  8. Copy the Following Visual Basic Code and paste it, over-writing the existing empty procedure lines, into the Module and save the Form.
Private Sub cmdBrowse_Click()
Dim VFile As String
On Error GoTo cmdBrowse_Click_Err
ChDrive ("C") 
ChDir ("C:\")
  cmDialog1.Filter = "All Files (*.*)|*.*| _ Text Files (*.txt)|*.txt|Excel WorkBooks (*.xls)|*.xls"  cmDialog1.FilterIndex = 1
  cmDialog1.Action = 1
  If cmDialog1.FileName =  "" Then
  VFile = cmDialog1.FileName 
  Me!lbldb = VFile
  End If  
  cmdBrowse_Click_Exit:
  Exit Sub
  cmdBrowse_Click_Err:
  MsgBox Err.Description, , "cmdBrowse_Click"
  Resume cmdBrowse_Click_Exit
End Sub 

Test Run

Open the Form in normal view and click on the Browse Button. The File Browsing Control, that we have seen on the top of this page, will open up. Select a file from any location you like and click Open. The selected file with its complete location address will be inserted into the Text Box control.



Download



Download Demo Database



Share:

MsgBox with Office Assistant

Introduction


Two Message Box images are shown above.  The  left side Message Box is the default style of MS-Access and the one at the right side, with the Office-Cat image below,  is created in Ms-Access with the use of Office Assistant in VBA Programs. The new programs uses the default Office Assistant’s features for Message Boxes. You can change the Animation character from the Tools Menu.  By adding few VBA Functions in your MS-Access Project enables you to make use of this feature, wherever you need them in your programs.

Some frequently used message box functions are created as user-defined functions, with the use of Office Assistant, separately for ease of use in your programs,  limiting the Number of Parameters required for these Functions are only two.  The first Parameter is for Message Text and the second one for Title. The 2nd Parameter is Optional and it can be omitted, if it is not important. Button Type and Icon Type (the question mark shown on left top corner on both message boxes) changes depending on the type of message box.  Default values for these features are already added to the Function. The following user-defined functions are available and their usage Syntax is as shown below:

MsgOK("Message Text","Title") - MessageBox with only OK Button

MsgYN("Message Text","Title") MessageBox with Yes & No Buttons. Returned Value is vbYes or vbNo

MsgOKCL("Message Text","Title") - MessageBox with OK, Cancel Buttons. Returned Value is vbOK or vbCancel

The function names shown above gives an indication as what type of Command Buttons will appear on the message box and which values are returned from User responses.


Essential Library Files

First of all, you must attach the Microsoft Office 9.0 Object Library files (or whatever version of Office you have) to your Project. This is required to make use of Office Assistant features in your programs.  You must add other essential Library Files (additional VBA functions which are not attached to Ms-Access by default) to your Project as well. Please refer my earlier Post Command-Button Animation for a list of Library Files and procedures explaining how to attach them to your Project. After attaching the library files copy the following Code into a Global Module and save them:


Public Function MsgOK(ByVal strmsg As String, Optional ByVal strHeading As String) As Integer
    On Error resume next
 MsgOK = MsgBalun(strmsg, strHeading, msoButtonSetOK, msoAnimationGestureUp, msoIconAlertInfo) 
End Function
Public Function MsgOKCL(ByVal strmsg As String, Optional ByVal strHeading As String) As Integer
  On Error Resume Next
  MsgOKCL = MsgBalun(strmsg, strHeading, msoButtonSetOkCancel, msoAnimationWritingNotingSomething, msoIconAlertQuery)
End Function 
Public Function MsgYN(ByVal strmsg As String, Optional ByVal strHeading As String) As Integer
on error resume next
    MsgYN = MsgBalun(strmsg, strHeading, msoButtonSetYesNo, msoAnimationWritingNotingSomething, msoIconAlertQuery)
End Function
Private Function MsgBalun(ByVal strText As String, ByVal strTitle As String, ByVal lngButtons As Long, ByVal intAnimation, ByVal intIcon) As Integer '------------------------------------------------------------ 
'Author : a.p.r. pillai 
'Date   : September 2006 
'Rights : All Rights Reserved by www.msaccesstips.com
'------------------------------------------------------------ 
Dim lngx As Long, intVal As Integer, Balu As Balloon 
On Error GoTo MsgBaloons_Err  
With Assistant   
If .On = False Then     
    .On = True
   '.FileName = "OFFCAT.acs"
     .Animation = msoAnimationGetAttentionMinor
     .AssistWithHelp = True
     .GuessHelp = True
     .FeatureTips = False
     .Visible = True
End If 
End With
  Set Balu = Assistant.NewBalloon
 With Balu
     .Animation = intAnimation
     .Icon = intIcon
    .Heading = strTitle
    .Text = strText
    .BalloonType = msoBalloonTypeButtons
    .Button = lngButtons
  Select Case Balu.Show
        Case msoBalloonButtonOK
            MsgBalun = vbOK
        Case msoBalloonButtonCancel
           MsgBalun = vbCancel
        Case msoBalloonButtonYes
           MsgBalun = vbYes
        Case msoBalloonButtonNo
           MsgBalun = vbNo
 End Select
 End With
  Assistant.Visible = False

MsgBaloons_Exit: 
Exit Function  

MsgBaloons_Err: 
MsgBox Err.Description, , "MsgBaloons" 
Resume MsgBaloons_Exit 
End Function 

You can use these Functions without bothering about selecting the Button-Type, IconType etc. that you normally need to give along with the Message Box Command like:

vbYesNo+vbDefaultButton2+vbQuestion

Usage Example:

If MsgYN("Select Yes to Proceed, No to Cancel.","cmdProcess") = vbYes then
    Docmd.runmacro "Process" 
End if 

OR

Second Parameter Title is omitted in the second example.

If MsgYN("Select Yes to Proceed, No to Cancel.") = vbYes then
    Docmd.runmacro "Process"
End if 


Testing the Code

You can type any of the above commands in the Debug Window and press Enter Key, like the sample given below, to test the commands before using them in your programs:

MsgOK "System is preparing to Shut Down","cmdExit_Click"

OR

MsgOK "System is preparing to Shut Down"

The MsgBalun() Function is not directly used in programs.

Implement the procedures in your Project and try them out.


Download


Download Demo Database


Share:

SHADOW3D HEADING STYLE

Introduction

This is the continuation of a series of different 3D-Heading-Styles introduced for designing Microsoft Access Form/Report Headings. This design is a variant of the 3D-Heading Style presented under the Title Create 3D Headings on Forms. Both the Styles has its own beauty and once they are created you may copy the same Controls and customize them with different Fore-color, Font & Font Styles (Bold, Italics etc.) and use it on Form or Report Headings.

I have several of this type of designs and if this is the first one you came across on this site then you must prepare your MS-Access Project by adding few Library Files and Main Programs of this series (if you have not already done) before you are able to run the Code for this Heading Style and others presented on this Site. Follow the steps given below:

  1. Link few Common Library Files (they are already there in your System, you only need to attach them) to your Project by following the steps described in my earlier post with the Title Command-Button Animation
  2. The VBA Programs

  3. Copy the following Main Program Codes given below into a new Global VBA Module in your Project and save it.

    If you have already copied them from earlier Posts then copy only the last Function: Shadow3D()

    Option Compare Database
     Option Explicit
     '-- Global declarations
     Private Const lngheight as long = 0.45 * 1440
     Private Const lngWidth as long = 4.5 * 1440
     Private Const intFontSize as integer = 26
     Private Const intTextAlign as integer = 0
     Private Const intBackStyle as integer = 0
     Private Const LngI as long = 0.0104 * 1440
     Private Const intX as long = 0.15 * 1440
     Private Const intY as long = 0.15 * 1440
     Dim MyFrm As Form 
    
    Public Function FormTxtLabels(Optional ByVal ControlType As Integer) As String 
    '---------------------------------------------------
    'Author : a.p.r. pillai 
    'Date : September 2006
    'ControlType = 0 for label
    'ControlTYPE = 1 for TextBox 
    '--------------------------------------------------- 
    Dim ctl As Control 
    'On Error GoTo FormTxtLabels_Err
    If ControlType > 0 Then ControlType = 1 
    
    Set MyFrm = CreateForm 
    
    If ControlType = 1 Then
       Set ctl = CreateControl(MyFrm.NAME, acTextBox, acDetail, , , (0.2 * 1440), (0.2 * 1440), lngWidth, lngheight) 
    
       With ctl
        .ControlSource = "=" & Chr(34) & "msaccesstips.com" & Chr(34)
       End With
    Else
       Set ctl = CreateControl(MyFrm.NAME, acLabel, _acDetail, , , (0.2 * 1440), (0.2 * 1440), lngWidth, lngheight)
    
      With ctl
        .Caption = "msaccesstips.com" 
      End With
    
    End If 
    
    FormTxtLabels = MyFrm.NAME 
    
    FormTxtLabels_Exit: 
    Exit Function
    
    Public Function Validate_Dup(ByRef MyFrm As Form, ByVal intNooflabels As Integer) As Integer 
    '---------------------------------------------------
    'Author : a.p.r. pillai 
    'Date : September 2006
    'ControlType = 0 for label
    'ControlTYPE = 1 for TextBox 
    '---------------------------------------------------
    Dim mysec As Section, lblcount As Integer 
    Dim myctrl As Control, newctrl As Control, j As Integer 
    Dim lngx As Long, lngY As Long, lngH As Long, lngW As Long 
    Dim strCap As String, ctrltype As Integer, intlbls As Integer 
    Dim ctrlName() As String, ctrlIndex() As Integer, i As Long
    Dim strFont As String, intFntSize As Integer, x As Integer 
    Dim intFntWeight As Integer  
    
    'On Error GoTo Validate_Dup_Err 
    
    Set mysec = MyFrm.Section(acDetail)
    intlbls = mysec.Controls.Count - 1 
    
    Set myctrl = mysec.Controls(0) 
    ctrltype = myctrl.ControlType 
    intNooflabels = intNooflabels - 1 
    If intlbls > 0 Then 
      ReDim ctrlName(intlbls) As String
      ReDim ctrlIndex(intlbls) As Integer 
    End If 
    If ctrltype = 109 And intlbls > 0 Then 
        For j = 0 To intlbls 
          Set myctrl = mysec.Controls(j) 
          ctrlIndex(j) = myctrl.ControlType 
          ctrlName(j) = myctrl.NAME 
        Next 
      For j = 0 To intlbls 
        If ctrlIndex(j) = 100 Then 
          DeleteControl MyFrm.NAME, ctrlName(j) 
        End If 
      Next 
      intlbls = mysec.Controls.Count - 1 
    End If 
    
    Set myctrl = mysec.Controls(0) 
    If intlbls < intNooflabels Then 
    With myctrl
      lngx = .Left
      lngY = .Top
      lngW = .Width
      lngH = 0.0208 * 1440 ' 0.0208 inches
      strFont = .FontName
      intFntSize = .FontSize
      intFntWeight = .FontWeight 
    End With 
    
    If ctrltype = 100 Then 
      strCap = myctrl.Caption 
    ElseIf ctrltype = 109 Then 
      strCap = myctrl.ControlSource 
    End If 
    If ctrltype = 109 And intlbls > 0 Then 
      For j = 0 To intlbls 
        Set myctrl = mysec.Controls(j) 
        ctrlIndex(j) = myctrl.ControlType 
        ctrlName(j) = myctrl.NAME 
      Next 
      For j = 0 To intlbls 
        If ctrlIndex(j) = 100 Then 
          DeleteControl MyFrm.NAME, ctrlName(j) 
        End If 
      Next 
    intlbls = mysec.Controls.Count - 1 
    Set myctrl = mysec.Controls(intlbls)
    With myctrl 
      lngx = .Left 
      lngY = .Top 
      lngW = .Width 
      lngH = .Height 
      ctrltype = .ControlType 
      strFont = .FontName 
      intFntSize = .FontSize 
      intFntWeight = .FontWeight 
    End With 
    End If 
    
    i = 0.0104 * 1440 + lngH ' 0.0104 inches 
    lngY = lngY + i 
    For j = intlbls + 1 To intNooflabels 
    
      Set newctrl = CreateControl(MyFrm.NAME, ctrltype, _acDetail, "","", lngx, lngY, lngW, lngH) 
    
      If ctrltype = 100 Then 
        newctrl.Caption = strCap 
        newctrl.FontName = strFont 
        newctrl.FontSize = intFntSize 
        newctrl.FontWeight = intFntWeight 
      Else 
        newctrl.ControlSource = strCap 
        newctrl.FontName = strFont 
        newctrl.FontSize = intFntSize 
        newctrl.FontWeight = intFntWeight 
      End If 
    lngY = lngY + i 
    Next 
    End If 
    
    If intlbls > intNooflabels Then 
      For j = intNooflabels + 1 To intlbls 
        Set myctrl = mysec.Controls(j) 
        ctrlIndex(j) = myctrl.ControlType 
        ctrlName(j) = myctrl.NAME 
      Next 
      For j = intNooflabels + 1 To intlbls 
        DeleteControl MyFrm.NAME, ctrlName(j) 
      Next 
      Validate_Dup = 0 
    End If 
    
    intlbls = mysec.Controls.Count - 1 
    Set myctrl = mysec.Controls(0) 
    ctrltype = myctrl.ControlType 
    If ctrltype = 109 Then 
      For j = 0 To intlbls 
        Set myctrl = mysec.Controls(j) 
        With myctrl 
         .Enabled = False 
         .Locked = True 
         .SpecialEffect = 0 
        End With 
      Next 
    End If 
    Validate_Dup = 0 
    
    Validate_Dup_Exit: 
    Exit Function 
    
    Validate_Dup_Err: 
    MsgBox Err.Description, ,"Validate_Dup" 
    Validate_Dup = 1 
    Resume Validate_Dup_Exit 
    End Function 
    
    Public Function MsgLabel() 
    '------------------------------------------------------------ 
    'Author : a.p.r. pillai 
    'Date : September 2006 
    '------------------------------------------------------------
    Dim mySection As Section, ctl As Control, xForm As Form 
    Dim l As Long, t As Long, w As Long, h As Long, F As Long 
    F = 1440 
    l = 0.5 * F: t = 1.2 * F: w = 3.6563 * F: h = 0.4896 * F 'values in inches 
    On Error Resume Next 
    Set mySection = MyFrm.Section(acDetail) 
    Set ctl = CreateControl(MyFrm.NAME, acLabel, _acDetail, , "", l, t, w, h) 
    ctl.Caption = "Click outside the Controls and Drag Over. "  & "Display the Property Sheet. " & "Type New Text for Caption/Control " & "Source Property area for Label/ Text Boxes. " & "Copy and Paste the Controls to " & "Target Form/Report Area." 
    End Function
    
    
  4. This Code implements the Main Functions of each Heading Styles presented so far including this one. Once you are ready with the above you may copy the Code for this Heading Style and try it out.
  5. Copy the Code below into the same Global Module where you have copied the Main Programs, or any Global Module you prefer and save it.
Public Function Shadow3D(ByVal intStyle As Integer, ByVal intForeColor As Integer, _
Optional ByVal Label0Text1 As Integer) As String  
'---------------------------------------------------------- 
'Author : a.p.r. pillai 
'Date   : September 2006
'Rights : All Rights Reserved by www.msaccesstips.com
'---------------------------------------------------------- 
Dim intlbls As Integer, intFSize As Integer  
Dim j As Integer, mySection As Section  
Dim lblName() As String, lngForecolor As Long, X As Integer  
Dim l As Long, t As Long   

On Error Resume Next
   Shade3D = FormTxtLabels(Label0Text1)
   Set mySection = MyFrm.Section(acDetail)
  intlbls = mySection.Controls.Count - 1
   On Error GoTo Shadow3D_Err
   X = Validate_Dup(MyFrm, 5) ' check type and duplicate
   If X = 1 Then
    Exit Function
  End If
  intlbls = mySection.Controls.Count - 1
   X = intStyle
  intStyle = IIf(X < 0, 0, IIf(X > 3, 3, intStyle))
  X = intForeColor
  intForeColor = IIf(X < 0, 0, IIf(X > 15, 15, intForeColor))
   ReDim lblName(0 To intlbls) As String
   For j = 0 To intlbls
   lblName(j) = mySection.Controls(j).NAME
  Next
   For j = 0 To intlbls
    With mySection.Controls(lblName(j))
      .Height = lngheight
      .Width = lngWidth
      .FontName = "Times New Roman"
      intFSize = .FontSize
      If intFSize < intFontSize Then
        .FontSize = intFontSize
      End If
      .FontUnderline = False
      .TextAlign = intTextAlign
      .BackStyle = intBackStyle
     Select Case j
       Case 0
        lngForecolor = 8421504
       Case 1 To intlbls - 2
        lngForecolor = 8421504
       Case intlbls - 1
        lngForecolor = 0 '12632256
       Case intlbls
        lngForecolor = QBColor(intForeColor)
      End Select
        .ForeColor = lngForecolor
   End With
  Next
  l = intX: t = intY
   With mySection.Controls(lblName(1))
    .Left = l
    .Top = t
  End With
  For j = 0 To intlbls
  Select Case intStyle
         Case 0
            l = l + LngI
            t = t + LngI
         Case 1
            l = l + LngI
            t = t - LngI
         Case 2
          l = l - LngI
          t = t + LngI
         Case 3
            l = l - LngI
            t = t - LngI
  End Select
    With mySection.Controls(lblName(j))
       .Left = l
       .Top = t
    End With
  Next
  MsgLabel

Shadow3D_Exit:
Exit Function
Shadow3D_Err:
Msgbox Err.Description,, "Shadow3D"
Resume Shadow3D_Exit  
End Function  

Create Shadow3D Heading Style

  1. Press Alt+F11 to Display the Visual Basic Editing Screen (you can toggle Database and VBA Window alternatively by pressing Alt+F11 Keyboard shortcut).  Press Ctrl+G (or View --> Immediate Window) to display the Debug Window.
  2. Type the following in the Debug Window and press Enter Key:
  3. Shadow3D 1, 4,0

You will see the Screen flashes briefly, as if it is refreshed. Minimize the Visual Basic Window and you will find the above Heading Style created on a new Form. Besides the 3D heading on the form you will find some help text with tips to customize the 3D heading with your own heading text, Font or Font Style you like.

Let us examine the Command Line Values.

Shadow3D is the Function Name.

The first Parameter Value 1 controls the Shadow position of the Heading Text.  First parameter value Range is 0 to 3

  • 0 - Shadow is tilted to the left top corner of the heading text.
  • 1 - bottom left corner
  • 2 - Right top corner
  • 3 - Right bottom corner

Second parameter value 4 (Red Color) is the topmost label's text color. The range of color values can be 0 to 15. The QBColor codes are given on the Page with the Title: Border2D Heading Text.

Third parameter value 0 creates 3D Text on Label controls. This is optional and can be omitted if you need only Label based 3D Text. When third parameter is omitted, do not use a coma after the second parameter. When this value is 1 it draws a Text Box based Design.  An expression, like ="Sample Text", with default text is inserted into the Control Source Property of all the Text Box layers created for the heading.

You can change the constant value in the expression with your own text, in the control source property, or change it to show values from the underlying field of Table/Query attached to the Form. Or you can write a Dlookup() Function to pick the Value from a different Table/Query.

Example: =Dlookup("CountryName","CountryTable","CountryCode = 'USA'")

The above example will show United States of America in 3D Style from the CountryTable based on the Values in CountryCode & CountryName Fields. If The criteria parameter of the Function needs the reference of a control on the Form then modify it to use the control name as criteria, as shown below:

=Dlookup("CountryName","CountryTable","CountryCode = '" & Me![CCode] & "'")

[CCode] is the Field Name where the country codes are stored in the Table/Query attached to the Form and the current value on the form is used for finding the country name. Note the single quote immediately after the equal sign followed by a double-quote and the closing single quote within double-quotes, before the closing parenthesis, indicates that [CCode] field value is character type data.

Tip: Search in MS-Access Help for more details on Dlookup() Function.

Download

Download Demo Database
Share:

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

http://clicky.com/101199826
Blog Directory Visit blogadda.com to discover Indian blogs

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email

Labels

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

Featured Post

WithEvents and All Form Control Types

Introduction So far we have worked extensively with only one type of Control: the TextBoxes on Form, enabled with Events to Raise and captu...

Labels

Blog Archive

Recent Posts