VBA/Excel/Access/Word/Application/DoCmd

Calling the Report from VBA

   <source lang="vb">

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

</source>
   
  

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

   <source lang="vb">

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

</source>
   
  

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

   <source lang="vb">

Public Sub MakeBackupCopy()

 DoCmd.CopyObject , "Employees Backup", acTable, "Employees"
 DoCmd.SetWarnings False
  
 DoCmd.OpenQuery "UpdatePrices"
 DoCmd.SetWarnings True

End Sub

</source>
   
  

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

   <source lang="vb">

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

</source>
   
  

DoCmd.Echo

   <source lang="vb">

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

</source>
   
  

Finding a Record

   <source lang="vb">

Private Sub Form_Open(Cancel As Integer)

  Dim strLastName As String
  strLastName = "Miller"
  
  DoCmd.GoToControl "ctlCustLastName"
  DoCmd.FindRecord strLastName

End Sub

</source>
   
  

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

   <source lang="vb">

Sub beep()

   DoCmd.Beep()

End Sub

</source>
   
  

Open a report

   <source lang="vb">

Sub dsub()

   DoCmd.OpenReport "reportName", acViewPreview

End Sub

</source>
   
  

Save changes to the client record

   <source lang="vb">

Private Sub cmdSave_Click()

   "
   DoCmd.RunCommand acCmdSaveRecord

End Sub

</source>
   
  

Sending Mail

   <source lang="vb">

Sub mail()

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

End Sub

</source>
   
  

Setting a Focus

   <source lang="vb">

Private Sub Form_Open(Cancel As Integer)

  DoCmd.GoToControl "ctlCustLastName"

End Sub

</source>
   
  

Undo changes made to the current record

   <source lang="vb">

Private Sub cmdUndo_Click()

   "Undo changes
   DoCmd.RunCommand acCmdUndo

End Sub

</source>
   
  

Use DoCmd.OpenForm to open a form

   <source lang="vb">

Private Sub ExampleTextFileObject()

   DoCmd.OpenForm "frmTextFile", acNormal
   

End Sub

</source>
   
  

Use DoCmd to close a form

   <source lang="vb">

Sub cmdClose_Click()

   DoCmd.Close acForm, "frmContacts"
   

End Sub

</source>
   
  

Use DoCmd to open a form

   <source lang="vb">

Sub openForm()

  DoCmd.openForm "frmCustomer"

End Sub

</source>
   
  

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

   <source lang="vb">

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

</source>
   
  

Using the DoCmd Object to Run Commands

   <source lang="vb">

Sub run()

   DoCmd.RunMacro "RemoveDuplicates"

End Sub Option Compare Database

</source>
   
  

Validating a Record

   <source lang="vb">

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

</source>