MS Access Database Template Tips
- Improve tables performances
- Complete the fractional part of a tax calculation
- Remove accents from international characters
- Split a name in two (First name - last name)
- Desable the Shift key effect at opening
- Enable the Shift key effect at opening
- Extract the file name part of a directory name
- Extract the directory part of a file name
- Programmable autonumber field on the On Click event of a button
- How to make a table invisible
- Function to know if a form or a report is open
- Open the zoom windows on a double-click
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
Open the zoom windows on a double-click
A trick i often use is to place this code on on a double-click event of a text field to
open the zoom windows. You can use this to help the user when there is not enought space to see the entire content
of the field.
SendKeys "+{F2}", True
| 