Info from Table

[SteveE]

Could anyone help with the methord of pulling a setting from one table and using it.

I have a form which used data from several tables.
I have a [settings] table which holds vat rates and exch rates etc and I need to be able to get the exch rate from [settings].[exch] and use it in calculations on the form.

I tried various methords but just can;t seem to get it right.

 

[ed333]

try using DLookUp

 

[SteveE]

Dlookup

I have tried several verision but this is the expression I am using, but the value in my form is just blank, could you advise where you think I am going wrong

=DLookUp("[ExchRate]","[Settings]","[ExchRate] = Forms![ExportsSheet]![Exch]")

I have also tried
=DLookUp("[Settings]![ExchRate]","[Settings]","[Settings]![ExchRate] = Forms![ExportsSheet]![Exch]")

but still no value in my form field [exch]

 

[Mile-O]

You are including a reference to a form object within a string. Access views this a the full reference and not the value within.


=DLookUp("[ExchRate]","[Settings]","[ExchRate] = " & [Forms]![ExportsSheet]![Exch])

 

[neileg]

I would do the calculation in a query and base the form on the query.

Some programmers would argue that this is less efficient that doing it on the form, but my success rate is much higher using the query and the difference in real world performance is negligible.

 

[SteveE]

Thanks for the reply but do you mean to base the form or the control on a query as the form has many other elements

 

[Mile-O]

Well, you can't base a form's control on an query; a field in a query though, yes.

Your form's are better based upon queries than directly to tables.

 

[Rich]

Use a combo box to look up the values