How do I link Subform to ListBox on Mainform? (Correct DB attached)
Hi folks,
I've attached the correct Access 2016 database with an example of the form I'm trying to create.
My goal: a form allowing the user to choose one or more currencies from a listbox and update their exchange rates for several years.
The background tables are as per this relation diagram:
So I have a mainform, where I can choose the currencies (plus a Type of foreign exchange conversion, which defaults to Standard).
Then a subform in datasheet form, which is meant to filter for the Currencies selected, showing the rates for all the years.
The user would then overwrite the rates in that subform as required. Here is a picture of a 'mocked-example':
The subform control has Master and Child Field Links to link the mainform listbox to the subform record source, as shown in this pic:
But the Currency link is not working.
However, the link to the mainform combobox for Type seems to be working.
So I've even tried a bit of VBA to get it working...no joy there either.
I'd like the subform to show rates for the years 2014 to 2017.
Thus the subform should show 4 rows for each currency selected.
At the moment, only the 1 row appears, and while it updates for the Type selected, it refuses to show the Currency selected.
Any advice on how to correct my form to achieve this?
Many thanks in advance,
Chris
PS, a cross-tab layout would be idea, with the selected currencies as column headers.
However, I've avoided this because it seemed a bit complex to get around the non-updateable nature of a cross-tab query.
FXRates.accdb
I've attached the correct Access 2016 database with an example of the form I'm trying to create.
My goal: a form allowing the user to choose one or more currencies from a listbox and update their exchange rates for several years.
The background tables are as per this relation diagram:
So I have a mainform, where I can choose the currencies (plus a Type of foreign exchange conversion, which defaults to Standard).
Then a subform in datasheet form, which is meant to filter for the Currencies selected, showing the rates for all the years.
The user would then overwrite the rates in that subform as required. Here is a picture of a 'mocked-example':
The subform control has Master and Child Field Links to link the mainform listbox to the subform record source, as shown in this pic:
But the Currency link is not working.
However, the link to the mainform combobox for Type seems to be working.
So I've even tried a bit of VBA to get it working...no joy there either.
I'd like the subform to show rates for the years 2014 to 2017.
Thus the subform should show 4 rows for each currency selected.
At the moment, only the 1 row appears, and while it updates for the Type selected, it refuses to show the Currency selected.
Any advice on how to correct my form to achieve this?
Many thanks in advance,
Chris
PS, a cross-tab layout would be idea, with the selected currencies as column headers.
However, I've avoided this because it seemed a bit complex to get around the non-updateable nature of a cross-tab query.
FXRates.accdb
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You're welcome.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You're welcome.
ASKER
Pat and Fabrice both provided valuable input, hence they share the points.
I simply took their input and recombined it in my response.
The reason for my response being Best Solution is simply to help future searchers.
That is, they could simply download my final database, and get the benefit of both inputs from Pat and Fabrice.
I simply took their input and recombined it in my response.
The reason for my response being Best Solution is simply to help future searchers.
That is, they could simply download my final database, and get the benefit of both inputs from Pat and Fabrice.
ASKER
From the frmMultiSelect form within it, I've realised that in this case its easiest to remove the subform, and instead base the whole form on the CurrencyCrossRates table.
Thus, VBA behind the input filter controls creates the SQL to act as the Form.Recordsource.
I've attached the new mockup, with the working code in it, and all seems to be good.
I can probably simplify/optimise the code, but I'll leave that for a later day when I've got some time...at the moment its working fine at least :)
Many thanks again for your generous help,
Chris
FXRates.accdb