Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

MS-Access and Reference Library


We are using several Object Libraries besides the default References Library during the course of developing an Access Database. Microsoft Data Access Objects (DAO), Microsoft ActiveX Data Objects (ADODB), Visual Basic for Application (VBA) and so on. These are essential to create Data Processing Routines in VBA.

I have briefly touched this subject in my first Article in this site: Command Button Animation and advised to link these files to your Database manually before attempting to run the Program given in there. A List of essential Library Files and the procedures to attach them to the Database manually also explained there.

Some of these Library References like: Microsoft Access 9.0 Object Library, Visual Basic for Applications, OLE Automation are attached automatically when a new database is created and others are added manually. We add new reference Libraries to use interesting features extended by them, which are not available in Microsoft Access.

We have used Microsoft Office Library (Office) to create customized Message Boxes, Option Baloons and Check-Boxes etc. with Office Assistant to capture user responses in an interesting way. We have created a Wizard to select Office Assistant of your choice in Access, without using the Option provided by Office Assistant.

Databases and Tables can be accessed in VBA only when we add Microsoft Data Access Objects (DAO) or Microsoft ActiveX Data Objects (ADODB) Library Files to Access Database.

If you have written some common Functions of your own then you can save them in a separate database on Server and attach it as a Library Database so that you don't have to copy and paste those Functions in other Databases.

You can use Object Browser of VBA (View - - > Object Browser) to browse the Properties and Methods extended by a particular Library.

Library Reference and Physical Files

Most of the time we use a fixed set of Library References in our Database and these are attached manually (Tools - - >References) one by one after opening the VBA Editing Window. Even though this is only a one-time exercise we can automate this and it is useful to quickly restore the links if they are lost as well.

First let us look at the few steps that we need to take to accomplish this.

A.     Prepare a list of Library References and save it in a Text File, on Server if you are developing databases to use on a Network.

B.     Write a VBA routine to read this list from the Text File and attach them to the Project.

A.     To prepare the required Object Library References List we must know what are these files and where on the Disk they can be located. Assume that we need to know what is the physical file name and location representing the Reference Library Description Microsoft DAO 3.6 Object Library in the Available References list.

To find out this manually, open VBA Module Window (View - - > Code or Alt+F11), select Tools- - >References and find the item with the description Microsoft DAO 3.6 Object Library among the list of other Microsoft Reference Library Files, see the image given below.

When you select the file; its Description, the physical File Pathname and Language are displayed. You can write down the Pathname to prepare the list, if you want to. But, we will do half of the task differently. We can attach the required files manually and prepare the list with a small VBA Routine.

Display List of Library Files with VBA

Following is a list of References that I use regularly to start with my Projects and we will use them as an example:

The first three items will be automatically selected by MS-Access when you create a new Database. Others must be added manually.

  1. Attach the above list of Library References manually.
  2. Copy and Paste the following lines of Code into a Standard Module of your Project and save it.
    Public Sub ReferenceList()
    Dim Ref As Reference
    For Each Ref In Application.References
       Debug.Print Ref.FullPath
    End Sub
  3. Press Ctrl+G to display the Debug Window (Immediate Window), if it is not already visible.
  4. Click in the middle of the Code and press F5 to Run it and print the following Path Names of the selected Reference Library Files in the Debug Window.
  1. C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL
  2. C:\Program Files\Microsoft Office\OFFICE11\MSACC.OLB
  3. C:\WINDOWS\system32\stdole2.tlb
  4. C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll
  5. C:\Program Files\Common Files\system\ado\msado15.dll
  6. C:\Program Files\Common Files\Microsoft Shared\OFFICE11\MSO.DLL
  7. C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB
  8. D:\MDBS\aprRefLib.mde

Project Names of Library Files.

The internal names (Project Names) of the above Library Files are different and it is important to know about it because if we need to remove any of this library items from the Current Project then you must use the Project Name in programs rather than using the above file names.

When you create Ms-Access database with the name abcd.mdb; by default the same name abcd will be inserted into the Project Name control in the database. You can check the Project Name of your database selecting Tools Menu in the VBA Editing Window. You will find a Menu Option like abcd Properties. You can open this option and set a different name in the Project Name control, if needed.

Note: But, it is important that you should not use any names of your Standard Modules for the Project Name.

You can read this Property Value in programs or in Debug Window using the statement x = Application.GetOption("Project Name").

