Nov 27 2021 01:55 PM
Hello,
I have a form with an unbound field cboCurrency
I want this field cboCurrency to list the available currencies per COID on tblBankAccounts.
The field in tblBankAccounts is [currency]. So basically if there are 3 currencies per the COID (ie records) then I would want to show those 3 currencies to and be able to choose 1 of them. I want to limit the choices available so I dont accidentally enter a currency that is not setup per that COID.
How can I do this? There might be a better way to do this than Dlookup.
I hope I explained correctly but I suspect I did not.
Let me know if you have questions.
Nov 27 2021 03:24 PM
The technique you need is usually referred to as Cascading Comboboxes. You can find many references to this method, such as this one.
Nov 27 2021 05:48 PM
Hello George,
That's exactly what I am looking for.
I have added the codes in the afterupdate as the link prescribed.
I also added it to the on focus event of cboCurrency.
Issue:
instead of showing the currency name it shows the ID.
here is my code:
Private Sub cboCoName_AfterUpdate()
' Set the Currency combo box to be limited by the selected Company
Me.cboCurrency.RowSource = "SELECT Currency " & _
"FROM tblBankAccounts " & _
"WHERE COIDfk = " & Nz(Me.txtCOID)
End Sub
this is on tblBankAccounts.Currency rowsource with bound column 1 and column count 2 and it shows the actual currency name and not the ID as it does in the above code.
SELECT tblCurrency.ID, tblCurrency.Currency FROM tblCurrency;
Do you happen to see where I am wrong?
thank you
Nov 27 2021 08:31 PM
Nov 28 2021 05:50 AM
Hi George, yes, you are correct. I had a lookup on tblBankAccounts.Currency.
I changed it to a text box and number. I have not used access in a few years and I remembered something about not putting lookups in tables.
Now My qryPmtProposal looks like: (note I added tblCurrency):
SELECT tblPmtProposal.ID, tblPmtProposal.CoName, tblPmtProposal.Amount, tblCurrency.ID, tblCurrency.Currency
FROM tblPmtProposal LEFT JOIN tblCurrency ON tblPmtProposal.Currency = tblCurrency.ID;
I still have the issue of the currency ID displaying in qryPmtProposal instead of the currency name (ie USD instead of "1").
here is the code on AfterUpdate in qryPmtProposal:
Private Sub cboCoName_AfterUpdate()
Me.cboCurrency.RowSource =
"SELECT Currency " & _
"FROM tblBankAccounts " & _
"WHERE COIDfk = " & Nz(Me.txtCOID)
In the above query, how can I adjust to see the name of the currency instead of the ID on Me.cboCurrency?
thank you George.
Nov 28 2021 06:31 AM
The Foreign Key field in tblPmtProposal will be the Primary Key in tblCurrency, as you now have it set up.
The row source for the "downstream" combo box will need to be two columns from the currency table:
Me.cboCurrency.RowSource =
"SELECT CurrencyID, Currency " & _
"FROM tblCurrency" & _
"WHERE ID = " & Nz(Me.txtCOID)
That is assuming that the "upstream" combo box is named "txtCOID".
Column 1 (the first column on the left) is set to 0" width so it is hidden, and Column 2 is set to a width wide enough to display the text value.
Actually, I have a demo on my website that might be helpful. It uses a different approach to filtering, but you can probably get some ideas about the overall process.
One other thing I do, and suggest you consider, is naming conventions for Primary and Foreign Keys. There are different points of view, though. I always rename the default "ID" that Access assigns to Primary Keys to reflect the table for which they are the PK, i.e. CurrencyID instead of ID.
Then, I also use the same name for the Foreign Key field in the related table. Others use suffixes like "CurrencyPK" and "CurrencyFK".
The main point is that having two fields called "Currency", one of which is the text value (in tblCurrency) and the other of which is the Foreign Key, not the text value (in tblPmtProposal) and a number, can be confusing. Disambiguation is important, IMO.
Nov 28 2021 11:38 AM
George, many thanks for the assistance.
I am still not getting the tblCurrency.ID to be replaced with the tblCurrrency.Currency (this is the name of the currency). I was getting an error on "ID". There are 2 fields named "ID" and not sure if that is causing it but I changed the names to append the name of the table in front but still was getting errors. I am not sure if in the code you use an underscore "_" or a "."
I can answer one of your queries:
<That is assuming that the "upstream" combo box is named "txtCOID".
The "upstream" combo box is named cboCoName and this is where the AfterUpdate code is placed.
I have pared down the db and attached.
Maybe you could kindly take a peek.
The form in question opens auto and I shaded the field in qstn.
When you click the 1st record and cboCurrency you should see only USD and EUR but I see all 4 currencies as an option and the record below it should only show USD as an option in cboCurrency
grateful for your assistance, George!
Attached: db
Nov 28 2021 11:48 AM
Nov 28 2021 01:55 PM
George, I didnt notice it wasnt attached. I did attach it and repeated in this post but I now notice an error of
I am going to reboot.
Nov 28 2021 02:04 PM
OK I rebooted. Will see if it still returns that error message above.
Its still giving me that error message.
I dont know why. I open the file in Access without any issues.
Nov 28 2021 02:12 PM
George, not sure why its not allowing me to upload the file.
I uploaded it to google drive.
Maybe you could view it by this means.
https://drive.google.com/file/d/1eG3_Z4xOzLjC9T9wM60mVPVM_t5OMnw3/view?usp=sharing
Nov 29 2021 03:01 AM - edited Nov 29 2021 03:03 AM
here is your db to check and test. see i made another query (not really necessary).
also cboCoName should be Bound to ID2.
Nov 29 2021 03:55 AM
Hello Arnel,
thank you for the response.
I have tested and I have a follow up if you would be so kind.
Once I click in the Co Name on the form and then cboCurency to choose the currency it doesnt "stick" when I go to the next record and I think its because of the requery. Do you see what I mean?
Nov 29 2021 05:55 AM - edited Nov 29 2021 05:57 AM
Solution
here is a hack since your form is Continuous, there is actually 1 currency combobox.
so changing the rowsource of the combo affects the entire form, making some
of the "values" of combo disappear.
i change the query qryPmtProposal to add currency from currency table (the new calculated field is CY).
next i added a textbox (txtCurrencyName, see it in property window) and add CY as it controlsource. in the property i also disallow Tabbing to this control. then i right-click and Position->bring to front. While the currency combo the Position is sent to back. I align both controls correct (reducing the width of the textbox).
next i remove the Border of txtCurrencyName and add code on it's GotFocus to move the focus to the combobox.
next i Overlay (the txtbox on top) the txtCurrencyName to the Currency combobox.
it is now correctly displaying (hopefully).
Nov 29 2021 06:32 PM
that was amazing Arnel.
Ingenious.
thank you so much for your kindness
It works great!
have a good night
Nov 30 2021 03:06 PM
First, a huge thank you to Arnel for stepping in with a solution while I was unable to return to the forum for a few days.
Next, I want to offer an alternative approach which may prove to be slightly simpler to implement, and which does so without having to manipulate the interface. It's based on a technique I learned from Armen Stein, who is an Access MVP. Instead of filtering out non-applicable values in a combo box, Armen suggested we leave them intact, but flag them as "inactive' or "inappropriate" to avoid both the blanking out of combo boxes in records in a continuous form and the hassles of manipulating controls on the form.
For example, the dropdown for currency might look like this for an option where only one Currency is applicable:
Or it might look like this where two of the five are applicable.
And for new records, where no company has been selected, all options are shown as available.
Nov 30 2021 07:36 PM
Dec 01 2021 06:15 AM
Thanks for your comments. I didn't say this approach would eliminate the possibility of selecting a non-applicable currency. What it does is "highlight" or "flag" the invalid options while avoiding the hassle of showing and hiding multiple controls on the form. It's a simple matter of training users to select a valid option.
Of course, if you'd rather do it the other way round, and flag the valid options, that's a simple adjustment to the SQL. Either way works without modifying the interface itself.
If you saved the text value, rather than the Primary Key for the currency, you would get the wrong thing. Most of us prefer to save Primary Keys in lookups, though. And the asterisk is intended only to be a visual cue in the combo box, so it wouldn't appear elsewhere.
Keep in mind that the idea here is to flag these currencies FOR THE CURRENTLY SELECTED RECORD, not for "ALL" of the records. That's the whole reason we have to do this in a continuous view form, in fact.
But you are right. If you are comfortable with one approach, and not comfortable with others, then you should stick with the one that you prefer.
Nov 29 2021 05:55 AM - edited Nov 29 2021 05:57 AM
Solution
here is a hack since your form is Continuous, there is actually 1 currency combobox.
so changing the rowsource of the combo affects the entire form, making some
of the "values" of combo disappear.
i change the query qryPmtProposal to add currency from currency table (the new calculated field is CY).
next i added a textbox (txtCurrencyName, see it in property window) and add CY as it controlsource. in the property i also disallow Tabbing to this control. then i right-click and Position->bring to front. While the currency combo the Position is sent to back. I align both controls correct (reducing the width of the textbox).
next i remove the Border of txtCurrencyName and add code on it's GotFocus to move the focus to the combobox.
next i Overlay (the txtbox on top) the txtCurrencyName to the Currency combobox.
it is now correctly displaying (hopefully).