Conditional Image on a Continuous Form (Reminders)…

Since Microsoft Access 2007 getting a different Image to show up for each record is pretty easy, you just need to bind the Image Control to the field that holds the path to the image and you are done. However, I wanted a conditional image on my Continuous Form based on whether there was a Reminder set… hmm.

Continuous Forms are great for listing things like Tasks or Orders even Customers but how to show a Reminder for a particular Order? You know something extra to be done when processing the Customer’s Order.  Putting that information under Order Notes doesn’t really help because you’re dependent on remembering to open the notes and then sift thru them to find the important one… odds are that’s not going to happen.

So, let’s create a Reminder…

For this example, we are going to be using Orders on the Order Details side. (Don’t forget to change Field Names to match the ones in your database!)  Whichever Table you decide to use it MUST have a Primary Key and in this example, it MUST be NUMERIC.  If you are using TEXT as a Data Type for your Primary Key, all code will need to be adjusted accordingly.

Step 1

To your table add two fields…

tblOrderDetails (My Table)
odReminder (Yes/No)
odReminderImagePath (Text, 255 or Short Text)

Step 2

Add tblReminders with the fields listed below…

rReminderID (Primary Key, Autonumber)
rID (Number, Long Integer) – This will hold the Primary Key for whichever Table(s) you are using the Reminders with.
rReminder (Memo, Rich Text or Long Text, Rich Text)
rShow (Yes/No)
rDate (Date/Time) – optional, if you want to know when the reminder was created
rNetworkID (Text, 50 or Short Text) – optional, if you want to know who created the reminder
rSystemForm (Text, 50 or Short Text) – This will only be needed if you plan to use the Reminder in multiple areas, such as, Orders and Tasks.

Step 3

Find two images, one for ON and one for OFF and place them in a shared folder on the Network preferably in a sub directory of the Backend, i.e. ServerLetter:\DirectoryName\Images. You will also need them on your Command Buttons on Step 4.  (To put a custom image on a Command Button click here.)  My favorite place to find images is Icon Archive.

Step 4

Create your Reminder Form…

Set the Recordsource for your Form:

SELECT tblReminders.rReminderID, tblReminders.rID, tblReminders.rReminder, tblReminders.rShow, tblOrderDetails.odReminder, tblOrderDetails.odReminderImagePath FROM tblOrderDetails INNER JOIN tblReminders ON tblOrderDetails.odOrderDetailID = tblReminders.rID;

Then place two Command Buttons on your Form in the Form Header section, one with the ON image named cmdOn and the other with the OFF image named cmdOff and place them on top of each other.

Then add an UNBOUND Text Control named txtFocus and place it in the Form Header, see the purple oval. Also, set it as the first Tab Stop and leave it Visible.

Add the other Controls (use Naming Conventions to correspond to the below or the code will not work, i.e. txtID, chkShow, etc.) in the Detail section and hide, Visible = No,  except for the rReminder which is where the reminder will be entered. This is a one-to-one so make sure your Form is set as a Single Form.

In the Forms On_Load Event Procedure place…

    If Me!chkShow = False Then
        Me!cmdOff.Visible = True
        Me.cmdOn.Visible = False
    Else
        Me!cmdOff.Visible = False
        Me.cmdOn.Visible = True
    End If

In the On_Click Event Procedure of the two Command Buttons cmdOn and cmdOff place (Yes, they it is the same code for both buttons.)…

    If Me!chkShow = False Then
        Me.chkShow = True
        Me.txtFocus.SetFocus
        Me!cmdOff.Visible = False
        Me.cmdOn.Visible = True
        Me.chkReminder = True
        Me.txtReminderImagePath = "Full Path to YOUR image goes here!"
        ‘Toggle the Command Buttons on the Order Details Form
        Forms![frmOrders]![sfrOrderDetails].Form![txtFocus].SetFocus
        Forms![frmOrders]![sfrOrderDetails].Form![cmdOff].Visible = False
        Forms![frmOrders]![sfrOrderDetails].Form![cmdOn].Visible = True
    Else
        Me.chkShow = False
        Me.txtFocus.SetFocus
        Me!cmdOff.Visible = True
        Me.cmdOn.Visible = False
        Me.chkReminder = True
        Me.txtReminderImagePath = ""
        ‘Toggle the Command Buttons on the Order Details Form
        Forms![frmOrders]![sfrOrderDetails].Form![txtFocus].SetFocus
        Forms![frmOrders]![sfrOrderDetails].Form![cmdOff].Visible = True
        Forms![frmOrders]![sfrOrderDetails].Form![cmdOn].Visible = False
    End If