Or, modify the Project Name with the statement like Application.SetOption "Project Name", "Myabcd"

The List of Library Files given above; also have unique Project Names and these are the Internal Object Library Reference names that will appear in our database, when linked. You can check their names by opening the Object Browser (View - -> Object Browser or F2) and clicking on the drop down control. Among the list you can see that your own current database Project Name abcd is also appearing in the list.

Let us inspect the Project Names of the first three files in the list given above.

  • VBA
  • Access
  • Stdole

    When you create a new database MS-Access attaches the above three references by default and they are important too. So we will exclude them from our add/remove operations.

    These names are unique irrespective of which Version of Access you are using and the same goes for the other Library References as well.

    If you attempt to attach a different version of the Reference Library File with the same Project Name then a conflict takes place. We have to check for the following two more References to avoid removing them before attaching items from our list.

  • abcd (current database project name)
  • aprRefLib (you can read this as your own Function Library Project Name and I will come to that later in this Article.)

Leaving aside the above five items we are left with the following four Reference Libraries selected out of the eight items listed above to attach to our new Projects automatically:

C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll
C:\Program Files\Common Files\Microsoft Shared\OFFICE11\MSO.DLL
C:\Program Files\Common Files\system\ado\msado15.dll
C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB
  1. Open NotePad.Exe, Copy and Paste the above File Names and save it on your Server common location where all your MS-Access Projects are installed.
  2. Let us call the Server PathName (the Location and File Name) of the target text file as


B.     The first stage of our preparation is over. The next thing what we need is a VBA Program that can read the above Text File contents and link the Reference Library files shown in the second image above automatically to your new Project.

I will give the VBA Routines below, but you have two choices to decide where to place the code so that it is easier to use in all of your Projects without duplicating the code. The second choice is to copy the Code into your new Projects and run it from there, which I don't advise you to do. Instead take the first option and do little ground work now by following the few steps given below. This will make your future development processes easier without duplicating common Programs in all your other Projects.

If you have not started to build a Reference Library Database of your own so far then this is the time to start doing it and you will find how useful it is. It is not that hard to do it either.

Attaching Missing Library Files with VBA

  1. Create a new Database with the name MyLib.mdb and save it to your Server Common Location. Let us take this location address same as the one we have saved our Text File with the list.
  2. Open the VBA Editing Window (Alt+F11).
  3. Create a Standard Module (Insert - ->Module) to create an empty Code Module with the name Module1. You may change the name of the Module after displaying its Property Sheet (View - - >Properties Window).
  4. Copy and Paste the following Code into the Module; save and close the VBA Editing Window.
Public Function AddReferences()
Dim j As Integer, i As Integer, msg As String
Dim Ref As Reference, RefObj As Object
Dim RefPath As String, LibName As String
Dim LibPath As String, libAttached() As String
Dim refcount As Integer, chk_flag As Boolean
Dim lib_Attached As String, validate As Boolean

Const LibraryList As String = "\\hosfs03\InhouseSys\CommonLib\RefLib.txt"

On Error GoTo AddReferences_Err

validate = Ref_Retain_Remove()
If validate = False Then
   msg = "Errors Encountered in Validation check, Program aborted. "
   Exit Function
End If

Set RefObj = Application.References
refcount = Application.References.Count
ReDim libAttached(1 To refcount) As String
i = 0
'Prepare list of exiting attached Library Files
For Each Ref In RefObj
    i = i + 1
    libAttached(i) = Ref.FullPath
Next'Open text file with List of required Reference Library files
Open LibraryList For Input As #1
msg = ""
Do While Not EOF(1)
    Input #1, LibPath
    chk_flag = False
    For j = 1 To i'check for missing cases
        lib_Attached = libAttached(j)
        If libAttached(j) = LibPath Then
            chk_flag = True
            Exit For
        End If
    If chk_flag = False Then 'Reference found missing, add to the Project
        Set RefObj = Application.References.AddFromFile(LibPath)
        msg = msg & LibPath & vbCr
    End If
Close #1

If Len(msg) <>  0 Then
    msg = "Following References Attached: " & vbCr & vbCr & msg
End If

MsgBox msg, , " AddReferences()"

Exit Function

MsgBox Err.Description, , "AddReferences()"
Resume AddReferences_Exit
End Function

Public Function Ref_Retain_Remove() As Boolean
Dim Ref As Reference, Reflist(), exclusion(1 To 5) As String
Dim ref_count As Integer, strRefName As String
Dim i As Integer, j As Integer, chk_flag As Boolean

