Solved Open a file without moving the focus (1 Viewer)

Sun_Force

Active member
Local time
Today, 17:54
Joined
Aug 29, 2020
Messages
396
I'm trying to open a pdf when I exit a textbox and leave the focus to the form.
I tried the following API, but when the file opens, the focus moves to the opened file.

Any solution ?

Code:
Call apiShellExecute(hWndAccessApp, "Open", FileName, vbNullString, vbNullString, vbNormalNoFocus)

this is the declaration of the api
Code:
Declare PtrSafe Function apiShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _
   ByVal hwnd As Long, _
   ByVal lpOperation As String, _
   ByVal lpFile As String, _
   ByVal lpParameters As String, _
   ByVal lpDirectory As String, _
   ByVal nShowCmd As Long) _
   As Long


Any kind of help is appreciated.
 

bastanu

AWF VIP
Local time
Today, 01:54
Joined
Apr 13, 2010
Messages
1,401
Just add a new line to set the focus back to your form?
Cheers,
 

Sun_Force

Active member
Local time
Today, 17:54
Joined
Aug 29, 2020
Messages
396
Just add a new line to set the focus back to your form?
Cheers,
I don't think it has any effect. Because it's a matter of Window focus.
The form's focus is OK. But Windows has moved the focus to PDF file. So even if I press Enter nothing happens because focus is on Adobe and not Access.


Edit:
I added the following. As I had guessed the focus is still on Adobe not Access. It means I have to click and select Access to set the focus.
Me.SetFocus
UnitPrice.SetFocus

Edit2 :
Tested the following. Still windows focus moves to the opened file and doesn't stay on Access.
Application.FollowHyperlink Filename
 

Gasman

Enthusiastic Amateur
Local time
Today, 08:54
Joined
Sep 21, 2011
Messages
14,037
I would expect you would need to select the Access window first, then the form? :unsure:
 

Sun_Force

Active member
Local time
Today, 17:54
Joined
Aug 29, 2020
Messages
396
I would expect you would need to select the Access window first, then the form? :unsure:
How? Any kind of hint is appreciated.

On a side note, I had a feeling that vbNormalNoFocus parameter is added to prevent these steps.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:54
Joined
May 7, 2009
Messages
19,169
you may try this.
add this to your form and i assume that you have a command button that opens the pdf:
Code:
#If VBA7 Then
    Private Declare PtrSafe Function SetForegroundWindow Lib "user32" _
        (ByVal hWnd As LongPtr) As Long
#Else
    Private Declare Function SetForegroundWindow Lib "user32" _
        (ByVal hWnd As Long) As Long
#End If

Private Sub cmdOpenThePDF_Click()
Dim strFile As String
Dim strFName As String
'You need to provide Your Own PDF below
strFile = "d:\e-books\100 Excel VBA Simulation.pdf"
strFName = "*" & fncFileNameWOext(strFile) & "*"
FollowHyperlink strFile
While FnFindWindowLike(strFName) = 0
    SetForegroundWindow Application.hWndAccessApp
    DoEvents
Wend
SetForegroundWindow Application.hWndAccessApp
End Sub


