Syntax error using DLookup function

[Amir]

Hi!
How can I use the DLookup function to return
all the values of the ProductKey field in the
table tblProduct, where the product key is equal
to the NewData parameter?
I need to do so in way that I can use variables
(All string typed), but I am having problem with
all the quotation marks.. I don't know how much to
put in each place.

The variables are:
strTriggeringValue = "" & NewData & ""
strTableName = "tblProducts"
strFieldName = "ProductKey"

I've tried (In the same line, of course):

MsgBox DLookup("""[" & strFieldName & "]""",
"" & strTableName & "",
""[" & strFieldName & "] = " & strTriggeringValue & """"), , "title"

but it produces a syntax error. How can I do it correctly?

Amir.

 

[Dirk Goldgar]

Hi!
How can I use the DLookup function to return
all the values of the ProductKey field in the
table tblProduct, where the product key is equal
to the NewData parameter?
I need to do so in way that I can use variables
(All string typed), but I am having problem with
all the quotation marks.. I don't know how much to
put in each place.

The variables are:
strTriggeringValue = "" & NewData & ""
strTableName = "tblProducts"
strFieldName = "ProductKey"

I've tried (In the same line, of course):

MsgBox DLookup("""[" & strFieldName & "]""",
"" & strTableName & "",
""[" & strFieldName & "] = " & strTriggeringValue & """"), , "title"

but it produces a syntax error. How can I do it correctly?

Amir.

It's not clear what you're after, Amir. Your syntax problems will be
solved, I think, with this variation on your code:

strTriggeringValue = NewData & ""
strTableName = "tblProducts"
strFieldName = "ProductKey"

MsgBox _
DLookup(strFieldName, strTableName, _
"[" & strFieldName & "] = """ & strTriggeringValue & """"), _
, "title"

But that is not going to return "all the values of the ProductKey field",
because DLookup can only return one value in any call to it. Further, your
DLookup is only going to return a piece of information you already have --
the product key. You're asking for the ProductKey value from a record where
the ProductKey = a given value, so naturally you're going to get back either
that given value, or Null if no such record exists.

What are you really trying to do?

 

[Dirk Goldgar]

I'm trying to tell if the user added a record with this product key to the
table, or not.

Are you sure it's

and not strTriggeringValue = "" & NewData & ""
?

Neither version does anything except convert NewData to a zero-length string
if it happens to be Null. If NewData can't be Null, you don't need either.
I left the trailing concatenation just in case. Neither version attaches
quotes to the value, if that's what you had in mind. My revised code takes
care of that later, with this:

"[" & strFieldName & "] = """ & strTriggeringValue & """"), _

If you want to know if a record with this product key exists in the table,
you might try something like this:

If IsNull(DLookup(strFieldName, strTableName, _
"[" & strFieldName & "] = """ & strTriggeringValue & """")) _
Then
MsgBox "This is a new product code."
Else
MsgBox "This product code already exists."
End If

Your use of NewData makes me think maybe this code is part of a combo box's
NotInList procedure, but if that's the case, it doesn't really make sense to
me to be testing it, since the NotInList event won't even fire if the item
entered isn't in the list. I only mention this in case you are working
under a misunderstanding.

 

[Amir]

I'm trying to tell if the user added a record with this product key to the
table, or not.

Are you sure it's

strTriggeringValue = NewData & ""

and not strTriggeringValue = "" & NewData & ""
?

Hi!
How can I use the DLookup function to return
all the values of the ProductKey field in the
table tblProduct, where the product key is equal
to the NewData parameter?
I need to do so in way that I can use variables
(All string typed), but I am having problem with
all the quotation marks.. I don't know how much to
put in each place.

The variables are:
strTriggeringValue = "" & NewData & ""
strTableName = "tblProducts"
strFieldName = "ProductKey"

I've tried (In the same line, of course):

MsgBox DLookup("""[" & strFieldName & "]""",
"" & strTableName & "",
""[" & strFieldName & "] = " & strTriggeringValue & """"), , "title"

but it produces a syntax error. How can I do it correctly?

Amir.

It's not clear what you're after, Amir. Your syntax problems will be
solved, I think, with this variation on your code:

strTriggeringValue = NewData & ""
strTableName = "tblProducts"
strFieldName = "ProductKey"

MsgBox _
DLookup(strFieldName, strTableName, _
"[" & strFieldName & "] = """ & strTriggeringValue & """"), _
, "title"

But that is not going to return "all the values of the ProductKey field",
because DLookup can only return one value in any call to it. Further, your
DLookup is only going to return a piece of information you already have --
the product key. You're asking for the ProductKey value from a record where
the ProductKey = a given value, so naturally you're going to get back either
that given value, or Null if no such record exists.

What are you really trying to do?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup, not by e-mail)

 

[Amir]

I'm trying to check if the user really added a product,
after I automatically opened a products form, because of a NotInList event.

I think it's working now, thank you very much!

I'm trying to tell if the user added a record with this product key to the
table, or not.

Are you sure it's

and not strTriggeringValue = "" & NewData & ""
?

Neither version does anything except convert NewData to a zero-length string
if it happens to be Null. If NewData can't be Null, you don't need either.
I left the trailing concatenation just in case. Neither version attaches
quotes to the value, if that's what you had in mind. My revised code takes
care of that later, with this:

"[" & strFieldName & "] = """ & strTriggeringValue & """"), _

If you want to know if a record with this product key exists in the table,
you might try something like this:

If IsNull(DLookup(strFieldName, strTableName, _
"[" & strFieldName & "] = """ & strTriggeringValue & """")) _
Then
MsgBox "This is a new product code."
Else
MsgBox "This product code already exists."
End If

Your use of NewData makes me think maybe this code is part of a combo box's
NotInList procedure, but if that's the case, it doesn't really make sense to
me to be testing it, since the NotInList event won't even fire if the item
entered isn't in the list. I only mention this in case you are working
under a misunderstanding.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup, not by e-mail)