DoCmd originally appeared in version 1.0 and 2.0 of Access. Later with the incorporation of VBA into Access, DoCmd has changed from language component to become a portion of the Access object model.
Methods of DoCmd object are used to run Microsoft Access actions from Visual Basic code. Most of the methods have parameters or
arguments — some are required, while others are optional. The methods correspond directly to the built-in 'macro actions' that are available in the Action column of the Macro dialog box. However, a few macro actions were not supported as members of the DoCmd object, since they are already available as parts of VBA itself. The DoCmd object does not work for the following macro actions :● AddMenu
● MsgBox - Replaces by MsgBox function
● RunApp - Use the Shell function instead to run another application
● RunCode - Run the function directly in Access Visual Basic
● SendKeys - Use the SendKeys command
● SetValue - Assign values directly in VBA
● StopAllMacros
● StopMacro
|
DoCmd Methods |
|||
|
ApplyFilter Beep CancelEvent Close CopyDatabaseFile CopyObject DeleteObject DoMenuItem Echo FindNext FindRecord GoToControl GoToPage GoToRecord Hourglass Maximize Minimize |
MoveSize OpenDataAccessPage OpenDiagram OpenForm OpenFunction OpenModule OpenQuery OpenReport OpenStoredProcedure OpenTable OpenView OutputTo PrintOut Quit Rename RepaintObject Requery |
Restore RunCommand RunMacro RunSQL Save SelectObject SendObject SetMenuItem SetWarnings ShowAllRecords ShowToolbar TransferDatabase TransferSpreadsheet TransferSQLDatabase TransferText |
|
Here is a list of all the RunCommand (acCommand) intrinsic Constants.
ApplyFilter method
Applies
a filter, a query, or a SQL WHERE clause to a table, form, or report
in order to limit or sort the underlying data.
Both arguments are optional. It is same as setting the Filter
property of a form.
DoCmd.ApplyFilter [filterName] [, whereCondition]
filterName - optional, a string expression that's the
valid name of a filter or query.
wWhereCondition - optional, a string expression that's a valid
SQL WHERE clause without the word WHERE.
Example :
Private Sub
Command1_Click()
Dim strCustID As String, strFilter As String
strCustID = InputBox("Enter customer ID:")
If
strCustID = " " Then
' if nothing
is entered.
' removes
any existing filters; same as assigning a null string to
the form's Filter property.
DoCmd.ShowAllRecords
Else
' to
display only records
that contain the strCustID (from the user's input) in
the CustID field.
strFilter =
txtCustID = ' " & strCustID &
" ' "
DoCmd.ApplyFilter , strFilter
End If
End Sub
Example:
display a dialog box asking user whether want to save changes to the
form frmCustomersBase (must be
open). If click on Yes, it saves changes; if click No, CancelEvent
will be triggered to cancel the update.
Close method
DoCmd.Close
[ObjectType] [, ObjectName]
[, Save] Example:
to close the form, automatically saving changes.
expression.FindRecord(FindWhat,
Match, MatchCase, Search, SearchAsFormatted,
OnlyCurrentField, FindFirst) FindWhat - Required Variant. An expression that
evaluates to text, a number, or a date.
expression.GoToControl(ControlName) ControlName - required, the name of a field or a control where
you desire the focus to go. The control name can be a string expression, (eg.
"btnComand1, cboSelect, lst_Select", etc), but
Consider using the Setfocus command in place of the DoCmd.GotoControl
command because the set focus command has increased functionality. Example:
move the focus to the second page of the
frmCustomers form at the position specified by the right and down arguments.
Example:
move the focus to the position specified by the
horizontal and vertical offsets on the second page of the active form. expression.MoveSize(Right,
Down, Width, Height) Example:
DoCmd.OpenForm formName, [view], [filterName], [whereCondition],
[dataMode], [windowMode], [OpenArgs] formName is
required. Example: to open the form in datasheet view, no matter what view it
was saved in.
RunSQL method
Save method
Note: Note:
TransferSpreadsheet method expression.TransferSpreadsheet(TransferType,
SpreadsheetType, TableName, FileName,
HasFieldNames, Range,
UseOA)
'
' this imports data from range A1:G12 of Lotus
spreadsheet into the Access Table1. ' this exports data from
tblSalesWK45
table into path name
C:\Sales\Week45.xls. ' this exports data from the same
table into network path
\\Spares\Planning\
Sales\Week45.xls,
by setting a Constant path.
expression.
Example:
expression.Quit(Option) AcQuitOption can be one of these AcQuitOption
constants:
' a dialog box prompts users to save any changed objects before they quit Access.
Beep method
Beep
method carries out the Beep action in Visual Basic.
This method has no arguments.
DoCmd.Beep(
)
CancelEvent method
It
cancels the event that caused Access to run the code containing this
action
This method has no
arguments
and can be called directly using the syntax DoCmd.CancelEvent.
Following are the events that can be cancelled using the CancelEvent
method.
ApplyFilter
BeforeInsert
BeforeDelConfirm
BeforeUpdateDelete
Dirty
DblClick
ExitFilter
Format
KeyPress
MouseDownNoData
Open
Print
Unload
Private Sub
Form_BeforeUpdate(Cancel As Integer)
If MsgBox("Sure want to save changes to this form?", vbYesNo,
"Pls Confirm" = vbYes Then
DoCmd.Save acForm, "frmCustomersBase"
Else
DoCmd.CancelEvent
' Cancel = True
End Sub
It
closes the window of an Access object.
-
can be one of these 12 AcObjectType constants:
ObjectName
-
optional, a string
expression that's the valid name of an object of the type selected by the
object type argument.
AcCloseSave
-
can be one
of the 3 AcCloseSave constants. The default
is acSavePrompt.
DoCmd.Close
acForm, "frmOrders", acSaveYes
Echo method
Specifies whether Microsoft Access repaints the display screen.
expression.Echo(EchoOn,
bstrStatusBarText)
EchoOn - required Integer or Boolean indicates that the screen
repainting is on. True (default).
bstrStatusBarText - optional, a string expression that specifies
the text to display in the status bar when repainting is turned on or off.
Example:
Private Sub Form_Load()
DoCmd.Echo False, "Visual Basic code is executing."
DoCmd.Hourglass True
DoCmd.Echo True, "Screen updating. Please wait..."
DoCmd.Hourglass False
End Sub
FindRecord method
Searches for the first
record that meets the designated criteria.
AcFindMatch - AcFindMatch can be one of these 3 constants:
acAnywhere, acEntire (default), acStart.
MatchCase - Optional, Use True for a case-sensitive search and
False (default) for a search that's not
case-sensitive.
AcSearchDirection - can be one of these 3 constants: acDown,
acSearchAll (default), acUp.
SearchAsFormatted - Optional. Use True to search for data as it's
formatted and False (default) to search for
data as it's stored in the database.
AcFindField - can be one of these 2 AcFindField constants:
acAll, acCurrent (default).
FindFirst - Optional. Use True (default) to start the search at
the first record. Use False to start the search at
the record following the
current record.
DoCmd.FindRecord "0234-1234A",
acEntire, True, acSearchAll, True, acCurrent, True
' perform a search
using the value entered into txtSearch (textbox) and
evaluates this against values in ctlCustomerID (combo or
list box).
DoCmd.ShowAllRecords
DoCmd.GoToControl "ctlCustomerID"
DoCmd.FindRecord Me!txtSearch,
acAnywhere
FindNext method
It find
the next record that meets the criteria specified by the previous
FindRecord method or the Find and Replace dialog box, available by
clicking Find on the Edit menu. You can use the FindNext method to
search repeatedly for records. This method has no arguments and can
be called using the syntax DoCmd.FindNext.
Example: uses Screen.PreviousControl.SetFocus to
ensure that the subsequent
FindNext starts
searching where the previous search left off.
Screen.PreviousControl.SetFocus
DoCmd.FindNext
oToControl method
G
It moves the focus (the
cursor) to a
particular control on a form. You can also use a variable declared as a Control
data type for this argument.
' moves focus to the
control named
txtSearch
of Form frmForm1.
Dim ctl As Control
Set ctl = Forms!frmForm1!txtSearch
DoCmd.GoToControl ctl.Name
GoToPage method
Moves the
focus to the first control on a specified page in the active form.
expression.GoToPage(PageNumber,
Right, Down)
PageNumber - required, a valid page
number on the active form.
Right - optional, Long. A
horizontal offset (in twips) from the left side of the window to the part of the
page to be viewed.
Down - Optional Long. A vertical
offset (in twips) from the top of the window to the part of the page to be
viewed.
Forms!frmCustomers.GoToPage 2, 1440, 600
DoCmd.GoToPage 2, 1440, 567
MoveSize method
Let you change the
position and size of the object that currently has the focus. The
units for the arguments are twips. You must include at least one
argument.
Private Sub
Form_Load()
' to make
the form opens in small size.
DoCmd.MoveSize Height:=3456
End Sub
OpenForm method
It
carries out the OpenForm action in Visual Basic. If you leave all
arguments blank except the required formName, it will load the
default values for all these arguments.
view is required, can be one of these AcFormView
constants:
acDesign - design view.
acFormDS - a datasheet.
acFormPivotChart - a pivot chart.
acFormPivotTable - a pivot table.
acNormal - normal view - the default.
filterName - optional, the valid name of a query in
the current database.
whereCondition - optional, a valid SQL WHERE clause
without the word WHERE.
dataMode - can be one of these constants:
acFormAdd - user can add new records but can't edit existing
records.
acFormEdit - user can edit existing records and add new records.
acFormPropertySettings - the default - the mode is determined by
the form's existing property values.
acFormReadOnly - user can only view records.
windowMode - can be one of these constants:
acDialog - the form's Modal and PopUp properties are set to Yes.
acHidden - the form is hidden.
acIcon - the form opens minimized in the Windows taskbar.
acWindowNormal - default, form is in the mode set by its properties
OpenArgs - optional, to set the form's OpenArgs
property.
' open the form in
datasheet view, page 2 of the multi-page form, and allow
to add new records.
DoCmd.OpenForm "Orders", acFormDS, , , acFormAdd,
, 2
Opens the query in
the current database.
expression.OpenQuery(QueryName, View,
DataMode)
AcView
- can be one of these 5 constants:
AcOpenDataMode
can be one of these 3 constants:
Example: open the weekly sales totals in datasheet
view but not to edit or add records.
DoCmd.OpenQuery
"Qry_WeeklySalesTotals",
, acReadOnly
Requery method
Requery method of the DoCmd object carries out the Requery action
in Visual Basic. ControlName is optional.
expression.Requery(ControlName)
Requery method updates the data underlying a specified form or a control that's
on the active form by requerying the source of data for the form or control.
expression.Requery
The Requery method does these:
■ Reruns the query on which the form or control is based.
■
Displays any new or changed records or removes deleted records from the table on
which the form or control is based.
■
Updates records displayed based on any changes to the Filter property of the
form.
Example:
requery the subform when the product name field is updated.
Private
Sub Form_AfterUpdate()
' requery the subform when the
required date changes
DoCmd.RunCommand acCmdSaveRecord
fsubProductList.Requery
End Sub
Example: use Requery method to requery the data from the CustomerSales list
box on frmCustomers form.
Sub RequeryList()
Dim ctlCombo As Control
' return
Control object pointing to a combo box.
Set ctlCombo = Forms!frmCustomers!SelectCustomerID
' requery
source of data for a list box.
ctlCombo.Requery
End Sub
RunMacro method
Run a macro action in Visual Basic.
expression.RunMacro(MacroName,
RepeatCount, RepeatExpression)
MacroName
- required,a string expression that's the macro macro in the current database.
RepeatCount
- optional, an integer indicating the number of times the macro will run.
RepeatExpression
- optional, a numeric expression that stops the macro from running When it
evaluates to False (0).
Example:
Private Sub cmdTrackComplete_Click()
If Me.Dirty Then Me.Dirty = False
' force record save.
DoCmd.RunMacro "querry_DailySales"
Me.cmdDailySales.Visible = False
' hide command button after macro
run.
End Sub
Runs a built-in menu or toolbar command.
Here is a list of all the
RunCommand (acCommand) intrinsic Constants.
And also
here.
Example: go to the next record if the search field is not null
If Not IsNull(Me.txtSelect) Then
DoCmd.RunCommand acCmdRecordsGoToNext
End If
RunSQL method performs the RunSQL action in Visual Basic, is also used to
execute SQL query code within Access Visual Basic.
expression.RunSQL(SQLStatement, UseTransaction)
SQLStatement - required, an SQL statement for an action query or a
data-definition query.
UseTransaction - optional boolean. Use True (–1) to include this query in a
transaction. Use False (0) if you don't want to use a transaction. Default is
True.
Example:
using DoCmd.RunSQL to execute the SQL string, which is an Update
Query to remove leading 0's in the field PartNumber.
Sub
RemoveLeadingZeros()
Dim strSQL As String
strSQL = "UPDATE tblMaterial SET tblMaterial.PartNumber = " & _
IIf(IsNumeric([PartNumber])=True,CStr(Val([PartNumber])),[PartNumber]);"
DoCmd.RunSQL strSQL
End Sub
It carries out the Save action in Visual Basic.
expression.Save(ObjectType, ObjectName)
can be one of the
12 AcObjectType constants:
ObjectName -
optional, a string expression that's the valid name of an object of the type
selected by the object type argument.
. . . . ..
If you leave the objecttype and objectname arguments
blank (the default constant, acDefault, is
assumed for the objecttype argument), Microsoft Access saves
the active object. If you leave the objecttype argument
blank, but enter a name in the objectname argument, Microsoft
Access saves the active object with the specified name. If you enter
an object type in the objecttype argument, you must enter an
existing object's name in the objectname argument.If you leave the objecttype argument blank, but enter a
name in the objectname argument, you must include the
objecttype argument's comma.
You can't use the Save method to save any of the following with a
new name:
A form in Form view or Datasheet view.
A report in Print Preview.
A module.
A data access page in Page view.
A server view in Datasheet view or Print Preview.
A table in Datasheet view or Print Preview.
A query in Datasheet view or Print Preview.
A stored procedure in Datasheet view or Print Preview.
If MsgBox("save
changes?", vbYesNo, "Pls Confirm" = vbYes Then
DoCmd.Save acForm, "frmCustomersBase"
Else
Cancel = true
End If
This method removes any existing filters that may exist on the
current table, query, or form. It can be called directly using the
syntax DoCmd.ShowAllRecords.
Dim strCustID As String, strFilter As String
strCustID = InputBox("Enter customer ID:")
If strCustID = " " Then ' if nothing is entered.
' removes any existing filters; same as assigning a null string to
the form's Filter property.
DoCmd.ShowAllRecords
Else
' display only records that contain the strCustID (from the
user's input) in the CustID field.
strFilter = txtCustID = ' " & strCustID & " ' "
DoCmd.ApplyFilter , strFilter
End If
It carries out the
TransferSpreadsheet action in Visual
Basic.
AcDataTransferTypecan be one of these
3 constants:
AcSpreadSheetTypecan be one of these
10 constants:
TableName
- Optional, name of Access table
FileName
- Optional, file name and path of the spreadsheet you want to import from,
export to, or link to.
HasFieldNames
- Optional. Use True (–1) to use the first row of the spreadsheet as field names
when importing or linking. Use False (0) to treat the first row of the
spreadsheet as normal data. If you leave this argument blank, the default
(False) is assumed.
Range -
Optional, a valid range of cells or the name of a range in the spreadsheet. This
argument applies only to importing. Leave this argument blank to import the
entire spreadsheet. When you export to a spreadsheet, you must leave this
argument blank. If you enter a range, the export will fail.
UseOA -
Optional Variant. This argument is not supported.
' explicitly naming when import an
Excel file, sheet name, with a specified range, into
Access Table1. It
uses the first row of the spreadsheet as field names.
DoCmd.TransferSpreadsheet TransferType:=acImport,
SpreadsheetType:=9, TableName:="Table1", _ FileName:="C:\Sales\Week45.xls",
HasFieldNames:=True, Range:="[MySheet1]!A1:G100"
DoCmd.TransferSpreadsheet acImport, 9, "Table1", "C:\Sales\Week45.xls", True,
"[Sheet1]!A1:G100"
DoCmd.TransferSpreadsheet acImport, 3,
"Table1","C:\Lotus\MyData.wk3", True, "A1:G100"
' Note: here I deliberately don't specify a sheet name,
it therefore sends data to the default VBA Properties
Sheet1.
DoCmd.TransferSpreadsheet acExport, 9, "tblSalesWK45", "C:\Sales\Week45.xls", True
Const netPath As String =
\\Spares\Planning\
DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel9, "tblSalesWK45",
netPath & "Sales_week45.xls",
True
AcTextTransferType
- Optional, can be one of these 10 constants.
If you leave this argument blank, the default constant (acImportDelim) is
assumed.
SpecificationName - Optional Variant. A string expression that's
the name of an import or export specification you've created and saved in the
current database.
TableName - Optional Variant. A string expression that's the name of
the Access table you want to import text data to, export text data
from, or link text data to, or the Access query whose results you
want to export to a text file.
FileName - Optional Variant. A string expression that's the full
name, including the path, of the text file you want to import from, export
to, or link to.
HasFieldNames Optional Variant. Use True (–1) to use the first row of
the text file as field names when importing, exporting, or linking. Use
False (0) to treat the first roRunCommand text file as normal data. If you
leave this argument blank, the default (False) is assumed.
HTMLTableName - Optional Variant. A string expression that's the name
of the table or list in the HTML file that you want to import or link. This
argument is ignored unless the transfertype argument is set to acImportHTML
or acLinkHTML.
CodePage - Optional Variant. A Long value indicating the character
set of the code page.
' Export Access table (tblWeeklySales)
as a comma-delimited CSV file (Sales_Week45.csv).
' Must reference to the Microsoft Scripting Runtime
library in order to load the FileSystemObject
Dim fso As FileSystemObject
Set fso = New FileSystemObject
If fso.FileExists("C:\Sales_Week45.csv") Then
fso.DeleteFile "C:\Sales_Week45.csv"
End If
On Error Resume Next
DoCmd.TransferText acExportDelim, "Sales Import Specification",
"tblWeeklySales", "C:\Sales_Week45.csv"
If Err.Number <> 0 Then
MsgBox "An Error occured during the transfer (" & Err.Description & ")"
End If
On Error GoTo 0
Go To Top
Quit method
Quit the access application.
acQuitPrompt - displays a dialog box that asks whether
you want to save any database objects that have been changed but not
saved. (Formerly acPrompt).
acQuitSaveAll - Default. Saves all objects without
displaying a dialog box. (Formerly acSaveYes).
acQuitSaveNone - quits Microsoft Access without saving
any objects. (Formerly acExit).
' display a dialog box prompts the user to save changes and the procedure quits Access.
Application.Quit acQuitPrompt
DoCmd.Quit acQuitPrompt
