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).
The technique you need is usually referred to as Cascading Comboboxes. You can find many references to this method, such as this one.
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
- Sounds like you might have used a Lookup Field for Currency in the tblBankAccounts table. Is that correct? If so, that's just one of the reasons experienced Access developers do not use Lookup fields in tables. If it's not the case, we'll have to dig deeper.
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.
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.