Determine type of object embedded in bound object frame MS Access

Question

We have a MS Access 365 form with a bound object frame, which is bound to a Varbinary(Max) column in the back end Azure SQL database.

The form was set up initially as a very quick fix so that users could paste screen shots from the snipping tool which would then be embedded in the database. However it now appears that users have been copying and pasting entire word documents, excel spreadsheets etc or segments of them.

Is there any way of programmatically determining the type of object which has been embedded for each record? We need to separate screen snips from all other types of object (Word/Excel etc.)

I am assuming there must be something, as if I loop through the records one at a time in the form and right click on the bound object frame, any Word/Excel etc objects give me the option of opening/editing the object, whereas if a screen snip has been pasted in, it doesn't.

Any help is very much appreciated, thanks! Jim

Answer 1

The following code will do what you want. Substitute oleImage with the name of your bound object frame. The best place would probably be in the BeforeUpdate event:

  With oleImage
    If .Class <> "StaticDib" And .Class <> "" Then
      MsgBox "The image content is not a valid bitmap and will be removed.", vbExclamation, "Invalid Image"
      .OleData = ""
      .Class = ""
    End If
  End With

You can also interrogate the contents of the clipboard. Create a new module and copy the code at the bottom of this answer in there. You can then call the function fCheckClipBoardDataType as follows to see if the content matches what you want.

If fCheckClipBoardDataType(CF_BITMAP) = False Then
  Msgbox "Wrong data type in clipboard"
End If

In your case, it would be CF_BITMAP to start with. Depending on your needs, you may also need to check for CF_DIB and CF_DIBV5 but probably not (see https://learn.microsoft.com/en-us/windows/win32/dataxchg/standard-clipboard-formats for details of clipboard formats):

Option Compare Database
Option Explicit

#If VBA7 Then
  Private Declare PtrSafe Function IsClipboardFormatAvailable _
                               Lib "user32" _
                                   (ByVal wFormat As Integer) As Long
#Else
  Private Declare Function IsClipboardFormatAvailable _
                       Lib "user32" _
                           (ByVal wFormat As Integer) As Long
#End If

Public Enum enClipDataType
  CF_BITMAP = 2
  CF_DIB = 8
  CF_DIBV5 = 17
  CF_DIF = 5
  CF_DSPBITMAP = 130
  CF_DSPENHMETAFILE = 142
  CF_DSPMETAFILEPICT = 131
  CF_DSPTEXT = 129
  CF_ENHMETAFILE = 14
  CF_GDIOBJFIRST = 768
  CF_GDIOBJLAST = 1023
  CF_HDROP = 15
  CF_LOCALE = 16
  CF_METAFILEPICT = 3
  CF_OEMTEXT = 7
  CF_OWNERDISPLAY = 128
  CF_PALETTE = 9
  CF_PENDATA = 10
  CF_PRIVATEFIRST = 512
  CF_PRIVATELAST = 767
  CF_RIFF = 11
  CF_SYLK = 4
  CF_TEXT = 1
  CF_TIFF = 6
  CF_UNICODETEXT = 13
  CF_WAVE = 12
End Enum

' Check if the data in the clipboard matches the requested type.
Public Function fCheckClipBoardDataType(lngType As enClipDataType) As Boolean

  If IsClipboardFormatAvailable(lngType) > 0 Then
    fCheckClipBoardDataType = True
  Else
    fCheckClipBoardDataType = False
  End If

End Function