Access MVP (2010-2015)

oOo.oOo.oOo.oOo.oOo.oOo

Open Excel to specific Worksheet…

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

Call fncOpenSpecificWorksheet("YOUR WORKSHEET NAME")

 714 total views

Send eMail with Default (Outlook) Signature…

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

 771 total views

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…
reminderDesign

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!)
reminder

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…
ContinuousFormTo 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.

 2,068 total views

Trump Excel

Yep, another Excel link has been added to the Microsoft Excel Tips links on the left and what a site…
TrumpExcel

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!

 870 total views

Tools, Utilities and more…


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.

 660 total views