Dlookup Question

[Guest]

I'm trying to use Dlookup to prevent a new record from a form being saved to
a table if it is already in the table. My problem is this - the records
contain a name and an award. The award is created by concatenating 2
textboxes with a blank space in between. In the "criteria" part of the
Dlookup statement, I'm trying to check the table for the name and the
concatenated award, but Access doesn't like my concatenated award. I get a
"Expected list separator or)" error. Here is the offending statement:

x = DLookup("[txtFullName]", "[tblAwards]", "([tblAwards].[txtAward] =
(Forms!frmAwards3.txtAward & " " & Forms!frmAwards3.txtAward2)) And
([tblAwards].[txtFullName] = Forms!frmAwards3.txtStuName)")

The error is right where I stick in the blank space - " ". Is there any way
around this or do I just not understand Dlookup?

Nick

 

[Guest]

I had already tried that but got the following error: "The expression you
entered as a query parameter produced this error. The object doesn't contain
the Automation
object "txtAwards". Any suggestions???

There were some syntax problems with your DCount. Also, just to make it more
readable:
txtAwards = Forms!frmAwards3.txtAward & " " & Forms!frmAwards3.txtAward2

x = DLookup("[txtFullName]", "[tblAwards]", "[tblAwards].[txtAward] =
txtAwards And [tblAwards].[txtFullName] = Forms!frmAwards3.txtStuName")

I'm trying to use Dlookup to prevent a new record from a form being saved to
a table if it is already in the table. My problem is this - the records
contain a name and an award. The award is created by concatenating 2
textboxes with a blank space in between. In the "criteria" part of the
Dlookup statement, I'm trying to check the table for the name and the
concatenated award, but Access doesn't like my concatenated award. I get a
"Expected list separator or)" error. Here is the offending statement:

x = DLookup("[txtFullName]", "[tblAwards]", "([tblAwards].[txtAward] =
(Forms!frmAwards3.txtAward & " " & Forms!frmAwards3.txtAward2)) And
([tblAwards].[txtFullName] = Forms!frmAwards3.txtStuName)")

The error is right where I stick in the blank space - " ". Is there any way
around this or do I just not understand Dlookup?

Nick

 

[Guest]

My bad, was going to fast. Still don't have the ability to test this, but I
saw i was putting in literals where I needed values. I aslo always have
troulbe getting the quote marks right, just play with it, it is basically good
x = DLookup("[txtFullName]", "tblAwards", "[tblAwards].[txtAward] = """ &
txtAwards & """ And "[tblAwards].[txtFullName] = """ &
Forms!frmAwards3.txtStuName & """")

I had already tried that but got the following error: "The expression you
entered as a query parameter produced this error. The object doesn't contain
the Automation
object "txtAwards". Any suggestions???

There were some syntax problems with your DCount. Also, just to make it more
readable:
txtAwards = Forms!frmAwards3.txtAward & " " & Forms!frmAwards3.txtAward2

x = DLookup("[txtFullName]", "[tblAwards]", "[tblAwards].[txtAward] =
txtAwards And [tblAwards].[txtFullName] = Forms!frmAwards3.txtStuName")

I'm trying to use Dlookup to prevent a new record from a form being saved to
a table if it is already in the table. My problem is this - the records
contain a name and an award. The award is created by concatenating 2
textboxes with a blank space in between. In the "criteria" part of the
Dlookup statement, I'm trying to check the table for the name and the
concatenated award, but Access doesn't like my concatenated award. I get a
"Expected list separator or)" error. Here is the offending statement:

x = DLookup("[txtFullName]", "[tblAwards]", "([tblAwards].[txtAward] =
(Forms!frmAwards3.txtAward & " " & Forms!frmAwards3.txtAward2)) And
([tblAwards].[txtFullName] = Forms!frmAwards3.txtStuName)")

The error is right where I stick in the blank space - " ". Is there any way
around this or do I just not understand Dlookup?

Nick