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

You may want to add examples, more detail to get a better answer. — Jon W
– Jon W, Commented Mar 27, 2009 at 18:42
I won't put this in an answer since it doesn't really answer the question posed, but this smells like a very bad idea in the making. My advice would be to find a better way to approach whatever you are trying to do. If you give more details I'll try and offer advice on a better approach. — JohnFx
– JohnFx, Commented Mar 27, 2009 at 21:05

Fionnuala · Accepted Answer · 2009-03-27 21:12:14Z

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

answered Mar 27, 2009 at 21:12

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

David-W-Fenton · Accepted Answer · 2010-09-22 03:13:36Z

@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)

Collectives™ on Stack Overflow

Getting Value from MS Access form from Excel VBA

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related