Set Default Value on List Box

You have List Box and you want the first item to be selected…

For VBA use…
Me.lstActivities.DefaultValue = Me.lstActivities.ItemData(0)


In the Properties window, on the Default Value line, of the List Box use…

26 total views, no views today

Run Query from Desktop

To run a Query from your Desktop without actually opening your Database…

1. Open your Database

2. Select the Query you want to run and drag it to your Desktop

3. Close your Database and then double-click the Query on your Desktop

333 total views, no views today

Get Initials

I use Initials in Combo Boxes for space conservation on a Form.  I don’t depend on the User to enter them, instead, I enter the below in the After_Update event of the Last Name Control on my Form…

Me.txtAssociateInitials = Trim(Left(Me.txtFirstName, 1)) & Trim((” ” + Left(Me.txtMiddleInitial, 1))) & Trim((” ” + Left(Me.txtLastName, 1)))

6,172 total views, no views today

Expand SubDatasheets

Okay, so we all know I recommend turning off the SubDatasheet but I can see by the Forums they are still quite popular.  For those of you that are still using them…SubDatasheetClosed

When you first open your Main Form/Subform (Datasheet View) it opens closed (Little plus signs).  This is often quite annoying to Users as they are the ones that have to click all those little plus signs to see the data in the SubDatasheet.  So, let’s fix that!


In the On_Load event of the Main Form put…

Private Sub Form_Load()
        With Forms(“YourMainFormName“)
         .SubdatasheetExpanded = True
        End With
End Sub

Then the next time you open the Form…SubDatasheetExpanded  Enjoy!


2,568 total views, no views today

Limit Number of Record Entries

Every once in a while you may want to restrict how many records can be entered. Maybe because of space limitations on a Report or perhaps you want to limit CD rentals to 4 per Customer or book a specific amount people per room. Whatever the reason here’s some code to make it happen… 

What I used to use (and it worked)…

Private Sub Form_BeforeInsert(Cancel As Integer)

   If DCount(“ICS”, “qryIncidentCrimes”) = 4 Then
       MsgBox “Only 3 Charges allowed per report, if you have additional charges you must start a new report!”, vbExclamation
       DoCmd.RunCommand acCmdUndo
       DoCmd.GoToRecord , , acPrevious
       Forms![frmIncidentInformation]![txtTimeOfOccurrence].SetFocus ‘Here I set focus back to the Main Form
   End If 

End Sub

But then I found a more efficient way… 

In a Subform
Private Sub Form_BeforeInsert(Cancel As Integer) 

   If Me.NewRecord = True And Me.RecordsetClone.RecordCount = 4 Then
       MsgBox “Only 4 sizes allowed per sheet!”, vbExclamation + vbOKOnly, “FF&E Size”
       DoCmd.GoToRecord , , acPrevious
   End If

End Sub 

In a Main Form 
Private Sub Form_Current() 

   If Me.NewRecord = True And Me.RecordsetClone.RecordCount = 4 Then
       MsgBox “Only 4 sizes allowed per sheet!”, vbExclamation + vbOKOnly, “FF&E Size”
       DoCmd.GoToRecord , , acPrevious
   End If 

End Sub

3,611 total views, no views today

Ampersand not showing in Caption

When using an Ampersand (&) in a Label or Button Caption you get an underscore (Jack_Jill) or nothing (JackJill).  To get Jack & Jill you need to double up on the ampersands…  Jack && Jill.

Why?  In Access the Ampersand is used as a Keyboard Shortcut.  So, if you have a Button Caption entered like &File Maintenance which shows as File Maintenance, using Alt + F on your Keyboard will jump you to that button.  That’s why we double up, so Access knows we really want to *see* the Ampersand.

5,929 total views, no views today

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

3,342 total views, 3 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.


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!


Save the form and that’s it.

3,367 total views, 1 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

5,306 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

3,707 total views, no views today