|
| |
|
|
Examples of Forms, Controls and Events
Form and Controls Techniques:
|
|
Sequence of
commonly used form and control events. |
When a form is opened:
Open ->Load ->Resize ->Activate ->Current
When a Focus is moved to a control as
the form is opened:
Open (form) ->Load (form) ->Resize (form) ->Activate (form)
->Current (form) ->Enter (control) ->GotFocus (control)
When a form is close:
Unload ->Deactivate ->Close
When a form is minimized:
Resize ->Deactivate
When a form is restored from minimized:
Activate ->Resize
When keys are pressed:
KeyDown ->KeyPress ->Dirty ->KeyUp
When a mouse is clicked:
MouseDown ->MouseUp ->Click
When Focus shifts from one form to
another:
Deactivate (1st form) ->Activate (2nd form)
When Focus leaves a control and the form is
closed:
Exit (control) ->LostFocus (control) ->Unload (form)
->Deactivate (form)
After characters
are typed in text/combo box, and before Focus moves to
another control:
KeyDown ->KeyPress ->Change ->KeyUp
After data are changed and committed to the form, and Focus
moves to another control:
BeforeUpdate ->AfterUpdate ->Exit ->LostFocus
For each key you press in a text box or in
the text box portion of a combo box:
KeyDown ->KeyPress ->Dirty ->Change ->KeyUp |
It is also good to understand how data buffering works on the background when
each time we edit data on the form.
[read
more...]
The Me keyword
When writing code with a form's module, use the
Me keyword to refer to that form and its controls. The Me
keyword refers to the form itself, or form/report that has this
code. For example, if the form frmOrders is open, me refers to that.
The Me object is available to every procedure in a Form or Report
module.
When writing code behind the form, for example, you can
assign the record source and change the form's
caption with this code using Me keyword as follow:
Me.RecordSource = "Qry_SalesQ3"
Me.Caption = "Q3 Customer Orders"
If you are writing code in a code module outside the
form, you need to explicitly reference the form through the Forms
collection; and must make sure that the form is loaded, or
else you will encounter runtime error. For example:
Forms!Form1.RecordSource =
"Qry_SalesQ3"
It is useful to pass Me
(the current form or report) to a
procedure module as in this Call
example:
Call
SerializeUDF(Me)
Example: changing the focus to the control
Private Sub
Form_Open(Cancel As Integer)
Dim strCustomerID As String
strCustomerID = InputBox("enter the CustomerID:")
'
DoCmd.GoToControl "ctlCustomerID"
' substituted by
the Me statement on the next line below.
' this line sets focus on the control, substitutes for
the line above. Both lines give the same result.
Me.ctlCustomerID.SetFocus
' the control is already
listed as a member in dropdown list.
See diagram
DoCmd.FindRecord strCustomerID
End Sub |
Example: Update ShipTo controls based on
the value selected in CustomerID combo box.
' The AfterUpdate event
is fired after the changes are committed to the form.
Private Sub
CustomerID_AfterUpdate()
Me!ShipName = Me![CustomerID].Column(1)
Me!ShipAddress = Me!Address
Me!ShipCity = Me!City
Me!ShipRegion = Me!Region
Me!ShipPostalCode = Me!PostalCode
Me!ShipCountry = Me!Country
End Sub |
Opening forms and Errors-handling
You can capture and handle errors when executing the event
procedures, using the simple example below. It uses On
Error Goto statement to identify a label where errors will be
trapped. Then a labeled Exit Sub statement is used to finish
the procedure without going into error-handler. Within the
error-handler, MsgBox displays property and description of the
built-in Err object, on the errors occurred. Refer to another
VBA page in more detail on Runtime Errors Handlers (On
Error Resume Next,
On Error GoTo 0, etc).
Example: create a On-Click event procedure to open a
form and handle errors.
You can either create your code using
example below, or following
these
steps using the create events wizard.
Private Sub
cmdCustomerOrders_Click()
On Error GoTo
HandleErr
DoCmd.OpenForm
"Orders"
ExitHere:
Exit Sub
HandleErr:
MsgBox "Error " &
Err.Number & ": " & Err.Description & " in
cmdOrders_Click"
Resume ExitHere
End Sub |
Open event
Occurs when a form is opened, but before the
first record is displayed. For reports, the event occurs before a
report is previewed or printed. Setting the Cancel argument to True
(–1) cancels the opening of the form or report. To run a macro or
event procedure when these events occur, set the OnOpen property to
the name of the macro or to [Event Procedure].
Private Sub object_Open
(Cancel As Integer) Example: to populate
the form with OpenArgs argument, using the form's Open event. The
RecordSource property is set with a SQL statement and a query. Note that
as the RecordSource code is in the form's Open event, so the records are
not loaded yet; whereas if we use the form's Load event procedure, it
will then be too late to change the RecordSource.
Private Sub
Form_Open(Cancel As Integer)
' set the data source depending on how the
form was called
Select Case Me.OpenArgs
Case "A"
Me.RecordSource = "SELECT Orders.* FROM
Orders WHERE Orders.ShipCountry="UK";"
Case "B"
Me.RecordSource = "qry_OrdersByDate"
Case Else
End Select
End Sub |
You can evaluate the code in the immediate window by
entering this code: DoCmd.OpenForm
"Orders", OpenArgs:="B"
Another way is to use DoCmd.ApplyFilter method to assign the
query or SQL clause to a CommandButton's OnClick event such as
below.
Sub Command1_Click()
DoCmd.ApplyFilter "qry_OrdersByDate"
End Sub
Load event
Happens when a form opens, and the first record is displayed.
It occurs after the Open event. A form's Open event can cancel the
opening of the form, but the Load event can't.
Private Sub object_Load
()
Example:
Private Sub Form_Load()
Dim obj As AccessObject
' loop through all queries in the current database,
and adding the name of each query to the list box.
For Each obj In CurrentData.AllQueries
Me.lstTables.AddItem obj.Name
Next obj
End Sub |
Resize event
The
Resize event occurs when a form is opened and whenever the size of a
form changes.
Private Sub Form_Resize()
Example: shows how a Resize event procedure can be used to
repaint a form when it is maximized.
Private Sub Maximize_Click()
DoCmd.Maximize
End Sub
Private Sub Form_Resize()
Forms!frmContacts.Repaint
End Sub |
Activate event
It occurs when a form or report receives the focus and becomes the
active window.
Private Sub object_Activate()
Example:
Private Sub Form_Activate()
' requery the
subform form when it becomes active. (ensures that
changes made are immediately
' reflected in the subform)
Me.fsubOrders.Requery
End Sub |
Current event
It occurs when the focus moves to a record, making it
the current record, or when the form is refreshed or requeried.
Private Sub object_Current()
Example: to show if the OrderID text box is null, move the focus
to the CustomerName control.
Private Sub Form_Current()
If IsNull(Me.txtOrderID) Then
Me.txtCustomerName.SetFocus
End If
End Sub |
BeforeInsert event
The BeforeInsert occurs when the user types the first character in a new
record, but before the record is actually created.
It applies to textboxes, combo boxes, list boxes, command buttons,
option groups, bound object frames.
Setting the Cancel argument to True (-1) cancels the
BeforeUpdate event.
Private Sub Form_BeforeInsert(Cancel
As Integer)
The BeforeInsert and AfterInsert events are
similar to the BeforeUpdate and AfterUpdate events. These events occur
in the following order: BeforeInsert → BeforeUpdate
→ AfterUpdate → AfterInsert.
BeforeInsert -- user types the first character in a new
record.
BeforeUpdate -- user updates the record.
AfterUpdate -- record is updated.
AfterInsert -- record updated is a new record.
BeforeUpdate event
The BeforeUpdate event occurs before changed data in a control or record
is updated. It occurs when you try to move to a different record (even a
record on a subform) or when the Records|Save Record command is
executed. This event can be used to cancel the update process when you
want to execute complex validations before the update.
Private Sub object_BeforeUpdate(Cancel
As Integer)
AfterInsert event
The AfterInsert event occurs after a new record is added.
You can use an AfterInsert event procedure or macro to requery a
recordset whenever a new record is added.
It applies to textboxes, combo boxes, list boxes, command buttons,
option groups, bound object frames.
Private Sub Form_AfterInsert()
AfterUpdate event
The AfterUpdate event occurs after changed data in a control or record
is updated. It applies only to controls on a form, Not controls
on a report; Not applies to option buttons, check boxes, or
toggle buttons in an option group. It applies only to the option group
itself.
Private Sub object_AfterUpdate()
Example:
to verify that the user wants to create a new record.
Private Sub Form_BeforeInsert(Cancel As Integer)
If MsgBox("Insert a new record here?", vbOKCancel) = vbCancel Then
Cancel = True
End If
End Sub |
Example: to check whether
an product name has already been entered in the database. After the user
types a product name in the Product Name text box, BeforeInsert event compared to
the ProductName field in the Products table. If there is a matching
value in the Products table, a message is displayed that informs the
user that the product name has already been created.
Private Sub
txtProductName_BeforeUpdate(Cancel As Integer)
If (Not IsNull(DLookup("[ProductName]", _
"Products", "[ProductName] ='"
& Me!txtProductName & "'")))
Then
MsgBox "Product has already been
entered in the database."
Cancel = True
Me!txtProductName.Undo
End If
End Sub |
Example: to requery the fsubProductList subform when the
ProductName field is updated.
Private Sub
Form_AfterUpdate()
' requery the subform when the required date changes
DoCmd.RunCommand acCmdSaveRecord
fsubProductList.Requery
End Sub |
Example:
requery the record source for the frmProduct form after a new record
(product name) has been added.
Private Sub Form_AfterInsert()
Forms!frmProducts.Requery
End Sub |
UnLoad event
Occurs after a form is closed but before it's removed from the
screen; i.e. before the Close event. It is triggered when you choose Close from the File Menu,
quits Access by choosing End Task from the task list, quits Windows,
or when your code closes the form.
Private Sub Form_Unload()
Example: checks if the Save button is enabled. If it is, the form
is in dirty state, and user is asked if he wants to save changes to
record. if he responds Yes, the code saves the changes, and form is
unloaded. If he responds No, the code cancels any changes to the
record and the form is unloaded. If he opts to cancel, the Cancel
parameter is set to True, and the form is not unloaded.
Private Sub Form_Unload(Cancel As Integer)
' determine if the the Save button is enabled.
If Me.cmdSave.Enabled Then
' if form is dirty, ask user if wants to save
Select Case MsgBox("Do you want to save?", vbYesNoCancel + vbQuestion,
"Attention")
' If user responds yes, save record and allow unload
Case vbYes
DoCmd.RunCommand Command:=acCmdSaveRecord
Cancel = False
' if user responds no, undo changes to record and allow unload
Case vbNo
On Error Resume Next
DoCmd.RunCommand Command:=acCmdUndo
Cancel = False
' if user clicks cancel, cancel unloading of form
Case vbCancel
Cancel = True
End Select
End If
End Sub |
Deactivate event
It occurs when a form or report loses the focus to a Table, Query, Form,
Report, Macro, or Module window, or to the Database window.
Private Sub object_Deactivate()
Example:
Private Sub Form_Deactivate()
DoCmd.RunCommand acCmdSave
' save record when the form deactivates
End Sub |
Close event
Occurs after the UnLoad event, when a form or report is closed
and removed from the screen. Remember this, the Unload event can be
canceled, but the Close event can't.
Private Sub object_Close()
Example:
Private Sub Form_Close()
' If the frmOrders form is loaded, unload it
If IsLoaded("frmOrders") Then
DoCmd.Close acForm, "frmOrders"
End If
End Sub |
Dirty event
Occurs when the contents of a form or the text portion of a combo box changes.
(then the
editing pencil appears in
the form's record selector).
It also occurs when you move from one page to another
page in a tab control . After the control is dirty, it won't get dirty again
until the value is saved.
Setting the Cancel argument to True cancels the
Dirty event. You can also use the CancelEvent method of the
DoCmd object to cancel the event.
Private Sub Form_Dirty(Cancel
As Integer)
Example: enables the btnUndo button when data is changed. The
Undo subroutine is called from the Dirty event of text box controls.
Clicking the enabled btnUndo button restores the original value of
the control by using the OldValue property.
Private Sub Form_Dirty()
If Me.Dirty Then
Me!btnUndo.Enabled = True
' enable button.
Else
Me!btnUndo.Enabled = False
' disable button.
End If
End Sub
Sub btnUndo_Click()
Dim ctl As Control
For Each ctl in Me.Controls
If ctl.ControlType = acTextBox Then
ctl.Value =
ctl.OldValue
' restore Old Value.
End If
Next ctl
End Sub |
KeyDown event
Occurred every time a key is pressed while a form or control has the
focus. This event also occurs if you send a keystroke to a form or
control by using the SendKeys action in a macro or the SendKeys
statement in Visual Basic.
Private Sub object_KeyDown(KeyCode
As Integer, Shift As Integer)
KeyCode
- tell you the ASCII code of the key
Shift - tell you which, if any, of the Ctrl, Alt, and
Shift keys were being pressed at the same time.
E xample:
Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
' If the form is dirty and the user presses page up or page down, ignore the keystroke
If Me.Dirty Then
If KeyCode = vbKeyPageDown Or
KeyCode = vbKeyPageUp Then
KeyCode = 0
End If
End If
End Sub |
KeyPress event
Occurs when you press and release a key or key
combination that corresponds to an ANSI code while a form or control has
the focus. The keyAscii returns a numeric ANSI key code.
KeyPress
event also occurs if you send an ANSI keystroke to a form or control by
using the SendKeys action in a macro or the SendKeys statement in
Visual Basic. Refer to a
table of the ANSI codes.
Private Sub object_KeyPress(KeyAscii
As Integer)
Example: shows
that if you press anything other than alphabetical characters (upper
or lowercase, and no space), the statement will cancel the keys
input, then displays a message.
Private Sub Form_KeyPress(KeyAscii As
Integer)
Select Case KeyAscii
' 65 To 90 and 97 To 122: These are
all alphas, upper and lowercase
Case 65 To 90, 97 To 122
Case Else
'
setting KeyAscii to zero cancels the
keystrokes
KeyAscii = 0
MsgBox ("Only alphabetical characters
allowed, and no space in between")
End Select
End Sub |
Change event
Occurs when the content of a text box or the text portion of a combo box
changes. It also occurs when you move from one page to another page in a
tab control.
Private Sub object_Change()
Example: display value of a field called "CustomerName" in the
title bar while you are typing in a text box bound to that field.
Note: you must use the control's Text property to refer to the text
in the field before it is committed.
Sub txtCustomerName_Change
()
Me.Caption = Me!txtCustomerName.Text
End Sub |
KeyUp event
Occurs when the user releases a key while a form or control has the
focus. This event also occurs if you send a keystroke to a form or
control by using the SendKeys action in a macro or the
SendKeys statement in Visual Basic.
Private Sub object_KeyUp(KeyCode As Integer, Shift As Integer)
KeyCode - such as vbKeyF1 (F1 key)
or vbKeyHome (HOME key).
Note: you can prevent an object from receiving a
keystroke by setting KeyCode to 0.
Shift - the state of the SHIFT, CTRL, and ALT keys at the
time of the event.
Example:
Private
Sub Form_KeyUp(KeyCode As Integer, Shift As Integer)
If (KeyCode = 27) OR
(KeyCode = 127)
Then
Me.txtCustomerName.Visible = Not Me.CustomerName.Visible
KeyCode = 0
MsgBox "KeyCode " & CStr(KeyCode) & "is not
allowed"
End If
End Sub |
NotInList event
Occurs when the user enters a value in the
text box portion of a combo box that isn't in the combo box list. This
is a special property that applies only to combo box that list box
lacks.
Private Sub object_NotInList(NewData
As String, Response As Integer)
NewData -
a string Access uses to pass the text the user entered in the text box
portion of the combo box to the event procedure.
Response - indicates how the NotInList event was handled. This
argument can be one of the following constants:
acDataErrContinue - ignore the default Access error message. You
can supply a custom error message.
acDataErrDisplay - Default. Display the default Access error
message.
acDataErrAdded - doesn't
display a message but enables you to add the entry to the combo box list
in the NotInList event procedure.
After the entry is added, Access updates the list by requerying the
combo box. Access then rechecks the string against the combo box list,
and saves the value in the NewData argument in the field the combo box
is bound to. If the string is not in the list, it displays an error
message.
Example.
Private
Sub cboCustomerID_NotInList(NewData As String, Response
As Integer)
Dim ctl As Control
' return Control
object that points to combo box.
Set ctl = Me!CustomerID
' prompt user to verify they wish to add new
value.
If MsgBox("CustomerID is not in the list. Add it?",
vbYesNo + vbQuestion, "Please Respond") = vbYes Then
'
set Response argument to indicate that data is being
added.
Response = acDataErrAdded
' add string in the NewData
argument to row source.
ctl.RowSource = ctl.RowSource & ";" & NewData
Else
' If user chooses Cancel,
suppress error message and undo changes.
Response = acDataErrContinue
ctl.Undo
End If
End Sub |
Enter event
It occurs just before a control receives focus
from another control on the same form and before the GotFocus
event.
It applies to textboxes, combo boxes, list boxes, command buttons,
option groups, object frames, subforms.
Private Sub object_Enter()
Example:
Private Sub
OrdersForm_Enter()
' If
the user clicks to enter qty orCurrent d the OrderID is
null, display a message and set focus
' back to the cboOrderID combo box
If IsNull(Me.OrderID) Then
MsgBox "Enter Order ID before entering other
orders detail"
Me.cboOrderID.SetFocus
End If
End Sub |
Exit event
It occurs just before a control
loses focus to another control on the same form and before
the LostFocus event.
It applies to tlist boxes, command buttons,
option groups, object frames.
Private Sub object_Exit()
Example:
Private Sub
OrdersForm_Exit(Cancel As Integer)
Dim strMsg As String
strMsg = "You entered '" &
Me!OrderID & "'. Is this
correct?"
If MsgBox(strMsg, vbYesNo) = vbNo Then
Cancel = True
' cancel exit.
Else
Exit Sub ' save
changes and exit.
End If
End Sub |
GotFocus event
The GotFocus event occurs when a form or control receives the focus in
response to a user action, or when your code issues the SetFocus,
SelectObject, GoToRecord, GotoControl, or GoToPage methods. This event
is rarely used.
Private Sub
object_GotFocus()
Example:
Private Sub
SearchText_GotFocus()
Me!txtSearch = Null
Me!btnFind.Caption =
"Search"
End Sub |
LostFocus event
The LostFocus event occurs when a form or control loses the focus
in response to a user action, or when your code issues the SetFocus,
SelectObject, GoToRecord, GotoControl, or GoToPage methods.
Private Sub object_LostFocus()
Example: displays a message in a label when the focus moves to
an option button.
Private Sub optYes_GotFocus()
Me!Yes.Caption = "Option button 'Yes' receives the focus."
End Sub
Private Sub OptionYes_LostFocus()
Me!Yes.Caption = ""
' clear caption as
Option button 'Yes' loses focus.
End Sub |
Error Event
Error event occurs when a Jet run-time error is produced during data
entry when a form or report has the focus. See here for the
list of error codes reserved by Microsoft Access and the Jet
Database Engine. Here is the function
example to generate a table of the error codes .
In the form's property sheet, set
the OnError property to the name of the macro or to [Event
Procedure]. Access Error event doesn't include run-time errors in
Visual Basic or errors from ADO.
Private Sub object_Error
(DataErr As Integer, Response As Integer)
Object - name of a Form or Report.
DataErr - error code returned by the Err object when
an error occurs.
Response - can be one of the following intrinsic
constants.
acDataErrContinue - ignore the default Access error message. You
can supply a custom error message.
acDataErrDisplay - Default. Display the default Access error
message.
' to replace a default
error message with a custom error message.
Private Sub OrdersForm_Error(DataErr As Integer, Response As Integer)
Const conDuplicateKey = 3022
Dim strMsg As String
If DataErr = conDuplicateKey Then
Response = acDataErrContinue
strMsg = "Each Orders record must have a unique Order ID. Please
re-enter your data."
MsgBox strMsg
End If
End Sub
Here is another example:
Private Sub Form_Error(DataErr As Integer, Response As Integer)
' Respond to Jet errors
Select Case DataErr
Case
3022
' Data
isn't right for the field
MsgBox "Each
Orders record must have a unique Order ID. Please re-enter your data",
vbInformation
' Suppress
the default Access message
Response = acDataErrContinue
Case Else
' Let
Access handle other errors
Response = acDataErrDisplay
End Select
End Sub |
Undo event
Occurs when you undo a change to a combo box control, a form, or a text
box control. It is triggered by clicking the Undo button, pressing the
ESC key, or calling the Undo method of the specified form.
Private Sub object_Undo(Cancel
As Integer)
Example:
Private Sub Form_Undo(Cancel As Integer)
Dim intResponse As Integer
Dim strPrompt As String
strPrompt = "You have attempted to cancel the undo operation. Do
you want to proceed?"
intResponse = MsgBox(strPrompt, vbYesNo)
If intResponse = vbYes Then
Cancel = True
Else
Cancel = False
End If
End Sub |
Updated event
Applies to a bound object frame only. It occurs when the OLE object's
data is modified.
Example: to show a dialog box modal form in
response to an Updated event. Enable a timer control in the Updated
event and then have the Timer's Timer event show the form.
Private Sub OLE1_Updated(Code As Integer)
Timer1.Enabled = True
End Sub
Private Sub Timer1_Timer()
Timer1.Enabled = False
MyForm.Show vbModal
End Sub |
Timer event
The Timer event occurs for a form at regular intervals as specified by
the form's TimerInterval property in milliseconds.
Private Sub Form_Timer()
Example: to create a digital clock you can display
on a form. Set your
label name as Clock.
The label control displays the current time according
to your computer's system clock.
Private Sub Form_Timer()
Clock.Caption = Time
' update
digital time display.
End Sub
Sub Form_Load()
Me.TimerInterval = 1000
End Sub |
NoData event
Occurs after Access formats a report for printing that has no data (the
report is bound to an empty recordset or a
blank report), but before the report is printed.
Example:
Private Sub Report_NoData(Cancel As Integer)
' if no data, display message and cancel report
MsgBox "There is no data for this report.Canceling report..."
Cancel = True
End Sub |
Delete event
Occurs when the user performs some action, such as pressing the
DEL key, to delete a record, but before the record is actually
deleted.
Private Sub
Form_Delete(Cancel As Integer)
Example:
Private Sub Form_Delete(Cancel As
Integer)
Cancel = True '
setting Cancel argument to True (–1) cancels the Delete
event.
MsgBox "This record can't be deleted."
End Sub |
BeforeDelConfirm event
Occurs after the Delete event, but before Access displays a
dialog box asking the user to confirm the deletions.
Private Sub
Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
Cancel - set to
True cancels the BeforeDelConfirm event and prevents the Delete
Confirm dialog box from being displayed
Response -can be set to one of the following intrinsic
constants:
acDataErrContinue - continues without displaying the Delete
Confirm dialog box.
acDataErrDisplay (Default) - displays the Delete
Confirm dialog box.
Example:
Private Sub
Form_BeforeDelConfirm(Cancel As Integer, Response As
Integer)
' suppress default Delete Confirm dialog box.
Response = acDataErrContinue
' display a custom dialog box.
If MsgBox("Delete this record?", vbOKCancel) = vbCancel Then
Cancel = True
End If
End Sub |
AfterDelConfirm
event
Occurs after the record is actually
deleted. If the code does not cancel the
BeforeDelConfirm event, the AfterDelConfirm event takes place after Access displays
the Confirmation
dialog box.
Private Sub
Form_AfterDelConfirm(Status As Integer)
Status - indicates whether a
record has been deleted, and can be any of the following constants:
acDeleteOK - indicates the deletion was successful.
acDeleteCancel - indicates the deletion was canceled in
Visual Basic.
acDeleteUserCancel - indicates the deletion was canceled by
the user.
Example: display a message indicating whether the
deletion was successful in the usual way, or
whether it was canceled in Visual Basic, or by the user.
Private Sub
Form_AfterDelConfirm(Status As Integer)
Select Case Status
Case acDeleteOK
MsgBox "Your
deletion is confirmed."
Case acDeleteCancel
MsgBox "You
IT support canceled the deletion."
Case acDeleteUserCancel
MsgBox "User
canceled the deletion."
End Select
End Sub |
DblClick event
Occurs when the left mouse button was
pressed and released
twice over an object
within the double-click time limit.
Private Sub object_DblClick(Cancel
As Integer)
Example: to show how you can use a DblClick event
on one form to open another form that displays records from the table that is the row
source of a combo box on the first form. When the user double-clicks the combo box in
an Orders form (first form), the Customers form is displayed, showing the record
for the customer selected in the combo box on the first form.
Private Sub
CustomerID_DblClick(Cancel As Integer)
DoCmd.OpenForm "Customers",
, , "CustomerID
= Forms!Orders!CustomerID"
End Sub |
Query event
Occurs whenever the specified PivotTable view query becomes
necessary.
Example: to demonstrate the syntax for a
subroutine that traps the Query event.
Private Sub
Form_Query()
MsgBox "A PivotTable view query has become necessary."
End Sub |
ApplyFilter event
The event occurs when a filter is applied to a form.
Private Sub Form_ApplyFilter(ByVal
Cancel As Integer, ByVal ApplyType As Integer)
Cancel - setting Cancel argument to
True cancels the event.
ApplyType - returns the type of filter that was applied.
ApplyFilter event doesn't occur when the user does one of the
following:
- Applies or removes a filter by using the ApplyFilter,
OpenForm, or ShowAllRecords actions in a macro, or their
corresponding methods of the DoCmd object in Visual Basic.
- Uses the Close action or the Close method of the DoCmd
object to close the Advanced Filter/Sort, Filter By Form.
- Sets
the Filter property or FilterOn
property in a macro or Visual Basic (although you can set these
properties in an ApplyFilter macro or event procedure).
Example: Here a
sample code by Alex Dybenko, which changes border of ActiveControl
to red and other controls to default color. When you remove the
filter, it will change all controls border color back to default.
Private Sub
Form_ApplyFilter(Cancel As Integer, ApplyType As
Integer)
Dim ctl As Access.Control, strActiveControl As String
If ApplyType = 1 Then
' apply filter
strActiveControl = Screen.ActiveControl.Name
End If
If Len(strActiveControl) > 0 Then
Me(strActiveControl).BorderColor = vbRed
End If
For Each ctl In Me.Controls
Select Case TypeName(ctl)
Case "TextBox", "Combobox"
If ctl.Name <> strActiveControl Then
ctl.BorderColor = 8421504
End If
End Select
Next ctl
End Sub |
MouseMove event
Occurs when you moves the mouse.
Private Sub object_MouseMove(Button As
Integer, Shift As Integer, X As Single, Y As Single)
Button - the button that was
pressed (MouseDown) or released (MouseUp) to trigger the event. If
you need to test for the Button argument, you can use one of the
following intrinsic constants as bit masks:
acLeftButton - The bit mask for the left mouse button.
acRightButton - The bit mask for the right mouse button.
acMiddleButton - The bit mask for the middle mouse button.
Shift - the state of the SHIFT, CTRL, and ALT keys when the
button specified by the Button argument was pressed or released. If
you need to test for the Shift argument, you can use one of the
following intrinsic constants as bit masks:
acShiftMask - The bit mask for the SHIFT key.
acCtrlMask - The bit mask for the CTRL key.
acAltMask - The bit mask for the ALT key.
X, Y - the x and y coordinates for
the current location of the mouse pointer. The X and Y arguments are
always expressed in twips.
Example: On the form, Label1 forecolor starts out
as Black. When the mouse moves over the Label1, the forecolor
changes to red; and when you click the mouse at the details section,
it changes to font size 10, and returns to font size 8 when you
released it.
Private Sub
txtDetail_MouseMove(Button As Integer, Shift As
Integer, X As Single, Y As Single)
Dim Ctrl As Control
For Each Ctrl In Me.Controls
If Ctrl.ControlType = acLabel And Ctrl.ForeColor
= vbBlack Then
Ctrl.ForeColor = vbRed
End If
Next Ctrl
End Sub
Private Sub Form_MouseDown(Button As Integer,
Shift As Integer, X As Single, Y As Single)
Me.Label1.FontSize = 10
End Sub
Private Sub Form_MouseUp(Button As Integer, Shift
As Integer, X As Single, Y As Single)
Me.Label1.FontSize = 8
End Sub |
MouseDown event
Occurs when you press a mouse button. This event does not apply to a
label attached to another control, such as the label for a text box.
It applies only to "freestanding" labels. Pressing and releasing a
mouse button in an attached label has the same effect as pressing
and releasing the button in the associated control.
Private Sub object_MouseDown(Button As
Integer, Shift As Integer, X As Single, Y As Single)
The 1st example display a count message each time
you click a mouse on the screen.
The 2nd example shows how you can find out which mouse button caused
a MouseDown event.
Private Sub
Form_MouseDown(Button As Integer, Shift As Integer, X As
Single, Y As Single)
Static c As Long
c = c + 1
MsgBox "Mousedown count: " & c, X, Y
End Sub
Private Sub Form_MouseDown(Button As Integer, Shift As
Integer, X As Single, Y As Single)
If Button = acLeftButton Then
MsgBox "You pressed the left button."
End If
If Button = acRightButton Then
MsgBox "You pressed the right
button."
End If
If Button = acMiddleButton Then
MsgBox "You pressed the middle
button."
End If
End Sub |
MouseUp event
Occurs when the user releases a mouse
button.
Private Sub object_MouseUp(Button As
Integer, Shift As Integer, X As Single, Y As Single)
Example:
display a
count message each time you release a left or right mouse click.
Private Sub
Form_MouseUp(Button As Integer, Shift As Integer, X As
Single, Y As Single)
Static c As Long
c = c + 1
Select Case Button
Case 1: MsgBox "MouseUp LeftButton count: "
& c, X, Y
Case 2: MsgBox "MouseUp RightButton count:
" & c, X, Y
End Select
End Sub |
Working with Form Controls
This section will show you how to program in the
various types of form controls including list box, combo box, checkbox,
option groups, and command button. Both List box and Combo box have two
common properties as follow:
■ Row Source Type ― specify whether the list is based
on a table/query, a list of values, or a list of table or query field
names.
■ Row Source ― depends on Row Source Type
setting to determine the data type displayed by the control.
There are three types of Row Source Type settings:
■ when Row Source Type is Table/Query, Row Source settings must be a table, query or SQL
statement. [
see diagrams
].
■ when Row Source Type is Value List, Row
Source settings must be an explicit list of items separated by
semicolons. [ see diagram ]
■ when Row Source Type is Field List, Row Source settings must be a table, query or SQL statement.
Filtering form's Control
Let's do a simple example by creating a new form.
Open the form's header and insert a combo box control into the
header. Name the combo box control as cboSearch and its label's
caption as Search. Next will insert the various fields from a table
called tblCustomers into the form's Detail section. Finally insert the
following codes for the combox box control's Click event and the
form's Open event. [
see diagrams which explain in more detail]. You can also
download this
database example.
Code for the
Table/Query RowSource Type.
Private Sub
Form_Open(Cancel As Integer)
cboSearch.RowSourceType = "Table/Query"
cboSearch.RowSource = "tblCustomers"
cboSearch.ColumnCount = 2
' specify the number of
columns displayed in the combo box.
cboSearch.ColumnWidths = "0.5 in;1 in"
' in inches or
centimeters, depending on your system setting.
End Sub
Private Sub cboSearch_Click()
Dim strSQL As String
'
Column(0) refers to the first column of the table where
the filter is based on.
strSQL = "SELECT * FROM tblCustomers WHERE CustomerID = " &
cboSearch.Column(0)
Forms!frmCustomers.RecordSource = strSQL
End Sub |
Code below for the Value List type is rather cumbersome as each time
you have to update
the RowSource property to update the list.
Private Sub
Form_Open(Cancel As Integer)
cboSearch.RowSourceType = "Value List"
cboSearch.RowSource = "London;Bern;Berlin;Barcelona;Milan;Moscow;Singapore"
cboSearch.ColumnCount = 1
End Sub |
Code for the Field List RowSource Type.
Private Sub
Form_Open(Cancel As Integer)
cboSearch.RowSourceType = "Field List"
cboSearch.RowSource = "tblCustomers"
End Sub |
Combo box - Bound list control
Form controls can be bound or Unbound to a
table or a query. Using the earlier
table/query List example, let's first try out a Bound control. Go to
the control cboSearch property sheet, set the ControlSource property
to CustomerID, and also set the Limit To List property to No.
[see
diagram]. Doing this will bound the control and allow to add a
non-list item to the underlying table. Go to VBE, add the following
code to the AfterUpdate event procedure. This is to control saving
the input when the new customer ID is added and then requery the
control.
Often it is easy to add a new item from the combo
box list, and accidentally overwrite the existing data form that
record. So use Bound list control with wisely than be sorry.
Private Sub cboFilter_AfterUpdate()
DoCmd.RunCommand acCmdSaveRecord
cboFilter.Requery
End Sub
Combo box - UnBound list control
You
have seen the above Bound control. Now we do an example on UnBound
list control. Use the same form that we had done with table/query
List, but change the label caption to UnBound, denoting an unbound
control. This time
set the
Limit To List property to Yes; the purpose is to enter a
non-list item (a new customer ID) that would trigger the combo box's
NotInList
event action. NotInList event passes two arguments, Newdata and
Response, that you can use to add a new value to the list, and hence
the underlying data source. This way you won't accidentally change
the existing data. Unbound control also allows use of lookup tables
as opposed to actual stored values.
Insert
the following codes into the NotInList event, form's Open event, control's Click
event, and the AfterUpdate event procedures.
Open the form and enter a new value into the combo box control's
text box. This trigger control's NotInList event, displays the
message box, and stores your response in the byteResponse variable.
Click Yes and the If statement executes the INSERT INTO SQL
statement, which then insert all your entries into the list's data
source. Open table tblCustomers, you will find your entries are
updated there.
[
download this
database example. You must reference to Microsoft ActiveX Data
Object 2.x Library for the code to work].
Code for the UnBound list control, where you you can
add new item to the list.
Private Sub cboSearch_NotInList(NewData As String, Response As
Integer)
Dim cnn As New ADODB.Connection
Dim strSQL As String, byteResponse As Byte
Set cnn = CurrentProject.Connection
byteResponse = MsgBox("Do you want to add this new
item to the list?", vbYesNo, "New item")
If byteResponse = vbYes Then
strSQL = "INSERT INTO tblCustomers (CustomerID) VALUES ('" & NewData
& "')"
cnn.Execute strSQL
Response = acDataErrAdded
' resets the Response
argument so that new input can be processed internally.
ElseIf byteResponse = vbNo Then
Response = acDataErrContinue '
if you click No, error message
is
suppressed.
Me!cboSearch.Undo
' the Undo method deletes
the entry from the control's text box.
End If
End SubPrivate Sub Form_Open(Cancel As Integer)
cboSearch.RowSourceType = "Table/Query"
cboSearch.RowSource = "tblCustomers"
Me.cboUnbound.SetFocus
End Sub
Private Sub cboSearch_Click()
Dim strSQL As String
' Column(0) refers to the first column
of the table where the filter is based on.
strSQL = "SELECT * FROM tblCustomers WHERE CustomerID = " &
cboSearch.Column(0)
Forms!frmCustomers.RecordSource = strSQL
End Sub
Private Sub cboSearch_AfterUpdate()
DoCmd.RunCommand acCmdSaveRecord
cboSearch.Requery
End Sub |
UnBound Text Box
It means the Text box's control that is not connected to any field
in a table. Unbound text book is more useful in the sense that you
can ask user to input data, and use that input to become the
expression criteria in your query. Consider the following example,
on a form there is a combo box list with a predefined customer names
list for user to select, a text box to input on a reference date (< or =>)
thereafter products were shipped, and a second text box to type in
the lower limit invoiced sum. A click on the command button will
open the report on the filtered result. [download
this database example]
Code and the Control names.
Combo box
Control name
: cboSelectText boxes
Min. ShipDate Control name
: txtShipDate
Min. InvoicedSum Control name : txtWhere
Command Button name
: cmdOpenReport
Report
Record Source to query name :
Qry_CustomersPurchase
Expression criteria in the query for
the combo box's control (to select customer name):
[Forms]![frmCustomerSales]![cboSelect]
Expression criteria in the query (to
select a baseline ship date):
>[Forms]![frmCustomerSales]![txtShipDate]
Expression criteria in the query
(to select a minimum invoiced sum):
>[Forms]![frmCustomerSales]![txtWhere]
' Code to handle the
click event. Place it in the form's module.
Private Sub
cmdOpenReport_Click()
On Error GoTo HandleErr
DoCmd.OpenReport "CustomersReport", acViewPreview,
WhereCondition:=txtWhere.Value
ExitHere:
Exit Sub
HandleErr:
MsgBox "Error " & Err.Number & ": " & Err.Description & " in
cmdOpenReport_Click"
Resume ExitHere
End Sub |
List box - MultiSelect Controls
Using the ListBox control in MultiSelect view
is very useful for generating reports. You can select multiple items in a List box
which the Combo Box can't do. To do that, you have set the list box's
control property to Simple or Extended. My following example shows
you a list box which obtains its content from a table, the result of
which is executed by running a query. Two tables with outer left
join that make up the query. The form allows you to make multiple
selection of rows of data, and view them in a report that it will
generate. Another form with combo boxes that accompanies the report,
allow you to be able to sort the data in multiple columns. Here is a
quick look of the forms. [see
diagrams ].
The following are the specific
controls and properties to the form 'frmMultiSelect'.
| List
Box |
Name
Row Source Type
Row Source
Column Count
Column width
Bound Column
Multi Select
Width
Height |
:
:
:
:
:
:
:
:
: |
lstMultiSelect
Table/Query
tbl_CustomersPurchase
9
0.25";0.7";0.9";0.7";0.5";0.8";0.6";0.65";0.5"
1
Simple
3.5"
0.75" |
|
The Sample database
download is here (all codes supplied).
Codes for the form frmMultiSelect:
Option Compare
Database
Option Explicit
Private Sub RunQuery_Click()
' Run the query
DoCmd.OpenQuery "Qry_tblCustomersPurchase"
End Sub
Private Sub txtHead_AfterUpdate()
' Make column headings visible
Me.lstMultiSelect.ColumnHeads = Me.txtHead
End Sub
Private Sub cmdReport_Click()
' Open the customized report
DoCmd.OpenReport "rptMultiSelect", acViewPreview, ,
SQL_Criteria
DoCmd.Maximize
End Sub
Private Sub cmdSelectAll_Click()
' Select All values in a listbox
Dim lngCounter As Long
Dim ctl As Control
Set ctl = Me.lstMultiSelect
For lngCounter = Abs(ctl.ColumnHeads) To ctl.ListCount
ctl.Selected(lngCounter) = True
Next lngCounter
End Sub
Private Sub cmdUndo_Click()
' Undo all values selection in a listbox
Dim varItm As Variant
Dim ctl As Control
Set ctl = Me.lstMultiSelect
For Each varItm In ctl.ItemsSelected
ctl.Selected(varItm) = False
Next varItm
End Sub
Private Sub cmdUnSelectAll_Click()
Dim i As Integer
' Loop through each of the items in the ListBox control
For i = 0 To Me![lstMultiSelect].ListCount - 1
Me![lstMultiSelect].Selected(i) =
False
Next i
End Sub
Private Sub cmdExit_Click()
On Error GoTo Err_cmdExit_Click
DoCmd.Quit
' exit Access database
Exit_cmdExit_Click:
Exit Sub
Err_cmdExit_Click:
MsgBox Err.Description
Resume Exit_cmdExit_Click
End Sub
Private Sub cmdClose_Click()
' close form without saving changes
DoCmd.Close acForm, Me.Form.Name
' Unload Me
End Sub
Private Sub cmdSaveClose_Click()
On Error GoTo Err_cmdSaveClose_Click
' Save any changes before close
DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close
Exit_cmdSaveClose_Click:
Exit Sub
Err_cmdSaveClose_Click:
MsgBox Err.Description
Resume Exit_cmdSaveClose_Click
End Sub
Function SQL_Criteria() As String
' determine SQL statement (Bound Column to use numerical
data type)
Dim ctl As Control, strCriteria As String, varItm As Variant
Set ctl = Me.lstMultiSelect
For Each varItm In ctl.ItemsSelected
' Use ItemData Property to select the Bound Column.
' In other cases, use Column
Property to identify the column & row.
strCriteria = strCriteria +
ctl.ItemData(varItm) & ","
Next varItm
If strCriteria = "" Then
SQL_Criteria = " SN Like
'*' "
Else
' Remove the last comma. [See
example]
SQL_Criteria = " SN IN(" &
left(strCriteria, Len(strCriteria) - 1) & ")"
End If
End Function |
Codes for the form with combo boxes
(frmSortBy) to operate on the report (rptMultiSelect):
Option Compare
Database
Option Explicit
Private Sub cmdSetSort_Click()
Dim strSQL As String, intCounter As Integer
' Build strSQL String
For intCounter = 1 To 4
If Me("cboSort" & intCounter) <> ""
Then
strSQL
= strSQL & "[" & Me("cboSort" & intCounter) & "]"
If
Me("Chk" & intCounter) = True Then
strSQL = strSQL & " DESC"
End If
strSQL
= strSQL & ", "
Else
End If
Next
If strSQL <> "" Then
' Strip Last Comma & Space
strSQL = left(strSQL, (Len(strSQL) -
2))
' Set the OrderBy property
Reports![rptMultiSelect].OrderBy =
strSQL
Reports![rptMultiSelect].OrderByOn =
True
Else
Reports![rptMultiSelect].OrderByOn =
False
End If
End Sub
Private Sub cmdClear_Click()
Dim intCounter As Integer
For intCounter = 1 To 4
Me("cboSort" & intCounter) = ""
Me("Chk" & intCounter) = ""
Next
End Sub
Private Sub cmdClose_Click()
DoCmd.Close acForm, Me.Form.Name
End Sub
Private Sub Form_Close()
DoCmd.Close acReport, "rptMultiSelect"
DoCmd.Restore
End Sub |
Codes behind the Report Module (rptMultiSelect):
Private Sub
Report_Open(Cancel As Integer)
' Fires up a form upon the report opening
DoCmd.OpenForm "frmSortBy"
End Sub
Private Sub Report_Close()
' Upon report closing, also closes the form that it had
opened up
DoCmd.Close acReport, "frmSortBy"
DoCmd.Restore
End Sub |
Option Group Controls
Option groups are themselves controls but can also contain controls
from text box, combo box, list box, toggle button. However, only one
control within an option group can be selected at a time, which
means when one control becomes selected, all the other controls
becomes deselected. The Value property of the option group is
equivalence of the option value of the selected control, which means
each control has a default property.
(see diagram). The Change event code below explains this relationship between the
text box's control within the option group and the option group
itself. [download
database sample]
Private Sub txtValue_Change()
grpOption = CInt(txtValue.Text)
End Sub
This code deselects the option button when you
double-click on it.
Private Sub
grpOption_DblClick(Cancel As Integer)
Me.grpOption = ""
End Sub |
Here is another technique that disable all the
option buttons at once, which is useful either for a click event.
For example, if user selects gender option as Female, you will want
all the option buttons in another option group to be disabled.
' Code to
disable the entire option group.
Dim ctl As Control
Callback ctl In grpOption.Controls
If ctl.ControlType = acOptionButton Then
ctl.Enabled = False
End If
Next ctl |
|
