VBA/Excel/Access/Word/Application/DoCmd

Calling the Report from VBA

 
Sub runReport()
    Dim con As ADODB.Connection
     Set con = New ADODB.Connection
     con.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
       "Data Source=C:\mydb.mdb;"
     DoCmd.OpenReport "rptCustomer", acViewPreview
End Sub

Closes the window of an Access object: DoCmd.Close([ObjectType][, ObjectName][, Save])

 
Public Sub CloseTables()
    Dim tbls As AllTables
    Dim tbl As Variant
    
    Set tbls = Access.Application.CurrentData.AllTables
    For Each tbl In tbls
       If tbl.IsLoaded Then
          If vbYes = msgBox("Close " & tbl.Name & "?") Then
             DoCmd.Close acTable, tbl.Name, acSavePrompt
          End If
       End If
    Next
End Sub

Copies a database object into the same or another database: DoCmd.CopyObject([DestinationDatabase][, NewName][, SourceObjectType][, SourceObjectName])

 
Public Sub MakeBackupCopy()
  DoCmd.CopyObject , "Employees Backup", acTable, "Employees"
  DoCmd.SetWarnings False
   
  DoCmd.OpenQuery "UpdatePrices"
  DoCmd.SetWarnings True
End Sub

Deletes a database object:DoCmd.DeleteObject([ObjectType][, ObjectName])

 
Public Sub DeleteBackupTables()
   Dim tbl As Variant
   For Each tbl In CurrentData.AllTables
      If InStr(1, tbl.Name, "Backup", vbTextCompare) > 0 Then
         If vbYes = msgBox("Delete table " & tbl.Name & "?", vbYesNoCancel Or vbQuestion, "Confirm Table Deletion") Then
            DoCmd.DeleteObject acTable, tbl.Name
         End If
      End If
   Next
End Sub

DoCmd.Echo

 
Private Sub Form_Load()
   DoCmd.Echo False, "Application loading..."
   DoCmd.Hourglass True
   " Load images for opening screen
   DoCmd.Echo True, "Screen update turned on..."
   DoCmd.Hourglass False
End Sub

Finding a Record

 
Private Sub Form_Open(Cancel As Integer)
   Dim strLastName As String
   strLastName = "Miller"
   
   DoCmd.GoToControl "ctlCustLastName"
   DoCmd.FindRecord strLastName
End Sub

Makes a sound using the computer"s built-in speaker.

 
Sub beep()
    DoCmd.Beep()
End Sub

Open a report

 
Sub dsub()
    DoCmd.OpenReport "reportName", acViewPreview
End Sub

Save changes to the client record

 
Private Sub cmdSave_Click()
    "
    DoCmd.RunCommand acCmdSaveRecord
End Sub

Sending Mail

 
Sub mail()
     DoCmd.SendObject acSendTable, "tblIceCream", acFormatXLS, _
            "A", "B; C", _
            "d@w.ru", "Prices", _
            "our latest price list."
End Sub

Setting a Focus

 
Private Sub Form_Open(Cancel As Integer)
   DoCmd.GoToControl "ctlCustLastName"
End Sub

Undo changes made to the current record

 
Private Sub cmdUndo_Click()
    "Undo changes
    DoCmd.RunCommand acCmdUndo
End Sub

Use DoCmd.OpenForm to open a form

 
Private Sub ExampleTextFileObject()
    DoCmd.OpenForm "frmTextFile", acNormal
    
End Sub

Use DoCmd to close a form

 
Sub cmdClose_Click()
    DoCmd.Close acForm, "frmContacts"
    
End Sub

Use DoCmd to open a form

 
Sub openForm()
   DoCmd.openForm "frmCustomer"
End Sub

Using the DoCmd Object to Programmatically Add Sections to Reports at Runtime

 
Private Sub cmdAddHeadersFooters_Click()
    "Open rptAny in Design view
    DoCmd.OpenReport "rptAny", acViewDesign
    "Add a report header and footer
    DoCmd.RunCommand acCmdReportHdrFtr
    "Add a page header and footer
    DoCmd.RunCommand acCmdPageHdrFtr
End Sub

Using the DoCmd Object to Run Commands

 
Sub run()
    DoCmd.RunMacro "RemoveDuplicates"
End Sub
Option Compare Database

Validating a Record

 
Private Sub Form_BeforeUpdate(Cancel As Integer)
   If IsNull(txtCustLastName) Then
      MsgBox "Please enter a last name"
      DoCmd.GoToRecord , , acLast
      Me.ctlCustLastName.SetFocus
   End If
End Sub