Open Access form from Excel

[jcaptchaos2]

Not sure if I should post this here or on the excel board but here is my question.

I am using this code to try and open a form in access from excel, it opens the database and the form that automatically opens when the database but not the form I want. Is there a way to only open the form I want and not the main form?

Sub OpenAccess2()
Dim ac As Object
On Error Resume Next
Set ac = GetObject(, "Access.Application")
If ac Is Nothing Then
Set ac = GetObject("", "Access.Application")
ac.OpenCurrentDatabase "N:\Fishbowl Databases\Labor Collection Min.accdb"
ac.DoCmd.Openform "Earned Hours_Cast"
ac.UserControl = True
Set ac = Nothing
End If
AppActivate "Microsoft Access"
End Sub

 

[s.ridd]

I'm not sure I really understood your question, but what I took it to mean was that you want to "override" the form that is displayed on start up and show a different form instead. I also assumed that you didn't want to alter this property so that it always opens the new form, just when the subroutine is ran? (otherwise you might as well just set the property in Access itself).

Anyway, my knowledge of the Access Object Model isn't the greatest, but I've amended your code to add these 3 lines in

Dim str As String
str = ac.CurrentDb.Properties("StartUpForm")
ac.DoCmd.Close acForm, str

like this

Sub OpenAccess2()
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 "N:\Fishbowl Databases\Labor Collection Min.accdb"
str = ac.CurrentDb.Properties("StartUpForm")
ac.DoCmd.Close acForm, str
ac.DoCmd.Openform "Earned Hours_Cast"
ac.UserControl = True
Set ac = Nothing
End If
AppActivate "Microsoft Access"
End Sub

Hope this helps

Simon

 

[jcaptchaos2]

Simon,

Thanks for the code but for some reason the form still stays open and does not close. Here is what I'm using

Sub OpenAccess2()
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 "N:\Fishbowl Databases\Labor Collection Min.accdb"
str = ac.CurrentDb.Properties("Shop Floor Data Entry")
ac.DoCmd.Close acForm, str
ac.DoCmd.OpenForm "Earned Hours_Cast"
ac.UserControl = True
Set ac = Nothing
End If
AppActivate "Microsoft Access"
End Sub

 

[s.ridd]

Hi JC,

The "StartUpForm" part of

[COLOR=#574123]str = ac.CurrentDb.Properties("StartUpForm")[/COLOR]

is an actual property relating to the form selected from the Office button>Access Options>Current Database>Display Form so it should pick up "Shop Floor Data Entry"

I suspect why this failed is that

ac.DoCmd.Close acForm, str

requires a reference to the Access Object Library to function, the acForm needs to be replaced with 2. I've amended your code to show you how to close a specific form or you can use the StartUpForm property if you wish. Hopefully this makes sense

Sub OpenAccess2()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 "N:\Fishbowl Databases\Labor Collection Min.accdb"
'str = ac.CurrentDb.Properties("Shop Floor Data Entry")
ac.DoCmd.Close 2, "Shop Floor Data Entry" 'or str
ac.DoCmd.OpenForm "Earned Hours_Cast"
ac.UserControl = True
Set ac = Nothing
End If
AppActivate "Microsoft Access"
End Sub

Simon

 

[jcaptchaos2]

Simon,

Thanks for your help, that did the trick.