in the Toolbar. Then click on the form where you'd lke the label appear and enter the text that you'd like to display in the label.
=IIf([From_Date]=[To_Date],1,DateDiff("d",[From_Date],[To_Date]))
To do this, open your form in Design View.
Under the View menu, select Properties. Highlight the textbox called Textbox1. Click on the property called "After Update". A button with 3 dots to the right should appear. Click on this button.
When the Choose Builder window appears, highlight Code Builder. Click on the OK button.
Then place the following code:
Private Sub Textbox1_AfterUpdate()
If Textbox1.Value = "1" Then
Textbox2.Value = "10"
End IfEnd Sub
Now, when a value of 1 is entered in Textbox1, Textbox2 will automatically be populated with a value of 10.
The command that you want to use to open a form as a dialog is as follows:
DoCmd.OpenForm "Calendar", , , , , acDialog
In this example, we are opening a form called Calendar as a dialog.
The acDialog option on the OpenForm command specifies that the parent form (ie: the original form that opened the Calendar form) will not be accessible until the Calendar form is closed.
First_Field.SetFocus
For example, if your first field was called CompanyName, you would use the following VBA code:
Private Sub Form_Current()
CompanyName.SetFocus
End Sub
For example, you could place the following code on the Form's On Error event to trap the primary key violation:
Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 3146 Then
MsgBox ("You have violated the primary key.")
Response = 0
End IfEnd Sub
Now, whenever a user tries to enter a record that violates the primary key, the following error message will appear:
Let's take a look at the example. Below, we have a form where the user can change the tab order by selecting a radio button.
If the user selects the "Order tabs down and then across" radio button, the tab order will be as follows: Supplier ID, Company Name, Contact Name, Contact Title, Phone, Fax, and Home Page.
If the user selects the "Order tabs across and then down" radio button, the tab order will be as follows: Supplier ID, Phone, Company Name, Fax, Contact Name, Home Page, and Contact Title.
This tab reordering is done by the following code:
Private Sub frameTabOrder_Click()
'Order tabs down and then across was chosen
If frameTabOrder.Value = 1 Then
[SupplierID].TabIndex = 0
[CompanyName].TabIndex = 1
[ContactName].TabIndex = 2
[ContactTitle].TabIndex = 3
[Phone].TabIndex = 4
[Fax].TabIndex = 5
[HomePage].TabIndex = 6'Order tabs across and then down was chosen
Else
[SupplierID].TabIndex = 0
[Phone].TabIndex = 1
[CompanyName].TabIndex = 2;
[Fax].TabIndex = 3
[ContactName].TabIndex = 4
[HomePage].TabIndex = 5
[ContactTitle].TabIndex = 6End If
End Sub
DoCmd.OpenForm "Categories Form"
Forms![Categories Form].Visible = False
This code will load a form called Categories Form. Then it will make the form invisible, so that the user can not see it.
You can then close your form with the following VBA code:
DoCmd.Close acForm, "Categories Form"
To sort the records in the form based on a calculated field, you need to add the calculated field to the result set in the Record Source property. We've provided a download to demonstrate how this can be done.
We've created a form called frmAccounts that lists all records in the Accounts table. We've then created a textbox called TotalUnits that displays the number of records in the Account_units table (for the account displayed on frmAccount).
Open the form in Design view and select Properties under the View menu. Select the property called Record Source. A button with three dots should appear to the right of this property. Click on this button.
When the Query Builder appears, select all of the fields from the Accounts table and then enter the function for the calculated field. In this example, we've entered the following:
CalcUnits([AcctNo])
Next, select how you've like to sort the calculated field. We've chosen to sort in Descending order.
You can close down the Query Builder. When prompted with the message box, "Do you want to save the changes made to the SQL statement and update the property?", click on the Yes button.
Now when you open the form, you'll see the form records sorted in descending order based on the "Total Units" field.
We've created a form called frmAccounts that lists all records in the Accounts table. We've then created a textbox called TotalUnits that displays the number of records in the Account_units table (for the account displayed on frmAccount).
If you view the Properties for the TotalUnits textbox, you can see that the Control Source property is set to:
=CalcUnits([AcctNo])
This textbox is linked to a custom function that we've created called CalcUnits. You can find this function defined in the Module called modGeneral in our sample database.
This function accepts the AcctNo as the parameter. It then queries the Account_units table and returns the number of records that exist for this AcctNo.
Then select the property called "On Key Down" and click on the button with the three dots to the right.
When the Choose Builder window appears, highlight Code Builder and click on the OK button.
Next, paste in the following code:
If KeyCode = vbKeyEscape Then
KeyCode = 0
End If
Your code should look as follows:
Now, the Esc key should be disabled on your form.
To do this, create a label. Right-click on the label and view its properties.
Set the Back Color property of the label to the color that you want. Then set the Special Effect property to Raised.
Then code it's Mouse Down event to:
[LabelName].SpecialEffect = 2
Code the Mouse Up event:
[LabelName].SpecialEffect = 1
Code the OnClick event as if it was a command button.
Center the label text horizontally using the text alignment tool button or setting the Text Align property to Center.
If you want to align text vertically, Press Shift + Enter then type in the label text.
It will look and act like a command button.
We recommend that you copy the MouseWheel.dll file to the following directory:
c:\Program Files\Microsoft Office\Office\
But you can copy this file to any directory that you wish. However, you should consistently copy this file to the same directory location on each computer.
Next, you'll need to register the file. This is particularly important if you are running Access 97. However, if you are running Access 2000 or higher, it is still a good idea to register the file.
To do this, open a Command Prompt. Type the following command and press enter:
cd /program files/microsoft office/office
This will change your prompt to point to the directory where you've saved the MouseWheel.dll file. Now, we want to register the file. Type the following command and press enter:
regsvr32.exe mousewheel.dll
The following window should appear indicating that the MouseWheel.dll file was successfully registered.
Next, open your Access database and press Alt-F11. This will take you to the Visual Basic Editor.
Under the Tools menu, select References.
When the References window appears, click on the Browse button.
Find the MouseWheel.dll file that you saved to the computer. In this example, the MouseWheel.dll file can be found under c:\Program Files\Microsoft Office\Office\.
Click on the Open button.
Now when you return to the References window, the MouseWheel.dll file should appear with a checkmark to the left. Click on the OK button.
Open your Form in design view and go to the Visual Basic editor for the form. You can do this by clicking on the button to the right of the On Open event for the Form.
When the Choose Builder window appears, highlight Code Builder and click on the OK button.
When the Microsoft Visual Basic window appears, paste in the following code:
Option Compare Database
Option ExplicitPrivate WithEvents clsMouseWheel As MouseWheel.CMouseWheel
Private Sub Form_Load()
Set clsMouseWheel = New MouseWheel.CMouseWheel
Set clsMouseWheel.Form = Me
clsMouseWheel.SubClassHookForm
End SubPrivate Sub Form_Close()
clsMouseWheel.SubClassUnHookForm
Set clsMouseWheel.Form = Nothing
Set clsMouseWheel = Nothing
End SubPrivate Sub clsMouseWheel_MouseWheel(Cancel As Integer)
Cancel = True
End Sub
The code should look as follows:
You can now close down the Microsoft Visual Basic editor. (save any changes, if prompted)
Save changes to your form. Now when you view your form and move the mouse wheel, the records should no longer scroll.
Let's take a look at an example. Often times, it is useful to know when a record has been created in your Access database. So we've created a Customers table that contains a field called Create_Date. We will use this field to store the exact date and time of when the record was created.
This is an example of a form that we've created. There is code on the "Before Insert" event of this form to update the field called Create_Date. When you are viewing this form in "form mode", you can not see the field called Create_Date.
However, if you view this form in Design View, you will notice that there is, in fact, a field called Create_Date. This field has been set to "not visible" so that the user can not interfere in the "Before Insert" event code that will run.
When a new record is inserted into the Customers table, the Create_Date field will be updated with the current system date/time.
The VBA code is quite simple:
Private Sub Form_BeforeInsert(Cancel As Integer)
'Set the Create_Date field in the Customers table to the current system
'date when a new record is created
Create_Date = Now()
End Sub
For example:
Form2.Repaint
In this example, there is a form called Form2. The Repaint method forces the form to redraw itself.
Private Sub Command1_Click()
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
End Sub
In this example, we've created a button called Command1. When the button is clicked, the form will requery the data displayed in the form. If the data has changed, these changes will now be displayed in the form.
Private Sub Command1_Click()
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70End Sub
In this example, we've created a button called Command1. We've placed the above code on the button's click event.
Dim LSQL as string
LSQL = "select * from Suppliers where Supplier_ID = " & txtSupplier_ID
The result set will return all records from the Suppliers table where the Supplier_ID equals the value that is found in the textbox control called txtSupplier_ID.
Our next example will look at a control whose value contains a string. The following code uses a control called txtSupplier_City in an SQL statement in VBA code.
Dim LSQL as string
LSQL = "select * from Suppliers where Supplier_City =' " & txtSupplier_City & "'"
You will notice in this example that you need to place a single quote in front of the control (txtSupplier_City) as well as one after. This is because string values must always be surrounded by single quotes in an SQL statement
This result set will return all records from the Suppliers table where the Supplier_City equals the value that is found in the textbox called txtSupplier_City.
For example, you could place the following code on the BeforeUpdate event for the form.
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim LResponse as integer Dim LMsg as string
LMsg = "Do you wish to save changes?"
LResponse = msgbox(LMsg, vbYesNo, "Save changes")If LResponse = vbNo then
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
End ifEnd Sub
In this example, before the changes are saved, the user is prompted with a message box. This message box asks the user if he/she wishes to save the changes. If the user responds "yes", then the changes are saved. If the user responds "No", then the changes are undone and the record is not updated.
For example:
If CurrentProject.AllForms("Suppliers").IsLoaded = True Then
.
End If
This code checks to see if the form called Suppliers has been loaded in the current Access session.
Here is an example of how we've used IsLoaded.
Function GetCity() as string
If CurrentProject.AllForms("Suppliers").IsLoaded = True Then
GetCity = Form_Suppliers.txtSupplier_City
Else
GetCity = ""
End IfEnd Function
This function is used to retrieve the value of the txtSupplier_City in the Suppliers form.
If the Suppliers form is loaded, it will return the value in the textbox called txtSupplier_City. If the form is not loaded, it will return a blank value. This helps us avoid errors if we try to access the txtSupplier_City value when the Suppliers form is not loaded.
You can then use this function in a Query or a Report. or for that matter, anywhere within your Access database.
Under the View menu, select Properties.
When the Properties window appears, set the "Close Button" property to Yes.
Now when you view your form, the Close button should now be re-enabled as follows:
Under the View menu, select Properties.
When the Properties window appears, set the "Close Button" property to No.
Now when you view your form, the Close button should be disabled as follows:
First, we've set up the form which displays the Customer records. The user can click on the Search button to perform up to 3 search criteria that are either textx or numeric.
When the Search button is pressed, the following form will appear:
The user can select up to 3 search criteria.
When the Search button is pressed, the following message box will appear:
Your form will then display the search results as follows:
Pay close attention to how the RecordSource is set on the form called frmCustomers. The RecordSource on this form is updated using VBA code and not in the Properties window.
We've also included a "Show all customers" button in case you wish to view all customer records, and a "Generate Report" button to display a report of the search results.
First, we've set up the form which displays the Customer records. The user can click on the Search button to perform a search either on one of the text fields or one of the numeric fields in the Customers table.
When the Search button is pressed, the following form will appear:
The user can select the field to search, the operation to perform (such as contains), and then enter the value to search for. In this example, we've selected the "CompanyName" field, "contains" as the operation, and "store" as the value to search for in the "CompanyName" field.
When the Search button is pressed, the following message box will appear:
Your form will then display the search results as follows:
You can also perform a numeric or mathematical search operation. For example, this time we've selected the "TotalSales" field, "is greater than" as the operation, and 5000 as the search value.
Now your form will then display the search results as follows:
Pay close attention to how the RecordSource is set on the form called frmCustomers. The RecordSource on this form is updated using VBA code and not in the Properties window.
We've also included a "Show all customers" button in case you wish to view all customer records, and a "Generate Report" button to display a report of the search results.
First, we've set up the form which displays the Customer records. The user can click on the Search button to perform a search on any text field in the Customers table.
When the Search button is pressed, the following form will appear:
The user can select the field to search on and the search criteria. In this example, we've selected the "CompanyName" field and "store" as the value to search for in the "CompanyName" field.
When the Search button is pressed, the following message box will appear:
Your form will then display the search results as follows:
Pay close attention to how the RecordSource is set on the form called frmCustomers. The RecordSource on this form is updated using VBA code and not in the Properties window.
We've also included a "Show all customers" button in case you wish to view all customer records, and a "Generate Report" button to display a report of the search results.
First, we've set up a form where the user can enter a search string. In the example below, we've entered "store" as the value to search for in the CompanyName field.
When the Search button is pressed, the following message box will appear:
Your form will then display the search results as follows:
Please note that this method of searching is achieved by using a form and a subform.
Pay close attention to how the RecordSource is set on the form called frmCustomers_sub. The RecordSource on this form is updated using VBA code and not in the Properties window.
We've also included a "Show all customers" button in case you wish to view all customer records.
When the Startup window appears, set the "Display Form/Page" option to the form that you wish to open at startup. Then click on the OK button.
Now when you open your Access database, the Suppliers form should start up automatically.