Dynamically set web browser control URL source in a report

[Thomasso]

Hi,



I have a report "rep_Invoices". I want to display a QR code that will take the customer directly to payment. The QR code is being generated using this parametrized URL:
https://api.qrfgen.cz/generator/imag...533&qrplatba=1

This works just fine in Access reports, but I want to change this URL dynamically, based on the invoice values. I created this function for it:

Public Function GenerateQRPayment(intDocumentType As Integer, intDocumentID As Integer) As String

    Dim strDocumentNo, strCurrencyCode, strCreatedDate, strTaxDate, strDueDate, strCustomerReference, strVSymbol As String
    Dim strCustomerICO, strCustomerVAT1, strAmountToPay, strSubTotal, strVATAmount As String
    Dim intCompanyAccountID As Integer
    
    Dim strURL As String
    
    
    strURL = "https://api.qrfgen.cz/generator/image?"
    
    
    Select Case intDocumentType
    
        Case 1  ' Faktura vydaná (daňový doklad)
        
            If IsNull(DLookup("InvoiceID", "v_InvoicesReceivable", "InvoiceID=" & intDocumentID)) Then
                GenerateQRPayment = ""
                Exit Function
            End If
            
        
            strDocumentNo = DLookup("InvoiceNo", "v_InvoicesReceivable", "InvoiceID=" & intDocumentID)
            strCurrencyCode = DLookup("CurrencyCode", "v_InvoicesReceivable", "InvoiceID=" & intDocumentID)
            strCreatedDate = Format(DLookup("InvoiceCreatedDate", "v_InvoicesReceivable", "InvoiceID=" & intDocumentID), "yyyymmdd")
            strTaxDate = Format(DLookup("InvoiceTaxDate", "v_InvoicesReceivable", "InvoiceID=" & intDocumentID), "yyyymmdd")
            strDueDate = Format(DLookup("InvoiceDueDate", "v_InvoicesReceivable", "InvoiceID=" & intDocumentID), "yyyymmdd")
            strCustomerReference = DLookup("CustomerReference", "v_InvoicesReceivable", "InvoiceID=" & intDocumentID)
            strVSymbol = DLookup("VSymbol", "v_InvoicesReceivable", "InvoiceID=" & intDocumentID)
            strCustomerICO = DLookup("CustomerICO", "v_InvoicesReceivable", "InvoiceID=" & intDocumentID)
            strCustomerVAT1 = DLookup("CustomerVAT1", "v_InvoicesReceivable", "InvoiceID=" & intDocumentID)
            strAmountToPay = Replace(Format(DLookup("TotalToPay", "v_InvoicesReceivable", "InvoiceID=" & intDocumentID), "0.00"), ",", ".")
            strSubTotal = Replace(Format(DLookup("SubTotal", "v_InvoicesReceivable", "InvoiceID=" & intDocumentID), "0.00"), ",", ".")
            strVATAmount = Replace(Format(DLookup("VATAmount", "v_InvoicesReceivable", "InvoiceID=" & intDocumentID), "0.00"), ",", ".")
            intCompanyAccountID = DLookup("BillToAccountID", "v_InvoicesReceivable", "InvoiceID=" & intDocumentID)
            
            
            ' dodělat vyúčtování záloh (SA)
            strURL = strURL & _
                    "ID=" & strDocumentNo & "&" & _
                    "CC=" & strCurrencyCode & "&" & _
                    "DD=" & strCreatedDate & "&" & _
                    "DUZP=" & strTaxDate & "&" & _
                    "DT=" & strDueDate & "&" & _
                    "INI=" & DLookup("InfoValue", "v_CompanyInfo", "Infoname='CompanyICO'") & "&" & _
                    "VII=" & DLookup("InfoValue", "v_CompanyInfo", "Infoname='CompanyVAT'") & "&" & _
                    "INR=" & strCustomerICO & "&" & _
                    "VIR=" & strCustomerVAT1 & "&" & _
                    "TP=0&" & _
                    "TD=9&" & _
                    "SA=0&" & _
                    "VS=" & strVSymbol & "&" & _
                    "AM=" & strAmountToPay & "&" & _
                    "TB0=" & strSubTotal & "&" & _
                    "T0=" & strVATAmount & "&" & _
                    "ACC=" & DLookup("IBANCode", "v_CompanyAccounts", "CompanyAccountID=" & intCompanyAccountID) & "&" & _
                    "qrplatba=1"
                    

    End Select
    
    GenerateQRPayment = strURL

End Function

With this, I am able to generate the correct URL for every invoice in my system. But the problem is assigning this URL to the web browser control.

I want to do something like this:

Private Sub Report_Load()

    Me.brwQRCode.ControlSource = GenerateQRPayment(1, Me.InvoiceID)

End Sub

But this does not work.

