Opening a Microsoft Access Form

You can open a Microsoft Access form from Microsoft Excel. You can also create a new form. The following example uses Automation to connect to Microsoft Access. Once the connection is established, the OpenCurrent-Database method is used to open the sample Northwind database. Next, the Customers form is opened with the OpenForm method of the DoCmd object. The form is opened in the normal view (acNormal). To display the form in the design view, use the acDesign constant instead. The Restore method of the DoCmd object ensures that the form is displayed on the screen in a window and not minimized. The Visible property of the Access Application object (objAccess) must be set to True for the form to become visible. Notice that the Access.Application object variable (objAccess) is declared at the top of the module. For this procedure to work correctly, you must set up a reference to the Microsoft Access Object Library. Figure 15-8 shows the Customers form after it's been opened.

' declare at the top of the module Dim objAccess As Access.Application

Sub DisplayAccessForm() Dim strDb As String Dim strFrm As String strDb = "C:\Program Files\Microsoft Office\" _

& "Office\Samples\Northwind.mdb" strFrm = "Customers"

Set objAccess = New Access.Application With objAccess

.OpenCurrentDatabase(strDb)

.DoCmd.OpenForm strFrm, acNormal .DoCmd.Restore .Visible = True End With End Sub

Access Form Sample
Figure 15-8: A Microsoft Access form can be opened by an Excel VBA procedure.

If you want to go a little bit further in your programming efforts, create a brand new Access form from within an Excel VBA procedure, as shown in this example:

' declare at the top of the module Dim myAccess As Access.Application

Sub CreateAccessForm() Dim myForm As Form Dim myDb As String Dim myCtrl As Control Dim strFrmName As String

On Error GoTo Error CreateForm myDb = "C:\Program Files\Microsoft Office\" & "Office\Samples\Northwind.mdb" strFrmName = "frmCustomForm" Set obAccess = New Access.Application obAccess.OpenCurrentDatabase myDb Set myForm = obAccess.CreateForm myForm.Caption = "Form created by Excel" myForm.RecordSource = "Employees" obAccess.DoCmd.Save , strFrmName ' Create a label and text box on the form

Set myCtrl = CreateControl(FormName:=strFrmName, _

ControlType:=acLabel, _ Left:=1000, Top:=1000)"

myCtrl.Caption = "Last Name:"

myCtrl.SizeToFit

Set myCtrl = CreateControl(FormName:=strFrmName, _

ControlType:=acTextBox, _ Parent:="", _ ColumnName:="LastName", _ Left:=2200, Top:=1000)

With obAccess With .DoCmd

.Save , strFrmName .Close acForm, strFrmName End With

.CloseCurrentDatabase .Quit

End With

Set obAccess = Nothing MsgBox "In the Northwind database there is now " & vbCrLf _ & "a new form named " & strFrmName & "." & vbCrLf _ & "Close Excel prior to opening the Northwind " & vbCrLf & "database to view this form." ErrorHandler: Exit Sub Error_CreateForm:

MsgBox Err & " :" & Err.Description Resume ErrorHandler

End Sub

End Sub

Vba Procedure Attributes
Figure 15-9: An Access form can be created by an Excel VBA procedure (see the code of the CreateAccessForm procedure shown above).
+3 -1

Responses

  • mario
    How to open excel vba form in ms access?
    3 months ago

Post a comment