VBA/Excel/Access/Word/Application/DoCmd
Содержание
- 1 Calling the Report from VBA
- 2 Closes the window of an Access object: DoCmd.Close([ObjectType][, ObjectName][, Save])
- 3 Copies a database object into the same or another database: DoCmd.CopyObject([DestinationDatabase][, NewName][, SourceObjectType][, SourceObjectName])
- 4 Deletes a database object:DoCmd.DeleteObject([ObjectType][, ObjectName])
- 5 DoCmd.Echo
- 6 Finding a Record
- 7 Makes a sound using the computer"s built-in speaker.
- 8 Open a report
- 9 Save changes to the client record
- 10 Sending Mail
- 11 Setting a Focus
- 12 Undo changes made to the current record
- 13 Use DoCmd.OpenForm to open a form
- 14 Use DoCmd to close a form
- 15 Use DoCmd to open a form
- 16 Using the DoCmd Object to Programmatically Add Sections to Reports at Runtime
- 17 Using the DoCmd Object to Run Commands
- 18 Validating a Record
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>