Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

External References in Conditional Formatting Expression

Introduction

Normally, we use a Form/Report Text box value in Conditional Formatting expressions, to set the Font or Background color of a control.

For example:

FIELD VALUE IS BETWEEN 1 and 10  - whatever formatting you define will be displayed on the selected field, provided the field value is between 1 and 10.

OR

FIELD HAS FOCUS – the conditional formatting becomes visible when the field becomes active.

OR

EXPRESSION IS [ID]=5 OR [LastName] = "Nancy" – formatting is applied to a field, depending on the current values of two other fields used in the expression.

The key values in capital letters are selected from a drop-down control on the Conditional Formatting Dialog Control. We will take the last example of our new method of reference and let us see how it can be used differently. 

In the above expression, both [ID] and [Last Name] controls are on the same Form/Report and refer to the current record values on the Form/Report.  This is the normal procedure.

A Different Approach

But, both values referenced in the expression can come entirely from other objects like: from the control of another open Form or from a Field of another Table.  As far as the table is concerned, it is not necessary to keep the table open like the other Form, because we are going to use the Dlookup() Function in the expression to pick the value from the table.

When the above example is re-written with external field reference values it will look like the expression given below.

EXPRESSION IS Forms![Orders]![OrderID] = Dlookup("Order_ID","tbl_OrderParam")

Assume that the above Conditional Formatting expression is written on a Field (say the Amount field) of frm_myForm.

If the current record OrderID value of Orders Form (the second form in open mode) and the Order_ID value of tbl_OrderParam Table (the expression assumes that the Parameter Table has only one record in it) matches then apply the defined conditional format on the active Form's (frm_myForm) Amount Field.

Try it out yourself and find out how it works.

Earlier Post Link References:

Share:

Storing Bitmap Images in OLEObject Attachments Fields

Introduction.

MS-Access Tables have the OLE Object/Attachment Field to store and display images on Forms/Reports.

Example: Storing the employee photo or Product image on the data table.

Depending on the Staff size of your Company or the product items in stock, the number of images stored directly in their respective table can increase the database size considerably.  In Access 2007 the Bitmap Images (.BMP image files) stored in the Attachment field are automatically compressed and reduced to the size of a JPG image.  In either case, the database size will increase, with the addition of each image in the table.  If you have hundreds or thousands of images to store, then you can imagine what will happen.  The maximum size of a database can be only 2 GigaBytes.

Identifying the Image with its Related Record.

But, if you can foresee this issue of your Project and plan to store the required images in a dedicated folder on the disk (Server or Local depending on the User requirements) then you don’t have to bother about the database size at all.  But, you should be able to identify and pick the correct image, related to a particular record from the disk easily, and display it on a Form or Report, whenever the need arises.

Organizing the Images.

It is important that we organize the images on disk in such a way that we can easily identify which image belongs to which record.  Once this is properly planned and executed we can pick the correct image and display it on Form/Report, in the Image Control very easily.

The easiest approach is to give names to images with some unique field value of the related table, like the Employee ID of the Employee record.  For example, an employee’s photo name can be the Employee’s ID value, like 1.bmp, 2.bmp, etc.  Each record in the Products Table will have Product Codes as unique values and images can be named with the product code of each item.

When a particular employee’s record is currently on the Form we can read the employee code from the form and add the image extension (like Me![ID] & “.jpg”) to create the image name and load it from the disk into an image control on the Form. 

If you prefer to create an image type smaller in file size, to save disk space, then you may choose any one of these types: png, jpg, or gif. GIF images are lesser in size, but image quality may not be good.  Select only one of these image types and save all the images in that type, say jpg, then the program will be a simple routine, to load the image into an image control on the form/report. 

We need only a small Sub-Routine that runs on the Form_Current() Event Procedure to load the picture into the Image control.

Prepare for a Trial Run.

