DLookup Syntax Help

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

Hi:

What would be the syntax for a Dlookup to find a name in a
table; if it finds it then open a form...this is not
working??

If (DLookup("[UserID]", "[pswc]", _
"[UserID]='" & [UserID] & "'")) Then

DoCmd.OpenForm stDocName, , , stLinkCriteria

Thanks,

Dan
 
If IsNull(DLookup("[UserID]", "[pswc]", _
"[UserID]='" & [UserID] & "'")) = False Then

DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Hi Ken:

Thank you but is still giving me this message:

Application can't find the field '|' referred to in your
expression

Thaks,

Dan
-----Original Message-----
If IsNull(DLookup("[UserID]", "[pswc]", _
"[UserID]='" & [UserID] & "'")) = False Then

DoCmd.OpenForm stDocName, , , stLinkCriteria


--

Ken Snell
<MS ACCESS MVP>

Hi:

What would be the syntax for a Dlookup to find a name in a
table; if it finds it then open a form...this is not
working??

If (DLookup("[UserID]", "[pswc]", _
"[UserID]='" & [UserID] & "'")) Then

DoCmd.OpenForm stDocName, , , stLinkCriteria

Thanks,

Dan
Click to expand...


.
Click to expand...
 
What would be the syntax for a Dlookup to find a name in a
table; if it finds it then open a form...this is not
working??
Click to expand...

Use DCount:

If DCount("*", "ATable", "SName = ""Smith""") >0 Then
DoCmd.OpenForm "strMyForm", etc, etc


End If

Hope that helps


Tim F
 
Hey Dan;

I'm sure you are already aware of this, but when refering to a VB variable, you don't need "[" and "]"... I'm not sure if this helps or not... but I had the same problem till I removed these brackets.

If NOT (DLookup("[UserID]", "[pswc]", "[UserID] = '" & User_ID & "'")) = "" Then

DoCmd.OpenForm stDocName, , , stLinkCriteria

End If

Sincerely;
~Lethoric De'clree
 
Hi Lethoric:

Oooh, thanks but still the same...I am going NUTS soon!

Dan
-----Original Message-----
Hey Dan;

I'm sure you are already aware of this, but when refering
Click to expand...
to a VB variable, you don't need "[" and "]"... I'm not
sure if this helps or not... but I had the same problem
till I removed these brackets.
If NOT (DLookup("[UserID]", "[pswc]", "[UserID] = '" & User_ID & "'")) = "" Then

DoCmd.OpenForm stDocName, , , stLinkCriteria

End If

Sincerely;
~Lethoric De'clree

Dan said:
Hi:

What would be the syntax for a Dlookup to find a name in a
table; if it finds it then open a form...this is not
working??

If (DLookup("[UserID]", "[pswc]", _
"[UserID]='" & [UserID] & "'")) Then

DoCmd.OpenForm stDocName, , , stLinkCriteria

Thanks,

Dan
Click to expand...
.
Click to expand...
 
Hey Dan;

I'm just curious...

[UserID] is the field your referencing
[pswc] is the Table your referencing

Correct?

Sincerely;
~Lethoric De'clree


Dan said:
Hi Lethoric:

Oooh, thanks but still the same...I am going NUTS soon!

Dan
-----Original Message-----
Hey Dan;

I'm sure you are already aware of this, but when refering
Click to expand...
to a VB variable, you don't need "[" and "]"... I'm not
sure if this helps or not... but I had the same problem
till I removed these brackets.
If NOT (DLookup("[UserID]", "[pswc]", "[UserID] = '" & User_ID & "'")) = "" Then

DoCmd.OpenForm stDocName, , , stLinkCriteria

End If

Sincerely;
~Lethoric De'clree

Dan said:
Hi:

What would be the syntax for a Dlookup to find a name in a
table; if it finds it then open a form...this is not
working??

If (DLookup("[UserID]", "[pswc]", _
"[UserID]='" & [UserID] & "'")) Then

DoCmd.OpenForm stDocName, , , stLinkCriteria

Thanks,

Dan
Click to expand...
.
Click to expand...
Click to expand...
 
Dan,

The syntax (your original question) is

DLookup(Expression, Domain, Criteria) where all parameters are expressed as
strings.

For example:
DLookup("FieldNameForTheValueYouAreLookingUp", "TableOrQueryName",
"FieldName = '" & SomeVariable & "'")

The criteria string does not have to use the same field as the Expression
string.

