Getting Value from MS Access form from Excel VBA.

Question

What is the best way to check is access form is open and get the value of textbox using Excel VBA.

I mean is there a way to check if MS Access application is running and if it is then check certain form is open then get the value from this form's textbox field.

Something like

  If MSAccess.([Application name]).Forms("FormName").isOpen then
     MyVar = MSAccess.([Application name]).Forms("FormName")![PO Number]
  end if

Answer 1

Here is some sample code.

Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
    (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Public Declare Function ShowWindow Lib "user32" _
    (ByVal hWnd As Long, ByVal nCmdShow As Long) As Long

Public Const SW_SHOW = 5
Public Const GW_HWNDNEXT = 2

Sub FindAccess()
Dim WinHandle As Long
Dim objAc As Object

'Form title'
FindWindow vbNullString, "Images"

'use it'
ShowWindow WinHandle, SW_SHOW

'to get the application'
Set objAc = GetObject(, "Access.Application")

'and print a control's value'
Debug.Print objAc.Forms("frmImages").Controls("Description")

Set objAc = Nothing
End Sub

Answer 2

@Remou's Debug.Print will error out if the form isn't open.

Most Access developer's import an IsLoaded() function into their database and use it. The code in my version of it (which may or may not be edited from the original MS version) is this:

  Function IsLoaded(ByVal strFormName As String) As Boolean
   ' Returns True if the specified form is open in Form view or Datasheet view.
    Const conObjStateClosed = 0
    Const conDesignView = 0

    If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
       If Forms(strFormName).CurrentView <> conDesignView Then
          IsLoaded = True
       End If
    End If
  End Function

To use that from Excel, you could rewrite it thus:

  Function IsLoaded(ByVal strFormName As String, objAccess As Object) As Boolean
   ' Returns True if the specified form is open in Form view or Datasheet view.
    Const conObjStateClosed = 0
    Const conDesignView = 0
    Const acSysCmdGetObjectState = 10
    Const acForm = 2

    If objAccess.SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
       If objAccess.Forms(strFormName).CurrentView <> conDesignView Then
          IsLoaded = True
       End If
    End If
  End Function

(I didn't test that from Excel, but you get the idea)