CODEWITHSUNDEEP
ms-accessvba
Ryan_W4588
Ryan_W4588

Reputation: 668

How can I bring my current Access DB to foreground via VBA?

I'm trying to get my access db to come to the foreground based on the couple of lines, below. I'm not sure why it isn't working though, or if there are better methods to do this.

From what I've read, this should work:

Access.Visible = False
Access.Visible = True

But doesn't actually bring the database to the front.

Edit for more info:

Private Sub Form_Open(Cancel As Integer)
    getStrUserName = Environ("username")

    dbName = "myDB.accdb" ' database name
    versionChckDB = "versionCheckDB.accdb" ' version check db name
    strServer = "C:\My\Path\to\Server" ' server location string
    strDesktop = "C:\My\Path\to\Local" ' desktop location string
    strVersionCheck = "C:\My\Path\to\Version" ' version check location

    Static acc As Access.Application
    Dim db As DAO.Database
    Dim strDbName As String

    If FileLocked(strDesktop & "\" & versionChckDB) Then
        Dim objAccess As Access.Application
        Set objAccess = GetObject(strDesktop & "\" & versionChckDB)
        objAccess.Application.Quit acQuitSaveAll
        Set objAccess = Nothing
        DoCmd.OpenForm "frmMainMenu"
        DoCmd.RunCommand acCmdAppMaximize
        Access.Visible = False
        Access.Visible = True
        GoTo exitSub
    Else
        strDbName = strDesktop & "\" & versionChckDB
        Set acc = New Access.Application
        acc.Visible = True
        Set db = acc.DBEngine.OpenDatabase(strDbName, False, False)
        acc.OpenCurrentDatabase strDbName
    End If
    'db.Close
    exitSub:
        Call SetForegroundWindow(Application.hWndAccessApp) ' bringing access DB to foreground

    End Sub

Upvotes: 2

Views: 4172

Answers (1)

Andre
Andre

Reputation: 27644

Usually one uses an API function for that.

From http://www.access-programmers.co.uk/forums/showthread.php?t=132129 :

Public Declare Function SetForegroundWindow Lib "user32" (ByVal hWnd As Long) As Long

and then

Call SetForegroundWindow(Application.hWndAccessApp)

Edit
If you want to get the newly opened Access application window to the front, you need its hWnd:

Call SetForegroundWindow(acc.hWndAccessApp)

Edit 2
This works for me. Notepad is briefly in the foreground, then the Access window.

Module:

Public Declare Function SetForegroundWindow Lib "user32" (ByVal hWnd As Long) As Long
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Public Sub TestAccessToForeground()

    Sleep 300
    Shell "notepad.exe", vbNormalFocus
    Sleep 300
    Call SetForegroundWindow(Application.hWndAccessApp)

End Sub

Form:

Private Sub Form_Open(Cancel As Integer)

    Call TestAccessToForeground

End Sub

Upvotes: 6

Related Questions