Sort Data by Double Clicking a Control

Private Sub CUSTNAME_Label_DblClick(Cancel As Integer)
If Me.OrderBy = "CUSTNAME DESC" Then
Me.OrderBy = "CUSTNAME"
Me.OrderByOn = True
Else
Me.OrderBy = "CUSTNAME DESC"
Me.OrderByOn = True
End If
End Sub

Displaying an Image in a Microsoft Access Form

In a previous article, we explained how you can include a bound picture on a Microsoft Access form. This article gives further details and a working example database of displaying images in the form, that will change for each record.

As per the previous example, we will be storing a path to the image location, we won't be actually storing the image in the database. It is not a good idea to store the image file in the database itself or you will very quickly suffer from database bloat. A better approach is to store the images in a dedicated folder and then simply enter the filename/path to the file in the database.

Our example file is based upon Property Details that we would store in something like an Estate Agents/Real Estate database system. We may have a form that appears similar to the following example:

Showing the completed form, containing image related to the record.

The information contained above is stored in various tables in our database. Our tblProperties table stores that main Property related details, including the path to the image stored in a folder on our PC. The table includes the following fields:

The Property details table, containing the PhotoLink field that stores the path to the image

We have locked the Photo field in the form, so to add an image to the form we need to click on the "Add Image" button. This opens up the Browse Files dialog box (see How to Use the Common Dialog API in an Access Database) and allows us to browse for an image file on our system.

Once we have selected a file, this inserts the filepath name into the Photo text box:

Showing the file path entered into the Photo field.

After updating the Photo field, the following AfterUpdate event is called:

Private Sub memProperyPhotoLink_AfterUpdate()
    setImagePath
    Forms![frmProperties].Form.Requery
End Sub

The setImagePath function is called (shown below), before the form.Requery action re-queries the form, to display the image.

Function setImagePath()
    Dim strImagePath As String
    On Error GoTo PictureNotAvailable
    strImagePath = Me.memProperyPhotoLink
    Me.memProperyPhotoLink.Locked = True
    Me.memProperyPhotoLink.Enabled = False
    Me.ImageFrame.Picture = strImagePath
    Exit Function
PictureNotAvailable:
    strImagePath = "C:\db_Images\NoImage.gif"
    Me.ImageFrame.Picture = strImagePath
End Function

The image is then displayed in the Image Frame, and will change with each record, providing that an image is assigned to that record. If there is no image, we display a message (image), informing the user that there is no current image available.

You can download, test and view the source code for this database example (including sample images), by downloading the database from either the Microsoft Access Forms page or the Microsoft Access Downloads page.

http://www.databasedev.co.uk/image-form.html

Saving a record through code

If you need to force a record to be saved, normally in order that you can then use that record for something, such as being printed in a report, then you have three options available:

• Using a DoMenuItem method. These have now been effectively replaced in Access 97, and are not automatically replaced when a database is converted between versions.

• Using DoCmd.RunCmd acCmdSaveRecord

• Using the Dirty property of the form. This is the recommended method, as you can firstly only force a save if needed, and secondly, you are guaranteed of ensuring that the correct form has its data saved:

  Private Sub cmdSave_Click() If Me.Dirty=True Then Me.Dirty=False End Sub

http://www.applecore99.com/frm/frm025.asp

Spell checking a Control in a Form

If you have the full version of Office installed, and want to use the Spell Checker to check that data has been entered correctly rather than using AutoCorrect to force changes, you can use code similar to this in the AfterUpdate event for the Control:

Private Sub txtDescription_AfterUpdate() If Len(Me!txtDescription & "") > 0 Then With Me!txtDescription .SetFocus .SelStart = 0 .SelLength = Len(Me!txtDescription) End With DoCmd.SetWarnings False DoCmd.RunCommand acCmdSpelling DoCmd.SetWarnings True End If End Sub

I use DoCmd.SetWarnings so that the user doesn't get a message box popping up saying that the Spelling Check is complete.

http://www.applecore99.com/frm/frm045.asp