If you’ve ever created a report and set a control’s Can Shrink property to Yes and then ran the report only for the item to be displayed with no data present then this post is for you.
The entire issue lies not with the control, but with it’s label. By having a label, Access assumes that it should be displayed regardless of the associated text control’s value. Therefore, Control with label = always display.
Continue readingSometimes we, as developers, need to determine certain fundamentals about the database that is running. One of which is the type of database that the users is currently running. Is it an mde/accde? What is the file extension? I thought I’d briefly touch upon this question today.
As reported by Access MVP Alum Arvin, Sagekey has decided to discontinue their Access Deployment Wizard and they don’t mince their reasons why, directly pointing the finger at Microsoft!
It was a good run, but all things must come to an end
SageKey Software’s Access Deployment Wizard has always been designed with the goal of making it easier for an Access Developer to deploy an Access Application, regardless of other present Access versions, and provide any other quality of life improvements that are possible in the install process and when launching your Access application.
The Access Deployment Wizard was preceded by Wise based install scripts and an earlier Wizard called the MSI Wizard. Through these tools the StartAccess launcher and its predecessors have worked behind the scenes to manipulate the active Access versions on a computer to ensure your Runtime version of Access can open smoothly, without reconfiguration messages or Macro warnings. This has always been done through working with and around Microsoft’s settings, while never changing their programs or having any special access from Microsoft.
This means that the process has always been at the mercy of Microsoft’s changes. This is much the same with Access Developers themselves and their Applications. These days when Microsoft makes a change to Access, Office, or Windows, they are unfortunately not thinking of how it can affect Access Applications and the Developers who distribute them using Access Runtime products.
We see lots of example on how to create a new Outlook Contact (now called People), but what about wanting to retrieve the information of an existing Contact through VBA? Well, similarily to my post List Outlook Calendar Appointments it is quite straightforward.
Access MVP Alum Scott Diamond just reported that version 1811 Build 11029.20079 apparently breaks Microsoft’s MAPI implementation. From the report, you simply receive the generic “Microsoft Access has stopped working …” error, so not much help there.
So if you built any e-mailing procedures around it or use libraries such as vbMapi or Redemption do not update or you’ll get a nasty surprise!!! If you have already updated and are facing this issue, then seriously consider reverting your installation back to a prior build, see Microsoft Office 365 – Uninstall an Update.
Ever needed to close a specific Excel Workbook, nothing could be easier. Below is the code I came up with to help out a user in an Access forum.
'---------------------------------------------------------------------------------------
' Procedure : XL_CloseWrkBk
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Close a specifc Excel WorkBook
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Uses Late Binding, so none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFileName : Filename (w ext) of the Excel WorkBook to close
'
' Usage:
' ~~~~~~
' Call XL_CloseWrkBk("Accounts.xlsx")
' Call XL_CloseWrkBk("Accounts.xlsx", True)
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2018-11-30 Initial Release (Forum help)
'---------------------------------------------------------------------------------------
Public Sub XL_CloseWrkBk(sFileName As String, Optional bSaveChanges As Boolean = False)
Dim oExcel As Object
Dim oWrkBk As Object
On Error Resume Next
Set oExcel = GetObject(, "Excel.Application") 'Bind to existing instance of Excel
If Err.Number <> 0 Then 'Excel isn't running
Err.Clear
GoTo Error_Handler_Exit
End If
On Error GoTo Error_Handler
' oExcel.WorkBooks(sFileName).Close
'Iterate through the open workbooks
For Each oWrkBk In oExcel.WorkBooks
If oWrkBk.Name = sFileName Then
oWrkBk.Close SaveChanges:=bSaveChanges 'Adjust the SaveChanges as suits your needs
End If
Next oWrkBk
'Close Excel if no other WorkBooks are open
If oExcel.WorkBooks.Count = 0 Then oExcel.Quit
Error_Handler_Exit:
On Error Resume Next
If Not oWrkBk Is Nothing Then Set oWrkBk = Nothing
If Not oExcel Is Nothing Then Set oExcel = Nothing
Exit Sub
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: XL_CloseWrkBk" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Sub
Hopefully, this will be useful for others as well.
Trying to help out in a Forum in which a user asked to update all the hyperlinks within a workbook excluding 2 worksheets. This happens if your servers get replaced, or you migrate your data to a new server …
So your hyperlink were pointing to say ‘\\192.168.22.127\Docs\’ and you want to update them all to use ‘\\167.25.235.27\Engineering\Manuals\’ instead, or you move your documents from the ‘C:\’ drive to the ‘G:\’ drive, the following will enable you to do so with a single command.
Anyways, in case it can help anyone else, here was what I came up with.
I had a question asked of me relating to how one could change the viewed page of an actively open PDF through VBA and that got me curious.
The concept is pretty straightforward, in the sense, that you need to identify the textbox in Adobe that you need to update once you know that, you can use APIs to locate it, update it’s value, …
I’m too stubborn to let it go, so last night I figured it out. The heart of the process is a very simple procedure
A quick post today to regarding a bug I’ve seen reported a few times (one thread is provided below as an example) in which a database runs fine as an accdb file, but once converted to accde the VBA will not run.
Obviously, the first thing required is to ensure the front-end file has a Trusted Location defined, but in this case, that is already done, yet the VBA code still would not run. As it turns out, the solution was to do some VBA spring cleaning and delete any and all empty procedures.
Stuff like
Private Sub Form_Load()
End Sub
needs to be purged.
So if you have any procedures you started, but never added content to, delete them all. Once your project is purged of such procedures, create a new accde and try again, it should run smoothly.
Hopefully this will help someone figure out why things aren’t working.
I’ve made a few posts in the past regarding image manipulations (resize, rotate, …) but never covered the most basic aspect, which is simply getting general information about an image. Below is a sample which utilizes WIA automation.