DLookup to retrieve ID (1 Viewer)

[Threcian]

Using Access 2013. I have two fields KPI_ID and KPI_Name in a table KPI.

I have a form where I've got the KPI_Name in a combo box. A text box is meant to display the KPI_ID of that KPI_Name.

I'm using DLookup on the text box which is:

=DLookUp("[KPI.KPI_ID]","[KPI]","[KPI.KPI_Name] = " & "[Combo18]")

The problem I am facing is that in Layout view, when I click on Control Source in the Property Sheet and accept the above expression, it shows the corresponding KPI_ID correctly. However, in the Form View when I select a KPI_Name in the combo box, the text box does not update.

Please help.

 

[JHB]

Try the below:

=DLookUp("[KPI_ID]","[KPI]","[KPI_Name] = '" & Me![Combo18] & "'")

 

[arnelgp]

also you can set the rowsource of your combo to:

SELECT pki_code, pki_name from table

set the column count to 2, column widths=0;1

on the after update of your combo, set the the textbox value:

private sub combo_afterupdate()
me.textbox = me.combo.column(0)
end sub

 

[Threcian]

Try the below:

=DLookUp("[KPI_ID]","[KPI]","[KPI_Name] = '" & Me![Combo18] & "'")

This code gives a #Name? error message.

KPI_Name field is a long text format
KPI_ID is a an AutoNumber format

 

[arnelgp]

why do you need long text?

remove Me! from the expression.

 

[JHB]

why do you need long text?

remove Me! from the expression.

Yes ofcause.

 

[Threcian]

also you can set the rowsource of your combo to:

SELECT pki_code, pki_name from table

set the column count to 2, column widths=0;1

on the after update of your combo, set the the textbox value:

private sub combo_afterupdate()
me.textbox = me.combo.column(0)
end sub

This worked. Also is the first piece of code I've written in Access.