Error for using Dlookup function in Subform

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gogzilla - 09 Mar 2005 04:43 GMT

Hi All,

I found some error in Dlookup function like this below.

A=DLookup("AggType", "Tbl_Agg",
"[AggID]=[Forms]![Frm_Main_Design]![Child]![AggType]")

It work fine when i run in access 2002 but i got this error when i run with
access 2003. and i try to change the word '[child]' to name of subform
'[Form_Sub_Design]!

it shows the error like this following.

runtime error '2001'
you canceled the previous operation

How can i solve this problem?

Thanks in advance
Gogzilla

Reply to this Message

Allen Browne - 09 Mar 2005 04:59 GMT

Yes, A2003 does not like the reference that way.

Try:
A=DLookup("AggType", "Tbl_Agg", "[AggID] = " &
[Forms]![Frm_Main_Design]![Form_Sub_Design].Form![AggType])

For an explanation of the ".Form" bit, see:
http://allenbrowne.com/casu-04.html

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Hi All,
>
[quoted text clipped - 17 lines]
> Thanks in advance
> Gogzilla

Reply to this Message

Gogzilla - 09 Mar 2005 06:07 GMT

Thanks for your reply. I try to use your syntax but it still shows the error.
This is my controlsourc
=DLookUp("AggType","Tbl_Agg","[AggID]=[Forms]![Frm_Main_Design]![Frm_Sub_Design].Form![AggType]")

and
=DLookUp("AggType","Tbl_Agg","[AggID]=" &
[Forms]![Frm_Main_Design]![Frm_Sub_Design].Form![AggType]")

it shows the error in its text "#Error"
Could you give me some reasons for this situation?Why?

Thanks

Gogzilla

> Yes, A2003 does not like the reference that way.
>
[quoted text clipped - 26 lines]
> > Thanks in advance
> > Gogzilla

Reply to this Message

Allen Browne - 09 Mar 2005 06:47 GMT

You do need to concatenate the value from the subform into the 3rd argument.

Presumably:
- the form is open, and
- there is a value in the AggType control in the subform.

If you open the table in design view, what type of field is AggID?
If it is Text (not Number), you need extra quotes:
=DLookUp("AggType","Tbl_Agg","[AggID] = """ &
[Forms]![Frm_Main_Design]![Frm_Sub_Design].Form![AggType] & """")

What type of field is your AggType text box bound to? Is it the same type as
the AggID you are comparing it to?

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Thanks for your reply. I try to use your syntax but it still shows the
> error.
[quoted text clipped - 42 lines]
>> > Thanks in advance
>> > Gogzilla

Reply to this Message

Gogzilla - 09 Mar 2005 07:23 GMT

Hi Browne,

I already try to use your syntax and check type of AGGID and AggType are
'text'

I still found error like this

Run-time error '2465'
Microsoft Office Access cna't find the field 'Frm_Sub_Design'referred to in
your expression

'Frm_Sub_Design' is the name of subform

Thanks
Gogzilla

> You do need to concatenate the value from the subform into the 3rd argument.
>
[quoted text clipped - 56 lines]
> >> > Thanks in advance
> >> > Gogzilla

Reply to this Message

Allen Browne - 09 Mar 2005 08:06 GMT

Open the main form in design view.
Right-click the edge of the subform control, and choose Properties.
On the Other tab of the Properties box, what is the Name of the control?

Its Name can be different from the name of the form that gets loaded into
the control (its SourceObject).

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Hi Browne,
>
[quoted text clipped - 77 lines]
>> >> > Thanks in advance
>> >> > Gogzilla

Reply to this Message

Gogzilla - 09 Mar 2005 11:25 GMT

Thank you very much for your advise. the actual name of subform is Child as
the first name in syntax. it can work now..

Gogzilla

> Hi All,
>
[quoted text clipped - 18 lines]
>
>

Reply to this Message