How to apply this type of dlookup?

[smyeong]

Hi, all.
I used below after_update code to dlookup particular data on my parent form. It did what i want.
Me!Material = DLookup("imdsc1", "tbl_itemmaster", "IMLitm = forms![frm_itemRequestDetail]![ILcode]")
Eg, i choose dept code and it auto show dept name in my unbound field on the parent form.

But when i came into subform (datasheet view) i applied above function into a dropdown field. It did lookup the field but it failed somehow
Eg,
subform
ColA..ColB...ColC
1... .... B......Boy
2........A......Boy
3........C......Boy
4........D......BOy

When i choose B from the dropdown box, it change the colC to Boy and does not remain the others.
Note : ColC is unbound field

Hope u could help...
Thank you very much

 

[Pat Hartman]

You can't really use unbound fields on a subform if it is in continuous or datasheet view. An unbound control exists ONCE for the form and so can hold only a single value and that will always be the value of the current record.

Better technique is to use a query that joins your lookup table to the main table using a left join. You can select any fields you want from either table. This query is used as the recordsource for the form. And now since all the fields are present in the form's recordsource, you have no need to use DLookup().

 

[smyeong]

Thanks giving

You can't really use unbound fields on a subform if it is in continuous or datasheet view. An unbound control exists ONCE for the form and so can hold only a single value and that will always be the value of the current record.

Better technique is to use a query that joins your lookup table to the main table using a left join. You can select any fields you want from either table. This query is used as the recordsource for the form. And now since all the fields are present in the form's recordsource, you have no need to use DLookup().

Thanks Hartman. U have inspired me how to do solve problem.