Access MVP (2010-2015)

oOo.oOo.oOo.oOo.oOo.oOo

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
       Me!cboICS.Undo
       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

 1,298 total views

May 20th, 2014 | Tags: , | Category: Forms | Comments are closed

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.

 8,843 total views

May 13th, 2014 | Tags: , , | Category: Access Tips, Command Buttons, Labels | 2 comments - (Comments are closed)

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

 19,194 total views

March 15th, 2014 | Tags: , | Category: Controls, Form Design | Comments are closed

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.

 4,926 total views

March 14th, 2014 | Tags: , | Category: Subforms | Comments are closed

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

 769 total views

February 15th, 2014 | Tags: | Category: Access Tips, Database Design | Comments are closed

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

 6,653 total views

January 19th, 2014 | Tags: , | Category: Access Tips, Controls, Fields, Forms | Comments are closed

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.  Jane A. Doe and if no middle initial Jane Doe use…

[cpFirstName] & (" " + [cpMiddleInitial] + ".") & (" " + [cpLastName])

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

([cpLastName] + ", ")  & [cpFirstName] & (" " + [cpMiddleInitial] + ".")

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

([cpLastName] + ", ") & [cpFirstName] OR [cpLastName] & (", " + [cpFirstName])

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

([cpLastName]+" ") & [cpFirstName]

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

[cpFirstName] & (" "+[cpLastName])

6.  9999-1 and if there is no Order Line Number 9999 use…

[odOrderID] & ("-"+[odOrderLineNumber])

 1,334 total views

January 11th, 2014 | Tags: , | Category: Access Tips, Fields, Strings | Comments are closed

To stop Code execution

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

 2,223 total views

January 4th, 2014 | Tags: | Category: Access Tips | Comments are closed

Check if File is there

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

 663 total views

December 11th, 2013 | Tags: | Category: VBA | Comments are closed

Check if File exists

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

Will delete the file if it exists

If FileExists(C:\Folder\Subfolder\YourDatabaseName.accdb) Then
     Kill "C:\Folder\Subfolder\YourDatabaseName.accdb"
Else
     Msgbox "Nothing to do!"
End if

Will rename the file if it exists

If FileExists("\\YourServerName\Folder\Subfolder\" & Me.cboYear & "-VendorReevaluation.xlsx") = True Then
     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

 2,544 total views

December 10th, 2013 | Tags: , | Category: VBA | Comments are closed
« Newer Entries  
  Older Entries »