Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

MS-Access and Reference Library

Introduction

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 for creating Data Processing Routines in VBA.

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

Some of these Library References like Microsoft Access 9.0 Object Library, Visual Basic for Applications, OLE Automation is attached by default, when a new database is created and other files are added manually. We add new reference Libraries for using the powerful features extended by them, which are not available in Microsoft Access.

We have used Microsoft Office Library (Office) to create customized Message Boxes, Option BalloonsCheck Boxes, and other controls. The Office Assistant feature we have used to capture user responses in an interesting way. We have created a Wizard to select the Office Assistant character Clippy, the Cat, or any other 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 the Server and attach it as a Library Database so that you don't have to copy and paste those Functions into other Databases.

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

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 process. Attach the required Library Files in a new Project or restore the lost links, if that happens at any point in time, like during the recovery of a corrupted database.

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

a).     Prepare a list of essential 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, open VBA Module Window (View - - > Code or Alt+F11), select Tools- - >References, and find the item in 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 Description, the physical File path name 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
    Next
    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 them because if we need to remove any of these library files from the Current Project then you must use the Project Name in programs rather than using the above filenames.

When you create the 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 by selecting the 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.  If you create a Standard Module with the name abcd you will run into Error: Name Conflicts with Existing Module, Project, or Object Library

Note: You may change the Project Name, but you are not allowed to use any of the Standard Modules' Names.

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 has 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 them 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

\\hosfs03\InhouseSys\CommonLib\RefLib.txt

b).     The first stage of our preparation is over. The next thing that 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 Routine is given 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 a little groundwork 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, 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 the 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
    Next
    If chk_flag = False Then 'Reference found missing, add to the Project
        Set RefObj = Application.References.AddFromFile(LibPath)
        msg = msg & LibPath & vbCr
    End If
Loop
Close #1

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

MsgBox msg, , " AddReferences()"

AddReferences_Exit:
Exit Function

AddReferences_Err:
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
        Next
        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
    Next
    ReDim Preserve Reflist(1 To i)'Remove the collected Reference Libraries
    For j = 1 To i
        Set Ref = References(Reflist(j))
        References.Remove Ref
    Next
End If

Ref_Retain_Remove = True

Ref_Retain_Remove_Exit:
Exit Function

Ref_Retain_Remove_Err:
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 as the .mdb file and move the .mdb file to your local drive or in your private server location where access to others is forbidden. When you add more common routines to this Library file, you may recompile it and replace it with the earlier file so that the new programs can be made available to all your projects without making any changes to 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 your own.

Application.References.AddFromFile("\\hosfs03\InhouseSys\CommonLib\MyLib.mde")

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 the Enter key to do that:

AddReferences

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 the Enter key to add all required Reference Libraries to your Project at once:

Application.References.AddFromFile("\\hosfs03\InhouseSys\CommonLib\MyLib.mde")

AddReferences

Share:

Inputbox and Simple Menus

Introduction.

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 exciting objects like MsgBox(). Probably its usage is not properly understood and it doesn't deserve this neglect. The usage of the 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 OK, Cancel, Retry, Yes, No, etc. MsgBox cannot be programmed to make selections from user-defined Options like the facility provided by the InputBox() Function.

We have learned how to use the Office Assistant with the preferred default Animation character instead of MsgBox. We have also seen how to collect user responses using the Office Assistant with CheckBoxes or Option Balloon Menus in them. But, I admit that the VBA routines involved in these methods are complex and need some time to understand their various ways of usage. 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 simplest usage of InputBox() function Syntax is given below:

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

The Title and Default Value 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 the 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 with a Command Button Click Event Procedure 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 are removed from the Title Bar except for 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 a 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 . . . End Case, statement structure 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 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 wants to get out of the Menu normally.

We have inserted 1 as the 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.

  3. 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"
          
  4. We will put the InputBox() Function Code within the Do While . . . Loop structure, which will keep repeating the statements within the Loop if the user doesn't make a valid selection from the Menu. The user will not be allowed to click the 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)

  Loop

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

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 that 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")

Loop

The Select Case. . . End Select statements 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 are 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")
Loop

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

End Sub
Share:

Change Form Modes on User Profile

Introduction

We have seen how to define a Pop-up Form and how it can be made to appear in 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 the 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 are not allowed to add new records. Data Entry Users can also edit data 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 an 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
Next
If chkFlag Then
    frm.DataEntry = True
    frm.AllowAdditions = True
Else
    frm.DataEntry = False
    frm.AllowAdditions = False
End If

ChangeFormMode_Exit:
Exit Function

ChangeFormMode_Err:
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() Event Procedure 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, to which all the users belong to. Therefore, we need to check through the User's Security Group Account list, whether he/she belongs to the Data Entry Group or not, and if found true then change the Form's open Mode accordingly.

Note: For 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 Group 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 logged 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 User ID or Password. In that case, you can try this procedure with the Admins or Users' workgroup name as a 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, the 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 to a specific Caption Value which can be checked and changed with every Click on the Command Button, and switch the Form Mode into 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 at 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.

With 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 in a 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
 Else
     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.

Earlier Post Link References:

Share:

Positioning Pop Up Forms

Introduction

We saw the Popup form in action last week in an article with the Title: Synchronized Floating Popup 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 most important object that we work with when we start developing a new Project. 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 on one of my colleague's machines. My friend got a brief introduction from the developer.  My friend started exploring the new Application.

I was developing in-house Access Applications for Audit Department and sitting a few workstations away from my friend. 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 was proud of having MS-Access Forms, even though I knew VB can do many things, which you cannot do in Access.

Screen Resolution and VB Form

A 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. Unlike MS-Access Form VB Forms don't auto-fit to the current resolution of the Screen.  

The VB Application's Main Switchboard Form overlaps all four sides of the Screen on my colleague's machine and some of the Objects like Command Buttons and Options Group etc., at the edges of the Form are only partially visible on the screen. The Form was designed for 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 a 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.

Popup Form extends Beyond Application Window

When the Database Window is in the 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 open 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 the 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 the left top corner direction of the screen, so that the MS-Access Application Window reduces to about half of its size or makes it even smaller. But you must be able to see one of your Popup Forms in the database window and you are going to open it next.

  2. Open one of your Popup 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 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 the design view.

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

Share:

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

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