opening an Access form from Excel using vba... (1 Viewer)

[ChazRab]

How would I open an Access form from Excel...
using vba? This opens up an Access window but dosen't open the form:


Private Sub openaccessform_Click()

Dim ac As Object
Dim str As String
On Error Resume Next
Set ac = GetObject(, "Access.Application")
If ac Is Nothing Then
Set ac = GetObject("", "Access.Application")
'ac.OpenCurrentDatabase "C:\Users\Charles\Desktop\PDOXDB.accdb"
'str = ac.CurrentDb.Properties("Shop Floor Data Entry")
ac.DoCmd.OpenForm "FORMVIEW"
ac.UserControl = True
Set ac = Nothing
End If
AppActivate "Microsoft Access"
ac.OpenCurrentDatabase "C:\Users\Charles\Desktop\PDOXDB.accdb"
'str = ac.CurrentDb.Properties("Shop Floor Data Entry")
ac.DoCmd.OpenForm "FORMVIEW"
End Sub

The name of the Access table is PDOXDB' the original name of the Access form I want to open from Excel is FORMVIEW
I renamed FORMVIEW in Access to AutoExec. This opens the form immediately when you click the table in Access.

I just want to open the Access form from Excel code and hide the table view some way

Thx for anyone's help.

cr

 

[JHB]

The name of the Access table is PDOXDB' the original name of the Access form I want to open from Excel is FORMVIEW

Which table? (Do you mean Database?)

Private Sub openaccessform_Click()

Dim ac As Object
Dim str As String
On Error Resume Next
Set ac = GetObject(, "Access.Application")
If ac Is Nothing Then
Set ac = GetObject("", "Access.Application")
'ac.OpenCurrentDatabase "C:\Users\Charles\Desktop\PDOXDB.accdb"
'str = ac.CurrentDb.Properties("Shop Floor Data Entry")
ac.DoCmd.OpenForm "FORMVIEW"
ac.UserControl = True
[B]Set ac = Nothing[/B]
End If
AppActivate "Microsoft Access"
ac.OpenCurrentDatabase "C:\Users\Charles\Desktop\PDOXDB.accdb"
'str = ac.CurrentDb.Properties("Shop Floor Data Entry")
ac.DoCmd.OpenForm "FORMVIEW"
End Sub

You set ac = Nothing, but later on you use ac again. (It is not ok)

Set the error handling out of function (On Error Resume Next), then run the code again and see which error you get.