DLookup

  • Thread starter Thread starter Martin
  • Start date Start date
M

Martin

Hello,

I have tried to write a DLookup but it doesnt work. What I want to do is
find the username in a table of active users. If the username is not in the
list then i want to close the database. Here is the code:

If Username = DLookup("[Names]", "tblUsersList") Then
DoCmd.OpenForm "Server - HO", acNormal, "", "", , acNormal
Else
DoCmd.Quit
End If

The table is called "tblUserslist" and the only field in that table is
called "Names".

Can anyone help? I have tried several variations of this code but just cant
seem to get it to work.

Many thanks in advance.

Martin
 
Hi Martin,
Dlookup is not the correct statement to use for this scenario.
You have a user name and you are wanting Access to look in the list of user
names and see if this name is on the list.
DCount can do this for you.

Dim lngCount as Long
lngCount = DCount("*", "tblUsersList", "[Username] = """ & Me.Username &
"""")
If lngCount > 0 Then
DoCmd.OpenForm "Server - HO", acNormal, "", "", , acNormal
Else
DoCmd.Quit
End If


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
<picky>

There's nothing wrong with using DLookup for this scenario: you just need to
use it properly.

If IsNull(DLookup("[Names]", "tblUsersList", _
"[Usename] = """ & Me.Username & """")) = False Then
DoCmd.OpenForm "Server - HO", acNormal, "", "", , acNormal
Else
DoCmd.Quit
End If

It was once pointed out to me that DCount queries the entire table, wherease
DLookup will return once it finds the first match. Granted, I wouldn't
expect tblUsersList to be that large, so it's probably not a real
consideration here.

</picky>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Jeanette Cunningham said:
Hi Martin,
Dlookup is not the correct statement to use for this scenario.
You have a user name and you are wanting Access to look in the list of
user names and see if this name is on the list.
DCount can do this for you.

Dim lngCount as Long
lngCount = DCount("*", "tblUsersList", "[Username] = """ & Me.Username &
"""")
If lngCount > 0 Then
DoCmd.OpenForm "Server - HO", acNormal, "", "", , acNormal
Else
DoCmd.Quit
End If


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Martin said:
Hello,

I have tried to write a DLookup but it doesnt work. What I want to do is
find the username in a table of active users. If the username is not in
the
list then i want to close the database. Here is the code:

If Username = DLookup("[Names]", "tblUsersList") Then
DoCmd.OpenForm "Server - HO", acNormal, "", "", , acNormal
Else
DoCmd.Quit
End If

The table is called "tblUserslist" and the only field in that table is
called "Names".

Can anyone help? I have tried several variations of this code but just
cant
seem to get it to work.

Many thanks in advance.

Martin
Click to expand...
Click to expand...
 
<pickier>

If NOT IsNull(DLookup("[Names]", "tblUsersList", _
"[Usename] = """ & Me.Username & """")) Then
DoCmd.OpenForm "Server - HO", acNormal, "", "", , acNormal
Else
DoCmd.Quit
End If

</pickier>

Cheers Paolo

Douglas J. Steele said:
<picky>

There's nothing wrong with using DLookup for this scenario: you just need to
use it properly.

If IsNull(DLookup("[Names]", "tblUsersList", _
"[Usename] = """ & Me.Username & """")) = False Then
DoCmd.OpenForm "Server - HO", acNormal, "", "", , acNormal
Else
DoCmd.Quit
End If

It was once pointed out to me that DCount queries the entire table, wherease
DLookup will return once it finds the first match. Granted, I wouldn't
expect tblUsersList to be that large, so it's probably not a real
consideration here.

</picky>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Jeanette Cunningham said:
Hi Martin,
Dlookup is not the correct statement to use for this scenario.
You have a user name and you are wanting Access to look in the list of
user names and see if this name is on the list.
DCount can do this for you.

Dim lngCount as Long
lngCount = DCount("*", "tblUsersList", "[Username] = """ & Me.Username &
"""")
If lngCount > 0 Then
DoCmd.OpenForm "Server - HO", acNormal, "", "", , acNormal
Else
DoCmd.Quit
End If


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Martin said:
Hello,

I have tried to write a DLookup but it doesnt work. What I want to do is
find the username in a table of active users. If the username is not in
the
list then i want to close the database. Here is the code:

If Username = DLookup("[Names]", "tblUsersList") Then
DoCmd.OpenForm "Server - HO", acNormal, "", "", , acNormal
Else
DoCmd.Quit
End If

The table is called "tblUserslist" and the only field in that table is
called "Names".

Can anyone help? I have tried several variations of this code but just
cant
seem to get it to work.

Many thanks in advance.

Martin
Click to expand...
Click to expand...
Click to expand...
 
Force of habit.

Access 97 had a bug that prevented Access from closing properly if you
simply referred to boolean control without the = True or = False (see
http://support.microsoft.com/?id=190074 if you're interested). As a result,
I always compare the value to True or False.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Paolo said:
<pickier>

If NOT IsNull(DLookup("[Names]", "tblUsersList", _
"[Usename] = """ & Me.Username & """")) Then
DoCmd.OpenForm "Server - HO", acNormal, "", "", , acNormal
Else
DoCmd.Quit
End If

</pickier>

Cheers Paolo

Douglas J. Steele said:
<picky>

There's nothing wrong with using DLookup for this scenario: you just need
to
use it properly.

If IsNull(DLookup("[Names]", "tblUsersList", _
"[Usename] = """ & Me.Username & """")) = False Then
DoCmd.OpenForm "Server - HO", acNormal, "", "", , acNormal
Else
DoCmd.Quit
End If

It was once pointed out to me that DCount queries the entire table,
wherease
DLookup will return once it finds the first match. Granted, I wouldn't
expect tblUsersList to be that large, so it's probably not a real
consideration here.

</picky>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Jeanette Cunningham said:
Hi Martin,
Dlookup is not the correct statement to use for this scenario.
You have a user name and you are wanting Access to look in the list of
user names and see if this name is on the list.
DCount can do this for you.

Dim lngCount as Long
lngCount = DCount("*", "tblUsersList", "[Username] = """ & Me.Username
&
"""")
If lngCount > 0 Then
DoCmd.OpenForm "Server - HO", acNormal, "", "", , acNormal
Else
DoCmd.Quit
End If


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Hello,

I have tried to write a DLookup but it doesnt work. What I want to do
is
find the username in a table of active users. If the username is not
in
the
list then i want to close the database. Here is the code:

If Username = DLookup("[Names]", "tblUsersList") Then
DoCmd.OpenForm "Server - HO", acNormal, "", "", , acNormal
Else
DoCmd.Quit
End If

The table is called "tblUserslist" and the only field in that table is
called "Names".

Can anyone help? I have tried several variations of this code but
just
cant
seem to get it to work.

Many thanks in advance.

Martin
Click to expand...
Click to expand...
Click to expand...
 
No offence intended Douglas, I just wanted to be more picky than you:-)
Your answers are always good and well exposed.
BTW I never noticed this bug in Access 97 but I didn't use it a lot.

Paolo

Douglas J. Steele said:
Force of habit.

Access 97 had a bug that prevented Access from closing properly if you
simply referred to boolean control without the = True or = False (see
http://support.microsoft.com/?id=190074 if you're interested). As a result,
I always compare the value to True or False.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Paolo said:
<pickier>

If NOT IsNull(DLookup("[Names]", "tblUsersList", _
"[Usename] = """ & Me.Username & """")) Then
DoCmd.OpenForm "Server - HO", acNormal, "", "", , acNormal
Else
DoCmd.Quit
End If

</pickier>

Cheers Paolo

Douglas J. Steele said:
<picky>

There's nothing wrong with using DLookup for this scenario: you just need
to
use it properly.

If IsNull(DLookup("[Names]", "tblUsersList", _
"[Usename] = """ & Me.Username & """")) = False Then
DoCmd.OpenForm "Server - HO", acNormal, "", "", , acNormal
Else
DoCmd.Quit
End If

It was once pointed out to me that DCount queries the entire table,
wherease
DLookup will return once it finds the first match. Granted, I wouldn't
expect tblUsersList to be that large, so it's probably not a real
consideration here.

</picky>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Martin,
Dlookup is not the correct statement to use for this scenario.
You have a user name and you are wanting Access to look in the list of
user names and see if this name is on the list.
DCount can do this for you.

Dim lngCount as Long
lngCount = DCount("*", "tblUsersList", "[Username] = """ & Me.Username
&
"""")
If lngCount > 0 Then
DoCmd.OpenForm "Server - HO", acNormal, "", "", , acNormal
Else
DoCmd.Quit
End If


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Hello,

I have tried to write a DLookup but it doesnt work. What I want to do
is
find the username in a table of active users. If the username is not
in
the
list then i want to close the database. Here is the code:

If Username = DLookup("[Names]", "tblUsersList") Then
DoCmd.OpenForm "Server - HO", acNormal, "", "", , acNormal
Else
DoCmd.Quit
End If

The table is called "tblUserslist" and the only field in that table is
called "Names".

Can anyone help? I have tried several variations of this code but
just
cant
seem to get it to work.

Many thanks in advance.

Martin
Click to expand...
Click to expand...
Click to expand...
 
My understanding of DCount is that it is very quick when you are searching
on the primary key, as in ("*", "tblName", strCriteria).
One quick probe to check the value is the way I have seen it described.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Douglas J. Steele said:
<picky>

There's nothing wrong with using DLookup for this scenario: you just need
to use it properly.

If IsNull(DLookup("[Names]", "tblUsersList", _
"[Usename] = """ & Me.Username & """")) = False Then
DoCmd.OpenForm "Server - HO", acNormal, "", "", , acNormal
Else
DoCmd.Quit
End If

It was once pointed out to me that DCount queries the entire table,
wherease DLookup will return once it finds the first match. Granted, I
wouldn't expect tblUsersList to be that large, so it's probably not a real
consideration here.

</picky>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Jeanette Cunningham said:
Hi Martin,
Dlookup is not the correct statement to use for this scenario.
You have a user name and you are wanting Access to look in the list of
user names and see if this name is on the list.
DCount can do this for you.

Dim lngCount as Long
lngCount = DCount("*", "tblUsersList", "[Username] = """ & Me.Username &
"""")
If lngCount > 0 Then
DoCmd.OpenForm "Server - HO", acNormal, "", "", , acNormal
Else
DoCmd.Quit
End If


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Martin said:
Hello,

I have tried to write a DLookup but it doesnt work. What I want to do
is
find the username in a table of active users. If the username is not in
the
list then i want to close the database. Here is the code:

If Username = DLookup("[Names]", "tblUsersList") Then
DoCmd.OpenForm "Server - HO", acNormal, "", "", , acNormal
Else
DoCmd.Quit
End If

The table is called "tblUserslist" and the only field in that table is
called "Names".

Can anyone help? I have tried several variations of this code but just
cant
seem to get it to work.

Many thanks in advance.

Martin
Click to expand...
Click to expand...
Click to expand...
 
Back
Top