MS Access Database Template Tips

1. Improve tables performances
2. Complete the fractional part of a tax calculation
3. Remove accents from international characters
4. Split a name in two (First name - last name)
5. Desable the Shift key effect at opening
6. Enable the Shift key effect at opening
7. Extract the file name part of a directory name
8. Extract the directory part of a file name
9. Programmable autonumber field on the On Click event of a button
10. How to make a table invisible
11. Function to know if a form or a report is open

Tips to improve tables performances in Microsoft Access (Documented by Microsoft)

1- Disable the Track Name Auto Correct properties. To do that open Microsoft Access. Go to Tools - Options and clic on the general tab. In the right part of the window, un-check the Track Name Auto Correct properties.

2- For Microsoft Access 2002 and later. If you work with link tables, you should always set the Subdatasheet Name property to none for each table in the backend database. To do that open the backend database. Open a table in design view. On the view menu, click properties. Set the Subdatasheet Name property to [None]. Save your work. Do that for each table.

This function can be used to complete the fractional part of a tax calculation so your total will for sure equal the sum of the sub-totals + the taxes

Function TComp(ByVal cAmount As Currency) As Currency
 
  'Completes the fractional part of a tax calculation
  If Int(cAmount * 100) < cAmount * 100 Then 'We have to complete
    If cAmount > 0 Then
      TComp = Int(cAmount * 100) / 100 + 0.01
    Else
      TComp = Int(cAmount * 100) / 100
    End If
  Else
    TComp = cAmount
  End If

End Function

This function removes accents from international characters

Function AccentRem(ByVal vntEntry As Variant)

  'Remove accents from international characters
 
  Dim X As Integer      'Counter
 
  For X = 1 To 1 'Len(vntEntry)
    Select Case Mid(vntEntry, X, 1)
      Case "é", "è", "ë", "ê"
        Mid(vntEntry, X, 1) = "e"
      Case "É", "È", "Ë", "Ê"
        Mid(vntEntry, X, 1) = "E"
      Case "ï", "î"
        Mid(vntEntry, X, 1) = "i"
      Case "Ï", "Î"
        Mid(vntEntry, X, 1) = "I"
      Case "à", "â"
        Mid(vntEntry, X, 1) = "a"
      Case "À", "Â"
        Mid(vntEntry, X, 1) = "A"
      Case "ô"
        Mid(vntEntry, X, 1) = "o"
      Case "Ô"
        Mid(vntEntry, X, 1) = "O"
      Case "ù", "û"
        Mid(vntEntry, X, 1) = "u"
      Case "Ù", "Û"
        Mid(vntEntry, X, 1) = "U"
      Case "ç"
        Mid(vntEntry, X, 1) = "c"
      Case "Ç"
        Mid(vntEntry, X, 1) = "C"
    End Select
  Next X
  AccentRem = vntEntry

End Function

This function splits a Name having one of the following forms in two, returning FirstName and setting the variable sent as a parameter to LastName.
 
Function SplitName(strName As String) As String

  'Split a strName having one of the following forms in two, returning FirstName
  'and setting the variable sent as a parameter to LastName.
  '
  'Forms allowed for StrName:
  '   LastName, FirstName
  '   LastName,FirstName
  '   FirstName LastName
 
  Dim pos As Integer
 
  ' LastName, FirstName
  pos = InStr(1, strName, ", ")
  If pos Then
    SplitName = Right$(strName, Len(strName) - pos - 1)
    strName = Left$(strName, pos - 1)
  Else
    pos = InStr(1, strName, ",")
    If pos Then
      SplitName = Left$(strName, pos - 1)
      strName = Right$(strName, Len(strName) - pos)
    Else
      pos = InStr(1, strName, " ")
      If pos Then
        SplitName = Right$(strName, Len(strName) - pos)
        strName = Left$(strName, pos - 1)
      Else
        'Nothing to change
      End If
    End If
  End If

End Function

This code desables the Shift key effect when opening a database. You can put the code behind a transparent button on your main form.

Public Sub DisableByPassKeyProperty()
On Error Resume Next
    Dim reponse
    Dim DB As Database
    Dim prp As Property
    reponse = MsgBox("Cancel the SHIFT key effect at opening ?", vbYesNo, "Message")
    If reponse = vbYes Then
        Set DB = CurrentDb
        Set prp = DB.CreateProperty("AllowByPassKey", dbBoolean, False)
        DB.Properties.Append prp
    End If
End Sub

This code enables the Shift key effect when opening a database. A password makes it safer. You can put the code behind a transparent button on your main form.

Public Sub EnableByPassKeyProperty()
On Error Resume Next
Dim RepCode As Variant
RepCode = InputBox("Password ? ", Title)
If RepCode = "298716" Then
    Dim reponse
    Dim DB As Database
    reponse = MsgBox("Put back the SHIFT key effect at opening ?", vbYesNo, "Message")
    If reponse = vbYes Then
        Set DB = CurrentDb
        DB.Properties.Delete "AllowByPassKey"
        DB.Properties.Refresh
    End If
Else
    MsgBox "Bad response !!!!", vbOKOnly, Title
End If
End Sub

Extract the file name part of a directory name

Public Function GetFileNamePart(strName As String) As String
Dim i As Integer
Dim strTmp As String
    For i = Len(strName) To 1 Step -1
    If Mid$(strName, i, 1) <> "\" Then
        strTmp = Mid$(strName, i, 1) & strTmp
    Else
        Exit For
    End If
    Next i
    GetFileNamePart = strTmp
End Function

Extract the directory part of a file name

Public Function GetDirPart(strName As String) As String
Dim i As Integer
    For i = Len(strName) To 1 Step -1
        If Mid$(strName, i, 1) = "\" Then Exit For
    Next i
    dbcGetDirPart = Left$(strName, i)
End Function

 Programmable autonumber field on the On Click event of a button

 Private Sub btnNewInvoice_Click()

    DoCmd.GoToRecord , , acNewRec  ' Position yourself on a new record

    If Not IsNull(DMax("FieldName", "TableName")) Then  ' FieldName is an integer field in the table
      Me.FieldName = DMax("FieldName", "TableName") + 1
    Else
      Me.FieldName = 1
    End If
End Sub

 Make a table invisible

A simple technique is to rename your table beginning by USYS (Ex: Usys_Customer). The table will then be invisible but accessible. To make the table reappear you have to go to Tools/Options View section, check "System object".

Here's a function to know if a form or a report is currently open

Function IsLoaded(stFrmName$) As Integer

  'Returns true if a the given Form/Report is currently open
     
  Dim I%

  'Scan the open forms...

  For I% = 0 To Forms.Count - 1
    If (Forms(I%).FormName = stFrmName$) Then
      IsLoaded = True
      Exit Function
    End If
  Next I%

  'Scan the open reports...

  For I% = 0 To Reports.Count - 1
    If (Reports(I%).FormName = stFrmName$) Then
      IsLoaded = True
      Exit Function
    End If
  Next I%

  IsLoaded = False

End Function