I was thinking along the lines of one of the other replies that suggested
there was a problem with the quotes around what I would guess to be a
numeric field (UserID). However, this gives a "data type mismatch" error,
not a "can't find field" error.

I can generate the same error message you get when I use a DLookup parameter
for a field that doesn't exist in the specified table, usually a
fat-fingered typo to blame. Are you sure the "UserID" field in the "pswc"
table is spelled correctly? It seems to me that if it is, Ken's code should
work (unless there's a data type mismatch, of course).

Another thought: You could do as Tim suggested, and try:
If DCount(1, "pswc", "UserID='" & UserID & "'")>0 Then
etc.
End If

Instead of loading the DCount with * (you don't need to return any field
information), give the expression a dummy value of 1, simply returning the
number of matches for the given criteria. Runs faster.

For what it's worth,
Paul Johnson
 
Assuming that UserID is a text-formatted field in your table pswc, try this:

If IsNull(DLookup("UserID", "pswc", _
"[UserID]='" & [UserID] & "'")) = False Then
DoCmd.OpenForm stDocName, , , stLinkCriteria


If it's a numeric-formatted field:

If IsNull(DLookup("UserID", "pswc", _
"[UserID]=" & [UserID])) = False Then
DoCmd.OpenForm stDocName, , , stLinkCriteria


I probably would use the DCount function in this situation though, instead
of DLookup, as you know a specific value that you want to test for (again
assuming text-formatted field):

If DCount("*", "pswc", "[UserID]='" & [UserID] & "'")) > 0 Then
DoCmd.OpenForm stDocName, , , stLinkCriteria

--

Ken Snell
<MS ACCESS MVP>



Dan said:
Hi Ken:

Thank you but is still giving me this message:

Application can't find the field '|' referred to in your
expression

Thaks,

Dan
-----Original Message-----
If IsNull(DLookup("[UserID]", "[pswc]", _
"[UserID]='" & [UserID] & "'")) = False Then

DoCmd.OpenForm stDocName, , , stLinkCriteria


--

Ken Snell
<MS ACCESS MVP>

Hi:

What would be the syntax for a Dlookup to find a name in a
table; if it finds it then open a form...this is not
working??

If (DLookup("[UserID]", "[pswc]", _
"[UserID]='" & [UserID] & "'")) Then

DoCmd.OpenForm stDocName, , , stLinkCriteria

Thanks,

Dan
Click to expand...


.
Click to expand...
Click to expand...
 
CORRECT...

Thanks,

Dan
-----Original Message-----
Hey Dan;

I'm just curious...

[UserID] is the field your referencing
[pswc] is the Table your referencing

Correct?

Sincerely;
~Lethoric De'clree


Dan said:
Hi Lethoric:

Oooh, thanks but still the same...I am going NUTS soon!

Dan
-----Original Message-----
Hey Dan;

I'm sure you are already aware of this, but when
Click to expand...
refering
to a VB variable, you don't need "[" and "]"... I'm not
sure if this helps or not... but I had the same problem
till I removed these brackets.
If NOT (DLookup("[UserID]", "[pswc]", "[UserID] = '" & User_ID & "'")) = "" Then

DoCmd.OpenForm stDocName, , , stLinkCriteria

End If

Sincerely;
~Lethoric De'clree

:

Hi:

What would be the syntax for a Dlookup to find a
Click to expand...
name
in a
table; if it finds it then open a form...this is not
working??

If (DLookup("[UserID]", "[pswc]", _
"[UserID]='" & [UserID] & "'")) Then

DoCmd.OpenForm stDocName, , , stLinkCriteria

Thanks,

Dan

.
Click to expand...
Click to expand...
.
Click to expand...
 
CORRECT..

Dan
-----Original Message-----
Hey Dan;

I'm just curious...

[UserID] is the field your referencing
[pswc] is the Table your referencing

Correct?

Sincerely;
~Lethoric De'clree


Dan said:
Hi Lethoric:

Oooh, thanks but still the same...I am going NUTS soon!

Dan
-----Original Message-----
Hey Dan;

I'm sure you are already aware of this, but when
Click to expand...
refering
to a VB variable, you don't need "[" and "]"... I'm not
sure if this helps or not... but I had the same problem
till I removed these brackets.
If NOT (DLookup("[UserID]", "[pswc]", "[UserID] = '" & User_ID & "'")) = "" Then

DoCmd.OpenForm stDocName, , , stLinkCriteria

End If

Sincerely;
~Lethoric De'clree

:

Hi:

What would be the syntax for a Dlookup to find a
Click to expand...
name
in a
table; if it finds it then open a form...this is not
working??

