Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
on the Pup-up form:
Private Sub Form_GotFocus()
Me![FileSize] = GetDirOrFileSize(varPopUpF
Me![FileSize].Requery
End Sub
Function that calcs the file size
Function GetDirOrFileSize(strFolder
'Call Sequence: GetDirOrFileSize("drive\pa
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.Fi
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
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
Private Sub Form_GotFocus()
Me![FileSize] = GetDirOrFileSize(varPopUpF
Me![FileSize].Requery
End Sub
Function that calcs the file size
Function GetDirOrFileSize(strFolder
'Call Sequence: GetDirOrFileSize("drive\pa
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.Fi
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
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
In the pop-up form there is a button that when clicked opens the acrobat file and allows the user to resize the acrobat fileHow 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
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.
The button that opens the PDF file is where the file path and name is Me![PathNameTxt] & Me![FileNameTxt]
Private Sub ResizeFileBtn_Click()
application.FollowHyperlin
End Sub
Private Sub ResizeFileBtn_Click()
application.FollowHyperlin
End Sub
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.
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
Tried the following code and got an error Object Required at "WScript.Sleep 100"
Private Sub ResizeFileBtn_Click()
application.FollowHyperlin
Dim oshell
Dim oWmg
Dim strWndprs
Dim objQResult
WScript.Sleep 100
Set oshell = CreateObject("WScript.Shel
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![PathN
Me![FileSize].Requery
End Sub
Private Sub ResizeFileBtn_Click()
application.FollowHyperlin
Dim oshell
Dim oWmg
Dim strWndprs
Dim objQResult
WScript.Sleep 100
Set oshell = CreateObject("WScript.Shel
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![PathN
Me![FileSize].Requery
End Sub
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
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
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
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
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)
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)
Premium Content
You need an Expert Office subscription to comment.Start Free Trial