Microsoft Access Help and Tips
Once you've learned Access properly you'll find it to be a powerful program; however its true power comes with knowing the nooks and crannies of its built-in abilities. Below we describe some helpful titbits from our experience.
Microsoft Access Videos
Software-Matters have a YouTube channel which is regularly updated with useful MS Access tips and training videos.
The first video is How to Create an Autokeys Closing Macro
Setting Error Traps
If some VBA code encounters an error, the user will be presented with the option to debug the code. If you do not wish to have your end users tinkering in your code then you can set it to have some other behaviour upon encountering an error. We do this with an 'error trap'. In the example below we edit the code in our 'paths' tip below to have an error trap.
Private Sub Form_Open(Cancel As Integer)
On Error GoTo Error_Trap
'The database property would have the path and name of the image stored in it.
Dim strPath as Variant
strPath = CurrentDb.Containers("Databases")!userdefined.Properties!ImagePath
Me!Logo.PictureType = 1
Me!Logo.Picture = strPath
Exit_Error_Trap:
Exit Sub
Error_Trap:
Msgbox Err.Description
Resume Exit_Error_Trap
End Sub
We have told the code to go to the section named 'Error_Trap' when errors occur, and have created this section by writing this title followed by a colon. This is usually placed at the end of the code. However, since this trap is part of the code, we need something before it to stop the code reaching it when there is not an error. Thus we have also inserted the 'Exit_Error_Trap:' section which simply ends the process as expected.
What our error trap actually does is give us a message box describing the error, to which the user can only press okay; then it uses the 'Resume' command to tell it where to go next. Here we have just told it to go to our little exit routine; however there may be cases where you want some special behaviour to trigger upon an error, or to just carry on ignoring the error (in which case we would write 'Resume Next' in our error trap instead).
Simplifying with Public Subs and Functions
If when coding in VBA you find yourself having lines that repeat themselves in multiple places in your database, you can
make your code both shorter and easier to edit by making a Public Sub (if the lines carry out a process) or Public Function (if the lines return something) with those lines as content. Not only will this save you the trouble of writing out those lines every time you need them, but it will make it easier to edit what they do as you need only change them in one location, rather than having to go around looking for each place you used them and making an edit.
If your set of lines occasionally needs to output something different, you might consider adding an extra argument to your Sub or Function that causes it to behave differently. This allows you to have lots of very similar processes all in one place for ease of editing.
Public Function StringMaker(Mode As Integer) As String
' A function to return a certain string, based on the integer input
If Mode = 1 Then
StringMaker = "Hello World!"
ElseIf Mode = 2 Then
StringMaker = "Good Day Planet Earth!"
ElseIf Mode = 3 Then
StringMaker = "Salutations, children of Terra!"
End If
End Function
You would use this function using a line of code like this: MyMessage=StringMaker(2).
Protecting Against Nulls with Nz or Validation Rules
If you are doing a calculation like Field1 * Field2, the process will fail if either of these fields contain a null on the record concerned. One way of avoiding this problem is to write nz(Field1,0)*nz(Field2,0). The nz function returns the second entry if the first entry is null; so here we are using 0 in cases there the fields are null, which will not cause an error in our calculation. Of course if we were dividing, we would have to rethink this.
Another way to avoid this problem is to never let a null occur. Do this by setting 'Is Not Null' as the validation rule on the fields in question (in the Properties section in Design view of their tables while the field is selected). You might wish to combine this with a default value for the field, so it is easier for the user to avoid leaving it empty. Remember to write in the validation message that the field cannot be null, or the user might not know why they cannot move on to the next field!
Storing paths in a Database Property and using Linked images
Rather than hard-writing paths in your VBA you can instead use a Database Property to store a path and then call it using VBA. This way if you change the file location or rename a folder you just need to change the database property value rather than rewrite any code. The Database Properties menu is found under File/Info far right hand side (2010) or Office Button/Manage (2007).
For example you could use a path to link an image to a form rather than embedding it. This confers the advantage of being able to change the image in the database by just replacing the file to which it links, rather than having to enter the database and manually change the embedded image. Additionally, it will reduce the file size of the Access database file by storing the image data separately; this could be important if you have a large number of images.
Combining the ideas of the last two paragraphs, we would write the following code into the On Open event of our form.
Private Sub Form_Open(Cancel As Integer)
'The database property would have the path and name of the image stored in it.
Dim strPath as Variant
strPath = CurrentDb.Containers("Databases")!userdefined.Properties!ImagePath
Me!Logo.PictureType = 1
Me!Logo.Picture = strPath
End Sub
NB: The above code requires an Image control named 'Logo' on the form.
How to switch the Windows themed controls on/off
Using the Windows themed controls can make your database appear more consistent with the rest of the user's operating system. When you start new databases, this is usually switched on by default. The switches are in the following locations:
MS Access 2003 - Tools/Options
MS Access 2007 - Microsoft Office Button (round button)/Access Options/Current Database
MS Access 2010 - File/Options/Current Database
How to return the Windows Username of the current user (tested in 2003-2010)
Add the following code to the OnClick event of a button to have a message box appear telling you the current user's Windows Username:
MsgBox("The current user is: " & Environ("Username"))
Note that this will not allow the return of a username on a Mac.
How to return the name of the current user account
Add the following code to the OnClick event of a button to have a message box appear telling you the current user:
MsgBox("The current user is: " & CurrentUser)
This will tell you the name is 'Admin' unless you have set up Workgroups with users assigned to them.
How to test current user group
This function checks to see if the current user is part of a specified user group. It works by sending the desired group to test (strGroup) and it returns true or false.
Public Function CHKGroup(strGroup As String) As Boolean
Dim wks As Workspace
Dim Usr As User
Dim Grp As Group
Dim ActualUsr, ActualGrp As String
Set wks = DBEngine.Workspaces(0)
CHKGroup = False
' We set it to return False unless otherwise found to be true below
With wks
'Compare all users within the
'workgroup with the current user
For Each Usr In .Users
'If the current user = user found within
'workgroup then verify to what group
'this user belongs
If CurrentUser() = Usr.Name Then
If Usr.Groups.Count <> 0 Then
For Each Grp In Usr.Groups
If Grp.Name = strGroup Then
CHKGroup = True
End If
Next Grp
End If
End If
Next Usr
End With
End Function
VBA References
A VBA Reference points the program in the direction of the libraries that store its built-in methods and properties for all the predefined objects e.g. Strings. If multiple users are sharing a database that uses VBA References to Excel/Word/Outlook, it is best for all users to have the same version of Office installed (e.g. all 2010). If they have different versions of Office, they will need their own copies of the Front end, with the References set appropriate to their version of Office (see below).
A common symptom of Reference problems is that suddenly an Office program stops being able to do things that you thought were built-in features, like telling you today’s date. The error messages you get in such situations suggest that the features are missing, and might even lead you to think that you need to update to a newer version of Office in order to get them.
But in reality this problem is likely to be due to incorrect References. To get around this you can manually set the References through the VBA Editor window under the ‘Tools’ menu. From here you can choose References for the version of Office in which the file was developed, likely leading to any misbehaving functions returning to their old selves.
Using VBA to copy files in a zip archive
If you have a task that regularly requires you to zip up large numbers of files, you can make your life much easier by having Access do it for you through a VBA routine that can be used stand-alone or inserted into an existing program.
You will need to edit this code where marked to specify the folder containing the files to zip (the process will zip up ALL files in the selected folder) and a location for the final zip to be placed.
MS Access Tips and Training Videos
How to Create an Autokeys Closing Macro
We'll walk you through creating a custom hotkey for Microsoft Access 2010 using an autokeys macro. The macro we'll show you is designed to avoid a problem where incorrectly closing an object can save filters and sorts into their properties and alter their behaviour in the future.
Watch the Autokeys Closing Macro video on YouTube: Autokeys Closing Macro
We also have a Microsoft Excel help and tips page.
If you enjoyed this article or found it useful, why not tell others about it?