Access MVP (2010-2015)


List Tables and Fields…

When I’m designing a database I usually start off with one of my Templates. But, as with any Template, the Tables therein do not always fit exactly with the Client’s needs. And, since I like to document changes I built this tool…

In a nutshell…
After the Template database is created I import the Objects from the Tables and File List database. From there I can go to Design Mode of the Table, the little blue button, and make my changes, as well as, mark what changes I’ve made in the *Comments* column. Then I have a record of my changes and, just in case someone needs a hard copy, output the results to Excel (template included).

To use…
Download the Table and File List zipped file and unzip the contents to a Folder, not your Desktop. Then open the database you want to run this on and import the Objects into your database and place TableFieldList.xlsx into the same Folder as the database you want to use this in. Open frmTableFileList and click the Redo Command Button.

The blue button to the left opens the Table in Design Mode. (Thanks to Tony Toews’ database for giving me the idea to go directly to Design Mode from here instead to dealing with the Navigation Pane.)

I use the Check Box to indicate I cam done. Once checked the record (row) will turn light grey (helps me stay focused).

Blue boxes at the top are for filtering. Once making a selection and/or entering text, partial entries accepted, select the Filter button at the top, to clear select the Clear Filter button.

The code (in case your interested)…
Pretty standard except that it loops thru the Tables in MSysObjects so it does not require the individual input of each Table run. Nor do you need to create tblTableFieldList before running as it will look to see if it’s there. (One caveat, I did not adjust the Form to run without the query which needs the Table, never got around to it. So, if you attempt to open the Form before creating the Table you will get a message. However, in the sample provided there is a Table so no worries there.)

Function fncTableFieldList()
'7.15.2016 Gina Whipp (Access Diva)
'Purpose:  Write all table and field names to tblTableFieldList
    Dim lngTable As Long
    Dim lngField As Long
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim tdf As DAO.TableDef
    Dim lngRow As Long
    Dim fld As DAO.Field
    Dim strTable As String
        Set db = CurrentDb
        strTable = "tblTableFieldList"
        If DCount("*", "MSysObjects", "Type = 1 AND Name='" & strTable & "'") = 0 Then
            'Create tblTableFieldList
            Set tdf = db.CreateTableDef("tblTableFieldList")

        With tdf
           Set fld = .CreateField("tflID", dbLong)
           fld.Attributes = dbAutoIncrField + dbFixedField
              .Fields.Append fld
              .Fields.Append .CreateField("tflTableName", dbText, 50)
              .Fields.Append .CreateField("tflFieldName", dbText, 50)
              .Fields.Append .CreateField("tflDataTypeID", dbLong)
              .Fields.Append .CreateField("tflSize", dbLong)
              .Fields.Append .CreateField("tflAttributeID", dbLong)
              .Fields.Append .CreateField("tflComment", dbText, 255)
              .Fields.Append .CreateField("tflExclude", dbBoolean)
        End With
            'Append new output table definition to database
            db.TableDefs.Append tdf
            Set fld = Nothing
            Set tdf = Nothing
            'Debug.Print "tblTableFieldList created."
            'Empty tblTableFieldList
            strSQL = "DELETE tblTableFieldList.* FROM tblTableFieldList"
                      CurrentDb.Execute strSQL, dbFailOnError
            'Debug.Print "tblTableFieldList emptied."
        End If
        Set rs = db.OpenRecordset("tblTableFieldList", dbOpenDynaset)
    'Set on error in case there is no tables
    On Error Resume Next
    'Loop through all tables
    For lngTable = 0 To db.TableDefs.Count
        'Ignore temporary (~) and system tables (MSys)
        If Left(db.TableDefs(lngTable).Name, 1) = "~" Or _
            Left(db.TableDefs(lngTable).Name, 4) = "MSys" Then
            'Otherwise, loop through each table and get Primary Key
            For lngField = 0 To db.TableDefs(lngTable).Fields.Count - 1
            'Use this if you don't want Primary Key
            'For lngField = 1 To db.TableDefs(lngTable).Fields.Count - 1
                lngRow = lngRow + 1
                    rs!tflTableName = db.TableDefs(lngTable).Name
                    rs!tflFieldName = db.TableDefs(lngTable).Fields(lngField).Name
                    rs!tflDataTypeID = db.TableDefs(lngTable).Fields(lngField).Type
                    rs!tflSize = db.TableDefs(lngTable).Fields(lngField).Size
                    rs!tflAttributeID = db.TableDefs(lngTable).Fields(lngField).Attributes
            Next lngField
        End If
    Next lngTable
    'Resume error breaks
    On Error GoTo 0
    'Release from memory
    Set rs = Nothing
    Set db = Nothing
End Function


5 total views, no views today

Hide Duplicate Rows in Excel…

Last year when I was really starting to jump into exporting data to Excel I ran into an issue. I wanted the rows with duplicated data to only show the first row and hide the following rows… Conditional Formatting, right? So, why couldn’t I get what I wanted. When all else fails *Google* it! Which is just what I did and found exactly what I needed on Contextures Blog then just convert the Excel Macro into VBA and this…

     Const xlUp As Long = -4162 'This can go at the very tip under the Declarations
     Dim ApXL As Object
     Dim xlWBk As Object
     Dim xlWSh As Object
     Dim lngLastRow As Long
     Dim Z As String

With xlWSh
     'Conditional Formatting
     lngLastRow = xlWSh.Cells(.Rows.Count, 1).End(xlUp).Row
     Z = "A4" & ":F" & lngLastRow
     '.Add Type:xlExpression use 1 for Early Binding, 2 for Late Binding
     ApXL.Selection.FormatConditions.Add Type:=2, Formula1:="=A3=A2"
          With ApXL.Selection.FormatConditions(1).Font
               .Color = RGB(255, 255, 255)
               .TintAndShade = 0
          End With
     ApXL.Selection.FormatConditions(1).StopIfTrue = False
End With

…produces this…

Note, the code includes some caveats… I never knew how many rows hence *Z* and *lngLastRow* and since I have a Totals row I needed to know for highlighting and summing. If you don’t have that limitation you can use (change the column and row numbers accordingly)…


…and eliminate *Z* and *lngLastRow*.

4 total views, no views today

Open Excel to specific Worksheet…

Function fncOpenSpecificWorksheet(strSheetName As String)
'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.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
        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")

2 total views, no views today

Send eMail with Default (Outlook) Signature…

Question: How do I send eMail and include my Outlook signature?

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


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
        '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
    End With

    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing

End Function

3 total views, no views today

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
        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!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![cmdOff].Visible = False
        Forms![frmOrders]![sfrOrderDetails].Form![cmdOn].Visible = True
        Me.chkShow = False
        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![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…


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 Sub

    MsgBox Err.Description
    Resume Exit_cmdOff_Click


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 Sub

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

51 total views, no views today