Let us prepare for a trial run of a small Program.

  1. Import the Employees Table from Microsoft Access Northwind.mdb sample database.

  2. Use the Form Wizard to create a Quick Form in Column Format.

  3. Move or resize the Fields (or remove some unwanted fields) so that we can add an Image Control on the Form, big enough to display sample images, which you are going to create now.

    A sample Employees Form Design is given below, with the Image Control on it:

  4. Select the Image Control from the Toolbox and draw an Image Control on the Form, as shown above.

  5. Display the Image Control’s Property Sheet (F4) and change the Name Property value to ImgCtl.

  6. Save the Form with the name Employees.

    Note: As I have mentioned earlier, the Employee ID field values are 1,2,3, etc. We will create a few images with the names: 1.jpg, 2.jpg, 3.jpg, etc.

    If you were able to Import the Employees Table from Northwind.mdb then you are lucky, you have the employee’s photo bitmap images in the OLE Object field.  You can save these images on the disk.  I am using Access2007 and I will explain the procedure of Access2007 to save the image from Employees Table to disk.  If you are using any other Access Version the menu options may be different.

    • Open Employees Table in Datasheet View.

    • Find the Photo field of the first record, the Employee ID of this record is 1.

    • Right-Click on the Photo field with the Bitmap Image caption, and select Open from the Bitmap Image Object option.  The image will open in the Paint program.

    • Before saving the image to disk, create a new folder C:\images.

    • Use Save As… option to save the image in folder C:\images with the name 1.jpg

    • Save a few more employee photos in this way.

    If you could not save images from Employees Table then open any image from your disk in Paint Program and save them as 1.jpg, 2.jpg, 3.jpg, etc., in C:\images Folder.

  7. Open the Employees Form (you have saved in Step-6) in Design View.

  8. Press ALT+F11 to display the VBA Window.

  9. Copy and paste the following code into the VBA Window of the Form, below the Module Global Option: Option Compare Database.

    Private Sub Form_Current()
    Dim strImagePath As String, pic As String
    On Error GoTo Form_Current_Err
    'image folder
    strImagePath = "c:\images\"
    'create image name
    pic = Me![ID] & ".jpg"
    strImagePath = strImagePath & pic
    'validate image name and location
    If Len(Dir(strImagePath)) > 0 Then
    'image found, load it into the image control
      Me!ImgCtl.Picture = strImagePath
    Else
    'image not found, make image control empty.
      Me!ImgCtl.Picture = ""
    End If
    
    Form_Current_Exit:
    Exit Sub
    
    Form_Current_Err:
    'Not necessary to display a message here
    Resume Form_Current_Exit
    End Sub
  10. Save and close the Form.

    The Trial Run.

  11. Open the Form in Normal View.

    If everything went on well then you will see the first employee photo in the image control.  The Sample Screenshot of  the Employees Form with the photo is given below:

  12. Use the record navigation control to move to the next record and display other images also.

Image Display on Report.

A sample Employees' Report Design, with Image Control, is given below.

  1. Design a Report using a few fields from the Employees table, as shown above, and see that you are using the Employee [ID] Field on the Report.  This is important because we need this number to create the image name corresponding to the record in print.

  2. Insert the Image Control on the Report, as shown in the above Image.

  3. Display its Property Sheet and change the Name Property value to ImgCtl.

  4. Click somewhere on the empty area of the Detail Section of the Report.  If you have closed the Property Sheet then press F4 to display it.

  5. Select the On Format Event Property and click on the build ( . . . ) Button to open the VBA Window of the Report.

  6. Copy and Paste the following code and replace the Detail_Format() Event procedure opening and closing statements:

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Dim strImagePath As String, pic As String
    On Error GoTo Detail_Format_Err
    'image folder
    strImagePath = "c:\images\"
    'create image name
    pic = Me![ID] & ".jpg"
    strImagePath = strImagePath & pic
    'validate image name and location
    If Len(Dir(strImagePath)) > 0 Then
    'if found load the image into the image control
      Me!ImgCtl.Picture = strImagePath
    Else
    'make image control empty.
      Me!ImgCtl.Picture = ""
    End If
    
    Detail_Format_Exit:
    Exit Sub
    
    Detail_Format_Err:
    'Not necessary to display a message here
    Resume Detail_Format_Exit
    
    End Sub
  7. Save the Report and close it.

    The Trial Run of the Report.

  8. Open the Report in Print Preview.

    The Sample Report Preview is given below:

If you want to give more flexibility to your project, with all the four image types (bmp, png, jpg, and gif) then you may use the following modified Code:

Private Sub Form_Current()
Dim strImagePath As String, pic As String
Dim strImagePathName As String, strI(0 To 3) As String
Dim j As Integer, strType As String

On Error GoTo Form_Current_Err

strI(0) = ".bmp"
strI(1) = ".png"
strI(2) = ".jpg"
strI(3) = ".gif"

strImagePath = "c:\images\"
pic = Me![ID]

strType = ""
For j = 0 To UBound(strI)
  strImagePathName = strImagePath & pic & strI(j)
  If Len(Dir(strImagePathName)) > 0 Then
     strType = strI(j)
     Exit For
  End If
Next
  
strImagePathName = strImagePath & pic & strType

If Len(strType) > 0 Then
  Me!ImgCtl.Picture = strImagePathName
Else
  Me!ImgCtl.Picture = ""
End If

Form_Current_Exit:
Exit Sub

Form_Current_Err:
'Not necessary to display a message here
Resume Form_Current_Exit

End Sub

Note: Report must be in Print-Preview mode to view the images on Report. The Images may not appear in Layout View.

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