DLOOKUP in Subforms

[kesam]

Hi,

Have searched in vain for most of the day for a solution.

I have created a form which includes an unbound textbox which has a dlookup as the control source. This works fine when viewed "stand alone" however when I use it as a subform the DLOOKUP fields return an error.

All of the other data is displayed correctly so I think the master and child fields are linked ok. It may also be pertinent to mention the subform is set up as continuous forms.

I just can't get my head around why it works by itself but not when in the main form.

Any and all pointers will be much appreciated.

Thanks in advance.

BK

 

[Rich]

post the syntax and relevant details

 

[kesam]

Rich,

This is what I have in the control source which brings up the relevant supplier names for ease of use(asoppsed to just the supplier ID.

=DLookUp("[Company]","TBL_Suppliers","[Supplier_ID1] = Forms![FRM_UPDATEVAL_sub]!SUPPCODE")

The master and child are linked via the reference number which is the primary key in the original spec.

The db is a bit big to post but if the above isn't any help I'll try to trim it down and post the relevant stuff.

Cheers

BK

 

[alastair69]

=DLookUp("[Company]","TBL_Suppliers","[Supplier_ID1] = Forms![FRM_UPDATEVAL_sub]!SUPPCODE")

= Forms![Main Form Name Here]![FRM_UPDATEVAL_sub]!SUPPCODE")

Try it and see if it work OK

 

[Rich]

=DLookUp("[Company]","TBL_Suppliers","[Supplier_ID1] = " & Me! [SUPPCODE])
although having said that there are easier ways to display the company name, in fact I wouldn't use DLookup at all

 

[kesam]

it returned an error from that line of code Rich.

Anyway though I thought all along there was probably an easier way but couldn't think of one. I tried basing the subform on a query which brought through the correct detail but as you need to input detail that didn't work.

I do like to figure things out for myself most of the time so maybe don't do it the best way but if you can give me a pointer towards what I should be looking at doing I'd be grateful.

BK