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

Possibly extract document filename which will provide filename extension. Review https://www.tek-tips.com/viewthread.cfm?qid=1039250. I've never used OLEObject nor Varbinary field so cannot confirm code is relevant. — June7, Jun 20 '23 at 18:14

BobS · Accepted Answer · 2023-06-21T03:36:40.503

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

Thanks Bob. I tried both of these but drew a blank. If I gather all of the properties of the bound image frame, the Class property is always null, regardless of what data is in the bound object frame. I tried the fCheckClipBoardDataType code as well, checking for all three CF_BITMAP, CF_DIB, CF_DIBV5 but it always returns false. — BiigJiim, Jun 21 '23 at 18:19
Checking the clipboard contents has a few loopholes unless you can check the clipboard contents immediately after a paste. It has its uses but probably not in this case. I had a bit of a play around and found that if you add a hidden Unbound Object Frame on your form then you can populate it with `UnboundObjectFrame.OLEData = BoundObjectFrame.Value` in the BeforeUpdate event. You can then check `UnboundObjectFrame.Class` for the value `StaticDib` and take it from there. — BobS, Jun 22 '23 at 00:37
I think this might have cracked it in a roundabout way. Interesting - I was testing by looping through 4 records, one which was an embedded word doc and 3 were screenshots, and comparing the entire properties collection of the bound object frame for each record. The property values were the same for each record (ObjectVerbsCount=3, Class = "") But if I do what you suggested, dump the OleData into an unbound object frame first, and then compare the properties of the unbound frame, the Class, OleClass and ObjVerbsCount properties tell me which one is the word doc. Big thank you Bob!!! — BiigJiim, Jun 22 '23 at 09:14

Determine type of object embedded in bound object frame MS Access

1 Answers1