This is a collection of tips that don't warrant a whole page to themselves, but could make your life easier.
Access is an event-driven language. Events happen when a form is loaded, a button is clicked, a selection is made from a combo box, and so on. We can create code that responds to such events so that certain controls are displayed or hidden, records are filtered, another form or report is launched, and much more. This tip shows you how to get into the VBA environment, so that you can create (or paste) code.
Let's assume that you have a button called cmdOpenForm and you want to open frmDetail when the button is clicked. Do the following:
Private Sub cmdOpenForm_Click() End Sub
Edit this so it reads
Private Sub cmdOpenForm_Click() DoCmd.OpenForm "frmDetail", acNormal End Sub
Save the form, go to Form view, and try it out.
The line that launches the form uses the DoCmd object, which enables you to replicate almost all of the commands in the Macro builder. The syntax for DoCmd.OpenForm is
DoCmd.OpenForm "form name", View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs
With the exception of the form name, all other arguments are optional. However, if you want to define a WhereCondition and not a FilterName, you need include a comma to show that you skipped an argument:
DoCmd.OpenForm "frmDetail", acNormal,,strWhere
For a more detailed description of the arguments in the OpenForm method, and a number of examples, see this page.
Sometimes, you will find that you can't create new reports or print existing ones. There are a number of possible reasons for this, and most have to do with how Access communicates with your default printer. Because the printer driver is used in both Design and Preview mode, faulty communication will prevent you from printing, designing or previewing reports. Try this:
If you have previously been able to preview and design reports in your database, and now you can't try this:
MDE files are compiled versions of Access databases that prevent users from changing the design of forms, reports and code modules. If you create a multi-user Access database, providing users with an MDE version will prevent them from making unwanted changes to the application. To create an MDE, do the following:
If a database needs maintenance (for example, making design changes to the back end, backing up or performing a compact and repair), you need to ensure that you are the only user in the system. With more than two or three users it can be difficult to contact everybody and ask them to please get out for a few minutes. If somebody goes out to lunch or leaves for the day while still logged in to the database, you can't do much about it.
That's the reason for this technique. With a hidden form that checks for the presence of a specific text file, you can give users a bit of warning and then force them to quit the database. This can simplify your job without causing your users undue frustration, if you use it sparingly.
Note: This tip has been updated so that the code works in all versions of Access. The previous code for the monitor form used the Application.Filesearch method which no longer works in Office 2007; now it uses the Windows Dir function instead.
|The logic is shown in the adjacent diagram|
Assuming that your database has a startup form (in this case, frmMenu), you need to put this line of code into the form's Open event:
DoCmd.OpenForm "frmMonitor", , , , , acHidden
This will launch frmMonitor in the background. Note: If you are unsure how to create the code for an event, check the tip at the top of this page.
The monitor form is very simple. It has some code in the Timer event, which is triggered when the Timer Interval is reached. Because the Timer operates in milliseconds, a value of 60000 in the Timer Interval will trigger the Timer event once a minute. Don't use a comma when entering the Timer Interval value.
Private Sub Form_Timer() 'searches for DataBaseOn.txt every minute. 'if not found, quits Access Dim fn fn = Dir(CurrentProject.Path & "\DatabaseOn.txt") If fn = "" Then 'display a form with a message DoCmd.OpenForm "frmClock", , , , , acDialog 'get out Application.Quit End If End Sub
This code uses Dir to look in the same folder as the current database for a file called DataBaseOn.txt. If it finds the file, nothing happens. If not, frmClock is launched; this counts down from 15 seconds. Once it gets to 0, the form closes and so does the database. Some points to note:
frmClock has a label called lblClock, with the caption "This database will be closing in 15 seconds". The following code changes the caption on the label, and closes the form when you get to 0. By setting the Timer Interval to 1000, the caption updates every second. This is the full code for the form:
Option Compare Database Option Explicit Dim iCounter As Integer Private Sub Form_Load() iCounter = 15 End Sub Private Sub Form_Timer() If iCounter > 0 Then iCounter = iCounter - 1 If iCounter = 1 Then Me.lblClock.Caption = "This database will be closing in " _ & iCounter & " second" Else Me.lblClock.Caption = "This database will be closing in " _ & iCounter & " seconds" End If If iCounter = 0 Then DoCmd.Close acForm, Me.Name, acSaveYes End Sub
That's it. Two forms, some generic code, and you can remove a major source of frustration. Adjust the intervals to suit your needs.