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:

Relationships between tables
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':

Mocked example of desired form
The subform control has Master and Child Field Links to link the mainform listbox to the subform record source, as shown in this pic:

 Link Master Fields and Link Child Fields
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
ChrisSelf EmployedAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
Multi-select listboxes are not bound and therefore will not work as master/child links.  I've attached an example that might help you get this to work.
FillFormFields.zip
0
ChrisSelf EmployedAuthor Commented:
Thanks very much Pat....that example database is an excellent help!

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
0
Fabrice LambertFabrice LambertCommented:
Well, I did some rework:
- Removed all links between the parent and child form (make no sens since the parent have no record source).
- Removed all combo-boxs from the sub form (and fields definition).
- Added a query
- Added a recordsource to the subform.
- Tweeked the main form's controls's event handler.

Please, see the attached file.
EE.accdb
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PatHartmanCommented:
You're welcome.
0
ChrisSelf EmployedAuthor Commented:
Thanks for the rework Fabrice.
I eventually used your simplified database, with the subform included.
I then added a bit of Pat's code, and now the subform allows for multiple currencies.
I also added back the dropdowns on the subform.
This is to allow the user to add additional currencies & rates in a fairly simple way, with some simple edit forms.
Attached db has the final result.

There's probably a better way to achieve the additional currencies & rates, without accidentally overwriting an existing currency/rate.
But at least this form now seems functional, is easy enough, and the user 'should' be able to notice the mistake if they accidentally overwrote a currency/rate.
So I'm happy enough for the moment.

This had been gumming up my progress for quite some time now, but we're back on track now.
Thanks so much to both of you for your time and advice, very much appreciated Pat and Fabrice!
EE_New.accdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
You're welcome.
0
ChrisSelf EmployedAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.