DLookup Syntax Help

[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

 

[Ken Snell]

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

DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[Dan]

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

.

 

[Tim Ferguson]

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??

Use DCount:

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


End If

Hope that helps


Tim F

 

[Guest]

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

 

[Dan]

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

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 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

.

 

[Guest]

Hey Dan;

I'm just curious...

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

Correct?

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

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 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

.

 

[Paul Johnson]

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

 

[Ken Snell]

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

.

 

[Dan]

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

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
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

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

.

.

 

[Dan]

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

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
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

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

.

.

 

[Dan]

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


.

.

 

[Dan]

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

 

[Ken Snell]

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>

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


.

.

 

[Tim Ferguson]

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

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

Tim F