Private Function fncFileNameWOext(ByVal param As String) As String
    Dim intPos As Integer
    Dim var As Variant
    var = Split(param, "\")
 
    param = var(UBound(var))
    fncFileNameWOext = param
    intPos = InStrRev(param, ".")
    If intPos > 0 Then
        fncFileNameWOext = Left$(param, intPos - 1)
    End If
End Function

on separate Modules:
Code:
Option Compare Database
Option Explicit


' Module Name: ModFindWindowLike
' (c) 2005 Wayne Phillips (http://www.everythingaccess.com)
' Written 02/06/2005
'
' mODIFIED bY aRNELgP FOR x64 aCCESS
'
               
#If VBA7 Then
    Private Declare PtrSafe Function EnumWindows Lib "user32" (ByVal lpEnumFunc As LongPtr, ByVal lParam As LongPtr) As Long
    Private Declare PtrSafe Function GetWindowText Lib "user32" Alias "GetWindowTextA" (ByVal hWnd As LongPtr, ByVal lpString As String, ByVal cch As Long) As Long
     
        'Could use global variables instead, but this is nicer.
        'Custom structure for passing in the parameters in/out of the hook enumeration function
        'Could use global variables instead, but this is nicer.
        Private Type FindWindowParameters
     
            strTitle As String  'INPUT
            hWnd As LongPtr     'OUTPUT
     
        End Type
#Else
                 
    Private Declare Function EnumWindows Lib "user32" _
       (ByVal lpEnumFunc As Long, _
        ByVal lParam As Long) As Long
 
    Private Declare Function GetWindowText Lib "user32" _
        Alias "GetWindowTextA" _
       (ByVal hwnd As Long, _
        ByVal lpString As String, _
        ByVal cch As Long) As Long
        'Could use global variables instead, but this is nicer.
        'Custom structure for passing in the parameters in/out of the hook enumeration function
        'Could use global variables instead, but this is nicer.
        Private Type FindWindowParameters
     
            strTitle As String  'INPUT
            hWnd As Long        'OUTPUT
     
        End Type
#End If

'''experimental''''''''''''''''
Private Const WM_CHAR = &H102
Private Const BM_CLICK As Long = &HF5&

''' close the window
Private Const WM_SYSCOMMAND = &H112
Private Const SC_CLOSE = &HF060

#If VBA7 Then
Private Declare PtrSafe Function SendMessageBynum Lib "user32" Alias "SendMessageA" (ByVal hWnd As LongPtr, ByVal wMsg As Long, ByVal wParam As LongPtr, lParam As Any) As LongPtr
Private Declare PtrSafe Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hWnd As LongPtr, ByVal wMsg As Long, ByVal wParam As LongPtr, ByVal lParam As LongPtr) As Long

#Else
private Declare Function SendMessageBynum Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As any) As Long
Private Declare Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
#End If
'''''''''''''''''''''



#If VBA7 Then
Public Function FnFindWindowLike(strWindowTitle As String) As LongPtr

#Else
Public Function FnFindWindowLike(strWindowTitle As String) As Long
#End If
 
    'We'll pass a custom structure in as the parameter to store our result...
    Dim Parameters As FindWindowParameters
    Parameters.strTitle = UCase(strWindowTitle) ' Input parameter
 

#If VBA7 Then
    Call EnumWindows(AddressOf EnumWindowProc, VarPtr(Parameters))
#Else
    Call EnumWindows(AddressOf EnumWindowProc, VarPtr(Parameters))
#End If
    FnFindWindowLike = Parameters.hWnd
 
End Function

#If VBA7 Then
Private Function EnumWindowProc(ByVal hWnd As LongPtr, _
                               lParam As FindWindowParameters) As LongPtr
#Else
Private Function EnumWindowProc(ByVal hWnd As Long, _
                               lParam As FindWindowParameters) As Long
#End If
   Dim strWindowTitle As String

   strWindowTitle = Space(260)
   Call GetWindowText(hWnd, strWindowTitle, 260)
   strWindowTitle = UCase(TrimNull(strWindowTitle)) ' Remove extra null terminator
                                       
   If strWindowTitle Like lParam.strTitle Then

        lParam.hWnd = hWnd 'Store the result for later.
        EnumWindowProc = 0 'This will stop enumerating more windows

   Else

        EnumWindowProc = 1

   End If
                       
End Function

Private Function TrimNull(strNullTerminatedString As String)

    Dim lngPos As Long

    'Remove unnecessary null terminator
 
    lngPos = InStr(strNullTerminatedString, Chr$(0))

    If lngPos Then
        TrimNull = Left$(strNullTerminatedString, lngPos - 1)
    Else
        TrimNull = strNullTerminatedString
    End If
   'Debug.Print TrimNull
End Function
 
Last edited:

Sun_Force

Active member
Local time
Today, 17:54
Joined
Aug 29, 2020
Messages
396
@Gasman @arnelgp It's late saturday here
I'll try your solution Monday morning as soon as I'm back to collage.

thanks for your help
 

Isaac

Lifelong Learner
Local time
Today, 01:54
Joined
Mar 14, 2017
Messages
8,738
@Sun_Force
You can also try AppActivate, which is actually meant for what you want to do - move windows focus back to Access.

It's sketchy, but it works sometimes.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:54
Joined
Feb 19, 2002
Messages
42,970
And then there's the one line solution:

Application.FollowHyperlink Me.txtDocName, , True

This works with any type of document that is defined to windows. You don't have to worry about 32/64 bit. So, web pages, spreadsheets, word docs, pdf's, etc. The big benefit is that you don't have to know what program is required.

I don't understand the point of opening the document but not moving focus to it. As someone else said, try adding a line of code after the FollowHyperlink command.
 
Last edited:

Isaac

Lifelong Learner
Local time
Today, 01:54
Joined
Mar 14, 2017
Messages
8,738
I don't understand the point of opening the document but not moving focus to it.
I think it's legit, I actually have this desire frequently. It usually has to do with outputting something to Excel, but then wishing the Access window was activated so that they could see whatever I have cooking up there - maybe a "finished" messagebox, etc.

Like I said, AppActivate is a reasonable thing to try for remedying this.

Some more information:

If you want to use AppActivate in situations where it may fail (and not find such a named window to activate), VBScript is more forgiving than VBA. VBA will raise an actual error, but VBScript simply returns FALSE. Subs for testing below:

Code:
Sub TestAppActivateVBA()
AppActivate "non-existent window name", True
End Sub
Code:
Sub TestAppActivateVBScript()
Dim blSuccess As Boolean
blSuccess = CreateObject("wscript.shell").AppActivate("non-existent window name", True)
If blSuccess = False Then
    Debug.Print "It didn't work, but no error was raised either"
Else
    Debug.Print "It worked"
End If
End Sub
 

Sun_Force

Active member
Local time
Today, 17:54
Joined
Aug 29, 2020
Messages
396
And then there's the one line solution:
Application.FollowHyperlink "D:\test.pdf", , True
@Pat Hartman
Unfortunately it doesn't work. Running above code, the PDF file and focus is moved to the opened file. I have to click Access window to bring back the focus and being able to work on inputting remaining fields.

I don't understand the point of opening the document but not moving focus to it.
@Isaac gave you a situation. This is my case.
The user starts registering an order. As soon an order No. is selected form a combo box, I need to open a PDF file on a secondary monitor (as a reference). The user watching the PDF, inputs the necessary order details (Delivery date, Order count, destination etc).
This PDF has received by email and saved in a specific folder.
As you see, the other document is just opened as a reference for inputting details. Nobody wants to work on it. I don't want to change the focus and interrupt inputting data.

As someone else said, try adding a line of code after the FollowHyperlink command.
And I explained why it doesn't work on #3 post above.


Many thanks for taking your time and trying to help. If you still think your solution works, it would be great if you upload a simple example to show it.

Thanks again.
 

Sun_Force

Active member
Local time
Today, 17:54
Joined
Aug 29, 2020
Messages
396
@Isaac
Thanks for trying to help. I tried any combination to make AppActivate to work, but I'm receiving Invalid Argument error.
Microsoft documentation on AppActivate here shows that I have to open the application first.
I couldn't find a way to use it for an already launched database.

I know you're busy with your job, I'd appreciate if you could set up a simple database to show how it works. I can wait (several months ;) )

