Access 2013 - Update fields on form

I have an access 2013 form that pops-up when an acrobat file is saved (by double clicking a field on another form) - the form has both pop-up and modal properties marked yes

In the pop-up form there is a button that when clicked opens the acrobat file and allows the user to resize the acrobat file

I update a text box that recalculates and displays the acrobat file size on the pop-up by double clicking the text box

What I would like to do is after I close the acrobat file have the text box that shows the file size automatically update. I tried using the on got focus event for the pop-up form, but that does not appear to trigger when I close the acrobat file

Is there an event that triggers and I can use to update the file size?

Thanks
rogerdjrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
Can you post the code you are using to do this please?
...or a small sample db would be better...
0
rogerdjrAuthor Commented:
on the Pup-up form:
Private Sub Form_GotFocus()

    Me![FileSize] = GetDirOrFileSize(varPopUpFileResizeFrmPathNameTxt, varPopUpFileResizeFrmFileNameTxt)
    Me![FileSize].Requery

End Sub

Function that calcs the file size

Function GetDirOrFileSize(strFolder As String, Optional strFile As Variant) As Long

'Call Sequence: GetDirOrFileSize("drive\path"[,"filename.ext"])

   Dim lngFSize As Long, lngDSize As Long
   Dim oFO As Object
   Dim oFD As Object
   Dim OFS As Object

   lngFSize = 0
   Set OFS = CreateObject("Scripting.FileSystemObject")

   If strFolder = "" Then strFolder = ActiveWorkbook.Path
   If Right(strFolder, 1) <> "\" Then strFolder = strFolder & "\"
   'Thanks to Jean-Francois Corbett, you can use also OFS.BuildPath(strFolder, strFile)

   If OFS.FolderExists(strFolder) Then
     If Not IsMissing(strFile) Then

       If OFS.FileExists(strFolder & strFile) Then
         Set oFO = OFS.GetFile(strFolder & strFile)
         GetDirOrFileSize = oFO.Size
       End If

       Else
        Set oFD = OFS.GetFolder(strFolder)
        GetDirOrFileSize = oFD.Size
       End If

   End If

End Function
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
In the pop-up form there is a button that when clicked opens the acrobat file and allows the user to resize the acrobat file
How are you opening the Adobe file? If you're using a command line, you might be able to use the ShellAndWait method instead. If you can do that, then you could run your resize code AFTER the ShellAndWait method finishes.

One way to do that is shown in this link: https://support.microsoft.com/en-us/kb/99940

The relevant code is here:
Option Explicit
   Declare Function GetModuleUsage% Lib "Kernel" (ByVal hModule%)

   Function WaitShell( AppName as String)
      Dim hMod as Integer
      hMod = Shell(AppName, 1)
      If (Abs(hMod) > 32) then
         While (GetModuleUsage(hMod))
            DoEvents
         Wend
       Else
         MsgBox "Unable to start " & AppName
      End If
   End Function

Open in new window

You'd add that to a Standard Module (and make sure the Declare section is in the General Declarations section of that module), and then open your Adobe process using this syntax:

WaitShell("Your command to open the Adobe file")
<now your code to calc filesize>

Note there are many other methods to create a "ShellAndWait" process, if this one doesn't quite work as needed. A quick search for "vba shell and wait" will turn up a lot of hits.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

rogerdjrAuthor Commented:
The button that opens the PDF file is where the file path and name is Me![PathNameTxt] & Me![FileNameTxt]


Private Sub ResizeFileBtn_Click()
     application.FollowHyperlink Me![PathNameTxt] & Me![FileNameTxt]

End Sub
0
Nick67Commented:
What I would like to do is after I close the acrobat file have the text box that shows the file size automatically update.

That's a bit of a tall order.
The code below will find a cmd.exe process and terminate it.
Adapt it to DoEvents while AcroRd32.exe exists and you can test for the existence to Acrobat Reader.
When the Reader stops, the code proceeds.

Tie it all together so that the sub that opens the Reader waits for Reader to cease and then updates things and you MAY be good.

The problem is as Jeff has come close to noting -- firing up the Reader means Access is no longer active -- so VBA processing may stop until Access is again in focus.

dim oshell
dim oWmg
dim strWndprs
dim objQResult

WScript.Sleep 100

Set oShell = CreateObject("WScript.Shell") 
Set oWmg = GetObject("winmgmts:") 

strWndprs = "select * from Win32_Process where name='cmd.exe'" 
Set objQResult = oWmg.Execquery(strWndprs) 

For Each objProcess In objQResult 

intReturn = objProcess.Terminate(1) 

Next 

Open in new window

0
rogerdjrAuthor Commented:
Tried the following code and got an error Object Required at "WScript.Sleep 100"

Private Sub ResizeFileBtn_Click()
    application.FollowHyperlink Me![PathNameTxt] & Me![FileNameTxt]
     
    Dim oshell
    Dim oWmg
    Dim strWndprs
    Dim objQResult

    WScript.Sleep 100

    Set oshell = CreateObject("WScript.Shell")
    Set oWmg = GetObject("winmgmts:")

    strWndprs = "select * from Win32_Process where name='cmd.exe'"
    Set objQResult = oWmg.Execquery(strWndprs)

    For Each objProcess In objQResult

    intReturn = objProcess.Terminate(1)

Next

    Me![FileSize] = GetDirOrFileSize(Me![PathNameTxt], Me![FileNameTxt])
    Me![FileSize].Requery


End Sub
0
Nick67Commented:
You haven't adapted what I posted at all.
It is still looking to terminate cmd.exe

Here's a sample that will determine the file size of a PDF name c:\tempPDF\test,pdf, open said file, stall while it is open and then re-determine the file size after no instance of AcroRd32.exe is open.

If you aren't using Acrobat Reader as your default PDF handler, this goes south quite quickly.
If you have multiple instances open, it also goes south -- although checking for the number of processes before and after opening could get done easily enough.
ClosedReader.mdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Have you tried the ShellAndWait process I suggested earlier? That would seem to be the most reliable, since you don't have to iterate anything, and immediately have a handle on the exact process you created.

You would have to change the way you open the PDF. You can use the AcroRd command line method to do that:

WaitShell "AcroRd32.exe "ThePathToYourPDF"

There are several command line args you can use with the AcroRd32.exe command. See this document (last page) for more info:

http://www.adobe.com/content/dam/Adobe/en/devnet/acrobat/pdfs/Acrobat_SDK_developer_faq.pdf
0
rogerdjrAuthor Commented:
I have not tried that process yet

After some more study I will give it a try this weekend

Thanks
0
rogerdjrAuthor Commented:
Solution works great I thanks for your help

The only "tweek" I needed to make was to adapt the functions in the module to work with 64 bit Access - added  PtrSafe

Public 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
   
Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.