MS Access - Exchange rates example
File(s): frmExchangeRatesDemo.accdb, html\exchangeRates.html, html\exchangeRates.js
The Exchange Rates demo has the following html:
<html>
<head>
<link rel="StyleSheet" href="exchangeRates.css" type="text/css">
<script language="JavaScript" src="https://cdn.jsdelivr.net/npm/chart.js@3.3.2/dist/chart.min.js" type="text/javascript"></script>
</head>
<body>
<div class="card">
<h1>Exchange Rate</h1>
<form class="hiddencontainer">
<div class="row">
<div class="value" id="currencyRate"></div>
<label for="result">Result =</label>
<div id="result"></div>
<div id="resultCurrency"></div>
</div>
</form>
<div class="container">
<canvas id="graph" width="300" height="300"></canvas>
</div>
</div>
<script language="JavaScript" src="exchangeRates.js" type="text/javascript"></script>
</body>
</html>
as you can see there's a bit of javascript involved as well. Let us have a look at what is in the exchangeRates.js file.
var currencyChart;
function configureChart() {
const ctx = document.getElementById("graph").getContext("2d");
currencyChart = new Chart(ctx, {
type: "line",
data: {
labels: "",
datasets: []
}
})
}
function queryRates(firstValue,firstCurrency,secondCurrency) {
let typingTime = null;
const url = 'api.frankfurter.dev/v1';
clearTimeout(typingTime);
typingTime = setTimeout(() => {
async function fetchCurrency() {
let res = await fetch(`https://${ url }/latest?from=${ firstCurrency }`);
let dataRes = await res.json();
for (const [key, value] of Object.entries(dataRes.rates)) {
if (secondCurrency === key) {
document.querySelector(".value").innerText = value;
document.querySelector("#result").innerText = (firstValue * value).toFixed(2);
document.querySelector("#resultCurrency").innerText = secondCurrency;
}
}
}
fetchCurrency();
}, 1000)
return document.querySelector(".value").innerText;
}
function displayGraph(fromCHis,toCHis) {
const url = 'api.frankfurter.dev/v1';
async function fetchHistory() {
let resHistory = await fetch(`https://${ url }/2025-01-01..?from=${fromCHis}&to=${toCHis}`);
let dataResHistory = await resHistory.json();
const xlabels = [];
const ylabels = [];
for (const [key, value] of Object.entries(dataResHistory.rates)) {
xlabels.push(key);
ylabels.push(value);
}
const yScale = [];
ylabels.forEach(y => {
yScale.push(y[`${toCHis}`]);
})
currencyChart.data = {
labels: xlabels,
datasets: [{
label: 'Exchange rate',
data: yScale,
borderColor: '#0000ff',
}]
}
currencyChart.update();
}
fetchHistory();
}
configureChart();
OK.. that's a bit too much to explain line by line, but the gist is as follows.
The first thing it runs is at the bottom.
The configureChart function is called to configure the chart as is defined via the npm chart.min.js javascript include.
Then there's two functions.
1. The queryRates function that asks the api.frankfurter.dev API for the current exchange rate for the value in firstValue and from currency firstCurrency in currency secondCurrency.
2. The displayRates function, also queries the api.frankfurter.dev API, but this time for drawing the history chart.
As you can imagine, these API calls take time to execute and you don't want your application to hang if there's no timely response (if at all)
Now have a look at the VBA side of things.
The "Get Rates" button has the initial logic.
Private Sub GetRatesButton_Click()
Dim nValue As Variant
Dim sCurFrom As String
Dim sCurTo As String
AmountTextBox.SetFocus
nValue = CDec(AmountTextBox.Text)
If nValue <> 0 Then
FromCombo.SetFocus
sCurFrom = FromCombo.Text
ToCombo.SetFocus
sCurTo = ToCombo.Text
If sCurFrom <> sCurTo Then
RateTextBox.SetFocus
RateTextBox.Text = "working.."
ResultTextBox.SetFocus
ResultTextBox.Text = ""
QueryRatings
Else
MsgBox "That's a rate of 1, no need to ask that."
End If
Else
MsgBox "Enter an amount first."
End If
End Sub
First we get the amount value from the amount TextBox control and also the comboform values "from" and "to" for the currencies.
Once you enter an amount and change the "from" and "to" currencies so that they don't match, the QueryRatings function is called.
Private Sub QueryRatings()
Dim nValue As Variant
Dim sCurFrom As String
Dim sCurTo As String
Dim jScript As String
Dim varParams(3) As Variant
jScript = "(amount,curFrom,curTo) => {" & vbCrLf
jScript = jScript & " displayGraph(curFrom,curTo);" & vbCrLf
jScript = jScript & " let obj = document.getElementById('currencyRate');" & vbCrLf
jScript = jScript & " if (obj !== undefined) {" & vbCrLf
jScript = jScript & " obj.innerText = '-1'" & vbCrLf ' -1 is initialized..
jScript = jScript & " }" & vbCrLf
jScript = jScript & " queryRates(amount,curFrom,curTo);" & vbCrLf
jScript = jScript & "}" & vbCrLf
AmountTextBox.SetFocus
nValue = CDec(AmountTextBox.Text)
FromCombo.SetFocus
sCurFrom = FromCombo.Text
ToCombo.SetFocus
sCurTo = ToCombo.Text
varParams(0) = nValue
varParams(1) = sCurFrom
varParams(2) = sCurTo
Document.CurrentBrowser = EdgeWebBrowser.Object
Document.RunAnonymousFunction 2, varParams, jScript
'
' We need a timer to poll for the results
Me.TimerInterval = 300
End Sub
We're taking the currency from, currency to and the amount and pass that to the anonymous javascript function, we also identify this as function "2".
The javascript itself calls both displayGraph as well as the queryRates function and it starts a timer.
As the API does not return immediately, we want to check the currencyRate html element for the result.
Again and again, until there's a result.
Let's look at the function the timer runs:
Private Sub Form_Timer()
Dim jScript As String
Dim varParams(0) As Variant
jScript = " () => {" & vbCrLf
jScript = jScript & " let rate = document.getElementById('currencyRate');" & vbCrLf
jScript = jScript & " if (rate !== undefined) {" & vbCrLf
jScript = jScript & " let data = rate.innerText;" & vbCrLf
jScript = jScript & " return data;" & vbCrLf
jScript = jScript & " } else {" & vbCrLf
jScript = jScript & " return '-2';" & vbCrLf ' error "-2" == currency rate not found
jScript = jScript & " }" & vbCrLf
jScript = jScript & "}" & vbCrLf
Document.CurrentBrowser = EdgeWebBrowser.Object
Document.RunAnonymousFunction 3, varParams, jScript
' Debug.Print "Tick"
End Sub
As you can see the timer checks the html element and returns either the value in the html or an error (-2). This function is identified as function "3". We don't pass any parameters to the anonymous function.
The OnRunAnonymousFunction is where we handle the event.
We had 2 different javascript functions, identified by "2" and "3".
Private Sub Document_OnRunAnonymousFunction(ByVal Id As Long, ByVal Params As Variant, ByVal Data As String, ByVal Error As Long)
Dim nRate As Variant
Dim nAmount As Variant
Dim nResult As Variant
Dim sCurTo As String
If Id = 2 Then
' do nothing
ElseIf Id = 3 Then
If GetDecimalSeparator = "," Then
Data = Replace(Data, ".", ",")
End If
nRate = CDec(Data)
If nRate <> -1 Then
ToCombo.SetFocus
sCurTo = ToCombo.Text
AmountTextBox.SetFocus
nAmount = CDec(AmountTextBox.Text)
nResult = nAmount * nRate
RateTextBox.SetFocus
RateTextBox.Text = Data
ResultTextBox.SetFocus
ResultTextBox.Text = CStr(nResult) & " " & sCurTo
'Call Timer.StopTimer
Me.TimerInterval = 0
End If
End If
End Sub
If you're not in the US then your decimal separate might be a comma (",") instead of a dot ("."), so we need to take that into account.
The timer keeps on running until it returns the correct data and the currencyRate html element no longer contains -1.
The moment it no longer contains -1, it has the actual currency Rate, which is returned in the -pass by reference- variable data.
We can now show the result in the resultTextBox.
Once the result is no longer -1, we stop the timer.
AntView - The MS Edge WebView2 ActiveX control Date last changed: 2026/07/13