OnClick Open a Form Based on Value on Current Form

The On Click event is triggered when you press the mouse button. The colloquial terms for mouse button actions are left click and right click, but the official Windows terminology is primary and secondary. The On Click event will only respond to the primary or the left click action. The same is true for the On Dbl Click event property as well. If you have selected to switch your mouse button, then these properties will trigger when the right mouse button is pressed.

As you can imagine, the On Click property is heavily used, as there is typically at least one command button on every form. Close the form, exit the application, print a report, or open another form are all common examples of On Click use.

For example, suppose that there are two forms, frmCustomer and frmOrder, which display information about customers and orders respectively. As you are browsing the customer information, perhaps you would like to quickly browse the order information, on frmOrder, that pertains only to the current customer. With the help of a command button, and some VBA code, this possibility is an easy reality.

The final pieces of information that complete this puzzle are the field names that correspond between the frmCustomer CustomerID and the frmOrder CustomerID. Typically, the table for each of these entities contains the field called CustomerID. Also, the name of the control that is bound to the CustomerID on frmCustomer is needed. So, assume that it is called txtCustomerID.

Put all of this together, and you can add the following type of code to any form to open another, where a dependency exists.

Private Sub cmdShowOrders_Click() If Not Me.NewRecord Then

DoCmd.OpenForm "frmOrder", WhereCondition:="CustomerID=" & txtCustomerID End If End Sub

The example above starts with the name of the command button. Following a standard naming convention, the prefix cmd signifies that this is a command button, and not a toggle button. Since the button will show the Orders form, the remainder of the name ShowOrders will make debugging and the maintenance of the code easy to recognize in the future. Finally, the event property is On Click.

The NewRecord property is a Boolean value that signifies if the user is on the New Record of the form. (In Access, all records are added on the New Record.) If the user is entering a new customer, then there should not be any existing orders, thus there is no need to open the Orders form.

The OpenForm method will open the form called frmOrder, and issue a Where clause to specify which records to display in the form. Some people call this a filter, others a query; either way, the records on the form will be restricted to only those that meets the criteria. In this case, the criteria specified will restrict the data to only those customer IDs that match the value in the text box called txtCustomerID. For clarification, the field name on the left of the "equals to" symbol (=) refers to the field on the opened object. The field name on the right refers to a field on the calling object.

To take this example one step further, suppose that each order has one invoice printed for it. When you are viewing the Order record, you can print the invoice for the order. The code is nearly identical, except that a Report is being opened.

Private Sub cmdShowInvoice_Click() If Not Me.NewRecord Then

DoCmd.OpenReport "rptInvoice", WhereCondition:="OrderID=" & txtOrderID End If End Sub

0 0

Post a comment