How do I achieve this?

Thanks.
Tomas

[June7]

Perhaps call the function directly in ControlSource property:

=GenerateQRPayment(1, [InvoiceID])

[Welshgasman]

That is a lot of DLookUps for the same table?
Why not just get the whole record?

[June7]

Good point. Open a filtered recordset and read data from its fields instead of 17 DLookup calls.

I expect v_InvoicesReceivable and v_CompanyInfo and v_CompanyAccounts have relationship(s) so SQL can possibly be a join of these tables.

However, there are two DLookup calls to field InfoValue so a JOIN would complicate code. May be simpler to keep these 2 DLookup calls.

Public Function GenerateQRPayment(intDocumentType As Integer, intDocumentID As Integer) As String
    Dim rs As DAO.Recordset
    Dim strURL As String
    
    Select Case intDocumentType
    
        Case 1  ' Faktura vydaná (danový doklad)
        
            Set rs = CurrentDb.OpenRecordset("SELECT v_InvoicesReceivable.*, IBANCode " & _
                            "FROM v_InvoicesReceivable INNER JOIN v_CompanyAccounts " & _
                            "ON v_InvoicesReceivable.BillToAccountID=v_CompanyAccounts.CompanyAccountID " & _
                            "WHERE InvoiceNo=" & intDocumentID)
        
            If Not rs.EOF Then
                With rs
                    ' dodelat vyúctování záloh (SA)
                    strURL = "https://api.qrfgen.cz/generator/image?" & _
                        "ID=" & !InvoiceNo & "&" & _
                        "CC=" & !CurrencyCode & "&" & _
                        "DD=" & !InvoiceCreatedDate & "&" & _
                        "DUZP=" & !InvoiceTaxDate & "&" & _
                        "DT=" & !InvoiceDueDate & "&" & _
                        "INI=" & DLookup("InfoValue", "v_CompanyInfo", "Infoname='CompanyICO'") & "&" & _
                        "VII=" & DLookup("InfoValue", "v_CompanyInfo", "Infoname='CompanyVAT'") & "&" & _
                        "INR=" & !CustomerICO & "&" & _
                        "VIR=" & !CustomerVAT1 & "&" & _
                        "TP=0&" & _
                        "TD=9&" & _
                        "SA=0&" & _
                        "VS=" & !VSymbol & "&" & _
                        "AM=" & Replace(Format(!TotalToPay, "0.00"), ",", ".") & "&" & _
                        "TB0=" & Replace(Format(!Subtotal, "0.00"), ",", ".") & "&" & _
                        "T0=" & Replace(Format(!VATAmount, "0.00"), ",", ".") & "&" & _
                        "ACC=" & !IBANCode & "&" & _
                        "qrplatba=1"
                End With
            End If
    End Select
    
    GenerateQRPayment = strURL

End Function

[Thomasso]

Thanks, that definitely makes sense with the recordset instead of Dlookups. Always room for improvements

However, this:

=GenerateQRPayment(1,[InvoiceID])

Does not work, because as soon as I click Enter, it changes to this:

="https://msaccess/GenerateQRPayment(1,[InvoiceID])"

If I try to assign this in VBA, I get an error that the control source cannot be set in preview mode.

[June7]

Well, that is an explicit error message.

I tested on form with simple function.
Public Function GenerateQRPayment() As String
GenerateQRPayment = "http://allenbrowne.com/ser-27.html"
End Function
After several attempts where Web Browser control shows error page with message

"Can't read this page.

• Make sure the web address http://'https is correct "

Finally started working. Not sure why except I retyped the function call in ControlSource.

When I try to copy/paste the WebBrowser control to report, get error "not supported on report or continuous form".

So how did you install on report?

Seems I read in another thread the solution is to install a form with WebBrowser control on a report.

[Thomasso]

I can insert a web browser into a report just fine. I'm using Microsoft 365.

I am now able to make this work using parameters in the control source URL, for example:

="https://api.qrfgen.cz/generator/image?ID=" & [InvoiceNo] & "&CC=CZK

This will work in theory but will be quite impractical to set up for every single QR code.

But I still have no success using my created function. When I try to concatenate like this:

="https://api.qrfgen.cz/" & GenerateQRPayment(1;34)

It gets replaced by this weird thing:

="https://msaccess/"https://api.qrfgen.cz/" & GenerateQRPayment(1;34)

(the semi colon isn't a mistake, it's required with my culture settings)

Any ideas how to solve this?

[June7]

No, offered all I can on this. As I said, the function call did work.

Why are you concatenating string in front of function call? I did not.

[Thomasso]

Because I thought that maybe if I start with ="https://", then the string wouldn't be replaced by "https://msaccess/ :-)

[Welshgasman]

I *thought* the msaccess was only required if you were linking to a file?