Group/Ungroup Controls

This tip is for Access 2007 and up…

  • Select all the Controls you want to keep together by holding down the Ctrl key on your keyboard while selecting
  • On the Arrange tab on the Ribbon, select Size/Space then Grouping and click Group/Ungroup

51 total views, no views today

Turn off Datasheet Subform filtering

Tip provided by Don Leverton

Beginning with Access 2007 …

Datasheet style subforms (which I use a LOT of) have been “improved” by offering dropdowns in the top row of fieldnames, which allow filtering and sorting options.

DatasheetView

This is something that I DO NOT want!

In order to turn OFF this behavior, go to the individual subform’s Design View, and open the form’s Property Sheet.

Scroll down to the “Shortcut Menu” and set it to “No”.

Reopen your datasheet subform, and voila!

DatasheetViewNoFilter

Save the form and that’s it.

860 total views, no views today

Minimize Ribbon

Using just…

CommandBars.ExecuteMso “MinimizeRibbon”
 
..fails.  Why?  Because if the Ribbon is already minimized it will actually maximize.  So, you need to check the Ribbon state first…

If Not CommandBars.GetPressedMso(“MinimizeRibbon”) Then
     CommandBars.ExecuteMso “MinimizeRibbon”
End If

197 total views, no views today

Set focus to start of field

When you open a Form, the first field (Control) in your preset Tab Stop is always highlighted.  This has been known to cause issues… 

  • Users press the Enter key, thereby eliminating the data that was present (and don’t always remember to press Undo).
  • Users begin typing not realizing they are not on a new record, again, eliminating the data that was present

To prevent that you can use the GotFocus event of the field to move the cursor to the beginning of the field…

Private Sub YourControlName_GotFocus()
    Me.YourControlName.SelStart = 0
End Sub

210 total views, no views today

Combining Fields

Combining fields is easy enough BUT when there’s a chance one of the fields might be blank (empty), well, that presents an issue.  Especially, when you don’t want the comma, period or dash to show if the adjoining field is blank (empty), ie: Jane . Doe (no Middle Inital so only the period shows) or 9999- (no Order Line Number so only the dash shows).

Below are examples of how to combine the fields taking into account the adjoining field might be empty leaving off the perios, comma or dash (or any other symbol you might be using).

1.  [cpFirstName] & (” ” + [cpMiddleInitial] + “.”) & (” ” + [cpLastName])  will produce…

Jane A. Doe and if no middle initial Jane Doe.

2.  ([cpLastName] + “, “)  & [cpFirstName] & (” ” + [cpMiddleInitial] + “.”) will produce…

Doe, Jane A.; and if no Middle Initial, Doe, Jane; and if no Last Name Jane, A.

3.   ([cpLastName] + “, “) & [cpFirstName] OR [cpLastName] & ( “, ” + [cpFirstName]) will produce…

Doe, Jane and leaving either First Name or Last Name blank will not result in an empty space before or after the name

4.  ([cpLastName]+” “) & [cpFirstName] will produce…

Doe Jane and leaving either First Name or Last Name blank will not result in an empty space before or after the name

5.  [cpFirstName] & (” “+[cpLastName])

Jane Doe and leaving either First Name or Last Name blank will not result in an empty space before or after the name

6.  [odOrderID] & (“-”+[odOrderLineNumber])

9999-1 and if there is no Order Line Number 9999

127 total views, 1 views today

To stop Code execution

Click Ctrl + Pause/Break on your keyboard.  You may have to do it twice.

293 total views, 1 views today

Check if File is there

    If Dir(“put full filepath and filename here“) = “” Then
        Msgbox “The file was not created.”
    End If

172 total views, no views today

Check if File exists

Sometimes you need to see if a file is in a directory for one reason or another…

If FileExists(C:\Folder\Subfolder\YourDatabaseName.accdb) Then
     ‘Will delete the file if it exists
     Kill “C:\Folder\Subfolder\YourDatabaseName.accdb”
Else
     Msgbox “Nothing to do!”
End if

        If FileExists(“\\YourServerName\Folder\Subfolder\” & Me.cboYear & “-VendorReevaluation.xlsx”) = True Then
             ‘Will rename the file if it exists
              xlWBk.SaveAs “\\YourServerName\Folder\Subfolder\” & Me.cboYear & “-VendorReevaluation.” & Format(Date, “mmddyyyy”) & “.xlsx”, 51
        Else
            ApXL.DisplayAlerts = False
            xlWBk.SaveAs “\\YourServerName\Folder\Subfolder\” & Me.cboYear & “-VendorReevaluation.xlsx”, 51
            ApXL.DisplayAlerts = True
        End If

249 total views, no views today

Remove Carriage Return from imported Excel Spreadsheets

You import a spreadsheet and you noticed in some of your fields you have a little box with a question mark in it (see image below).  This is a Carriage Return (Alt+Enter in Excel).

Figure 1

  To remove create an UPDATE query and run against the offending field…

UPDATE YourTable
     SET FieldContainingCarriageReturn = Replace([FieldContainingCarriageReturn],Chr(10),Space(1))

1,512 total views, 18 views today

Hyperlink to open Forms

You’re using a Continuous Form, you can’t fit all your fields on the Form but you need to see and/or edit the record and you don’t want to use a Command Button.  (Sample file located at the bottom of the article.)  Here’s an alternate way to open the record with a Field…

Client List

Client List

a. Put an UNBOUND field on your Form and enter the below as the Control Source (Figure 1).  Note, where cpClientID is my Autonumber and Primary Key you should replace with your Autonumber and/or Primary Key…

=IIf(IsNull([cpClientID]),”(New)”,”Open”)

Open New

Figure 1

b.  In the Properties Sheet window for your Field set the Display As Hyperlink to Always.  Note, you can leave the Font Color as Hyperlink Color or change to any color you like.  However, since most Users are accustomed to the blue it is safer to accept the default Font Color of Hyperlink Color.

Figure 2

Figure 2

c.  Now, just add a bit of code the On_Click event to open your details form (See sample of my frmClientProfile below)…

    If (IsNull(txtClientID)) Then
        ‘If the Field displays (New)
        DoCmd.OpenForm “frmClientProfile”, , , , acFormAdd
    Else
         ‘If the Field displays Open
        DoCmd.OpenForm “frmClientProfile”, , , “[cpClientID]=” & Me![txtClientID]
    End If

frmClientProfile ~ New Record

frmClientProfile ~ New Record

frmClientProfile ~ Existing

frmClientProfile ~ Existing

To download a sample file click here

1,854 total views, 9 views today