Making QRCode ActiveX Control for MS Access: Control Source Property

Question

I wanted to implement a QR Code to Access2010 and I found https://github.com/yas78/QRCodeLibVBA. Referencing XLAM from Access did not work and I didn't want to insert all modules directly to Access as it makes the project messy. So I decided to create an OCX file using ancient VB6 as it seemed to be the easiest way to encapsulate all the bits together into one simple object.

Finally, I have made an OCX that has several key properties: DataString which is the string to be displayed, ByteModeCharsetName, ErrorCorrectionLevel, ForeRGB and BackRGB, there are also methods Refresh, Cls and events OnClick and OnDblClick

It works fine in VB6 apps + Excel sheets + Excel forms but it behaves weird in Access forms, reports, etc.

Everything looks as one would expect in Excel:

This is how it looks in Access:

• The custom properties are visible on the tab "Other" but they are not offered in VBA editor at all! However it does compile when entered manually.
• Resizing control behaves weird
• Control's Events like OnClick are not displayed at tab Event of Property Sheet

Here are my questions:

1. Are the Controls for Access "different" than for other office apps?
2. Why the hell are the properties hidden in editor?
3. How to "move" some properties to other tabs (categories), for example ForeRGB to tab Format (as usual for TextBoxes etc.)?
4. How to create ControlSource propety (on the DATA tab) which could be directly bound to a recordset without having to use a VBA? This way, I hope, I could use the control on the continuous forms as well. In fact, this is most important question.
5. Some tips for resizing? (not important)

I think I'm pretty close to my goal but I'm stuck at this point. I know the VB6 is obsolete but after reading Creating Custom Controls for ms access 2010 VB6 seems to be easy choice. Any alternatives for writing OCX?

EDIT: Final working control is available here https://github.com/Combinatix/QRCodeAX

Answer 1

For 4. try setting your control's DataBindingBehavior to vbSimpleBound so that a scalar property (ControlSource in your case) can be bound via DataSource and DataMember properties.

For 3. use Tools->Procedure Attributes... menu, select ControlSource in Name, expand Advanced>> and select Data in Property Category combobox. You can do the same through Object Browser (F2). Find your control, right click your property/method (should be bold) and choose Properties... context menu option. This works with methods and is more versatile than Tools->Procedure Attributes... approach.

Answer 2

To answer your questions one by one:

1. Yes. ActiveX controls in Access are certainly different than in other Office applications.

   In Access, there's one general CustomControl control that encapsulates all ActiveX controls, and offers a default set of events, properties and methods for any control, such as border properties, the requery method, and the enter event.

   The object of the control being encapsulated can be accessed by using the CustomControl.Object proprty

2. These properties aren't displayed because you're referring to a custom control in the general sense, and only get the properties for it.

   To get the object you want, use the following:

   Dim qrObj As QRCode
   Set qrObj = QR.Object
   

3. That's plain not possible, tab layout is determined by Access

4. Also not possible. The ActiveX control must include that functionality, this one doesn't. Generally, using ActiveX controls in continuous subforms to display something different for every row is hard to impossible

5. Resizing the outer control, and then calling CustomControl.SizeToFit should generally just work

Some of the things I deem not possible can be achieved by modifying the ActiveX Control's source code, of course.

Answer 3

A very different and much less cumbersome approach would be to generate the QR code online and download them to be displayed in a (bound) picture control.

I wrote an article on displaying online images:

Show pictures directly from URLs in Access forms and reports

Some code is, of course, needed, but much less than that at GiHub you refer to, though to much to list here in full.

It uses this function retrieve the images:

' Download (picture) file from a URL of a hyperlink field to a
' (temporary) folder, and return the full path to the downloaded file.
'
' This can be used as the control source for a bound picture control.
' If no Folder is specified, the user's IE cache folder is used.
'
' Typical usage in the RecordSource for a form or report where Id is
' the unique ID and Url is the hyperlink field holding the URL to
' the picture file to be displayed:
'
'   - to a cached file where parameter Id is not used:
'
'   Select *, UrlContent(0, [Url]) As Path From SomeTable;
'
'   - or, where Id is used to create the local file name:
'
'   Select *, UrlContent([Id], [Url], "d:\somefolder") As Path From SomeTable;
'
' Then, set ControlSource of the bound picture control to: Path
'
' 2017-05-28. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function UrlContent( _
    ByVal Id As Long, _
    ByVal Url As String, _
    Optional ByVal Folder As String) _
    As Variant

    Const NoError   As Long = 0
    Const Dot       As String = "."
    Const BackSlash As String = "\"
    
    Dim Address     As String
    Dim Ext         As String
    Dim Path        As String
    Dim Result      As String
    
    ' Strip leading and trailing octothorpes from URL string.
    Address = HyperlinkPart(Url, acAddress)
    ' If Address is a zero-length string, Url was not wrapped in octothorpes.
    If Address = "" Then
        ' Use Url as is.
        Address = Url
    End If
    
    If Folder = "" Then
        ' Import to IE cache.
        Result = DownloadCacheFile(Address)
    Else
        If Right(Folder, 1) <> BackSlash Then
            ' Append a backslash.
            Folder = Folder & BackSlash
        End If
    
        ' Retrieve extension of file name.
        Ext = StrReverse(Split(StrReverse(Address), Dot)(0))
        ' Build full path for downloaded file.
        Path = Folder & CStr(Id) & Dot & Ext
        
        If DownloadFile(Address, Path) = NoError Then
            Result = Path
        End If
    End If
    
    UrlContent = Result
    
End Function

I pasted this URL into a record:

https://chart.googleapis.com/chart?cht=qr&chs=300x300&chl=23457

and it worked right away:

Full code can be found at GitHub: VBA.PictureUrl