Forum Discussion

Tony2021's avatar
Tony2021
Steel Contributor
Nov 27, 2021

Dlookup

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.  

 

access
  • arnel_gp's avatar
    arnel_gp
    Nov 29, 2021

    Tony2021 

     

    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).

    • Tony2021's avatar
      Tony2021
      Steel Contributor
      Nov 28, 2021

      George_Hepworth 

      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

      • George_Hepworth's avatar
        George_Hepworth
        Silver Contributor
        Nov 28, 2021
        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.

Resources