Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Showing posts with label msaccess forms. Show all posts
Showing posts with label msaccess forms. Show all posts

DIRectory and File Copy Utility

Introduction.

Last week we saw how to use Dir () DOS Command, its ability to read files from the Disk one by one and display it in the Debug Window.

In continuation of that, we will create a VBA Utility using DIR Command with a very useful VBA Statement FileCopy (it is a statement, not a Function) to read and transfer files from one folder to a different location on the disk.  The files can be of any type, like *.pdf, *.docx, Xls, or *.* (all files).

The files will be read and listed in a Listbox from the selected folder, specified in a text box.,  with the use of the DIR() Command.  All the files in the list or selected ones can be copied to a different location specified in a text box, defined as the target location.

The Utility Form.

The design view image of a Form created for this purpose is given below for reference:

filecopy_design0

The design is simple with two text boxes, one Listbox,  three Command Buttons, and a Label Control to display messages from this Utility Program.  You can download this Utility Form in a sample database at the end of this article.

These are the names of the Controls on the Form:

  1. Top Text box: Source

  2. Text Box 2:  Target

  3. List Box:  List1

  4. Top Command Button: cmdDir

  5. Second Command Button : cmdSelected

  6. Last Command Button : cmdClose

  7. Bottom empty Label Name: msg

Note: If you are designing this form yourself, then ensure that you give the controls the same names as given above because the VBA code, that you are going to copy, and paste into the Module, will reference all these names in the Code

Besides the above main controls, there is a Label Control below the first Source Textbox showing examples as to how to specify Source File Path correctly.

The label control at the bottom of the form shows messages that pop up during validation checks of the inputs and when errors are detected, during the execution of the VBA Code.

An Image of a sample run of the FileCopy Statement is given below:

filecopy_run0

You may create this User Interface with the names of the Controls as given above.  After designing the form with the correct names for the controls, display the VBA Window of the Form, Copy and Paste the following code into the Form’s VBA Module:

The Form Module Code.

Option Compare Database
Option Explicit
Dim strSource1 As String
Dim strSource2 As String, strMsg As String

Private Sub cmdClose_Click()
On Error GoTo cmdClose_Click_Error

If MsgBox("Close File Copy Utility?", vbOKCancel + vbQuestion, "cmdClose_Click()") = vbOK Then
   DoCmd.Close acForm, Me.Name, acSaveYes
End If

cmdClose_Click_Exit:
Exit Sub

cmdClose_Click_Error:
MsgBox Err.Description, , "cmdClose_Click()"
Resume cmdClose_Click_Exit
End Sub

Private Sub cmdDir_Click()
'=========================================================
'Author : a.p.r.pillai
'Date   : June 2018
'Purpose: Take directory listing
'Rights : All Rights Reserved by www.msaccesstips.com
'=========================================================
Dim strSource As String, strMsg As String
Dim i As Integer, x As String
Dim j As Integer, strfile As String
Dim strList As ListBox, LList As String

On Error GoTo cmdDir_Click_Err
msg.Caption = ""

'Read Source location address
strSource = Nz(Me!Source, "")
If Len(strSource) = 0 Then
    strMsg = "Source Path is empty."
    MsgBox strMsg,vbOKOnly + vbCritical, "cmdDir_Click()"
msg.Caption = strMsg
    Exit Sub
End If

'check for the last back-slash location
'this can be used to split the folder name
'and file name type values separately.