Set the Form as a Pop Up, then save, and close. (Oh, you probably want to take a peek first!)

Side note: While I added Spell Check, an Eraser and a Close button to mine they are not needed to make this work, so, consider those optional.

Step 5

Open your Orders/Order Details Form in Design Mode and on the Order Details Subform (many side) add two Command Buttons on your Form, one with the ON image named cmdOn and the other with the OFF image named cmdOff and place them on top of each other.

Then add an UNBOUND Text Control to the Details section named txtFocus and leave it Visible.

Step 6

Let’s add the code…

In the On_Click Event Procedure of cmdOn and cmdOff place…

cmdOff

On Error GoTo Err_cmdOff_Click

    DoCmd.OpenForm "sfrReminder", , , "[rID] = " & Me![txtOrderDetailID] & " And rSystemForm Like ""*order*"""
    Forms![sfrReminder]![txtID] = Me.txtOrderDetailID
    Forms![sfrReminder]![txtSystemForm] = “frmOrders”
    Forms![sfrReminder].Caption = "Order Reminder

Exit_cmdOff_Click:
    Exit Sub

Err_cmdOff_Click:
    MsgBox Err.Description
    Resume Exit_cmdOff_Click

cmdOn

On Error GoTo Err_cmdOn_Click

    DoCmd.OpenForm "sfrReminder", , , "[rID] = " & Me![txtOrderDetailID] & " And rSystemForm Like ""*order*"""
    Forms![sfrReminder]![txtID] = Me.txtOrderDetailID
    Forms![sfrReminder]![txtSystemForm] = “frmOrders”
    Forms![sfrReminder].Caption = "Order Reminder

Exit_cmdOn_Click:
    Exit Sub

Err_cmdOn_Click:
    MsgBox Err.Description
    Resume Exit_cmdOn_Click

In the Forms On_Current Event Procedure place…
IMPORTANT: Because there is an *Exit Sub* in this routine you want to make sure to put this at the end of any other events you have running so it does not exit prematurely.

    Dim bnReminder As Boolean

    If DCount("rID", "tblReminders", "[rID] = " & Me![txtOrderDetailID] & " And rSystemForm Like ""*order*""") = 0 Then
    Me!cmdOff.Visible = True
    Me.cmdOn.Visible = False
    Exit Sub
    End If

    bnReminder = DLookup("rShow", "tblReminders", "[rID] = " & Me![txtOrderDetailID] & " And rSystemForm Like ""*order*""")

    If bnReminder = False Then
        Me!cmdOff.Visible = True
        Me.cmdOn.Visible = False
    Else
        Me!cmdOff.Visible = False
        Me.cmdOn.Visible = True
    End If

Then save and close. (Don’t worry, we’re almost there…)

Step 7

Open the Continuous Form with the Orders and add odReminder and odReminderImagePath to the Recordsource. Then, on the Form add a Command Button, cmdReminder, and make it Transparent and change the Cursor on Hover to Hyperlink Hand. (Note, you can simply use the On_Click event of the Image Control if you’re not concerned about the Hyperlink Hand, I just like the little hand!)

Then add an Image Control named imgReminder and set the Control Source as odReminderImagePath. Place the Command Button on top of the Image Control. Finally, place a Check Box with a Control Source of odReminder and set Visible to No and Tab Stop to No.

In the Command Button cmdReminder’s On_Click Event Procedure place…

    If Me.chkReminder = True Then
        DoCmd.OpenForm "sfrReminder", , , "[rID] = " & Me![txtOrderDetailID] & " And rSystemForm Like ""*order*"""
        Forms![sfrReminder].Caption = "Order Reminder
    Else
        DoCmd.CancelEvent
    End If

All done!

Now, once a Reminder has been set from the Orders Form it will show on the Continuous Form…
To use, from the Orders Form click the little alarm to open the Reminder Form, type your reminder and click the little alarm to turn it on and close.

In case you’re interested… This works because each record has a field for the image.  If there is no reminder we remove the path and since the Image Control for each record is bound rReminderImagePath, nothing will show if there is no path entered.