If (DLookup("[UserID]", "[pswc]", _
"[UserID]='" & [UserID] & "'")) Then

DoCmd.OpenForm stDocName, , , stLinkCriteria

Thanks,

Dan

.
Click to expand...
Click to expand...
.
Click to expand...
 
Hi Ken:

It is just a simple table with a userid field (text); if
dlookup finds this name then open the data entry form...

everything is text but , I have no clue, why I get the
message:field '|' not found...

thanks,

Dan
-----Original Message-----
Assuming that UserID is a text-formatted field in your table pswc, try this:

If IsNull(DLookup("UserID", "pswc", _
"[UserID]='" & [UserID] & "'")) = False Then
DoCmd.OpenForm stDocName, , , stLinkCriteria


If it's a numeric-formatted field:

If IsNull(DLookup("UserID", "pswc", _
"[UserID]=" & [UserID])) = False Then
DoCmd.OpenForm stDocName, , , stLinkCriteria


I probably would use the DCount function in this situation though, instead
of DLookup, as you know a specific value that you want to test for (again
assuming text-formatted field):

If DCount("*", "pswc", "[UserID]='" & [UserID] & "'")) > 0 Then
DoCmd.OpenForm stDocName, , , stLinkCriteria

--

Ken Snell
<MS ACCESS MVP>



Hi Ken:

Thank you but is still giving me this message:

Application can't find the field '|' referred to in your
expression

Thaks,

Dan
-----Original Message-----
If IsNull(DLookup("[UserID]", "[pswc]", _
"[UserID]='" & [UserID] & "'")) = False Then

DoCmd.OpenForm stDocName, , , stLinkCriteria


--

Ken Snell
<MS ACCESS MVP>

Hi:

What would be the syntax for a Dlookup to find a name in a
table; if it finds it then open a form...this is not
working??

If (DLookup("[UserID]", "[pswc]", _
"[UserID]='" & [UserID] & "'")) Then

DoCmd.OpenForm stDocName, , , stLinkCriteria

Thanks,

Dan


.
Click to expand...
Click to expand...


.
Click to expand...
 
Hello evryone and a big Thank!

I will try one more time what Paul Johnson suggested...if
not, I wil put a hard coded password for that button...

Thanks again,

Dan
 
Are you sure that you have a control or field named UserID on the form that
is running this code? Post the SQL of the form's RecordSource query.
--

Ken Snell
<MS ACCESS MVP>


Dan said:
Hi Ken:

It is just a simple table with a userid field (text); if
dlookup finds this name then open the data entry form...

everything is text but , I have no clue, why I get the
message:field '|' not found...

thanks,

Dan
-----Original Message-----
Assuming that UserID is a text-formatted field in your table pswc, try this:

If IsNull(DLookup("UserID", "pswc", _
"[UserID]='" & [UserID] & "'")) = False Then
DoCmd.OpenForm stDocName, , , stLinkCriteria


If it's a numeric-formatted field:

If IsNull(DLookup("UserID", "pswc", _
"[UserID]=" & [UserID])) = False Then
DoCmd.OpenForm stDocName, , , stLinkCriteria


I probably would use the DCount function in this situation though, instead
of DLookup, as you know a specific value that you want to test for (again
assuming text-formatted field):

If DCount("*", "pswc", "[UserID]='" & [UserID] & "'")) > 0 Then
DoCmd.OpenForm stDocName, , , stLinkCriteria

--

Ken Snell
<MS ACCESS MVP>



Hi Ken:

Thank you but is still giving me this message:

Application can't find the field '|' referred to in your
expression

Thaks,

Dan
-----Original Message-----
If IsNull(DLookup("[UserID]", "[pswc]", _
"[UserID]='" & [UserID] & "'")) = False Then

DoCmd.OpenForm stDocName, , , stLinkCriteria


--

Ken Snell
<MS ACCESS MVP>

message
Hi:

What would be the syntax for a Dlookup to find a name
in a
table; if it finds it then open a form...this is not
working??

If (DLookup("[UserID]", "[pswc]", _
"[UserID]='" & [UserID] & "'")) Then

DoCmd.OpenForm stDocName, , , stLinkCriteria

Thanks,

Dan


.
Click to expand...


.
Click to expand...
Click to expand...
 
I will try one more time what Paul Johnson suggested...if
not, I wil put a hard coded password for that button...
Click to expand...

Password???? Who said anything about passwords? You were asking about
checking the existence of a value in a table.

Tim F
 
Back
Top