i = InStrRev(strSource, "\")

'get the folder name part into the variable
strSource1 = Left(strSource, i)

'take file type (*.docx, *.exl, *.txt etc.) value into a separate
'variable temporarily
If Len(strSource) > i Then
    strSource2 = Right(strSource, Len(strSource) - i)
End If

'define Listbox object
Set strList = Me.List1

'Read the first file from the folder
strfile = Dir(strSource, vbHidden)
If Len(strfile) = 0 Then
    strMsg = "No Files of the specified type: '" & strSource2 & "' in this folder."
    MsgBox strMsg, vbCritical + vbOKOnly, "cmdDir()"
    msg.Caption = strMsg
    Exit Sub
End If

j = 0
LList = ""
Do While Len(strfile) > 0
   If Left(strfile, 1) = "~" Then 'ignore backup files, if any
      GoTo readnext:
   End If
    j = j + 1 'File list count
    LList = LList & Chr(34) & strfile & Chr(34) & ","
    
readnext:
    strfile = Dir() ' read next file
Loop

LList = Left(LList, Len(LList) - 1) ' remove the extra comma at the end of the list
strList.RowSource = LList 'insert the files list into the listbox RowSource property
strList.Requery 'refresh the listbox
msg.Caption = "Total: " & j & " Files found."

Me.Target.Enabled = True

cmdDir_Click_Exit:
Exit Sub

cmdDir_Click_Err:
MsgBox Err.Description, , "cmdDir_Click()"
Resume cmdDir_Click_Exit

End Sub


Private Sub cmdSelected_Click()
'=========================================================
'Author : a.p.r.pillai
'Date   : June 2018
'Purpose: Copy Selected/All Files to Target Location
'Rights : All Rights Reserved by www.msaccesstips.com
'=========================================================

Dim lstBox As ListBox, ListCount As Integer
Dim strfile As String, j As Integer, t As Double
Dim strTarget As String, strTarget2 As String
Dim chk As String, i As Integer, yn As Integer
Dim k As Integer

On Error GoTo cmdSelected_Click_Err

msg.Caption = ""
'Read Target location address
strTarget = Trim(Nz(Me!Target, ""))

'validate Destination location
If Len(strTarget) = 0 Then
   strMsg = "Enter a Valid Path for Destination!"
   MsgBox strMsg, vbOKOnly + vbCritical, "cmdSelected()"
   msg.Caption = strMsg
   Exit Sub
ElseIf Right(strTarget, 1) <> "\" Then
      strMsg = "Correct the Path as '" & Trim(Me.Target) & "\' and Re-try"
      MsgBox strMsg, vbOKOnly + vbCritical, "cmdSelected()"
      msg.Caption = strMsg
      Exit Sub
End If

'Take a count of files in listbox
Set lstBox = Me.List1
ListCount = lstBox.ListCount - 1

'take a count of selected files, if any, for copying
i = 0
For j = 0 To ListCount
If lstBox.Selected(j) Then
  i = i + 1
End If
Next

'identify user's response for copy
If (i = 0) And (ListCount > 0) Then
       strMsg = "Copy all Files..?"
       Me.cmdSelected.Caption = "Copy All"
Else
       strMsg = "Copy Selected Files..?"
       Me.cmdSelected.Caption = "Copy Marked files"

End If

'Me.cmdSelected.Requery

'get copy option from User
yn = MsgBox(strMsg, vbOKCancel + vbQuestion, "cmdSelected_Click()")

'Run Copy selected option
If (i = 0) And (yn = vbOK) Then
    GoSub allCopy
ElseIf (i > 0) And (yn = vbOK) Then
    GoSub selectCopy
Else
    Exit Sub
End If

'disable Copy button to stop a repeat copy of the same files.
'Remarks: User can make fresh selections from the same list
'To copy them to the same target locatiion.
'Or to a different location by specifying different Path
'in the Destination Text Box
Me.List1.SetFocus
Me.cmdSelected.Enabled = False

'Display copy status
strMsg = "Total " & k & " File(s) Copied." & vbCrLf & "Check the Target Folder for accuracy."
MsgBox strMsg, vbInformation + vbOKOnly, "cmdSelected_Click()"
Me.msg.Caption = strMsg

cmdSelected_Click_Exit:
Exit Sub

allCopy:
k = 0
For j = 0 To ListCount
    strfile = lstBox.ItemData(j)
   
    strSource2 = strSource1 & strfile
    strTarget2 = strTarget & strfile
    
    FileCopy strSource2, strTarget2
  'give enough time to copy the file
  'before taking the next file
  k = k + 1
  t = Timer()
  Do While Timer() > (t + 10)
    'do nothing
  Loop
Next
Return

selectCopy:
k = 0
For j = 0 To ListCount
   If lstBox.Selected(j) Then
        strfile = lstBox.ItemData(j)
        strSource2 = strSource1 & strfile
        strTarget2 = strTarget & strfile
        
            FileCopy strSource2, strTarget2
               'give enough time to copy the file
               'before taking the next file
               k = k + 1
                t = Timer()
                Do While Timer() > (t + 10)
                    'do nothing
                Loop
   End If
Next
Return


cmdSelected_Click_Err:
MsgBox Err.Description, , "cmdSelected_Click()"
Me.msg.Caption = Err.Description
Resume cmdSelected_Click_Exit

End Sub


Private Sub List1_AfterUpdate()
On Error GoTo List1_AfterUpdate_Error
Me.cmdSelected.Enabled = True
List1_AfterUpdate_Exit:
Exit Sub

List1_AfterUpdate_Error:
MsgBox Err.Description, , "List1_AfterUpdate()"
Resume List1_AfterUpdate_Exit
End Sub

You may save the Form with the name FileCopy.

Note: FileCopy is a VBA Statement, not a built-in Function.

You may copy different sets of files from the list of files displayed in the List Box to different Target Folders by selecting the files (after de-selecting earlier selections) and after changing the Destination Location address in the Text Control.

Download the Demo Database.

You may download the sample database with the VBA Code from the Link given below:

Download FileCopy2007.zip

Download (2003) FileCopy.zip


Share:

Microsoft Access Form Move Size Action

Introduction.

We design Access Forms that fits into the existing Application Window Width (to display/edit records), or design popup Forms with specific size without borders or scroll bars (can be moved out of the Application Window area too) or Modal type form (popup type forms with its Modal property value set to True) that must be closed, after taking suggested action on it, before you are able to work with other forms.

This type of form opens one over the other (when more than one form is open) on the Application Window. You must enable Overlapping Windows by selecting Office Button - - > Access Options - - > Current Database - - > Document Window Options - - > Overlapping Windows to open the forms in this way, otherwise they will be opened in the Tabbed style in the Application Window.

The Pop-Up Forms.

Popup Forms will open on the exact location of the Application Window, from where you have saved it during design time. If you need more details on this topic visit this Article Link: Positioning popup Forms.

We can open a Microsoft Access Form and move it to a particular location of the Application Window is in the resized state, if necessary, with MoveSize Action on Docmd Object.

The MoveSize Action.

Here, we will learn the usage of MoveSize Action of the DoCmd Object in VBA.

View the YouTube Demo Video given below for reference. Select the 720p HD Option from the Settings for better quality viewing.

Demo Video

When the Supplier Code is selected on the Supplier List Form, the related Product List is displayed,  above the Supplier Form to the right of the main form. The width of the Product List Form is not changed, but the height of it changes, depending on the number of items on it.

We need two tables, two Queries, and the Supplier List Form from the Northwind sample database to build this trick. You need to design a Form for the Product List. A Demo Database is given at the end of this Article to download and try out, right away.

The list of Tables, Queries, and Forms required, to build this database, is given below.

    Tables:

  • Suppliers
  • Products
  • Queries:

  • Suppliers Extended
  • ProductListQ
  • SQL Code:

    SELECT Products.[Supplier IDs], Right([Product Name],Len([product name])-17) AS Product, Products.[List Price], Products.[Quantity Per Unit]
    FROM Products
    WHERE (((Products.[Supplier IDs].Value)=[forms]![Supplier List]![id]));
    

    Forms:

  • Supplier List
  • Product List

Copy and Paste the following VBA Code into the Supplier List Form's VBA Module and save the Form:

Private Sub Company_Click()
Dim frm As Form, ProductForm As String, items As Integer
Dim mainFormHeight As Integer
Dim intHeader As Integer, intFooter As Integer
Dim intH As Integer, frmchild As Form, oneInchTwips As Integer

On Error GoTo Company_Click_Err

ProductForm = "Product List"
oneInchTwips = 1440 'Form's internal value conversion factor

mainFormHeight = Me.WindowHeight

For Each frm In Forms
  If frm.Name = ProductForm Then
    DoCmd.Close acForm, ProductForm
    Exit For
  End If
Next
DoCmd.OpenForm ProductForm
Forms(ProductForm).Refresh
items = DCount("*", "ProductListQ")

Set frmchild = Forms(ProductForm)
'Calc the required height of the chid-form
'based on number of items for selected supplier
intHeader = frmchild.Section(acHeader).Height
intFooter = frmchild.Section(acFooter).Height
'0.272 inch - product item row height
intH = intHeader + items * 0.272 * oneInchTwips + intFooter
intH = intH + oneInchTwips '- one inch margin from bottom
'Move and resize the height of the child form
'4.275 inches to the right from left of the Application Window
'1.25 inches - arbitrary value taken for bottom margin
DoCmd.MoveSize 4.275 * oneInchTwips, mainFormHeight - intH, , (items * 0.272 + 1.25) * oneInchTwips

Company_Click_Exit:
Exit Sub

Company_Click_Err:
MsgBox Err & ": " & Err.Description, , "Company_Click()"
Resume Company_Click_Exit

End Sub

Private Sub Form_Current()
Me.Refresh
End Sub

Note: Don't forget to change the Overlapping Windows option in the Access Option settings mentioned in paragraph two from the top.

  1. Open Supplier List Form.
  2. Click on the Supplier ID Field (with the Company column heading) of any record, to open the  Supplier products List to display, in Resized Product List Form, and Moved to its specified location.

Download the Demo Database.


Download Demo MoveSize Demo.zip

Share:

Form Background with Gradient Color

Introduction

Form background pictures are not only used for giving the Form a different look, but also for other useful purposes as well.

For example, if the user is keying in data from a pre-printed Document like a Telephone Bill or Electricity Bill etc.; it is a good idea to scan an image of this Document and use it as the Data Entry Form's background picture. The Data Fields can be positioned in appropriate locations on the image matching the physical Document so that it will be easier for the user to find information on the Document and key in exactly the same locations on the Form.

Microsoft Access Form Wizard has several images to use as a background picture, but I didn't find the one that I need; the one with a gradient background. So, I thought I would make one of my own and use it.


Creating a Sample Image

The steps that I have taken are given below to create the image for the Form Background picture.

  1. Opened a new Microsoft Word Document and went through the following steps to create a Gradient Bit Map Image:
  2. Selected Format - - >Background - - > Fill Effects.
  3. Under Colors, options selected One Color.
  4. Clicked on the Color 1 drop-down control and selected a light color.
  5. Used the Darker/Lighter slider control to adjust the color tone.
  6. Selected the Horizontal Radio Button under Shading Styles.
  7. Clicked on one of the Style Variants.
  8. Clicked OK to apply the selected shade as the Background of the Word Document.
  9. Maximized the Word Document Window.
  10. Push the Prt Scrn key (at the top row right side). The full-screen image is now captured and copied to Clipboard.
  11. Opened MS-Paint Program. You can find this in Start - - > Programs - - > Accessories.
  12. Displayed the Toolbox (View - ->Toolbox or press Ctrl+T).
  13. Selected the Select Tool (the one at the top right with a rectangle picture).
  14. Drawn a rectangle from the left top corner of the gradient image area to the right bottom corner to select the image large enough for my Form's width and height. If you find the width is not enough to cover your form then change the Page Orientation of the Word Document into Landscape before capturing it in MS Paint.
  15. Use Ctrl+C to copy the selected area into Clipboard.
  16. Clicked outside the selected area to deselect the earlier selection.
  17. Selected Clear Image from Image Menu (Ctrl+Shift+N) to clear the Canvas. Inspected the right Scroll Bar to check whether it is positioned at the top or not. It was positioned slightly down so I dragged it up to the top. Similarly adjusted the Scroll bar at the bottom to the position at the left end.
  18. Pressed Ctrl+V to paste the copied image from the Clipboard.
  19. Clicked outside the pasted image to de-select it.
  20. Dragged the Sizing control at the bottom of the Canvas up and stopped at the edge of the pasted image to eliminate the white area of the canvas.
  21. Similarly adjusted the right side of the image.
  22. Selected Save from File Menu to save the image at a convenient location with a name.

If you were with me so far then we will enter into the next stage of implementing the gradient image as a background picture. But, there are some limitations to using images like this one as a background picture, but we can overcome this with a little trick. If you are thinking about the increase in the size of the database then you are in the right direction. If you are considering the idea of saving the image in .jpeg or .GIF format; then you are far ahead of me. Saving the image in .GIF format in MS Paint will not give you a good quality image. If you have software like Adobe Photoshop then you can make better quality .GIF images.

We will do another trick to use the .bmp image itself to maintain the background image quality without affecting the database size.

But, first, let us see how to apply the gradient image as the Form's Background picture.

Inserting the Image on the Form Background

Before opening your database if you would like to check the present size of your database you may open Windows Explorer, check and note down the current size of the database. I suggest you better Compact the Database before you take its file size. You may check it again after the background picture is added to a Form to find the difference.

  1. Open your Database and open a Form in Design View or create a new one.
  2. Display the Property Sheet of the Form (View - -> Properties).
  3. Find the Picture Property and click on it.
  4. Click on the build button (. . .) at the right side of the Property Sheet and click on it to browse to the location of the image you have saved and select the image.
  5. The next four Properties are used for formatting and displaying the Background Picture.
  • Picture Type
  • Picture Size Mode
  • Picture Alignment
  • Picture Tiling

Linked or Embedded Image Methods

The Picture Type Property has two choices Embedded or Linked to select from and the default value is embedded. The embedded option is provided for invoking the default Image Editor Program from within MS-Access by double-clicking on the image and making changes to the image if needed. To facilitate this; the necessary image editor program invocation information is also stored along with the image in the database, resulting in a substantial increase of the database size for each image you add this way on your Form or Report. But, the background image inserted this way cannot be edited by double-clicking on the background as we do on the inserted image as an OLE object, on the Form.

A better choice is the Linked option which will keep the image outside and the database size is not affected. In either case, the image should go along with your database in the same location as the image wherever you take your database.

The Picture Size Mode has three options Clip (default) displays the picture in actual size wherever you position it by setting one of the Picture Alignment property Values (Top Left, Top Right, Bottom Right, Bottom Left, Center, and Form Center). The Center option will position the image vertically and horizontally centered on the Form and the Form Center option will position the image vertically centered on the left side.

The Stretch/Zoom/Tile Picture Methods

The Stretch Picture Size Mode option will stretch the image to fit the dimension of the Form and result in distortion of the image if it is a picture of something.

The Zoom Picture Size Mode option will attempt to maintain the right proportions of the image when stretched to match the dimensions of the Form. But both the Stretch and Zoom options will show stretch marks when the Form is restored to its original size. This is very much evident when a .jpeg or .GIF image is used.

The One Pixel Width Image

We can modify our earlier .bmp file to a one-pixel width image and Tile it across the Form that will give a good quality gradient picture effect and the image size also will be small.

  1. Open the earlier saved .bmp image in MS Paint Program.
  2. Select Attributes. . . from Image Menu.
  3. Change the Width value to 1 and let the Height value remain without change.
  4. Check the Units selected is Pixels.
  5. Save the image with a different name.
  6. Change the name of the background image to the new image in the Picture property of the Form.

    Caution: Do not attempt to use the Picture Size Mode property options Stretch or Zoom; MS-Access will hang up.

  7. Set the Picture Tiling Property Value to Yes and save the Form.
  8. Open the Form in Normal View and enjoy your creation.
Share:

Drill-Down Inquiry Screen-2

Continuation of Last Week's Post.

This is the second part of the earlier Article: Drill-Down Inquiry Screen. If you have landed straight on this page, then please visit the above link and go through the first part of this article before continuing..

I hope you have downloaded the sample database from the first part of this article published last week and tried it out. Then you have a general idea of what we are going to explore further on this topic.

Here, we will design three small Forms, assemble them on the Main Form on a Tab Control, and use a few lines of code to refresh the Main Form to update the contents of the Sub-forms before bringing them into view.

First, we will design the top layer of the Form for the Employee-wise Order Summary Information.

03_Employee_Summary Form

  1. Select the Query 03_Employee_Summary.

  2. Design a Tabular Form (continuous form) like the sample given below. You may use the Form Wizard from Insert Menu to create the Form quickly, but it may insert background images and the controls may be created with shadows and other settings. In that case, you have to modify the design to look like the sample given below.


    Click to Enlarge

  3. Select all the Data Fields together by clicking on the left side border of the Detail Section or click on the Detail Section somewhere and drag the mouse over all the controls to select them together.

  4. Display the Property Sheet (View - ->Properties).

  5. Change the following Property Values:

    • Enabled = No
    • Locked = Yes
    • Tab Stop = No
    • Back Color = 12632256
    • Special Effect = Raised
  6. Expand the Detail Section of the Form down to get enough room to draw a lengthy Command Button.

  7. Draw a Command Button as wide as the full length of all the Fields above, so that we can cover the fields by placing it over them.

  8. Display the Property Sheet of the Command Button.

  9. Change the following Property Values:

    • Name = cmd1
    • Transparent = Yes
    • Hyperlink SubAddress = #
  10. We must make the Command Button's height the same as of the Fields above. Click on the Command Button to select it, hold the Shift Key and click on any one of the Fields above to select it along with the Command Button. Alternatively, you can click and drag over the Command Button and any one of the fields above.

  11. Select Format - -> Size and select Shortest, if the Command Button's height is more than the field, or select Tallest to make the Command Button as tall as the fields above.

  12. Drag the Transparent Command Button and place it over the Fields above.

  13. To make sure that the Transparent Command Button stays above all the data fields, select Format - - > Bring to Front.

  14. Now, reduce the Detail Section height, but there must be a little gap above and below the Data Fields.

  15. Draw a Text Box in the Form Footer Section below in the same position of the TORDERS field in the Detail Section and write the following expression in the Control Source Property:

    =Sum([TORDERS]) 
  16. Change the Caption of the child label to Total Orders.

  17. Create a Label at the Header of the Form and change the Caption value to EMPLOYEE-WISE ORDERS SUMMARY. Change the font size to 12.

  18. Display the Code Module of the Form (View - -> Code), Copy and paste the following VBA lines into the Module:

    Private Sub cmd1_Click()
    Me.Parent.Refresh
    Me.Parent.Tabctl0.Pages(1).SetFocus
    End Sub
  19. Save the Form with the Name: 03_Employee_Summary.

04_Order_ListQ Form

  1. Select the Query 04_Order_ListQ and create a Tabular Form (continuous Form) as we did at the top.

  2. Change the design to look like the sample Image given below:

  3. Click to Enlarge

  4. Select all the fields as we did earlier and change the following Property Values:

    • Enabled = No
    • Locked = Yes
    • Tab Stop = No
    • Back Color = 16777215
    • Special Effect = Flat
  5. Follow Step-6 to 8 given above.

  6. Change the following Properties of the Command Button:

    • Name = cmdOrder
    • Transparent = Yes
    • Hyperlink SubAddress = #
  7. Follow Step-10 to 13 explained above. Reduce the height of the Detail Section without leaving the gap above and below the fields.

  8. Create a Command Button at the Footer Section of the Form.

  9. Display the Property Sheet of the Command Button and change the following Property Values:

    • Name = cmdMain
    • Caption = Goto Main
  10. Expand the Header Section of the Form and drag the Field Headings down to get enough room to create a Heading to the Form.

  11. Add a Text Box above the Field Headings and write the following expression in the Control Source Property of the Text Box:

    = "Order List of " & [EmpName]
  12. Display the Code Module of the Form (View - - >Code), Copy and paste the VBA Code given below into the Module and save the Form with the name 04_Order_ListQ.

Private Sub cmdMain_Click()
    Me.Parent.Tabctl0.Pages(0).SetFocus
End Sub

Private Sub cmdOrder_Click()
   Me.Parent.Refresh
   Me.Parent.Tabctl0.Pages(2).SetFocus
End Sub

05_Order_DetailQ Form

  1. Select the Query 05_Order_DetailQ and Create a Tabular Form.

    Here, we don't need the Transparent Command Button. Change the design of the form to make it like the sample image given below.

  2. Create a Text Box in the Form Footer below the Quantity field and write the following expression in the Control Source Property:

    =Sum([Quantity])
  3. Create another Text Box in the Form Footer below the ExtendedPrice Column and write the following expression in the Control Source Property:

    =Sum([ExtendedPrice])
  4. Create a Command Button in the Form Footer below the TextBoxes.

  5. Change the following Property Values of the Command Button.

    • Name = cmdBack
    • Caption = << Back

  6. Display the Code Module of the Form (View - -> Code), copy and paste the following lines into the Module:

    Private Sub cmdBack_Click()
        Me.Parent.TabCtl0.Pages(1).SetFocus
    End Sub
  7. Save the Form with the Name 05_Order_DetailsQ.

Now, we are ready for the Main Form Inquiry_Main to assemble all the three Sub-Forms on a Table Control and make the Tab Control invisible.

Inquiry_Main Form

  1. Select the Parameter Table Date_Param, select Form from Insert Menu, and select Design View from the displayed list.

  2. Select the Tab Control Tool from the Toolbox and draw a Tab Control on the Detail Section of the Form.

  3. Check the sample image given below. The Tab Control will have only two pages when created, but we need one more page.

  4. Click to Enlarge

  5. Click on the Tab Control to select it and select Tab Control Page from Insert Menu.

    You may select each Page of the Tab Control, display their Property Sheet, and Change the Caption Property value of Page1, Page2, and Page3 as Summary, Orders, and Order Detail respectively, if needed. It is used for reference purposes only and will not use those names anywhere or display them either.

    The next step is to drag and drop the Sub-Forms (03_Employees_Summary, 04_Order_ListQ, and 05_Order_DetailQ) one by one, on the Tab Control Pages.

  6. Position the Database Window with the Forms Tab active and the Inquiry_Main Form with the Tab Control side by side.

  7. Drag and drop the 03_Employee_Summary Form on the Tab Control Page1.

  8. You may delete the child label attached to the Sub-Form. Re-size the Sub-Form and Tab Control to display the contents properly on the Screen. You may save the Form with the name Inquiry_Main and open it in Normal View to check as to how the Information is displayed on the Sub-Form. Check whether the Form and Tab control size need size increase or decrease is necessary or not. Leave some gap between the Tab control and the top of the Detail Section of the Form to insert a few TextBoxes for StartDate and EndDate fields and two more Text Boxes for control purposes. We need space for a heading above these controls as well.

  9. When you are satisfied with the Sub-Form dimension of the design, click on the Sub-Form, and display the Property Sheet. Take a piece of paper and note down the following Property Values for resizing and positioning the other two forms we are going to insert into Page2 and Page3 of the Tab Control.

    • Top
    • Left
    • Width
    • Height
  10. Right-click on the Sub-Form and select Copy from the displayed Shortcut Menu.

  11. Select Tab Control Page2, press and hold Shift Key, right-click on the Tab Control Page2 and select Paste from the displayed menu.

    The pasted control will be an empty form displaying the Source Object Name of the copied Form with white background.

  12. Display the Property Sheet of the Form and change the Source Object Property value to 04_Order_ListQ. After this change, the Form will appear on the Tab Control Page2.

  13. Change the dimension property values to the same value you have noted down. Since you have copied the Form (no drag and drop from Database Window); you need to change only the Top and Left Property Values, Width, and Height values will be the same. If not, then change it.

  14. Follow Step-9 to 12 above to bring in the 05_Order_DetailQ Form to the third Page of the Tab Control.

  15. Display the Field List (View - ->Field List), if it is not visible.

  16. Drag and drop StartDate and EndDate fields above the Tab Control, create labels above and left, and change their Caption values as shown on the design above.

  17. Create a Command Button to the right of the EndDate field and change its property values as given below:

    • Name = cmdRefresh
    • Caption = <<Refresh Screen

    The Date Fields we have created and another two TextBoxes, which we are going to create, will be referenced in the Queries we have created earlier to filter the data for the Forms to display. Even though this refresh action is not necessary, as we do that before we display the results, it is an additional feature to refresh the changes manually by the User.

  18. Create a Text Box to the right of the Command Button and display its property sheet and change the following Property Values:

    • Name = EID
    • Control Source = =[03_Employee_Summary].Form!EmployeeID
    • Visible = No
  19. Create another Text Box below the earlier one and change the property values as given below.

    • Name = OID
    • Control Source = =[04_Order_ListQ].Form!OrderID
    • Visible = No
  20. Create a heading on top of the Form with the Caption Value SALES PERFORMANCE INQUIRY.

  21. Create a Command Button below the Tab Control and change the following property values:

    • Name = cmdQuit
    • Caption = Quit

    Now, we are going to make the Tab Control disappear, well the Tab Control will not look like a Tab Control after the change, to be exact.

  22. Click on the Tab Control, display the Property sheet, and change the following property values:

    Caution: Immediately after you change the Property values make a point to click the Save Toolbar Button or select Save from File Menu to save the changes. There is a tendency to lock up the Form in Office2000 if you attempt to do anything else.

    • Back Style = Transparent
    • Multirow = No
    • Style = None
  23. Display the Code Module of the Form, copy, and paste the following few lines of code into the Module:
    Private Sub cmdQuit_Click()
      DoCmd.Close acForm, Me.Name
    End Sub
    
    Private Sub cmdRefresh_Click()
       Me.Refresh
    End Sub
    
    Private Sub EndDate_LostFocus()
    Me.Refresh
    End Sub
    
    Private Sub Form_Load()
    Me.TabCtl0.Pages(0).SetFocus
    End Sub
  24. We will finish the design by setting the Property Values of the Inquiry_Main Form. Display the Property Sheet of the Form and change the following Property Values:
    • Allow Edits = Yes
    • Allow Deletions = No
    • Allow Additions = No
    • Data Entry = No
    • Scroll Bars = Neither
    • Record Selectors = No
    • Navigation Buttons = No
    • Dividing Lines = No
    • Auto Re-size = Yes
    • Auto Center = Yes
    • Pop up = Yes
    • Modal = No
    • Border Style = Dialog
    • Control Box = Yes
    • Min Max Buttons = None
    • Close Button = Yes
    • Allow Design Changes = Design View Only
  25. Save the Form, open it in Normal View, and try out your creation.

    Note: If you face any difficulty in running your design normally, use the downloaded database as a reference, find the mistake and correct it.

Share:

Drill-Down Inquiry Screen

Introduction

Following is an image of a three-layer Inquiry Screen, designed for the Management, to keep track of the Northwind Traders' staff-level Sales Performance:

The top layer of the Form shows the Salesperson level summary of Orders and the Percentage of Total Orders. The Footer of the Form shows the Total of all Orders put together. When clicked on a particular Employee's record the individual Order-level Summary information will be visible on the main screen overlaying the earlier view. Check the image given below.

Several items can be ordered under a particular Order and details of all items can be displayed by clicking on one of the Order records. Check the image given below.

The above Form shows order-level details and summary information at the Footer of the form with the Total Quantity of all Orders and the Total Net Value of all items after discount. The Command Buttons at the footer of each form are used to switch the view back to the upper layer of the form.

The Date Parameter values at the top can be changed to display information for a different period.

First of all, I would like to assure you that there is no complicated VBA Code to drive this design except one or two lines of them here and there to refresh the controls and for switching from one layer of the form to the other. Other than that, the entire Screen works on Queries and Forms.

Designing the Forms

We need a few Tables from the Northwind.mdb sample database, to create six simple Queries, three Sub-Forms, and the Main Form to organize all of them together to present the information to the User.

Note: If you would like to see the Inquiry Screen in action before you design it, you can download it from the bottom of this post and perform a demo run. If you find it difficult to figure out how it works or how to put all of them together, then come back and go through the design task step by step so that you will know how simple or difficult it is and how each element of this trick works together with others.

Here, we will see the usage of Transparent Command Buttons again, which we have already seen in an earlier article with the same title.

We will have to split this topic into more than one Blogpost so that we can do the design task with ease. Even though I can show you how to do it in a few minutes, explaining that on paper with images and Property settings takes a lot more than that.

I know that, if you download the database and take a look at it first, you will be better prepared to find out more about it and ready to go through the design task with more interest.

The Design Task

So, let us start with the first part.

  1. Download the following Tables from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb (MSOffice2003 address, you can replace Office in place of Office11 if your Version of Access is 2000):
    • Employees
    • Orders
    • Order_Details
    • Products

    A Parameter Table.

  2. Create a Parameter Table with the following name and Structure:
    Parameter Table: Date_Param
    Srl. Field Name Data Type Size
    1. StartDate Date/Time  
    2. EndDate Date/Time  
  3. Open the Table and create a single record with the following StartDate and EndDate values and save the record:
    Parameter Table: Date_Param
    StartDate EndDate
    01-01-1996 31-12-1996
  4. Open new Queries and Copy the following SQL Strings into Query's SQL Editing Window and save them with the exact Name given for each of them. Create the Queries in the same order as given here as they have dependencies on Queries created first.
  5. Query Name: 00_Orders_MainQ
    SELECT Orders.*
    FROM Orders, Date_Param
    WHERE (((Orders.OrderDate) Between [StartDate] And [EndDate]));
    
  6. Query Name: 01_OrderCount1
    SELECT Employees.EmployeeID,
      [FirstName] & " " & [LastName] AS EmpName,
       Count([00_Orders_MainQ].OrderID) AS TORDERS
    FROM Employees INNER JOIN 00_Orders_MainQ ON Employees.EmployeeID = [00_Orders_MainQ].EmployeeID
    GROUP BY Employees.EmployeeID, [FirstName] & " " & [LastName];
    
  7. Query Name: 02_OrderCount2
    SELECT Count([00_Orders_MainQ].OrderID) AS TOTALORDERS
    FROM 00_Orders_MainQ;
    
  8. Query Name: 03_Employee_Summary
    SELECT [01_OrderCount1].*,
          [TORDERS]/[TOTALORDERS] AS PCNT
    FROM 01_OrderCount1, 02_OrderCount2;
    
  9. Query Name: 04_Order_ListQ
    SELECT [00_Orders_MainQ].OrderID,
            UCase([FirstName] & " " & [Lastname]) AS EmpName,
           [00_Orders_MainQ].OrderDate,
            [00_Orders_MainQ].RequiredDate
    FROM Employees INNER JOIN 00_Orders_MainQ ON Employees.EmployeeID = [00_Orders_MainQ].EmployeeID
    WHERE ((([00_Orders_MainQ].EmployeeID)=[Forms]![Inquiry_Main]![EID]));
    
  10. Query Name: 05_Order_DetailQ
SELECT [FirstName] & " " & [LastName] AS EmpName,
        [Order Details].OrderID,
        [Order Details].ProductID,
        [Order Details].Quantity,
        [Order Details].UnitPrice,
        [Order Details].Discount,
        (1-[Discount])*[UnitPrice]*[Quantity] AS ExtendedPrice
FROM (Employees INNER JOIN 00_Orders_MainQ ON Employees.EmployeeID = [00_Orders_MainQ].EmployeeID) INNER JOIN [Order Details] ON [00_Orders_MainQ].OrderID = [Order Details].OrderID
WHERE ((([Order Details].OrderID)=[Forms]![Inquiry_Main]![OID]));

Contd/-

Download


Download Demo InquiryDemo00.zip


  1. Multiple Parameters for Query
  2. Form Menu Bars and Toolbars
  3. Seriality Control - Missing Numbers
  4. Wave Shaped Reminder Ticker
  5. No Data and Report Error
Share:

Custom Calculator and Eval Function

Introduction.

When we think of a Calculator the image that comes into our mind is the traditional type with button labels 0-9 and with Arithmetic Operators. We have it on Windows Accessories Menu that can be converted into a Scientific Calculator as well. So we don't need to build that again in MS-Access.

We are considering another type of Calculator that can accept a User Defined Expression (consisting of Data Field Names, Constants, Arithmetic Operators, and Parenthesis to alter the Order of Calculations), evaluate that expression, and provide a quick result.

We don't need to embark on a fancy design task for this Calculator. All we need is a TextBox to enter the Expression, a Command Button, and a few lines of code. The result of the calculation can be displayed either in MsgBox or in another Text Box.

Before we start working on the Calculator Project, let us take a look at one of the built-in functions EVAL(), which is the main workhorse of our Custom Calculator. With this function we don't have to worry much about the calculation rules, like evaluating expressions in parenthesis first, exponentiation next, multiplication and division next (from left to right, if more than one operator), doing addition, and subtractions last, etc. All you need to do is to give the Expression in String Form into EVAL() Function and get the result as output, as simple as that.

EVAL() Function Usage

Want to try a few examples quickly to get a feel of it? Open the VBA Module Window (Alt+F11) and open the Debug Window (Ctrl+G). Type the following expressions in the Debug Window and press Enter Key:

? EVAL("2+3*4/2")

Result: 8

? EVAL("(2+3)*4/2")

Result: 10

? EVAL("log(SQR(625))/log(10)")

Result: 1.39794000867204

? Eval("10^" & "Eval('log(Sqr(625))/log(10)')")

Result: 25

? EVAL("Datediff('yyyy',Forms!Employees!BirthDate,date())")

Result: 45 (the Employees Form must be open)

EVAL() the Expression Parser

What we can see from the above examples is that you can write an expression using built-in Functions, Data Field References, and Numeric Constants in a TextBox.  The Eval() Function parses the expression and gives the result. It gives the User the ability to define an expression and do calculations with the use of Data Field values on the Form

Tip: The Eval() Function can do a lot more than simply evaluate the formula, it can run another Function, trigger the Click Event of a Command Button or Run a Macro. For more details and examples of Eval() Function search Microsoft Access Help in the VBA Window.

The sample Demo Project.

I have created a sample Database (you can download it from the bottom of this post) for an Auto Dealer who extends Credits to his Customers with a nominal charge of interest rate of 6%, 7%, or 8% repayable in 24, 36, or 60 installments slabs respectively. The Salesman has been instructed to deal with the Customers suitably to promote Vehicle Sales. The Salesman has to decide which slab of repayment is appropriate for a particular customer, the percentage of down payment, repayment schedule with the applicable interest rate. The Salesman has been given the freedom of allowing a discount up to a maximum of 20% on the Retail Price. 

An image of the Form he uses to run these calculations is given below:

This is a stand-alone Form (which can be linked to Table) with Unbound Text Boxes. The Text Box names are given as Labels to the left. While writing the Expression it is a must to qualify the control Names correctly, like Forms!CustomCalculator!Balance. It will not accept references like Me!Balance. It is difficult to use these lengthy names to build the expression when several Field names are involved in the Formula.

The VBA Code

We have a small VBA routine to accept the Text Box Names in square brackets [], to pick their value and replace them in the expression before submitting it to EVAL() function.

A Combo Box with Arithmetic Operators and Text Box Names in square brackets is also provided on the Screen for easy entry of expressions. The colored control below displays (displayed by the routine for information only) the replaced value of Text Boxes in the expression before submitting it to the EVAL() Function.

When the Calculate Command Button is clicked the result of the calculation is displayed in the Text Box with the dark background and the label Result. The VBA Code is given below:

Private Sub cmdCalc_Click()
'-----------------------------------------------------------
'Author : a.p.r. pillai
'Date    : November, 2008
'URL     : www.msaccesstips.com
'All Rights Reserved by www.msaccesstips.com
'-----------------------------------------------------------
Dim str As String, loc1 As Integer, chk As Integer
Dim strout As String, loc2 As Integer, loc3 As Integer
Dim strin As String
Const sqrleft As String = "["
Const sqrright As String = "] "

On Error GoTo cmdCalc_Click_Err

'change the reference if different
str = Me![Expression]

loc1 = InStr(1, str, sqrleft)
If loc1 > 0 Then
   strin = Left(str, loc1 - 1)
   strout = Left(str, loc1 - 1)
   loc2 = InStr(loc1, str, sqrright)
End If
Do While loc2 > 0
   strin = strin & Mid(str, loc1, (loc2 - loc1) + 1)
   strout = strout & Me(Mid(str, loc1, (loc2 - loc1) + 1))
   loc1 = InStr(loc2 + 1, str, sqrleft)
   If loc1 > 0 Then
     loc2 = InStr(loc1, str, sqrright)
      If loc2 = 0 Then
         MsgBox "Errors in Expression, correct and retry. "
         Exit Sub
      Else
         strout = strout & Mid(str, Len(strin) + 1, loc1 - (Len(strin) + 1))
         strin = strin & Mid(str, Len(strin) + 1, loc1 - (Len(strin) + 1))
      End If
   Else
     loc3 = loc2
     loc2 = 0
   End If
Loop

If Len(str) > loc3 Then
   strout = strout & Mid(str, loc3 + 1)
End If

'this line can be removed if not required
Me![parsed] = strout

'change the reference, if different
Me![result] = Eval(strout)

cmdCalc_Click_Exit:
Exit Sub

cmdCalc_Click_Err:
MsgBox Err.Description, , "cmdCalc_Click()"
Resume cmdCalc_Click_Exit
End Sub

Private Sub cmdReset_Click()
Me![Expression] = Null
Me![parsed] = Null
End Sub

Note: There is no validation check included in the Code to detect misspelled names or unbalanced parentheses etc. These shortcomings will automatically generate an error when the EVAL() function executes. The user will be able to review the expression, make corrections, and re-try.

Download

You can implement this program on any Form with a small change in str = Me![Expression] and Me![result] = Eval(strout) lines in the Code, if different names are used. Customize the Combo Box contents based on your input Field Names.


Share:

Data Editing And Security Issues

Introduction.

Data Entry and Editing are the major activities for maintaining the information up-to-date in databases before we are able to prepare any meaningful output for human consumption. It is important that we make these tasks easier for the User by providing Combo Boxes, Check Boxes, or Calculated Fields, like Payment-Due-Date after 30 days of Material-Delivery-Date and so on.

Another important aspect is data security. I am not referring to the protection against unauthorized access from outside, for which we have adequate built-in MS-Access Security features that can be put to use. Here, our emphasis is on unintentional changes or deletions of important information by Authorized Users.

For example, assume that our Employee database has a Memo Field that holds important detailed information on the educational, and past experience of employees. Normally when the Cursor (Insertion Point) moves into a field the entire field contents will get highlighted and selected. At this point, if the User's attention is drawn to something else and touches a key by mistake the field contents will be erased. If she is not aware of this or forgot to recall the lost data by pressing Ctrl+Z (Edit - -> Undo Typing) then it is lost forever.

Protecting from un-intended Changes.

We will concentrate on this particular behavior of the Cursor and how we can give some form of protection against such inadvertent mishaps. The Cursor behavior, while entering into a field, is governed by the settings in the Keyboard Tab of Options, in the Tools Menu. Find the three different options available under the Behavior entering field Options Group in the image below:

The first option Select the entire field is the default setting. One of the other two settings is the most preferable one to avoid the loss of information that we are focusing on. Out of the other two options, I prefer the last one, Go to the end of the field, why because even if you press the Delete Key or any other Key by mistake, the insertion point is at the end of the field contents, and nothing will happen to the existing information.

Since this is the global setting of MS-Access Application, the changes that you make here manually affect all the Forms in all Databases that you load into your machine. Conversely, the database that you have designed for your machine will not carry this option setting along with it when open on some other machine in a multi-user environment. Moreover, we may not need this setting to take effect on all Forms in the Database either.

So the best approach is to enable this feature through VBA Sub-Routines, wherever we need it on Forms, and turn it back to the default settings while the Form class. If your database is shared on a Network the Behavior Entering Field default settings can be different in other machines set by the User. We don't want to alter these settings permanently.

Following is the numeric value of each Behavior Entering Field Option setting:

Behavior | Description | Option Values.
  1. Select Entire Field - 0
  2. Go to Start of Field - 1
  3. Go to End of Field - 2

When we open a Form, where we need this change of behavior, we will go through the following steps to enable it and when the Form is closed put the default setting back in its place:

  1. Save the current default setting of Behavior Entering Field, before changing it.
  2. Change the setting to Go to the end of Field behavior for the current session of the Form.
  3. Reset it back to the saved value in Step 1 above before closing the Form.

We can achieve this with the following Event Procedures in the Form Module:

Option Compare Database
Dim DefaultBehavior As Integer

Private Sub Form_Load()
    DefaultBehavior = Application.GetOption("Behavior Entering Field")
    Application.SetOption "Behavior Entering Field", 2
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Application.SetOption "Behavior Entering Field", DefaultBehavior
End Sub

Copy and paste the above Code into the Form's Code Module and save the Form. The Dim DefaultBehavior As Integer statement must be placed in the Global area of the Module as shown above.

Try out the New Setting

Open the Form in normal View and try moving the cursor from one field to the other by tapping Tab Key or Enter Key. The insertion point will position at the end of the field contents.

Share:

Event Trapping Summary On Datasheet

Introduction.

How do we use Event Procedures like LostFocus(), and GotFocus() in Datasheet view?

How to display the Summation of numeric values on the Datasheet view?

For answers to both questions, we need a sample Table and a Datasheet Form.

Import the following Tables from the Northwind.mdb sample database from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb.

  • Order_Details
  • Products

We require the Products table also because it has a reference in the ProductID field of Order_Detail Table in a Combo Box. We don't want to leave the ProductID field empty on the Order_Detail Table, without the Products Table.

Design a Datasheet Form

  1. To Create a Datasheet Form, click on the Order_Detail Table, select Form from Insert Menu, and select Autoform: Datasheet from the displayed list of options in the Formwizard.

    A Datasheet form will be created and will open the Table contents in Datasheet View.

  2. Select Save As. . . from File Menu and give the name Order Details for the Form. The Datasheet Form will be saved, but it will not be closed.

  3. Select Design View from View Menu to change the Form in Design View mode.

The Form in Design View looks like the image given below or may be different depending on your version of MS-Access.

Doesn't matter how the Fields are arranged on the Form, whether in row format or Column Format the data will be always displayed in Datasheet format. The placement of Fields will not affect the way the data is displayed on the Datasheet but the Tab Order does.

The Tab Order of Controls

Let us try and find out how the Tab Order of Fields influences the Datasheet View.

  1. Change the Form into Datasheet View (View - - > Datasheet View) and check the order of fields appearing in there.

  2. Change the View into Design mode again and re-arrange the ProductID and UnitPrice fields by switching their places.

  3. Change the View back to Datasheet and inspect the data field order.

    There will not be any change in the Order of Fields displayed from the previous View. If you move the Cursor using Tab Key then it moves in the same order of the fields' placement as you have seen earlier before switching the fields.

  4. Change the Form back to Design View again.

  5. Select Tab Order. . . from the View menu. Click at the left border of the UnitPrice field on the Tab Order Control, click and drag it up and place it below the OrderID field.

    Tip: You can click on the Auto Order Command Button to re-arrange the Tab Order according to the field placements on the Form.

  6. Open the Form in normal view now and check the change of field placements.

The Unbound Text Box

We will add one Unbound Text Box on the Form to calculate the Extended Price after adjusting the Discounts of each item.

  1. Open the form in design view, if you have closed it.

  2. Drag the Form Footer Section down to get more room to place another Text Box below, or you can place it to the right of the Discount Field too.

  3. Create a Text Box and write the formula =(1-[Discount])*[UnitPrice]*[Quantity] in it.

  4. While the Text Box is still in the selected state display the Property Sheet (View - -> Properties).

  5. Change the Format Property value to Currency format. Change the Name Property value to Extended Price.

  6. Open the Form in normal view and check the newly added Text control heading on the top. It will be something like Text10:.

    In Datasheet View of Forms MS-Access uses the Caption of the Child Labels attached to the TextBoxes as Field headings. We have changed the Name Property of the Text Box to Extended Price, but that is ignored here.

  7. Now, change the Form into the design view and delete the Child Label attached to the Extended Price Text Box.

  8. Change to Datasheet view again and check the field name appearing on the top, it will be Extended Price now.

Datasheet Event Procedure

  1. To try an Event Procedure on the Datasheet view, copy and paste the following VBA Code into the Form's Code Module (View - -> Code to display the Code Module of the Form) and save the Form with the Code.

    Private Sub UnitPrice_LostFocus()
    Dim newUnitPrice As Double, msg As String
    Dim button As Integer
    
    button = vbQuestion + vbYesNo + vbDefaultButton2
    
    If Me![UnitPrice] = Me![UnitPrice].OldValue Then
       msg = "Replace UnitPrice: " & Me![UnitPrice].OldValue & vbCr & vbCr
       msg = msg & "with New Value: " & Me![UnitPrice]
    
       If MsgBox(msg, button, "UnitPrice_LostFocus()") = vbNo Then
            Me![UnitPrice] = Me![UnitPrice].OldValue
        End If
    End If
    
    End Sub
    

    We will attempt to trap the change in the UnitPrice field and will ask the user to confirm whether to retain the change or Cancel it.

  2. Open the Form in the datasheet view and make some changes in the UnitPrice Field and leave the Field by pressing Tab Key or Enter key.

A Message Box will appear asking for permission to retain the change or to cancel it.

Datasheets can be programmed with Event Procedures (Field level or Form level) for validation checks and display of information.

Displaying of Summary Information

Method-1

We will attempt to answer the second question we have raised on top of this page.

  1. Open the Order_Details Form in Design View.

  2. Drag the Form Footer Section down to get enough room to place two TextBoxes. Create two TextBoxes in the Form Footer Section.

  3. Write the formula =Sum([Quantity]) in the first Text Box.

  4. Display the Property Sheet of the Text Box and change the Name Property value to TOTALQTY.

  5. Write the formula =Sum((1-[Discount])*[UnitPrice]*[Quantity]) in the second Text Box.

  6. Change the Name Property Value to TOTALVALUE.

When we open the Order_Details Form in Datasheet View it will calculate the Summary Values in TOTALQTY and TOTALVALUE TextBoxes on the Footer of the Form, but we must do something to display it.

The first thought that comes into one's mind is to create a MsgBox and display the results in it on some Event Procedure of the Form. Besides, changes may take place on the records and they should reflect in the result summary values and we must be able to refresh the change before displaying it again.

We will implement this method before we settle down with a better one.

  1. Copy and paste the following Code into the Form's Code Module and save the Form:

    Private Sub Form_DblClick(Cancel As Integer)    
    Dim msg As String
         Me.Refresh
        msg = "Total Quantity = " & Me![TOTALQTY] & vbCr & vbCr
        msg = msg & " | Total Value = " & Format(Me![TOTALVALUE], "Currency")
    
         MsgBox msg
    End Sub
    
  2. Open the Form in Datasheet View.

  3. Double-Click on the Record Selector at the left border of the Form.

    A Message Box pops up with the Summary Values from the TextBoxes in the Form Footer Section.

  4. Make some changes to the Quantity/UnitPrice Fields and try Step 3 again. The change of Value will reflect on the Message Box.

  5. You can filter the Data on ProductID or on OrderID by Right-Clicking on these fields and selecting Filter by Selection or other Options available on the displayed Shortcut Menu and by executing Step-3 to get the Summary of selected records.

Method-2

After trying out the above method your response may be something like "Yah.. it serves the purpose, but it doesn't give the impression of a sophisticated method. After all it takes so many clicks and pop-up Message Boxes". I agree with you too.

With a small change of the above Code we can make the results the way you like them, I hope!

  1. Open the Form in Design View.

  2. Display the Property Sheet (View - - > Properties).

  3. Select the Mouse Move Property and select Event Procedure from the drop-down control.

  4. Click on the build (. . .) button on the right side of the Property to open the Form's Code Module.

  5. Cut the Code lines from within the Form_DblClick() Event Procedure:

    Private Sub Form_DblClick(Cancel As Integer)
    
    End Sub
    

    Leave the above lines alone and paste the Code into the Form_MouseMove() Event Procedure.

  6. Change the line that reads:

    MsgBox msg

    to

    Me.Caption = msg

    After the change the Code will look like the following:

    Private Sub Form_DblClick(Cancel As Integer)
    
    End Sub
    
    Private Sub Form_MouseMove(button As Integer, Shift As Integer, X As Single, Y As Single)
    Dim msg As String
        Me.Refresh
        msg = "Total Quantity = " & Me![TOTALQTY] & vbCr & vbCr
        msg = msg & " | Total Value = " & Format(Me![TOTALVALUE], "Currency")
        Me.Caption = msg
    End Sub
    
  7. Open the Form in Datasheet View and move the Mouse over into the data area by crossing the Field Headings or the Record Selectors on the left side.

  8. Check the Title Area of the Datasheet View and you will find the Summary information is displayed there. A sample image is given below:

Now you can try changing the field values or filtering the data and moving the Mouse over the Field Headings or Record Selectors at the left to get the result on the Title Bar instantly. No Message Boxes or Double-Clicks and what do you say about that?

Share:

List Box and Date Part Two

Continued from LIST BOX AND DATE PART ONE.

Continued from LIST BOX AND DATE PART ONE

We have seen that when working with date values in List Boxes we have to convert the selected value into one form or the other before they can be used for data processing tasks. We will try two more examples here with different date expressions. In all these cases the output is the same, but data selection parameters are used differently.

We can Filter Data using Data Range parameters, i.e., by entering Start-Date in a TextBox and End-Date in another, on a Report Parameter Screen, or by entering these values directly into Parameter Queries to filter data from the Source Table or Other Queries. But here we are trying to do it differently and get some familiarity in working with Date related expressions.

Modifying the Form.

  1. Open the Form LISTBOXDATE that we have created in the earlier example in Design View.

  2. Make a copy of the List Box and Paste it into the same area of the Form. Drag and place it on the right side of the Combo Box. See the sample image given below:

  3. Place the child Label on the top and display its Property Sheet (View - -> Properties). Change its Caption Property to List (Type-2).

  4. Click on the List Box and display the Property Sheet (if you have closed it) and change the following Properties:

    • Name = List2
    • Row Source Type = Value List
    • Row Source = 01;"Jan";02;"Feb";03;"Mar";04;"Apr";05;"May";06;"Jun";07;"Jul";08;"Aug";09;"Sep";10;"Oct";11;"Nov";12;"Dec"
    • Column Count = 2
    • Column Heads = No
    • Column Width = 0";1.5"
    • Bound Column = 1
    • Default Value = 1
    • Multi Select = None

    At this stage, your attention is drawn to a few property settings on this List Box. Check the Row Source Property setting. Here the List Box item values are inserted in pairs like 01; "Jan" for January and others. The Column Count property says there are 2 columns in this List Box. When the contents of the List Box are displayed, these values should appear in two columns, value 01 in the first column and Jan in the second. But here the value Jan from the 2nd column only shows up.

    The first column value is kept hidden with the Column Width Property settings 0";1.5". The first column width of 0" prevents the value from showing up in the List Box. The Bound Column = 1 property setting takes the selected value from the first column, even if it is kept hidden. The Default Value = 1 says to take Jan as the default value if nothing is selected by the User.

  5. Create two TextBoxes to the left of the List Box and below the other TextBoxes. Change the Caption of the child Labels to Method-2 and Method-3.

  6. Change the Property Values of the first Text Box that you have drawn now to the following Values:

    • Name = Method2
    • Control Source = =Format(DateSerial([cboyear],[List2],1),"mmm-yyyy")
  7. Change the Property Values of the second Text Box to the following Values:

    • Name = Method3
    • Control Source = =[cboYear]*100+[List2]
  8. Create a Command Button to the right of the existing two buttons and change the Property Values as shown below:

    • Name = cmdDisplay2
    • Caption = Display-2
  9. Create another Command Button and place it to the right and change the Property Values as given below:

    • Name = cmdDisplay3
    • Caption = Display-3
  10. Display the VBA Code Module of the Form (View - - > Code), and add the following VBA Code into the Module by copying and pasting it below the existing Code:

    Private Sub cmdDisplay2_Click()
    Me.Refresh
    DoCmd.OpenQuery "Display2_listbox", acViewNormal
    End Sub
    
    Private Sub cmdDispaly3_Click()
    Me.Refresh
    DoCmd.OpenQuery "Display3_listbox", acViewNormal
    End Sub
    
  11. Open a New Query in Design View without selecting any file from the displayed list. Open the SQL editing Window (View - -> SQL View), copy and paste the following SQL String, and save the Query with the name DISPLAY2_LISTBOX:

    SELECT Orders.*
    FROM Orders
    WHERE (((Format([orderdate],"mmm-yyyy"))=[Forms]![LISTBOXDATE]![Method2]));
    
  12. Open another New Query in Design View, Copy and Paste the following SQL String into the SQL editing window and save it with the name DISPLAY3_LISTBOX:
    SELECT Orders.*
    FROM Orders
    WHERE (((Format([orderdate],"yyyymm"))=[Forms]![LISTBOXDATE]![Method3]));
    

    Test Runs

  13. Open the LISTBOXDATE Form in normal view and click on the Command Button Display-2. The Query DISPLAY2_LISTBOX will open up with filtered output data using the current value in the Text Box with the name Method2. Select different Values in the Year Combo Box and the new List Box and try it again. Check the accuracy of the data filtered.

    NB: If the Query displays some error then try to link the essential Library Files to your Project. Visit the Page Command Button Animation for details of Library Files and follow the procedures explained there. The Orders Table doesn't have all twelve months' data except for the Year 1997. Check for the Range of months available in 1996 and 1998 years' data in this Table and select a month for available data for testing.

    Every time you select different Values in the List Box and the Combo Box, the value in the Text Box with the name Method2 and Method3 also changes. Close the Datasheet View of the Query before clicking the Command Button with a different selection of values.

  14. Click on the Command Button Display-3 to open the Query DISPLAY3_LISTBOX with the filtered output using the Text Box named Method3 Value.

NB: You must change the Visible Property of these TextBoxes set to No to keep them hidden from your Application Users. Study the expressions written in the TextBoxes and their corresponding Formula written in the Query Column to compare both values.

The List Box Settings

We have used the Multi-Select Property of the List Boxes in the first two Articles Selected List Box Items and Dynamic Query and Create List from Another List Box With Simple and Extended value settings respectively but here we have turned it off.

When you open the Form with the List Boxes with these settings (Extended or Simple) for the first time, the TextBoxes with the expressions using values from the List Boxes will be loaded with the values calculated from the default values, if the Default Value Property is set to some value. But when you click on an item in the List Box the TextBoxes will show either #Error or will be Empty.

When the Multi-Select Property is set to the Simple Value you can click on List items one by one to select one or more items or to deselect them when clicked on it again. When the Extended value is set, then you can select a series of items, adjacent to each other, by clicking the first and last item by holding the SHIFT Key. Or click and drag the Mouse over the list of items without the use of the SHIFT Key. You must hold CTRL Key and click on items one by one for random selections as we did with the Simple value setting.

We have used the Multi-Select Property value equal to None (default setting) because our examples presented here work on a single item from the List Box.

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