Function fncOpenSpecificWorksheet(strSheetName As String)
'http://www.access-diva.com/tips.html
'Open Excel to the specified Worksheet
Dim xlApp As Object
Dim xlWBk As Object
Dim xlWSh As Object
On Error GoTo Err_Handler
Set xlApp = CreateObject("Excel.Application")
Set xlWBk = xlApp.Workbooks.Open("C:\... ENTER YOUR PATH HERE")
xlApp.Sheets(strSheetName).Select
xlApp.Visible = True
Set xlApp = Nothing
Set xlWBk = Nothing
Set xlWSh = Nothing
'Only for use with Access 2010 and above will not work with lower versions
'Brings Excel to the front
AppActivate "Microsoft Excel", True
Exit Function
Err_Handler:
DoCmd.SetWarnings True
MsgBox Err.Description, vbExclamation, Err.Number
Exit Function
End Function
To use, copy/paste into your modUtilities and then in the Event Procedure of your Command Button…
Question: How do I send eMail and include my Outlook signature?
Answer:
Create a Module, name it anything you like except the name of the Function.
At the top of your Module under…
Option Compare Database
Option Explicit
…place
Function streMailWithOutlookSignature()
'Works in Office 2000-2016
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
strbody = ""
On Error Resume Next
With OutMail
.Display
'Sets the From
.To = "Fill in an eMail Address"
'To use a Distribution List put the name of the list on the *To* line
'.To = "Name of Distribution List goes here"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line test"
.HTMLBody = strbody & "
" & .HTMLBody
'Stopped from automatically sending
'.Send
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Function
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.
Yep, another Excel link has been added to the Microsoft Excel Tips links on the left and what a site…
Third best website dedicated to Microsoft Excel! (Big *THANK YOU* to Sumit Bansal, Microsoft MVP (Excel) for dropping me a line). In addition to the great content on the site is a free eBook download with some great tips!
Recently, I watched Free Tools for Microsoft Access recorded by Crystal Long and Adrian Bell (Microsoft Access MVP’s) on Channel 9. Upon looking at comments below it dawned on me there is no central location for all those great free tools! How is anyone to find them? After all, you may not know they even exist let alone how helpful they can be to you in your development. So… I have added a new page just for them. (Okay, not all of them are free but most are.)
If you know of a free tool for Microsoft Access send me an eMail.
Recent Comments
No comments.