3.2. The DoCmd Object

In Microsoft Access, the DoCmd object is a built-in object used to execute actions, commands, and operations within the Access environment. It provides a way to interact with the Access interface, such as opening forms, running queries, printing reports, and executing various other tasks programmatically. The DoCmd object is crucial when automating tasks, handling events, or interacting with other objects in Access using VBA (Visual Basic for Applications).

What Is the DoCmd Object?

The DoCmd object is part of Access's programming model and allows you to programmatically control actions, such as opening and closing forms, running queries, printing reports, and manipulating database objects. It is often used in VBA code to perform operations that users would otherwise perform manually through the Access interface.

Key Features of DoCmd Object:

1. Executing Actions:
   • The DoCmd object can be used to perform a variety of actions, such as opening forms, reports, queries, and even printing.
   • These actions are equivalent to user interactions, but they are triggered via VBA code, making the process more automated and efficient.
2. Simplified Syntax:
   • The syntax for using the DoCmd object is simple and straightforward. You call the object and specify the action you want to perform, often with optional arguments that define specific parameters.
   • Example: DoCmd.OpenForm "FormName" opens a form named "FormName".
3. Wide Range of Operations:
   • The DoCmd object is capable of handling a wide range of tasks. Some common actions include:
     • Opening forms: DoCmd.OpenForm "FormName"
     • Running queries: DoCmd.OpenQuery "QueryName"
     • Printing reports: DoCmd.OpenReport "ReportName", acViewPreview
     • Closing objects: DoCmd.Close acForm, "FormName"
     • Setting focus: DoCmd.GoToControl "ControlName"
     • Navigating records: DoCmd.GoToRecord acDataForm, "FormName", acNext

Common Methods of the DoCmd Object:

1. OpenForm
   • The OpenForm method opens a form in a specified view (such as Form View, Design View, or Datasheet View).
   • Example: DoCmd.OpenForm "EmployeeForm", acNormal opens the "EmployeeForm" in normal view.
   • You can also apply filters and pass parameters when opening a form.
2. Close
   • The Close method closes an open object, such as a form, report, or query. You need to specify the type of object to close and its name.
   • Example: DoCmd.Close acForm, "EmployeeForm" closes the "EmployeeForm" form.
3. OpenReport
   • The OpenReport method is used to open a report. You can specify the view (e.g., Print Preview or Report View).
   • Example: DoCmd.OpenReport "SalesReport", acViewPreview opens the "SalesReport" in print preview.
4. RunSQL
   • The RunSQL method executes an SQL statement. This can be used to run action queries (like UPDATE, DELETE, or INSERT queries) directly from VBA.
   • Example: DoCmd.RunSQL "UPDATE Employees SET Salary = 50000 WHERE Department = 'HR'" executes an SQL update query.
5. FindRecord
   • The FindRecord method finds a specific record based on criteria.
   • Example: DoCmd.FindRecord "EmployeeName = 'John Doe'" searches for the record where the employee name is "John Doe."
6. GoToRecord
   • The GoToRecord method is used to navigate to a specific record in a form or datasheet view.
   • Example: DoCmd.GoToRecord acDataForm, "EmployeeForm", acLast moves to the last record in the "EmployeeForm."
7. RunCommand
   • The RunCommand method executes a predefined command, such as undoing the last action, copying data, or refreshing a view.
   • Example: DoCmd.RunCommand acCmdSaveRecord saves the current record.
8. SetWarnings
   • The SetWarnings method is used to control the display of warning messages when executing actions (such as deleting records or running action queries).
   • Example: DoCmd.SetWarnings False disables the warning messages, and DoCmd.SetWarnings True enables them again.

Example Usage of DoCmd in VBA:

1. Opening a Form:

   DoCmd.OpenForm "EmployeeForm"
   

   This code opens the form named "EmployeeForm" in the default view.
2. Running a Query:

   DoCmd.OpenQuery "UpdateEmployeeSalaries"
   

   This opens and executes the query "UpdateEmployeeSalaries".
3. Closing a Form:

   DoCmd.Close acForm, "EmployeeForm"
   

   This closes the "EmployeeForm" form.
4. Running an SQL Command:

   DoCmd.RunSQL "UPDATE Employees SET Salary = 55000 WHERE Department = 'Sales'"
   

   This runs an SQL update query to change the salary for employees in the Sales department.
5. Navigating Records:

   DoCmd.GoToRecord acDataForm, "EmployeeForm", acNext
   

   This navigates to the next record in the "EmployeeForm".
6. Setting Warnings:

   DoCmd.SetWarnings False
   

   This disables warnings (such as confirmation prompts when deleting records) to avoid interruption during execution.

Key Benefits of the DoCmd Object:

1. Automation: The DoCmd object allows you to automate repetitive tasks like opening forms, running queries, and generating reports, saving time and effort.
2. Customization: You can customize the behavior of forms, reports, and queries by interacting with them through VBA code.
3. Flexibility: With various methods like OpenForm, OpenQuery, and Close, you have flexibility in controlling how Access objects behave in response to user actions or automated processes.
4. Error Handling: The DoCmd object can be combined with error handling techniques to ensure that commands execute smoothly, even if errors occur.

Conclusion:

The DoCmd object is an essential tool in Microsoft Access for automating tasks and interacting with the Access interface. It offers a wide range of actions for controlling forms, reports, queries, and other database objects, making it an invaluable resource for developers looking to create efficient, automated database solutions in Access. By understanding and utilizing the DoCmd object, you can enhance the functionality and user experience of your Access applications.