How to modify the system warning messages.
Q: How can I intercept and modify the "You are about to delete one (1) record" system warning message to read: "You are about to delete record ### for patient John Doe"?
A: One can't really "intercept" this warning message, but it can be turned off and a custom message can be written to replace it in a VBA procedure to warn the user. The warnings can either be turned off in code or by using the database options. However, if the database options are changed to prevent the system warning, then when the user opens the table and deletes records, no message will warn the user.
To turn off the warning in code, use syntax such as the following:
MsgBox "You are about to delete record #" & RecID & _
vbCrLf & " for patient " & Patient_Name & "!"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True ' Turn warnings back on.
Make sure that the error handler for this procedure sets the warnings back on, just in case something goes wrong. Otherwise, no further system warnings will be displayed for the user during the current session.
To alert the user only when there is a problem (such as someone has renamed the table, so it doesn't exist when the query tries to delete a record from this table), then don't bother with the SetWarnings method. Instead, use the Execute method with the dbFailOnError parameter. It will remain silent unless it encounters a problem, in which case the operation will be rolled back and the user alerted as to what problem caused the roll back.
MsgBox "You are about to delete record #" & RecID & _
vbCrLf & " for patient " & Patient_Name & "!"
CurrentDb().Execute strSQL, dbFailOnError
Top
How to prevent "Invalid use of Null" error when using the DLookup( ) function.
Q: I'm checking for an entry in a table using the DLookup( ) function. If there is no entry in table, I get the "Invalid use of Null" error. How do I prevent this Null error from stopping the routine?
A: Use the Nz( ) function to replace the Null with a zero-length string. For example:
DeptName = Nz(DLookup("DEPT", "EMPS", "EMP_ID = " & EmpID), "")
Top
How to prevent users from sharing a common front end database file located on a networked server.
Q: We have a multiuser, split database, where each user has a copy of the front end on his own workstation. There is also a copy of the front end located on the network server, so that whenever a new version of the application is released, users can download a copy of the file to their own hard drives. The trouble is that some users are opening and using the database file on the server, not the front end on their own workstations. Some operations require exclusive access to the database, so when others are sharing the file, these operations fail. Is there any way to keep users from sharing this database file, even inadvertently?
A: Sharing an Access database has been identified by Microsoft personnel as the number one cause of database corruption, so it should be avoided. One way to prevent users from accidentally sharing the database file located on the server is to force all users to open the file in exclusive mode. If the first user opens the networked file in exclusive mode, then subsequent users will not be able to open the file. They'll receive a "file in use" error. When these users call tech support, they'll be reminded to use the front end on their own workstations, not the front end on the server.
The benefits of using this method over other methods (such as checking the path of the front end) to ensure that the database file on the networked server is not shared are:
1.) Flexibility on the path, because it doesn't matter whether the path is on the C:\ drive, another partition on the local hard drive, or in a user's home directory on a network server that may or may not have been mapped with a drive letter instead of using a UNC path; and
2.) Path and file names aren't hard coded, so if there's a change in location or file name, the code still works with no changes; and
3.) Less chance of corruption, because the code doesn't allow a user to first open the database, and then find out that the user is not complying with your organization's policy of not sharing the front end, since no user can open the database after it's been opened exclusively by someone else; and
4.) This code can be reused in other other applications because paths and file names are irrelevant.
If you the database doesn't already have a startup form, then create one which runs code in the OnOpen( ) event that checks whether or not the database was opened exclusively. If it was opened in shared mode, it shuts down the application, so the first user can only open the application in exclusive mode and all subsequent users will get a "file in use" error message if they attempt to open the same file on the server. In order to prevent users from bypassing the startup form, disable the ShiftBypass key. The code for is readily available on the Web page listed below. There's an issue about users re-enabling the ShiftBypass key, but this can be avoided by applying User-level security and removing the dbSecWriteDef permission from all users not in the Admins group. Of course, the default Admin user can't be a member of the Admins group.
To force users to open the database in exclusive mode, paste the following code into the startup form's module:
Private Sub Form_Open(Cancel As Integer)
On Error GoTo ErrHandler
Dim sPathAndFileName As String
Dim nFileNum As Integer
sPathAndFileName = CurrentProject.FullName ' Use this for Access
' 2000 & later versions.
'sPathAndFileName = CurrentDb().Name ' Or use this for Access
' 97 & earlier versions.
nFileNum = FreeFile
Open sPathAndFileName For Input Access Read Shared As #nFileNum
Close #nFileNum ' Always close the file.
'------------------------------------------------------------------------
' If this file was opened and closed successfully, then it
' was opened in shared mode.
'------------------------------------------------------------------------
MsgBox "You must open this database exclusively." & vbCrLf & _
vbCrLf & "Please ensure that this file is located on your " & _
vbCrLf & "own hard drive, not on the server, where it " & _
vbCrLf & "would be shared with other users.", _
vbExclamation + vbOKOnly, "Cannot Open Database!"
Application.Quit
Exit Sub
ErrHandler:
If (Err.Number = 70) Then ' Permission denied, share violation.
Close #nFileNum
Else
MsgBox "Error in Form_Open( ) in " & Me.Name & _
" form." & vbCrLf & vbCrLf & "Error #" & _
Err.Number & vbCrLf & Err.Description, _
vbExclamation + vbOKOnly, "Error!"
End If
Err.Clear
End Sub ' Form_Open( )
Save and compile the code.
Select the Tools -> Options... menu to open the Options dialog window. Select the "Advanced" tab. Set the "Default open mode" option to "Exclusive." Select the "OK" button.
Select the Tools -> Startup... menu to open the Startup dialog window. Select the name of the startup form in the "Display Form/Page" combo box. Select the "OK" button.
Next, disable the AllowBypass key by running the ChangePropertyDdl( ) function listed on the following Web page to disable the ShiftBypass key: http://www.mvps.org/access/general/gen0040.htm
However, changes need to be made to make that function work in more recent versions of Access. If you are using a more recent version, then:
Add the following line:
Const ITEM_NOT_IN_COLLECTION = 3265
Change the following line:
If Err.Number = conPropNotFoundError Then
To:
If ((Err.Number = conPropNotFoundError) Or _
(Err.Number = ITEM_NOT_IN_COLLECTION)) Then
Sample usage:
Call ChangePropertyDdl("AllowBypassKey", dbBoolean, False)
Subsequent users who attempt to open the database file will receive a trappable error: Run-time error 3045: Couldn't use '<filename>'; file already in use.
Top
How to programmatically select the first record on the subform.
Q: How can I select the first record in the subform using VBA?
A: For an example, place a button on the main form and paste the following code into the main form's module:
Private Sub SelRecBtn_Click()
On Error GoTo ErrHandler
Me!subformCtrlName.SetFocus
Me.Controls("subformCtrlName").Form.Requery ' Sets focus to 1st record.
RunCommand acCmdSelectRecord
Exit Sub
ErrHandler:
MsgBox "Error in SelRecBtn_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
End Sub ' SelRecBtn_Click( )
. . . where subformCtrlName is the name of the subform control, and SelRecBtn is the name of the button. Save and compile the code, then open the form in Form View. Navigate to any record except the first one on the subform, then select the button on the main form. Focus will jump to the first record and it will be selected. This will work even if the main form is bound and has records displayed.
Top
How to rename a file in VBA.
Q: How can I rename a file in VBA?
A: To rename a file, one would think the command would be something simple, such as "Rename." No. It's much more difficult to remember in VBA. Use the following syntax:
Top
How to set database options using VBA while creating a new database.
Q: I would like to set several options using VBA in an Access database I am creating in code. The options I would like to set are "Track Name AutoCorrect Info" and "Perform Name AutoCorrect" to False and "Compact on Close" to True. Is there a way to set these options on a database while creating it?
A: Yes. First, set a reference to the DAO library. Then paste code from the following example into your module to be executed from the click of a command button:
Private Sub SetOptionsBtn_Click( )
On Error GoTo ErrHandler
Dim accApp As New Access.Application
Dim db As Database
Set db = CreateDatabase("C:\Test\NewDB.mdb", dbLangGeneral)
'-------------------------------------------------------------------------
' Do whatever you need to do in your new database . . .
'-------------------------------------------------------------------------
db.Close
DoEvents ' Give OS chance to save file before accessing it.
'-------------------------------------------------------------------------
' Connect to the new database using an instance of
' Access.
'-------------------------------------------------------------------------
accApp.OpenCurrentDatabase "C:\Test\NewDB.mdb", True
accApp.SetOption "Track Name AutoCorrect Info", False
accApp.SetOption "Perform Name AutoCorrect", False
accApp.SetOption "Auto Compact", True
CleanUp:
Set db = Nothing
Set accApp = Nothing
Exit Sub
ErrHandler:
MsgBox "Error in SetOptionsBtn_Click( ) in " & Me.Name & " form." & _
vbCrLf & vbCrLf & "Error #" & Err.Number & vbCrLf & Err.Description
GoTo CleanUp
End Sub ' SetOptionsBtn_Click( )
Top
How to set the macro security level in Access 2003 runtime.
Q: How can I change the macro security level in Access 2003 runtime to "low"? The retail version allows me to access the menu to make the changes to security levels, but the runtime version of Access doesn't have the built-in menus.
A: Creating a signed Digital Certificate is the best long-term strategy since it allows users to keep their Macro Security settings at either medium or high, but setting the security level to low is a quick solution to stop the annoying (and confusing) pop-up messages until the Digital Certificate is in place. To change the Macro Security setting to low, you can either write some code or you can make a change to the Windows Registry for each computer that needs the new security level. The easiest way is to change the Registry setting. However, many people are uncomfortable altering their Windows Registry, and making mistakes can cause serious problems, such as the inability to reboot the computer. Make sure that you back up the Registry before making changes, and make sure that you also know how to restore the Registry from a backup before making changes, too.
The Registry keys that need to be changed or added are:
\\HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\SandboxMode
- Change the value of this key to 2 if it isn't set there already. (It's the default.)
Values available for the Sandbox mode:
|
0 =
|
Sandbox mode is disabled at all times.
|
|
1 =
|
Sandbox mode is used for Access applications, but not for non-Access Applications.
|
|
2 =
|
Sandbox mode is used for non-Access applications, but not for Access Applications. (Default)
|
|
3 =
|
Sandbox mode is used at all times.
|
The security level can be changed for either individual users or created for all users who log onto the computer.
If you need to set the security for an individual user, then you need to change this key:
\\HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Access\Security\Level
- Change the value of this key to 1 for low security. (2 = medium security; 3 = high security)Or for all users on the computer, you need to create this key:
If you need to set the same security level for all users who log onto the computer instead, then you need to create this new Registry key:
\\HKEY_LOCAL_MACHINE\Software\Microsoft\Office\11.0\Access\Security\Level
- Create the key of type DWORD and set the value to 1 for low security. (2 = medium security; 3 = high security)
If you'd rather use code to make the change in the security setting for the current user, then you may copy and paste the code below into a module in your runtime application so that the user will be able to use the menu to alter this security setting manually. The subroutine can be called during the startup of the runtime application or from the click event of a command button.
'==============================================
' Sub: openSecurityDialog( )
' Author: Q-Built Solutions; www.QBuilt.com
' Date: 3 Aug. '04
' Note: This sub requires the Microsoft Office 11.0 Library
' as a Reference.
' This sub activates the Macro -> Security submenu to open
' the Macro Security dialog window for the user who is using
' the runtime version of Access '03 instead of the retail version,
' which may need to have the default security level changed in
' order to run the code in the modules.
' Thanks to Victor Escalera, we have the code needed for the
' Spanish version of Access, as well. To use the Spanish version
' just replace the lines marked "English version" with the lines
' marked "Espanol (Spanish version)." Thanks, Victor!
'==============================================
Public Sub openSecurityDialog( )
On Error GoTo ErrHandler
Dim CmdBar As CommandBar
Dim CmdBarPopup As CommandBarPopup
Set CmdBar = Application.CommandBars("Menu Bar")
Set CmdBarPopup = CmdBar.Controls("Tools") ' English version.
'Set CmdBarPopup = CmdBar.Controls("Herramientas") ' Espanol (Spanish version).
Set CmdBarPopup = CmdBarPopup.Controls("Macro")
CmdBarPopup.Controls("Security...").Execute ' English version.
'CmdBarPopup.Controls("Seguridad...").Execute ' Espanol (Spanish version).
CleanUp:
Set CmdBarPopup = Nothing
Set CmdBar = Nothing
Exit Sub
ErrHandler:
MsgBox "Error in openSecurityDialog( )" & vbCrLf & _
"in SecurityFunctions module." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp
End Sub ' openSecurityDialog( )
Copyright © 2004 - 2006 Q-Built Solutions. All rights reserved.
Top
|