Thanks again.
 

Sun_Force

Active member
Local time
Today, 17:54
Joined
Aug 29, 2020
Messages
396
you may try this.
add this to your form and i assume that you have a command button that opens the pdf:
Code:
#If VBA7 Then
    Private Declare PtrSafe Function SetForegroundWindow Lib "user32" _
        (ByVal hWnd As LongPtr) As Long
#Else
    Private Declare Function SetForegroundWindow Lib "user32" _
        (ByVal hWnd As Long) As Long
#End If

Private Sub cmdOpenThePDF_Click()
Dim strFile As String
Dim strFName As String
'You need to provide Your Own PDF below
strFile = "d:\e-books\100 Excel VBA Simulation.pdf"
strFName = "*" & fncFileNameWOext(strFile) & "*"
FollowHyperlink strFile
While FnFindWindowLike(strFName) = 0
    SetForegroundWindow Application.hWndAccessApp
    DoEvents
Wend
SetForegroundWindow Application.hWndAccessApp
End Sub


Private Function fncFileNameWOext(ByVal param As String) As String
    Dim intPos As Integer
    Dim var As Variant
    var = Split(param, "\")

    param = var(UBound(var))
    fncFileNameWOext = param
    intPos = InStrRev(param, ".")
    If intPos > 0 Then
        fncFileNameWOext = Left$(param, intPos - 1)
    End If
End Function

on separate Modules:
Code:
Option Compare Database
Option Explicit


' Module Name: ModFindWindowLike
' (c) 2005 Wayne Phillips (http://www.everythingaccess.com)
' Written 02/06/2005
'
' mODIFIED bY aRNELgP FOR x64 aCCESS
'
              
#If VBA7 Then
    Private Declare PtrSafe Function EnumWindows Lib "user32" (ByVal lpEnumFunc As LongPtr, ByVal lParam As LongPtr) As Long
    Private Declare PtrSafe Function GetWindowText Lib "user32" Alias "GetWindowTextA" (ByVal hWnd As LongPtr, ByVal lpString As String, ByVal cch As Long) As Long
    
        'Could use global variables instead, but this is nicer.
        'Custom structure for passing in the parameters in/out of the hook enumeration function
        'Could use global variables instead, but this is nicer.
        Private Type FindWindowParameters
    
            strTitle As String  'INPUT
            hWnd As LongPtr     'OUTPUT
    
        End Type
#Else
                
    Private Declare Function EnumWindows Lib "user32" _
       (ByVal lpEnumFunc As Long, _
        ByVal lParam As Long) As Long

    Private Declare Function GetWindowText Lib "user32" _
        Alias "GetWindowTextA" _
       (ByVal hwnd As Long, _
        ByVal lpString As String, _
        ByVal cch As Long) As Long
        'Could use global variables instead, but this is nicer.
        'Custom structure for passing in the parameters in/out of the hook enumeration function
        'Could use global variables instead, but this is nicer.
        Private Type FindWindowParameters
    
            strTitle As String  'INPUT
            hWnd As Long        'OUTPUT
    
        End Type
#End If

'''experimental''''''''''''''''
Private Const WM_CHAR = &H102
Private Const BM_CLICK As Long = &HF5&

''' close the window
Private Const WM_SYSCOMMAND = &H112
Private Const SC_CLOSE = &HF060

#If VBA7 Then
Private Declare PtrSafe Function SendMessageBynum Lib "user32" Alias "SendMessageA" (ByVal hWnd As LongPtr, ByVal wMsg As Long, ByVal wParam As LongPtr, lParam As Any) As LongPtr
Private Declare PtrSafe Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hWnd As LongPtr, ByVal wMsg As Long, ByVal wParam As LongPtr, ByVal lParam As LongPtr) As Long

#Else
private Declare Function SendMessageBynum Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As any) As Long
Private Declare Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
#End If
'''''''''''''''''''''



#If VBA7 Then
Public Function FnFindWindowLike(strWindowTitle As String) As LongPtr

#Else
Public Function FnFindWindowLike(strWindowTitle As String) As Long
#End If

    'We'll pass a custom structure in as the parameter to store our result...
    Dim Parameters As FindWindowParameters
    Parameters.strTitle = UCase(strWindowTitle) ' Input parameter


#If VBA7 Then
    Call EnumWindows(AddressOf EnumWindowProc, VarPtr(Parameters))
#Else
    Call EnumWindows(AddressOf EnumWindowProc, VarPtr(Parameters))
#End If
    FnFindWindowLike = Parameters.hWnd

End Function

#If VBA7 Then
Private Function EnumWindowProc(ByVal hWnd As LongPtr, _
                               lParam As FindWindowParameters) As LongPtr
#Else
Private Function EnumWindowProc(ByVal hWnd As Long, _
                               lParam As FindWindowParameters) As Long
#End If
   Dim strWindowTitle As String

   strWindowTitle = Space(260)
   Call GetWindowText(hWnd, strWindowTitle, 260)
   strWindowTitle = UCase(TrimNull(strWindowTitle)) ' Remove extra null terminator
                                      
   If strWindowTitle Like lParam.strTitle Then

        lParam.hWnd = hWnd 'Store the result for later.
        EnumWindowProc = 0 'This will stop enumerating more windows

   Else

        EnumWindowProc = 1

   End If
                      
End Function

Private Function TrimNull(strNullTerminatedString As String)

    Dim lngPos As Long

    'Remove unnecessary null terminator

    lngPos = InStr(strNullTerminatedString, Chr$(0))

    If lngPos Then
        TrimNull = Left$(strNullTerminatedString, lngPos - 1)
    Else
        TrimNull = strNullTerminatedString
    End If
   'Debug.Print TrimNull
End Function
@arnelgp Thanks for the code.
But I'm receiving several compiling errors one after another.
The first one is :
Compile Error: Internal Error
on this line
Dim Parameters As FindWindowParameters
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:54
Joined
May 7, 2009
Messages
19,169
did you put the other code in a module?
 

Sun_Force

Active member
Local time
Today, 17:54
Joined
Aug 29, 2020
Messages
396
did you put the other code in a module?
Yes


Edit : Sorry. I hate those #IF VBA7
So while deleting them and fixed the code for 64 bit version, I had cut some part that I shouldn't have.


@arnelgp Everything is fine now. You're a genius.
I simply don't know how to thank you.
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:54
Joined
May 7, 2009
Messages
19,169
i only have x64 so cannot test on x32.
test form1. you need to open in design view
and change the pdf path.

//Edit: on my test MSA stays on "foreground".
you click on square (restore button) besides the "x" button
so you can see that the pdf is opening at the background.
 

Attachments

  • Database1.zip
    25.6 KB · Views: 548

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:54
Joined
May 7, 2009
Messages
19,169
just seen the Edit part of post #16.
goodluck:)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:54
Joined
Feb 19, 2002
Messages
42,970
I didn't say that the one line solution would affect the focus only that it was far simpler than the code you were working with. What code did you add AFTER the FollowHyperlink to move the focus?

Are you sure that copying from the PDF wouldn't be useful to reduce typos?
 
Last edited:

Isaac

Lifelong Learner
Local time
Today, 01:54
Joined
Mar 14, 2017
Messages
8,738
I know you're busy with your job, I'd appreciate if you could set up a simple database to show how it works. I can wait (several months ;) )
Sure.
But remember, AppActivate may need adjustment on your computer. When I open Excel and a new workbook, I get a window called Book1 - Excel (but maybe you get something different). When I open Notepad, I get a window titled "Untitled - Notepad" (presumably you get the same thing, but maybe you don't).

Here are my results. You can see how effectively one line of code was the key.

1614612517377.png
 

Attachments

  • AppActivate.accdb
    372 KB · Views: 347

Users who are viewing this thread

Top Bottom