On Error GoTo Ref_Retain_Remove_Err

exclusion(1) = "VBA"
exclusion(2) = "Access"
exclusion(3) = "stdole"
exclusion(4) = Application.GetOption("Project Name")
'Replace this line with your own Reference Library Name
exclusion(5) = "aprRefLib"

ref_count = Application.References.Count

If ref_count > 4 Then
    ReDim Reflist(1 To ref_count)
    i = 0
    For Each Ref In Application.References
       strRefName = Ref.Name
       chk_flag = False
       For j = 1 To 5
          If strRefName = exclusion(j) Then
              chk_flag = True
              Exit For
           End If
        If chk_flag = False Then
            i = i + 1
       'Collect the Reference Library Project Names, if any, other than
       'the Names in the exclusion list to remove them
       'before attaching the new ones, to avoid Project Name conflict.
            Reflist(i) = Ref.Name
        End If
    ReDim Preserve Reflist(1 To i)'Remove the collected Reference Libraries
    For j = 1 To i
        Set Ref = References(Reflist(j))
        References.Remove Ref
End If

Ref_Retain_Remove = True

Exit Function

MsgBox Err.Description, , "Ref_Retain_Remove()"
Ref_Retain_Remove = False
Resume Ref_Retain_Remove_Exit
End Function

Note: Before closing the Database, if you would like to save a copy of the Library Database in a Compiled state you can create it by running Tools- ->Database Utilities - - > Make MDE File . . . option and name the database as MyLib.MDE. You may save it at the same location of the .mdb file and move the .mdb file to your local drive or in your private server location where access to others are forbidden. When you add more common routines into this Library file you can recompile it and replace the earlier .MDE file so that the new programs can be made available to all your Projects without making any changes in them. You can attach your Library Database MyLib.mde along with others to all of your future and current running Projects.

We have two programs; the second one is to validate the existing attached References and remove them except the essential ones mentioned above to avoid conflicts.

The Demo Run from a new Database

The preparations are in place and we are going to do a Trial Run.

  1. Create a new Database.
  2. Open the VBA Editing Window (Alt+F11).
  3. Open the Debug Window (Immediate Window) . . . Ctrl+G.
  4. Type the following line in the Debug Window and press Enter so that we can attach your own Common Library File MyLib.mde and Run the main program AddReferences() from there:

Note: Change the Server Location address to match with your own.


If you select Tools- ->Reference; you can see that your own Library File is now attached to your Project.

We can now call the AddReferences() program from your Function Library from the Debug Window itself and attach other Library Files List we have created in the Text File.

Type the following line in the Debug Window and press Enter key to do that:


You can now check the Reference Library List to re-confirm that all the required files are in place. In your next Project all you need to do after creating a new Database is to type the following two lines in the Debug Window and press Enter key to add all required Reference Libraries to your Project at once:




Inputbox and Simple Menus


We have already learned how to create Menus and Toolbars through the following Articles:

I think InputBox() function is one of the few things that we come across when start learning VBA and discard it later altogether when we see more exiting objects like MsgBox(). Probably its usage is not properly understood and it doesn't deserve this neglect. The usage of Inputbox() function is very simple like MsgBox() but seldom used in programs to gather user responses. Instead, MsgBox() is the most preferred control, which has a variety of options available to configure it with different options of Command Buttons. But these options are like OK, Cancel, Retry, Yes, No etc. MsgBox cannot be programmed to make selections from user defined Options like the facility provides by InputBox().

We have learned how to use Office Assistant with preferred default Animation character instead of MsgBox. We have also seen how to collect user responses using Office Assistant with Check-Boxes or Option Balloon Menus in them. But, I admit that the VBA routines involved in these methods are complex and needs some time to understand the intricacies. But, once the program is in place the usage is very simple and can be inserted freely in your Code across projects, when placed in a library database and when linked to your other projects.

A Simple Example

Coming back to the Inputbox() function; let us look into a simple example in a sub-routine. A Report Command Button on the Main Switchboard when clicked; a small menu is displayed asking the User whether she would like to get (1) Report Preview or (2) Print the Report directly to the Printer or (3) Exit, if there is a change of mind.

The simple usage of InputBox() function Syntax is given below:

X = InputBox("Message Text","Title",Default Value)

The Title and Default Values parameters are Optional. There are other optional values as well. You may check for them in the Microsoft Access Help Documents.

