How to loop through a table

[Dougljm]

I am teaching myself Access and I am using Access 2007. My question is I am
using
a text box to enter and ID and when I push a button I want the macro to
search a table that has several records with ID and password info on it. The
problem is I can not get the function to loop through the table from first
record to last to see if the ID is there. If the tex box value ID is the
first record on the table it finds it and acts accordingly. If the text box
ID value is the third record on the table it does not find it. I do not
believe that the comannd goes beyond the first record read. I used DLookUp.
Could someone advise? Thanks. Also are there any good books out there that
has examples of Access coding?

 

[Ken Snell \(MVP\)]

Show us the DLookup function's expression that you tried. DLookup is the
likely best way to do what you seek.

 

[Dougljm]

Here is my DLookup expression

[txtbUser_ID]<>DLookUp("[User_ID]","Employees Extended")

txtbuser-ID is the text box on the form that is entered.

Thanks.

 

[Ken Snell \(MVP\)]

You need to provide information for the third argument of the DLookup
function; else, it will return the first record it finds in your table and
not necessarily the one you want. So perhaps this (assumes that User_ID is a
numeric field):

If IsNull(DLookUp("[User_ID]","Employees Extended","[User_ID]=" &
[txtbUser_ID]) = True Then
........


If the field is a text field, then this:

If IsNull(DLookUp("[User_ID]","Employees Extended","[User_ID]='" &
[txtbUser_ID] & "'") = True Then
........

--

Ken Snell
<MS ACCESS MVP>

Here is my DLookup expression

[txtbUser_ID]<>DLookUp("[User_ID]","Employees Extended")

txtbuser-ID is the text box on the form that is entered.

Thanks.

Show us the DLookup function's expression that you tried. DLookup is the
likely best way to do what you seek.

 

[Dougljm]

thanks..

Can I have two DLookups in an embedded macro that must both return a value
of true? What I am doing is verifying the user id and password entered in two
text boxes on a form with the valid info on a table.

for example:

DLookUp("[User_ID]","Employees Extended","[User_ID]='" &
[txtbUser_ID] & "'") & DLookUp("[Password]","Employees
Extended","[Password]='" & [txtbPassword] & "'")

Again, I appreciate your help.

You need to provide information for the third argument of the DLookup
function; else, it will return the first record it finds in your table and
not necessarily the one you want. So perhaps this (assumes that User_ID is a
numeric field):

If IsNull(DLookUp("[User_ID]","Employees Extended","[User_ID]=" &
[txtbUser_ID]) = True Then
........


If the field is a text field, then this:

If IsNull(DLookUp("[User_ID]","Employees Extended","[User_ID]='" &
[txtbUser_ID] & "'") = True Then
........

--

Ken Snell
<MS ACCESS MVP>

Here is my DLookup expression

[txtbUser_ID]<>DLookUp("[User_ID]","Employees Extended")

txtbuser-ID is the text box on the form that is entered.

Thanks.

Show us the DLookup function's expression that you tried. DLookup is the
likely best way to do what you seek.

--

Ken Snell
<MS ACCESS MVP>


I am teaching myself Access and I am using Access 2007. My question is I
am
using
a text box to enter and ID and when I push a button I want the macro to
search a table that has several records with ID and password info on
it.
The
problem is I can not get the function to loop through the table from
first
record to last to see if the ID is there. If the tex box value ID is
the
first record on the table it finds it and acts accordingly. If the text
box
ID value is the third record on the table it does not find it. I do not
believe that the comannd goes beyond the first record read. I used
DLookUp.
Could someone advise? Thanks. Also are there any good books out there
that
has examples of Access coding?

 

[Ken Snell \(MVP\)]

Under the assumption that you want to compare the entered password for the
user against the user's password in the table, you don't need tow DLookup
functions -- just one:

DLookUp("[Password]","Employees Extended","[User_ID]='" & [txtbUser_ID] &
"'")

--

Ken Snell
<MS ACCESS MVP>

thanks..

Can I have two DLookups in an embedded macro that must both return a value
of true? What I am doing is verifying the user id and password entered in
two
text boxes on a form with the valid info on a table.

for example:

DLookUp("[User_ID]","Employees Extended","[User_ID]='" &
[txtbUser_ID] & "'") & DLookUp("[Password]","Employees
Extended","[Password]='" & [txtbPassword] & "'")

Again, I appreciate your help.

You need to provide information for the third argument of the DLookup
function; else, it will return the first record it finds in your table
and
not necessarily the one you want. So perhaps this (assumes that User_ID
is a
numeric field):

If IsNull(DLookUp("[User_ID]","Employees Extended","[User_ID]=" &
[txtbUser_ID]) = True Then
........


If the field is a text field, then this:

If IsNull(DLookUp("[User_ID]","Employees Extended","[User_ID]='" &
[txtbUser_ID] & "'") = True Then
........

--

Ken Snell
<MS ACCESS MVP>

Here is my DLookup expression

[txtbUser_ID]<>DLookUp("[User_ID]","Employees Extended")

txtbuser-ID is the text box on the form that is entered.

Thanks.



:

Show us the DLookup function's expression that you tried. DLookup is
the
likely best way to do what you seek.

--

Ken Snell
<MS ACCESS MVP>


I am teaching myself Access and I am using Access 2007. My question
is I
am
using
a text box to enter and ID and when I push a button I want the macro
to
search a table that has several records with ID and password info on
it.
The
problem is I can not get the function to loop through the table from
first
record to last to see if the ID is there. If the tex box value ID is
the
first record on the table it finds it and acts accordingly. If the
text
box
ID value is the third record on the table it does not find it. I do
not
believe that the comannd goes beyond the first record read. I used
DLookUp.
Could someone advise? Thanks. Also are there any good books out
there
that
has examples of Access coding?

 

[Dougljm]

Thanks that answers my question.

Under the assumption that you want to compare the entered password for the
user against the user's password in the table, you don't need tow DLookup
functions -- just one:

DLookUp("[Password]","Employees Extended","[User_ID]='" & [txtbUser_ID] &
"'")

--

Ken Snell
<MS ACCESS MVP>

thanks..

Can I have two DLookups in an embedded macro that must both return a value
of true? What I am doing is verifying the user id and password entered in
two
text boxes on a form with the valid info on a table.

for example:

DLookUp("[User_ID]","Employees Extended","[User_ID]='" &
[txtbUser_ID] & "'") & DLookUp("[Password]","Employees
Extended","[Password]='" & [txtbPassword] & "'")

Again, I appreciate your help.

You need to provide information for the third argument of the DLookup
function; else, it will return the first record it finds in your table
and
not necessarily the one you want. So perhaps this (assumes that User_ID
is a
numeric field):

If IsNull(DLookUp("[User_ID]","Employees Extended","[User_ID]=" &
[txtbUser_ID]) = True Then
........


If the field is a text field, then this:

If IsNull(DLookUp("[User_ID]","Employees Extended","[User_ID]='" &
[txtbUser_ID] & "'") = True Then
........

--

Ken Snell
<MS ACCESS MVP>



Here is my DLookup expression

[txtbUser_ID]<>DLookUp("[User_ID]","Employees Extended")

txtbuser-ID is the text box on the form that is entered.

Thanks.



:

Show us the DLookup function's expression that you tried. DLookup is
the
likely best way to do what you seek.

--

Ken Snell
<MS ACCESS MVP>


I am teaching myself Access and I am using Access 2007. My question
is I
am
using
a text box to enter and ID and when I push a button I want the macro
to
search a table that has several records with ID and password info on
it.
The
problem is I can not get the function to loop through the table from
first
record to last to see if the ID is there. If the tex box value ID is
the
first record on the table it finds it and acts accordingly. If the
text
box
ID value is the third record on the table it does not find it. I do
not
believe that the comannd goes beyond the first record read. I used
DLookUp.
Could someone advise? Thanks. Also are there any good books out
there
that
has examples of Access coding?

 

[Ken Snell \(MVP\)]

And to complete the circle - Yes, you can have two test conditions (e.g.,
two DLookup functions, etc.) in a single Condition column of a macro.

--

Ken Snell
<MS ACCESS MVP>

Thanks that answers my question.

Under the assumption that you want to compare the entered password for
the
user against the user's password in the table, you don't need tow DLookup
functions -- just one:

DLookUp("[Password]","Employees Extended","[User_ID]='" & [txtbUser_ID] &
"'")

--

Ken Snell
<MS ACCESS MVP>

thanks..

Can I have two DLookups in an embedded macro that must both return a
value
of true? What I am doing is verifying the user id and password entered
in
two
text boxes on a form with the valid info on a table.

for example:

DLookUp("[User_ID]","Employees Extended","[User_ID]='" &
[txtbUser_ID] & "'") & DLookUp("[Password]","Employees
Extended","[Password]='" & [txtbPassword] & "'")

Again, I appreciate your help.

:

You need to provide information for the third argument of the DLookup
function; else, it will return the first record it finds in your table
and
not necessarily the one you want. So perhaps this (assumes that
User_ID
is a
numeric field):

If IsNull(DLookUp("[User_ID]","Employees Extended","[User_ID]=" &
[txtbUser_ID]) = True Then
........


If the field is a text field, then this:

If IsNull(DLookUp("[User_ID]","Employees Extended","[User_ID]='" &
[txtbUser_ID] & "'") = True Then
........

--

Ken Snell
<MS ACCESS MVP>



Here is my DLookup expression

[txtbUser_ID]<>DLookUp("[User_ID]","Employees Extended")

txtbuser-ID is the text box on the form that is entered.

Thanks.



:

Show us the DLookup function's expression that you tried. DLookup
is
the
likely best way to do what you seek.

--

Ken Snell
<MS ACCESS MVP>


I am teaching myself Access and I am using Access 2007. My
question
is I
am
using
a text box to enter and ID and when I push a button I want the
macro
to
search a table that has several records with ID and password info
on
it.
The
problem is I can not get the function to loop through the table
from
first
record to last to see if the ID is there. If the tex box value ID
is
the
first record on the table it finds it and acts accordingly. If
the
text
box
ID value is the third record on the table it does not find it. I
do
not
believe that the comannd goes beyond the first record read. I
used
DLookUp.
Could someone advise? Thanks. Also are there any good books out
there
that
has examples of Access coding?