Forum Discussion
Dlookup
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).
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
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.
- there is a little problem with this approach.
1. it shows all currency, which the OP does not want.
2. i am not very good in analysing so what is "*" in the combobox?
3. you can select either item with or without "*" from the list.
4.on my test, if you deliberately select "*dkk" currency on the first record (ID=45), it is saved with
the asterisk.
if you move to record 3 (ID=47) and change the currency to DKK (without asterisk), the first record changes also to DKK (without asterisk). 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.
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).
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?