The Message Text is displayed on the body of the Form and the Title text appears on the Title Bar of the Control. We have used 1 as third Parameter (Default Value) and that value is inserted in the Text Box as shown in the following example image:

The Code that brings this Form up on a Command Button Click EventProcedure is given below.

Private Sub cmdRpt_Click()
Dim varResponse as Variant

varResponse = InputBox("(1) Print Preview (2) Print (3) Exit", "Select 1/2/3",1)

Select Case varResponse
    Case 1
        DoCmd.OpenReport "Orders", acViewPreview
    Case 2
        DoCmd.OpenReport "Orders", acViewNormal
    Case 3
        Exit Sub
End Select

End Sub

When the User makes a selection as per the Options provided, the selected value is tested and executes the actions programmed in the Select Case. . .End Select statements.

Note: The Inputbox() function brings up a Form with its Border Style Property Value set as Dialog so that the Minimize/Maximize & Restore Buttons removed from the Title Bar except the Window Close control. You are not allowed to click on any other Form or Objects in the Application window before you are done with the InputBox() Function, indicating that this Form's Modal Property is set to Yes. We have familiarized the usage of Pop up and other Property settings of the Form in our earlier discussions on Form Properties in the following Articles:

Taking Care of Pitfalls

While using Inputbox() this way in programs we must take care of few things to avoid some undesirable side effects and pay attention to give the Menu a better look as well.

First, we will take the side effects part. We have provided Option 3 to close the Dialog Box normally, if the User has a change of mind after bringing up the Menu. But, she may ignore option 3 in the Menu and may use the Window Close control on the Title bar or click on the Cancel Command Button instead; that can invite trouble in the Code.

Since we have defined the Option Values in the Menu as Integer (1,2 and 3) naturally we will think about using an Integer Type Variable to record the response of the User. But, the User may click on the Cancel Command Button or Window Close Title Bar control, without selecting any of the options displayed, to close the Window. In that case the InputBox() Function will return an Empty String ("") as response. If we have defined an Integer Variable to accept the returned value, then this will end up with a Type Mismatch Error and the program will crash.

We can use a string variable to accept the Empty string result to avoid this problem and the Select Case. . . statement will work without modification even though we are testing for Integer values rather than in Case "1" style. A reasonable Variable Type selection to record the user response in these circumstances is a Variant Type that can accept any data type.

With a little trick we can make the program to ignore the Cancel Button and Window Close Title Bar control Clicks and force the user to make a selection from the Menu Options only, if she want to get out of the Menu normally.

We have inserted 1 as third Optional Parameter Value to take care of the OK Command Button Click by the User just in case she doesn't type a selection value from the Menu Options and simply hits the OK Button. If she types one of the Option values in the Text Box and hits the Enter key, as we expect her to do, that will return the value selected into the varResponse Variable and the InputBox() control will close normally.

We will modify the above Code:

  1. to make it a better looking real Menu
  2. to ignore the Cancel Command Button and Window Close Title Bar control clicks.
  1. To prepare the Menu we will define a string Variable and write the following expression before inserting it into the InputBox() Function:
    Dim strMenu as String
        strMenu = "1. Report Preview" & vbCr & vbCr
        strMenu = strMenu & "2. Print Report" & vbCr & vbCr
        strMenu = strMenu & "3. Exit"
  2. We will put the InputBox() Function within a Do While . . . Loop with a condition so that the Function will keep repeatedly executing if the user doesn't make a valid selection from the Menu and discourage her from Clicking Window Close or Cancel Buttons to get out of the Dialog Box.
varResponse = ""
   Do While Len(varResponse) = 0 Or varResponse < 1 Or varResponse > 3

       varResponse = InputBox(strMenu, "Select 1/2/3",1)


After the above changes the Menu will look like the following image:

Modified VBA Code

The modified Code is given below:

Private Sub cmdRpt_Click()
'Author : a.p.r. pillai
'Date : March-2009
'URL  : www.msaccesstips.com
'All Rights Reserved by www.msaccesstips.com
Dim varResponse As Variant
Dim strMenu As String

strMenu = "1. Report Preview" & vbCr & vbCrstrMenu = strMenu & "2. Print Report" & vbCr & vbCrstrMenu = strMenu & "3. Exit"

varResponse = ""
Do While Len(varResponse) = 0 Or varResponse < 1 Or varResponse > 3
    varResponse = InputBox(strMenu, "Select 1/2/3", 1)

