ANY IDEAS? (DLOOKUP is killing me)

[Austin Gelbard]

I am trying to use the DLOOKUP function as part of a
SELECT function to build a list for use as a ROW SOURCE to
create a pull down menu for data entry in a table. (wow
that sounds like a lot).
I keep reading that i can create a string/text criteria
for my dlookup function from a value in a form by adding a
single quote before the field name and then again after

SELECT [ProductNames] FROM ClientProducts
WHERE ClientID=DLookup
("[ClientID]","ClientNames","[ClientName]= ' " &
ClientName & " ' ")
GROUP BY [bbbName];

If i substitute a sample name instead of " & ClientName
& " it works like a charm... i get a list of all the
client's products whose name is selected in ClientName.
However, when i use the syntax recommended (shown above)
all i get is a prompt asking for a parameter value.

Is this not possible in a table and only in a form?

 

[Albert D. Kallal]

Hum...I not clear why you using dlookup anyway?

SELECT [ProductNames] FROM ClientProducts
WHERE ClientID = (select cliendId from ClietNames where
ClientNames.ClientName = ???)
GROUP BY [bbbName];

It is not clear which clientname value you are looking for?

The above is the general format. Are you creating this sql in-line code, or
is it in a query?


Note that the sub-query will only work if ONE value is going to be
returned..you need to modify it slightly if more then one value can be
returned.

So, you can use a sub-query to pluck out any value..and they run VERY fast
in terms of performance.

 

[david epsom dot com dot au]

I'm not sure what you are looking for:

"[ClientName]= '" & [ClientProduct].[ClientName] & "' "
(that should not use a DLOOKUP)

or

"[ClientName]= '" & froms!myform!ClientName & "' "


Where is the Client Name supposed to come from? Is
the field/control name spelled correctly?