DLookup #Error Main Form with Sub Form

[nogero]

I have a main form named main and a subform named sub. In sub I have a text box named txtresult and I am using a DLookup to show query results and this work fine when just running sub. However, when I load main the txtresult text box shows #Error. I know this has to do with my DLookup syntax but I am unclear on what to use.

My current DLookup syntax is =DLookUp("pscodename_vc","qrypsname")

this is set in the control source for the text box. Any help would be appreciated.

 

[PHV]

What is the SQL code of qrypsname ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[nogero]

SELECT psplanmaster.pscodename_vc
FROM data INNER JOIN (psplanmaster INNER JOIN cpsplan ON psplanmaster.pspid = cpsplan.pspid) ON data.dataid = cpsplan.dataid
WHERE (((psplanmaster.pspid)=[Forms]!_{![txtpspid]))
GROUP BY psplanmaster.pscodename_vc;}

 

[PHV]

You may try this:
=DLookUp("pscodename_vc","psplanmaster","pspid=" & [txtpspid]"

if pspid is not defined as numeric you have to surround the value with single quotes.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[nogero]

I discovered the answer to my problem. I changed my query to include the full path to the sub-form. Now when you open main it shows the query in the text box on the sub form named sub.


SELECT psplanmaster.pscodename_vc
FROM data INNER JOIN (psplanmaster INNER JOIN cpsplan ON psplanmaster.pspid = cpsplan.pspid) ON data.dataid = cpsplan.dataid
WHERE (((psplanmaster.pspid)=[Forms]![main]!_{![txtpspid]))
GROUP BY psplanmaster.pscodename_vc;}