Select Case varResponse
    Case 1
        DoCmd.OpenReport "Orders", acViewPreview
    Case 2
        DoCmd.OpenReport "Orders", acViewNormal
    Case 3
        Exit Sub
End Select

End Sub

Probably the next question comes into one's mind is, do we have to use the Menu Options always in the form 1,2,3 etc., can we use alphabets instead, like

  • R. Report Preview
  • P. Print Report
  • E. Exit ?

Yes, we can with few changes in the Code as given below:

strMenu = "R. Report Preview" & vbCr & vbCr
strMenu = strMenu & "P. Print Report" & vbCr & vbCr

strMenu = strMenu & "E. Exit"

varResponse= ""

Do While instr(1, "RPE",varResponse)=0 or len(varResponse)=0
    varResponse = InputBox(strMenu, "Select R/P/E", "R")


The Select Case. . . statement must be changed to:

Select Case varResponse
  Case "R"

      DoCmd.OpenReport "Orders", acViewPreview

  Case "P"

      DoCmd.OpenReport "Orders", acViewNormal

  Case "E"

      Exit Sub

End Select

Option Alphabet Values

The changed Code with alphabets as Options is given below:

Private Sub cmdRpt_Click()
'Author : a.p.r. pillai
'Date : March-2009
'URL  : www.msaccesstips.com
'All Rights Reserved by www.msaccesstips.com
Dim varResponse As VariantDim strMenu As String

strMenu = "R. Report Preview" & vbCr & vbCr
strMenu = strMenu & "P. Print Report" & vbCr & vbCr
strMenu = strMenu & "E. Exit"

varResponse = ""

Do While InStr(1, "RPE", varResponse) = 0 Or Len(varResponse) = 0
    varResponse = InputBox(strMenu, "Select R/P/E", "R")

Select Case varResponse
    Case "R"
        DoCmd.OpenReport "Orders", acViewPreview
    Case "P"
        DoCmd.OpenReport "Orders", acViewNormal
    Case "E"
        Exit Sub
End Select

End Sub

Change Form Modes on User Profile


We have seen how to define a Pop up Form and how it can be made to appear on a particular position on the Application window, when it is opened. We have also seen that the Form behavior is controlled by setting or modifying the Property values of the Form. In those cases we have changed the Form's Pop up, Border Style and Auto Center Property Values manually during design time.

Here, we will try something different and will attempt to change the Form opening Modes (Data Entry Mode or Edit/Search Mode) at run time automatically based on the identity of the Current User of the Form. When the Database is shared on a Network the same Form can be opened by different Users and the Form should behave differently for each User or Users Group.

For Example, a single Form can be used for Data Entry by one group of Users, who are allowed only to key-in new records and others are allowed to View, Edit and Search but not allowed to add new records. Data Entry User can also edit data that he/she keys-in during the current data entry session.

The Open Mode change can be done by checking the Security User Account or Group Account name of the Current User and by changing the Data Entry, Allow Additions Property Values.

Checking the Current User's credentials

We need a small VBA program to check the Current User's Group Account information to determine whether he/she belongs to the DATAENTRY Group Account or not (the User Workgroup name DATAENTRY is used here only as example). Accordingly the program will set the Mode of the Form for the current session.

The VBA Routine is given below:

Public Function ChangeFormMode(ByVal strFormName As String, ByVal UserGroupName As String)
'Author : a.p.r. pillai
'Date   : Feb-2009
'All Right Reserved by www.msaccesstips.com
Dim wsp As Workspace, strUser As String
Dim j As Integer, frm As Form
Dim chkFlag As Boolean, GrpCount As Integer

On Error GoTo ChangeFormMode_Err

Set frm = Forms(strFormName)
strUser = CurrentUser
Set wsp = DBEngine.Workspaces(0)
GrpCount = wsp.Users(strUser).Groups.Count
'checks whether 'DATAENTRY' Group Name is present
'within the User's Group Account Names.
chkFlag = False
For j = 0 To GrpCount - 1
    If wsp.Users(strUser).Groups(j).Name = UserGroupName Then
        chkFlag = True
        Exit For
    End If
If chkFlag Then
    frm.DataEntry = True
    frm.AllowAdditions = True
    frm.DataEntry = False
    frm.AllowAdditions = False
End If

Exit Function

MsgBox Err.Description, , "ChangeFormMode"
Resume ChangeFormMode_Exit
End Function

