DLookup Expression or VBA OR Query! Nothings working....!! Please Help

[JonoGee]

HI all, I am at my wits end with this one and it strikes me as it should be so simple...


I am basically trying to do the equivalent of an Index/Match or Vlookup would do in Excel...
I have tried DLookup's and Queries and no matter what I do it seems i always get syntax errors..... To be honest the simple ability to do this seemingly small thing is holding me back massively on this cry fest!

I have attached an screenshot explaining what i want to do combined with the description below...
Basically, I want to look for the Yellow Circled Code in FORM_1 (Bill_Payment_Currency_Code), in this case, "INR", in TABLE_1, then return the value of whatever is in the column "Currency_to_GBP_Rate_Multiplier" (Cell Highlighted in green) so in this case 0.011.
Then, I want that value (0.011) to be displayed in the green circled box named "Bill_Payment_Currency_To_GBP_Rate".

I am sure there would be a best practise way to do this, Query, Expression or VBA but i literally can't get any success at all...

[ranman256]

'the where clause using a string field
sWhere = "[field]='" & txtBox & "'"

'the where clause using a numeric field
sWhere = "[field]=" & txtBox

=Dlookup ("[field2return]", "[TableOrQry]",sWhere)

but for combo boxes, you dont event need code,
the value in Combo1, can fill the value in combo2. Combo1 has 2 columns, and places the value picked in combo2

sub combo1_afterupdate()
combo2 = combo1.column(1)   'in vb, columns begin with zero , not 1)
end sub

[JonoGee]

OK. I have no idea what all that meant above the Combo boxes bit, but Combo boxes sounds like it would do the job. Let me give that a go. Thank you.

[JonoGee]

SO i have tried that and now the Combobox1 still opens up the list of choices, but will now not let me pick one..
Any ideas? Obviously when I am in Form View and not as shown below

Private Sub Bill_Payment_Currency_AfterUpdate()
Bill_Payment_Currency_To_GBP_Rate = Bill_Payment_Currency.Column(1)   'in vb, columns begin with zero , not 1)
End Sub

JPG Screenshot attached....

[Micron]

You have 3 green indicators telling you something is wrong. What are those messages? Might help to see the combo row source as well.

[JonoGee]

I don't know anything about three status indicators. Whats that like>? lol.
This is the Combobox1 Row Source that was Auto Generated...

[Micron]

No idea what your 1st comment means. Your first pic clearly shows the indicators?
The rowsource is partially evident in that first pic (in the property sheet) and begins with SELECT. That's what I was referring to.

[Gicu]

In your first post you have #Name? in the last two fields meaning the controls are bound to non-existing fields (in the form's record source). Same thing probably with Bill_Payment_Currency_Code, make sure it exists in the form's source. And you do not need to make CurrencyRate a combo, you can leave it as a textbox and simply add =[Bill_Payment_Currency_Code].Column(1) to the control source property (assumes that "Bill_Payment_Currency_Code" is the name of the combo, make sure you rename it and it is bot combobox1).

Cheers,

[JonoGee]

SELECT tbl_SETTINGS_FINANCE_Currencies.Id, tbl_SETTINGS_FINANCE_Currencies.Currency_Code, tbl_SETTINGS_FINANCE_Currencies.[Currency-Symbol], tbl_SETTINGS_FINANCE_Currencies.Currency_Name FROM tbl_SETTINGS_FINANCE_Currencies ORDER BY tbl_SETTINGS_FINANCE_Currencies.[Id];

Oh Sorry, I have never noticed them before... I am colourblind,, but still I can't believe i have never nopticed those before..

[davegri]

Micron's green indicators.

[JonoGee]

I am lost now... I have tried that but its just returning #Name?

[NoellaG]

Maybe you can post a copy of your application with some dummy data in it?

[Gicu]

What is the record source of the form? #Name? means the record source (table or query) does not include the Bill_Payment_Currency_Code field.

Cheers,