{"id":1227,"date":"2016-07-11T01:08:08","date_gmt":"2016-07-11T05:08:08","guid":{"rendered":"http:\/\/regina-whipp.com\/blog\/?p=1227"},"modified":"2019-11-17T19:35:54","modified_gmt":"2019-11-18T00:35:54","slug":"reminder","status":"publish","type":"post","link":"https:\/\/regina-whipp.com\/blog\/?p=1227","title":{"rendered":"Conditional Image on a Continuous Form (Reminders)…"},"content":{"rendered":"

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\u2026 hmm.<\/p>\n

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\u2019s Order.\u00a0 Putting that information under Order Notes doesn\u2019t really help because you\u2019re dependent on remembering to open the notes and then sift thru them to find the important one\u2026 odds are that\u2019s not going to happen.<\/p>\n

So, let\u2019s create a Reminder\u2026<\/p>\n

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

Step 1<\/h6>\n

To your table add two fields\u2026<\/p>\n

tblOrderDetails (My Table)<\/span>
\nodReminder<\/strong> (Yes\/No)
\nodReminderImagePath<\/strong> (Text, 255 or Short Text)<\/p>\n

Step 2<\/h6>\n

Add tblReminders<\/strong> with the fields listed below\u2026<\/p>\n

rReminderID<\/strong> (Primary Key, Autonumber)
\nrID<\/strong> (Number, Long Integer) \u2013 This will hold the Primary Key for whichever Table(s) you are using the Reminders with.
\nrReminder<\/strong> (Memo, Rich Text or Long Text, Rich Text)
\nrShow<\/strong> (Yes\/No)
\nrDate<\/strong> (Date\/Time) \u2013 optional, if you want to know when the reminder was created
\nrNetworkID<\/strong> (Text, 50 or Short Text) \u2013 optional, if you want to know who created the reminder
\nrSystemForm<\/strong> (Text, 50 or Short Text) \u2013 This will only be needed if you plan to use the Reminder in multiple areas, such as, Orders and Tasks.<\/p>\n

Step 3<\/h6>\n

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.\u00a0 (To put a custom image on a Command Button click here<\/a>.<\/span>)\u00a0 My favorite place to find images is Icon Archive<\/a>.<\/p>\n

Step 4<\/h6>\n

Create your Reminder Form\u2026
\n\"reminderDesign\"<\/p>\n

Set the Recordsource for your Form:<\/p>\n

SELECT tblReminders.rReminderID, tblReminders.rID, tblReminders.rReminder, tblReminders.rShow, tblOrderDetails.odReminder, tblOrderDetails.odReminderImagePath FROM tblOrderDetails INNER JOIN tblReminders ON tblOrderDetails.odOrderDetailID = tblReminders.rID;<\/pre>\n
Then place two Command Buttons<\/strong> on your Form in the Form Header<\/strong> section, one with the ON image named cmdOn<\/strong> and the other with the OFF image named cmdOff<\/strong> and place them on top of each other.<\/p>\n
Then add an UNBOUND Text Control named txtFocus<\/strong> and place it in the Form Header<\/strong>, see the purple oval. Also, set it as the first Tab Stop<\/strong> and leave it Visible<\/strong>.<\/p>\n
Add the other Controls<\/strong> (use Naming Conventions<\/a> to correspond to the below or the code will not work, i.e. txtID, chkShow, etc.) in the Detail<\/strong> section and hide, Visible = No, \u00a0except for the rReminder<\/strong> which is where the reminder will be entered. This is a one-to-one so make sure your Form<\/strong> is set as a Single Form<\/strong>.<\/p>\n
In the Forms On_Load Event Procedure place…<\/p>\n
    If Me!chkShow = False Then\r\n        Me!cmdOff.Visible = True\r\n        Me.cmdOn.Visible = False\r\n    Else\r\n        Me!cmdOff.Visible = False\r\n        Me.cmdOn.Visible = True\r\n    End If\r\n<\/pre>\n
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.)\u2026<\/p>\n
    If Me!chkShow = False Then\r\n        Me.chkShow = True\r\n        Me.txtFocus.SetFocus\r\n        Me!cmdOff.Visible = False\r\n        Me.cmdOn.Visible = True\r\n        Me.chkReminder = True\r\n        Me.txtReminderImagePath = "Full Path to YOUR image goes here!"\r\n        \u2018Toggle the Command Buttons on the Order Details Form\r\n        Forms![frmOrders]![sfrOrderDetails].Form![txtFocus].SetFocus\r\n        Forms![frmOrders]![sfrOrderDetails].Form![cmdOff].Visible = False\r\n        Forms![frmOrders]![sfrOrderDetails].Form![cmdOn].Visible = True\r\n    Else\r\n        Me.chkShow = False\r\n        Me.txtFocus.SetFocus\r\n        Me!cmdOff.Visible = True\r\n        Me.cmdOn.Visible = False\r\n        Me.chkReminder = True\r\n        Me.txtReminderImagePath = ""\r\n        \u2018Toggle the Command Buttons on the Order Details Form\r\n        Forms![frmOrders]![sfrOrderDetails].Form![txtFocus].SetFocus\r\n        Forms![frmOrders]![sfrOrderDetails].Form![cmdOff].Visible = True\r\n        Forms![frmOrders]![sfrOrderDetails].Form![cmdOn].Visible = False\r\n    End If\r\n<\/pre>\n
Set the Form as a Pop Up, then save, and close. (Oh, you probably want to take a peek first!)
\n\"reminder\"<\/p>\n
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.<\/p>\n
Step 5<\/h6>\n
Open your Orders\/Order Details Form<\/strong> in Design Mode<\/strong> and on the Order Details Subform<\/strong> (many side) add two Command Buttons<\/strong> on your Form<\/strong>, one with the ON image named cmdOn<\/strong> and the other with the OFF image named cmdOff<\/strong> and place them on top of each other.<\/p>\n
Then add an UNBOUND Text Control<\/strong> to the Details<\/strong> section named txtFocus<\/strong> and leave it Visible<\/strong>.<\/p>\n
Step 6<\/h6>\n
Let\u2019s add the code\u2026<\/p>\n
In the On_Click Event Procedure<\/strong> of cmdOn<\/strong> and cmdOff<\/strong> place\u2026<\/p>\n
cmdOff<\/strong><\/p>\n
On Error GoTo Err_cmdOff_Click\r\n\r\n    DoCmd.OpenForm "sfrReminder", , , "[rID] = " & Me![txtOrderDetailID] & " And rSystemForm Like ""*order*"""\r\n    Forms![sfrReminder]![txtID] = Me.txtOrderDetailID\r\n    Forms![sfrReminder]![txtSystemForm] = \u201cfrmOrders\u201d\r\n    Forms![sfrReminder].Caption = "Order Reminder\r\n\r\nExit_cmdOff_Click:\r\n    Exit Sub\r\n\r\nErr_cmdOff_Click:\r\n    MsgBox Err.Description\r\n    Resume Exit_cmdOff_Click\r\n<\/pre>\n
cmdOn<\/strong><\/p>\n
On Error GoTo Err_cmdOn_Click\r\n\r\n    DoCmd.OpenForm "sfrReminder", , , "[rID] = " & Me![txtOrderDetailID] & " And rSystemForm Like ""*order*"""\r\n    Forms![sfrReminder]![txtID] = Me.txtOrderDetailID\r\n    Forms![sfrReminder]![txtSystemForm] = \u201cfrmOrders\u201d\r\n    Forms![sfrReminder].Caption = "Order Reminder\r\n\r\nExit_cmdOn_Click:\r\n    Exit Sub\r\n\r\nErr_cmdOn_Click:\r\n    MsgBox Err.Description\r\n    Resume Exit_cmdOn_Click\r\n<\/pre>\n
 <\/p>\n
In the Forms On_Current Event Procedure<\/strong> place\u2026
\nIMPORTANT<\/strong><\/span>: Because there is an *Exit Sub<\/strong>* 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.<\/p>\n
    Dim bnReminder As Boolean\r\n\r\n    If DCount("rID", "tblReminders", "[rID] = " & Me![txtOrderDetailID] & " And rSystemForm Like ""*order*""") = 0 Then\r\n    Me!cmdOff.Visible = True\r\n    Me.cmdOn.Visible = False\r\n    Exit Sub\r\n    End If\r\n\r\n    bnReminder = DLookup("rShow", "tblReminders", "[rID] = " & Me![txtOrderDetailID] & " And rSystemForm Like ""*order*""")\r\n\r\n    If bnReminder = False Then\r\n        Me!cmdOff.Visible = True\r\n        Me.cmdOn.Visible = False\r\n    Else\r\n        Me!cmdOff.Visible = False\r\n        Me.cmdOn.Visible = True\r\n    End If\r\n<\/pre>\n
Then save and close. (Don\u2019t worry, we\u2019re almost there\u2026)<\/p>\n
Step 7<\/h6>\n
Open the Continuous Form<\/strong> with the Orders and add odReminder<\/strong> and odReminderImagePath<\/strong> to the Recordsource<\/strong>. Then, on the Form add a Command Button, cmdReminder<\/strong>, and make it Transparent<\/strong> and change the Cursor on Hover<\/strong> to Hyperlink Hand<\/strong>. (Note, you can simply use the On_Click event of the Image Control if you\u2019re not concerned about the Hyperlink Hand, I just like the little hand!)<\/p>\n
Then add an Image Control<\/strong> named imgReminder<\/strong> and set the Control Source<\/strong> as odReminderImagePath<\/strong>. Place the Command Button<\/strong> on top of the Image Control<\/strong>. Finally, place a Check Box<\/strong> with a Control Source<\/strong> of odReminder<\/strong> and set Visible<\/strong> to No<\/strong> and Tab Stop<\/strong> to No<\/strong>.<\/p>\n
In the Command Button cmdReminder\u2019s On_Click Event Procedure<\/strong> place\u2026<\/p>\n
    If Me.chkReminder = True Then\r\n        DoCmd.OpenForm "sfrReminder", , , "[rID] = " & Me![txtOrderDetailID] & " And rSystemForm Like ""*order*"""\r\n        Forms![sfrReminder].Caption = "Order Reminder\r\n    Else\r\n        DoCmd.CancelEvent\r\n    End If\r\n<\/pre>\n
All done!<\/p>\n
Now, once a Reminder has been set from the Orders Form it will show on the Continuous Form…
\n\"ContinuousForm\"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.<\/p>\n
In case you’re interested… This works because each record has a field for the image.\u00a0 If there is no reminder we remove the path and since the Image Control for each record is bound\u00a0rReminderImagePath, nothing will show if there is no path entered.<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,63],"tags":[41,18,31,46,80],"class_list":["post-1227","post","type-post","status-publish","format-standard","hentry","category-access-tips","category-database-design","tag-continuous-forms","tag-database-design","tag-forms","tag-image-handling","tag-reminders","odd"],"a3_pvc":{"activated":true,"total_views":2297,"today_views":0},"_links":{"self":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1227","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1227"}],"version-history":[{"count":19,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1227\/revisions"}],"predecessor-version":[{"id":1408,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1227\/revisions\/1408"}],"wp:attachment":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1227"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1227"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1227"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}