Copy and Paste the above program into a Global Module (Standard Module) in your database and save it. The program is called from the Form_Load() EventProcedure of the Form with the Form Name and the User Group Account name as parameters as follows:

Private Sub Form_Load()
   ChangeFormMode Me.Name, "DataEntry"
End Sub

About Security Accounts

A security User Account may belong to one or more security Group Accounts besides the default Users Group Account, which all the Users belong. Therefore, we need to check through the User's Security Group Account list, whether he/she belongs to the DataEntry Group or not and if found true then change the Form's open Mode accordingly.

Note: This method to work, the Database must be secured by implementing MS-Access Security and assume that the Users are grouped under different Work Groups like Data Entry Group, Team Leaders Groupb or Managers Group and so on. To learn more about Microsoft Access Security visit the pages under Security in the Main Menu.

If your machine is not configured to use a common MS-Access Work Group Information file or if you have not implemented MS-Access Security using the local Work Group Information File then you are automatically log-in using the default User Account Admin, a member of the Admins and Users Group. This is happening silently and you are not asked to enter a UserID or Password. In that case you can try this procedure by passing the Admins or Users work group name as parameter to the above Program.

Form View Mode Changing Manually

But, if all Users need both features inter-changeably at their will then this can be implemented with the use of a Command Button on the Form. Since, this procedure is manually controlled, requirement of checking the Security User Account or Group Accounts doesn't arise.

All we need is a Command Button at the Footer of the Form, set with specific Caption Value which can be checked and changed on every Click on the Command Button and switch the Form Mode to Data Entry or Normal.

Assume that the Form Opens in Edit/Search Mode by setting the following Property Values at design time:

  • Data Entry = False
  • Allow Additions = False

The Caption of the Command Button will be set as Data Entry indicating that if the User wishes to change the Form into Data Entry Mode then she may click on the Command Button. Since, the same Command Button click is needed to switch the Form into one of these two Modes we must check the Caption of the Command Button to determine what is the User's intention. She may click on the Command Button repeatedly too.

On every click we must check the Caption Value and change the Mode of the Form as well as the Caption of the Command Button to Edit/Search or Data Entry interchangeably.

Form Mode change with VBA

We can implement this with few lines of Code on the Form's Class Module itself. The Command Button's Name Property Value is cmdEdit.

Private Sub cmdEdit_Click()
If Me.cmdEdit.Caption = "Data Entry" Then
     Me.cmdEdit.Caption = "Edit/Search"
     Me.DataEntry = True
     Me.AllowAdditions = True
     Me.cmdEdit.Caption = "Data Entry"
     Me.DataEntry = False
     Me.AllowAdditions = False
End If
End Sub

The above technique we have used for a different function on a Form and you may take a look at that Article with the Title: Double Action Command Button.


Positioning Pop Up Forms


We have seen the Pop up form in action last week in an Article with the Title: Synchronized Floating Pop up Form. I hope you have downloaded the Demo Database as well.

I know, I don't have to talk at length about Forms and their importance in a database, because that is the first piece of object we would like to lay our hands on, the moment we decide to start learning MS-Access and wants to put all kinds of fancy objects all over the Form. MS-Access Forms are far better than Forms designed in Visual Basic.

There was an interesting incident in our office long back. A Visual Basic Application developed by our Computer Department was installed in one of my colleague's machine. My friend got a brief introduction from the developer before he left the scene. My friend started exploring the new Application.

I was sitting few workstations away from him and after about half an hour he called me and asked for some help. I was also very curious to have a look at the new Application. When I set my eyes on the screen I realized why he called me and he confirmed that too. I couldn't resist a smile on my face and pat myself on my back for working with MS-Access Forms, even though I knew VB can do many things, which you cannot do in Access.

Screen Resolution and VB Form

Few years ago, before LCDs came into existence, the Computer Monitors were used with 640 x 480 Resolution and the Users preferred to maintain this way because dBase and Foxprow 2.5/3.0 based Applications were also running on their machines. Some Monitors can be set with some higher resolutions. When VB Forms are designed on high resolution Screens and installed on computers with 640 x 480 resolution the Form will overlap the Screen.

The VB Application's Main Switchboard Form overlaps on all four sides of the Screen on my colleague's machine and some of the Objects like Command Buttons and Option Group etc. at the edges of the Form are only partially visible on the screen. The Form was designed on a machine with a higher resolution without knowing where he is going to install it and ended up this way.

