DLookup and Run-Time error 2001

[Dan]

I am pulling my hair out on this one. I have tried this statement with
variable and without. I have moved "" all over based on criteria I have
seen posted all over the web, but in the end I continue to get the same
error. "You canceled the previous operation". Please tell me if there is
something that you can see wrong with the code.

Dim FldNm, Box, var
Dim x As Integer
FldNm = "SL3Item"
Box = "SL3Item"
x = 1

Do While x <= 40

var = _
DLookup("[FldNm & x]", "[SL3NSNDetail]", _
"[ID] = '" & Forms![IssueForm]![cboWpnType1] & "'")

x = x + 1
Me.Controls(Box & x).Value = var
Loop

Thanks for the help,

 

[Allen Browne]

The message indicates that one of the arguments you are supplying to
DLookup() is not formed correctly.

In your example, you need to concatenate the x into the string, i.e.:
DLookup("[FldNm" & x & "]", ...

If ID is a number field, drop the extra quotes in the 3rd argument:
"[ID] = " & Forms![IssueForm]![cboWpnType1]
and test that the combo is not Null, e.g.:
"[ID] = " & Nz(Forms![IssueForm]![cboWpnType1], 0)

 

[Dan]

Thanks for the help,

I tried the change to FldNm, but that created a problem where there wasn't
one previously. I had already verified that the statement had concatenated
the way it should.

I also tried dropping the 3rd quote as you suggested, but that gives me a
new error, 3075 (missing operator). I have been here before also. I just
can't figure how to get past this. BTW, ID is not a number field because it
has alpha-numeric characters.

Thanks again,

The message indicates that one of the arguments you are supplying to
DLookup() is not formed correctly.

In your example, you need to concatenate the x into the string, i.e.:
DLookup("[FldNm" & x & "]", ...

If ID is a number field, drop the extra quotes in the 3rd argument:
"[ID] = " & Forms![IssueForm]![cboWpnType1]
and test that the combo is not Null, e.g.:
"[ID] = " & Nz(Forms![IssueForm]![cboWpnType1], 0)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am pulling my hair out on this one. I have tried this statement with
variable and without. I have moved "" all over based on criteria I have
seen posted all over the web, but in the end I continue to get the same
error. "You canceled the previous operation". Please tell me if there is
something that you can see wrong with the code.

Dim FldNm, Box, var
Dim x As Integer
FldNm = "SL3Item"
Box = "SL3Item"
x = 1

Do While x <= 40

var = _
DLookup("[FldNm & x]", "[SL3NSNDetail]", _
"[ID] = '" & Forms![IssueForm]![cboWpnType1] & "'")

x = x + 1
Me.Controls(Box & x).Value = var
Loop

Thanks for the help

 

[Dan]

The total error is
Run-Time errr '3075'
Syntax error (missing operator) in query expression '[ID] = 05538C'.

Thanks for the help,

I tried the change to FldNm, but that created a problem where there wasn't
one previously. I had already verified that the statement had concatenated
the way it should.

I also tried dropping the 3rd quote as you suggested, but that gives me a
new error, 3075 (missing operator). I have been here before also. I just
can't figure how to get past this. BTW, ID is not a number field because it
has alpha-numeric characters.

Thanks again,

The message indicates that one of the arguments you are supplying to
DLookup() is not formed correctly.

In your example, you need to concatenate the x into the string, i.e.:
DLookup("[FldNm" & x & "]", ...

If ID is a number field, drop the extra quotes in the 3rd argument:
"[ID] = " & Forms![IssueForm]![cboWpnType1]
and test that the combo is not Null, e.g.:
"[ID] = " & Nz(Forms![IssueForm]![cboWpnType1], 0)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am pulling my hair out on this one. I have tried this statement with
variable and without. I have moved "" all over based on criteria I have
seen posted all over the web, but in the end I continue to get the same
error. "You canceled the previous operation". Please tell me if

there

is

something that you can see wrong with the code.

Dim FldNm, Box, var
Dim x As Integer
FldNm = "SL3Item"
Box = "SL3Item"
x = 1

Do While x <= 40

var = _
DLookup("[FldNm & x]", "[SL3NSNDetail]", _
"[ID] = '" & Forms![IssueForm]![cboWpnType1] & "'")

x = x + 1
Me.Controls(Box & x).Value = var
Loop

Thanks for the help

 

[Allen Browne]

The quotes are needed as the delimiter if the field is a Text field.
Pop them back into the 3rd argument.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

The total error is
Run-Time errr '3075'
Syntax error (missing operator) in query expression '[ID] = 05538C'.

Thanks for the help,

I tried the change to FldNm, but that created a problem where there
wasn't
one previously. I had already verified that the statement had concatenated
the way it should.

I also tried dropping the 3rd quote as you suggested, but that gives me a
new error, 3075 (missing operator). I have been here before also. I
just
can't figure how to get past this. BTW, ID is not a number field because it
has alpha-numeric characters.

Thanks again,

The message indicates that one of the arguments you are supplying to
DLookup() is not formed correctly.

In your example, you need to concatenate the x into the string, i.e.:
DLookup("[FldNm" & x & "]", ...

If ID is a number field, drop the extra quotes in the 3rd argument:
"[ID] = " & Forms![IssueForm]![cboWpnType1]
and test that the combo is not Null, e.g.:
"[ID] = " & Nz(Forms![IssueForm]![cboWpnType1], 0)


I am pulling my hair out on this one. I have tried this statement
with
variable and without. I have moved "" all over based on criteria I have
seen posted all over the web, but in the end I continue to get the same
error. "You canceled the previous operation". Please tell me if

there

is
something that you can see wrong with the code.

Dim FldNm, Box, var
Dim x As Integer
FldNm = "SL3Item"
Box = "SL3Item"
x = 1

Do While x <= 40

var = _
DLookup("[FldNm & x]", "[SL3NSNDetail]", _
"[ID] = '" & Forms![IssueForm]![cboWpnType1] & "'")

x = x + 1
Me.Controls(Box & x).Value = var
Loop

Thanks for the help

 

[Dan]

Ok, Now I am back to my original error.
Any other ideas?

The quotes are needed as the delimiter if the field is a Text field.
Pop them back into the 3rd argument.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

The total error is
Run-Time errr '3075'
Syntax error (missing operator) in query expression '[ID] = 05538C'.

Thanks for the help,

I tried the change to FldNm, but that created a problem where there
wasn't
one previously. I had already verified that the statement had concatenated
the way it should.

I also tried dropping the 3rd quote as you suggested, but that gives me a
new error, 3075 (missing operator). I have been here before also. I
just
can't figure how to get past this. BTW, ID is not a number field

because
it

has alpha-numeric characters.

Thanks again,

The message indicates that one of the arguments you are supplying to
DLookup() is not formed correctly.

In your example, you need to concatenate the x into the string, i.e.:
DLookup("[FldNm" & x & "]", ...

If ID is a number field, drop the extra quotes in the 3rd argument:
"[ID] = " & Forms![IssueForm]![cboWpnType1]
and test that the combo is not Null, e.g.:
"[ID] = " & Nz(Forms![IssueForm]![cboWpnType1], 0)


I am pulling my hair out on this one. I have tried this statement
with
variable and without. I have moved "" all over based on criteria I have
seen posted all over the web, but in the end I continue to get the same
error. "You canceled the previous operation". Please tell me if there
is
something that you can see wrong with the code.

Dim FldNm, Box, var
Dim x As Integer
FldNm = "SL3Item"
Box = "SL3Item"
x = 1

Do While x <= 40

var = _
DLookup("[FldNm & x]", "[SL3NSNDetail]", _
"[ID] = '" & Forms![IssueForm]![cboWpnType1] & "'")

x = x + 1
Me.Controls(Box & x).Value = var
Loop

Thanks for the help

 

[Allen Browne]

To find out what is wrong, create a string for each argument, so you can
test whether it is correct or not:
Dim FldNm As String
Dim strWhere As String
Dim varResult As Variant
Dim x As Integer

strWhere = "[ID] = '" & Forms![IssueForm]![cboWpnType1] & "'"

For x = 1 to 40
FldNm = "SL3Item" & Str(x)
varResult = DLookup(FldNm, "SL3NSNDetail", strWhere)
Me(FldNm).Value = varResult
Next

If that still fails, you can now see the values of the strings, and figure
out what is wrong, e.g.:
Debug.Print FldNm

From there, you can create a query that uses:
SELECT a FROM b WHERE c;
where a is the value of FldNm, b is "SL3NSNDetail", and c is the value of
strWhere. You can then see in the query design why this fails.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Ok, Now I am back to my original error.
Any other ideas?

The quotes are needed as the delimiter if the field is a Text field.
Pop them back into the 3rd argument.

The total error is
Run-Time errr '3075'
Syntax error (missing operator) in query expression '[ID] = 05538C'.

Thanks for the help,

I tried the change to FldNm, but that created a problem where there
wasn't
one previously. I had already verified that the statement had
concatenated
the way it should.

I also tried dropping the 3rd quote as you suggested, but that gives
me a
new error, 3075 (missing operator). I have been here before also. I
just
can't figure how to get past this. BTW, ID is not a number field because
it
has alpha-numeric characters.

Thanks again,

The message indicates that one of the arguments you are supplying to
DLookup() is not formed correctly.

In your example, you need to concatenate the x into the string,
i.e.:
DLookup("[FldNm" & x & "]", ...

If ID is a number field, drop the extra quotes in the 3rd argument:
"[ID] = " & Forms![IssueForm]![cboWpnType1]
and test that the combo is not Null, e.g.:
"[ID] = " & Nz(Forms![IssueForm]![cboWpnType1], 0)


I am pulling my hair out on this one. I have tried this statement
with
variable and without. I have moved "" all over based on criteria
I
have
seen posted all over the web, but in the end I continue to get the
same
error. "You canceled the previous operation". Please tell me if
there
is
something that you can see wrong with the code.

Dim FldNm, Box, var
Dim x As Integer
FldNm = "SL3Item"
Box = "SL3Item"
x = 1

Do While x <= 40

var = _
DLookup("[FldNm & x]", "[SL3NSNDetail]", _
"[ID] = '" & Forms![IssueForm]![cboWpnType1] & "'")

x = x + 1
Me.Controls(Box & x).Value = var
Loop

Thanks for the help