As far as I know, VB Form doesn't have any Control Buttons (Min/Max, Restore and Close) on the Title Bar; no sizing handles, no Header/Footer Sections or able to define Pages and it looked like an oversize wooden board placed behind the screen frame.

I really felt few inches taller when positive remarks came from the Users of my MS-Access Databases.

Coming back to the point, we can change the behavior of the Form by setting its Property Values and we have seen several of them through earlier examples. We have set the Pop up Property Value to Yes in the previous Article to position the EmployeeSub Form above all other Forms.

Pop-up Form extends Beyond Application Window

When the Database Window is in maximized state if you open a normal Form it will also adjust itself to the maximized state. But, if the Pop up Property value is set to Yes then the form will open in its original designed size. This is true if you set the Border Style Property value to Dialog or Thin instead of setting the Pop up Property value. But, this will remove the Minimize/Maximize; Restore Window Buttons on the Title Bar. Setting the Border Style property value to None will remove the Title Bar totally.

It is a well known fact that all MS-Access Objects like Forms/Reports opens and stays within the MS-Access Application Window. You cannot take any of them out of the Application Window boundaries, right?

Wrong, you can, try this to find out:

  1. If the Access Application Window is in maximized state then click the Restore Down control button (the middle one between minimize/maximize and close buttons) on the Title bar and you will find a Window sizing control at the right bottom corner of the Application window. When you place the mouse pointer there it will change into a double-headed arrow. Click and drag the corner up, moving the mouse towards left top corner direction of the screen, so that the MS-Access Application Window reduces to about half of its size or make it even smaller. But you must be able to see one of your Pop up Forms in the database window and you are going to open it next.
  2. Open one of your Pop up Forms and you will see that it jumps out of the Application Window.
  3. If you didn't make the Application window small enough then click on the Title Bar of the Pop up Form and drag it out of the Application Window. You can place both the Pop up Form and Application Window side by side or place the Pop up Form over the small Application window to hide it completely.
  4. Don't attempt to minimize the MS-Access Application Window, if you do then the Pop up Form will jump in and hide. If the Application window is very small then the Pop up Form may open over the Application window hiding it completely.

Positioning PopUp Form

Forms have another Property Auto Center. Usually this property is set to Yes along with the Pop up Property settings to make the Form to appear in the middle of the Application Window.

But, what to do if we want the Pop up Form to appear in the top right corner or right bottom corner or any place other than the center of the Screen? There are no Property settings to make this happen. But there is a simple way to get this done, try the following method:

  1. Maximize the Application Window.
  2. Open a Pop up Form in Design View.
  3. Display the Property Sheet of the Form (View - ->Properties) and see that the Auto Center property is set to No.
  4. If the Form's design frame is larger than the actual design size then reduce the design frame size by dragging the right bottom corner to the desired size.
  5. Click on the Title Bar of the Form and drag it to the right top corner (or any other position you prefer) and place it there.
  6. Click on the Save Toolbar Button or select Save from File Menu.
  7. Close the Design view of the Form
  8. Now, Open the Form in Normal View. The Form will appear where you have placed and saved it in design view.

Tip: This works not only for Pop up Forms, works on any Form.



Subscribe in a reader
Your email address:

Delivered by FeedBurner


Popular Posts

Blog Archive

Powered by Blogger.

Follow by Email


Forms Functions How Tos MS-Access Security Reports Class Module msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Array External Links Queries msaccess reports Downloads msaccess tips Accesstips Objects Collection Object Property Event Menus and Toolbars Controls MsaccessLinks Process Controls WithEvents Art Work VBA msaccess How Tos Combo Boxes Dictionary Object Graph Charts List Boxes Query msaccessQuery Calculation Command Buttons Form Report Command Button Data Emails and Alerts RaiseEvent Custom Functions Custom Wizards DOS Commands Data Type Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Item Key msaccessprocess security advanced Access Security Add Auto-Number Field Type Fields Form Instances Macros Menus Recordset SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 Copy Excel Expression Join Methods Microsoft Numbering System Records Security Split Table Time Difference Utility Workgroup Wrapper Classes database function ms-access msaccess wizards reference text tutorial vba code Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Data Filtering Database Records Defining Pages Diagram Disk Dynamic Lookup Error Handler 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 hyperlinks iSeries Date iif msaccess msaccess alerts pdf files restore switch toolbar updating upload