Using DLookup in macro

[Karen]

Hi,

I have a form which the user enters their UserName in an unbound text box
and Password in another unbound text box. I want the macro to verify what
was entered in these two text boxes against information in the user table.
If the UserName and Password match, then the database will open the
switchboard. I have the following condition:

[UserName]=DLookUp("[UserName]","tblUsers") And
[Password]=DLookUp("[Password]","tblUsers")

The macro is only reading the first row of data in the UserTable. When I
enter that UserName and Password, the Switchboard opens. If I enter another
UserName and Password that have been entered in the table, it does not work.

Please help!!

 

[tina]

try

DCount(1, "tblUsers", "[UserName] = '" & [UserName] & "' And [Password] = '"
& [Password] & "'") > 0

hth

 

[Karen]

Sorry, I may not have been clear. I do not want to count the records, I want
to verify that the user has entered the correct username and password into
the login form. If they have, the switchboard will open.

try

DCount(1, "tblUsers", "[UserName] = '" & [UserName] & "' And [Password] = '"
& [Password] & "'") > 0

hth

Hi,

I have a form which the user enters their UserName in an unbound text box
and Password in another unbound text box. I want the macro to verify what
was entered in these two text boxes against information in the user table.
If the UserName and Password match, then the database will open the
switchboard. I have the following condition:

[UserName]=DLookUp("[UserName]","tblUsers") And
[Password]=DLookUp("[Password]","tblUsers")

The macro is only reading the first row of data in the UserTable. When I
enter that UserName and Password, the Switchboard opens. If I enter another
UserName and Password that have been entered in the table, it does not work.

Please help!!

 

[tina]

i understand that, hon. the expression

DCount(1, "tblUsers", "[UserName] = '" & [UserName] & "' And [Password] = '"
& [Password] & "'") > 0

all goes on one line in the macro Condition column. the expression evaluates
to True if a matching record is found in the table, and evaluates to False
if no record is found. when the condition = True, then the macro action will
run.

one thing to note is that if two users happen to have the same name, and
happen to choose the same password (not likely, but not impossible unless
you've disallowed duplicate values in those fields at the table level), then
the DCount() expression will return True and the macro action will run -
with nothing to stop one user from logging in as the other.

hth

Sorry, I may not have been clear. I do not want to count the records, I want
to verify that the user has entered the correct username and password into
the login form. If they have, the switchboard will open.

try

DCount(1, "tblUsers", "[UserName] = '" & [UserName] & "' And [Password] = '"
& [Password] & "'") > 0

hth

Hi,

I have a form which the user enters their UserName in an unbound text box
and Password in another unbound text box. I want the macro to verify what
was entered in these two text boxes against information in the user table.
If the UserName and Password match, then the database will open the
switchboard. I have the following condition:

[UserName]=DLookUp("[UserName]","tblUsers") And
[Password]=DLookUp("[Password]","tblUsers")

The macro is only reading the first row of data in the UserTable. When I
enter that UserName and Password, the Switchboard opens. If I enter another
UserName and Password that have been entered in the table, it does not work.

Please help!!

 

[Karen]

Thank you for the explanation. The D functions are still new to me and I am
trying to learn more and your explanation helped alot. I will give it a go.

i understand that, hon. the expression

DCount(1, "tblUsers", "[UserName] = '" & [UserName] & "' And [Password] = '"
& [Password] & "'") > 0

all goes on one line in the macro Condition column. the expression evaluates
to True if a matching record is found in the table, and evaluates to False
if no record is found. when the condition = True, then the macro action will
run.

one thing to note is that if two users happen to have the same name, and
happen to choose the same password (not likely, but not impossible unless
you've disallowed duplicate values in those fields at the table level), then
the DCount() expression will return True and the macro action will run -
with nothing to stop one user from logging in as the other.

hth

Sorry, I may not have been clear. I do not want to count the records, I want
to verify that the user has entered the correct username and password into
the login form. If they have, the switchboard will open.

try

DCount(1, "tblUsers", "[UserName] = '" & [UserName] & "' And [Password] = '"
& [Password] & "'") > 0

hth


Hi,

I have a form which the user enters their UserName in an unbound text box
and Password in another unbound text box. I want the macro to verify what
was entered in these two text boxes against information in the user table.
If the UserName and Password match, then the database will open the
switchboard. I have the following condition:

[UserName]=DLookUp("[UserName]","tblUsers") And
[Password]=DLookUp("[Password]","tblUsers")

The macro is only reading the first row of data in the UserTable. When I
enter that UserName and Password, the Switchboard opens. If I enter
another
UserName and Password that have been entered in the table, it does not
work.

Please help!!

 

[tina]

you're welcome
post back if you run into problems with it.

Thank you for the explanation. The D functions are still new to me and I am
trying to learn more and your explanation helped alot. I will give it a go.

i understand that, hon. the expression

DCount(1, "tblUsers", "[UserName] = '" & [UserName] & "' And [Password] = '"
& [Password] & "'") > 0

all goes on one line in the macro Condition column. the expression evaluates
to True if a matching record is found in the table, and evaluates to False
if no record is found. when the condition = True, then the macro action will
run.

one thing to note is that if two users happen to have the same name, and
happen to choose the same password (not likely, but not impossible unless
you've disallowed duplicate values in those fields at the table level), then
the DCount() expression will return True and the macro action will run -
with nothing to stop one user from logging in as the other.

hth

Sorry, I may not have been clear. I do not want to count the records,

I
want

to verify that the user has entered the correct username and password into
the login form. If they have, the switchboard will open.

:

try

DCount(1, "tblUsers", "[UserName] = '" & [UserName] & "' And

[Password]
= '"

& [Password] & "'") > 0

hth


Hi,

I have a form which the user enters their UserName in an unbound

text
box

and Password in another unbound text box. I want the macro to

verify
what

was entered in these two text boxes against information in the

user
table.

If the UserName and Password match, then the database will open the
switchboard. I have the following condition:

[UserName]=DLookUp("[UserName]","tblUsers") And
[Password]=DLookUp("[Password]","tblUsers")

The macro is only reading the first row of data in the UserTable. When I
enter that UserName and Password, the Switchboard opens. If I enter
another
UserName and Password that have been entered in the table, it does not
work.

Please help!!

 

[tina]

btw, one thing that may help you understand the use of a domain aggregate
function in a macro Condition, is to be sure you understand how a condition
works.

the expression in a Condition column is always an equation (or more than
one, but we won't get into complex expressions), that evaluates to True or
False. think of it as making a statement, like "It's 70 degrees outside
right now." Sometimes that statement is true, sometimes it isn't. if you
write an equation, like
2+2 = 1+3
Access calculates the expression on the left side of the equation (2+2), and
returns 4. then Access calculates the expression on the right side of the
equation (1+3), and returns 4. then it evaluates the expression
4=4
as being a True or False statement. the statement evaluates to True, and so
the macro action runs. of course, hard-coded values are what they are, so 4
will always equal 4. but when you replace a hard-coded value with domain
aggregate function, for instance, then the statement may be True or False,
depending on what the function returns. in the statement

DCount(1, "tblUsers", "[UserName] = '" & [UserName] & "' And [Password] = '"
& [Password] & "'") > 0

Access first runs the DCount() function.

DCount(1, "tblUsers", "[UserName] = '" & [UserName] & "' And [Password] = '"
& [Password] & "'")

if no records that match the criteria are found in the domain (a table, in
this case), then the function returns zero (0). so the equation
0 > 0
is evaluated, and of course it's False. but if one or more records that
match the criteria is found, then the DCount() function counts the matching
records (of course) and returns that count. so the equation
1 > 0
(or 2 > 0, or however many records are found)
is evaluated, and of course any positive number is greater than zero, so the
expression is True. and the macro action runs.

hth

Thank you for the explanation. The D functions are still new to me and I am
trying to learn more and your explanation helped alot. I will give it a go.

i understand that, hon. the expression

DCount(1, "tblUsers", "[UserName] = '" & [UserName] & "' And [Password] = '"
& [Password] & "'") > 0

all goes on one line in the macro Condition column. the expression evaluates
to True if a matching record is found in the table, and evaluates to False
if no record is found. when the condition = True, then the macro action will
run.

one thing to note is that if two users happen to have the same name, and
happen to choose the same password (not likely, but not impossible unless
you've disallowed duplicate values in those fields at the table level), then
the DCount() expression will return True and the macro action will run -
with nothing to stop one user from logging in as the other.

hth

Sorry, I may not have been clear. I do not want to count the records,

I
want

to verify that the user has entered the correct username and password into
the login form. If they have, the switchboard will open.

:

try

DCount(1, "tblUsers", "[UserName] = '" & [UserName] & "' And

[Password]
= '"

& [Password] & "'") > 0

hth


Hi,

I have a form which the user enters their UserName in an unbound

text
box

and Password in another unbound text box. I want the macro to

verify
what

was entered in these two text boxes against information in the

user
table.

If the UserName and Password match, then the database will open the
switchboard. I have the following condition:

[UserName]=DLookUp("[UserName]","tblUsers") And
[Password]=DLookUp("[Password]","tblUsers")

The macro is only reading the first row of data in the UserTable. When I
enter that UserName and Password, the Switchboard opens. If I enter
another
UserName and Password that have been entered in the table, it does not
work.

Please help!!

 

[Karen]

Great information. I have another question about another macro. I created
the login form and it works great. (Thanks again) Now I am stuck on another
macro.

I have users who will need to run reports based on the Division and/or
Division and Region they are assigned to. There are users who are assigned
to multiple Divisions and/or Regions. I want them to click on the report
menu item and have the report run automatically for the division(s)/region(s)
they are assigned to. They currently have to enter their password for each
report and the macro has each password and the filter criteria for each
person. This is very cumbersome for the user and maintaining when
adding/deleting users. I just want them to be able to click the button and
have the report run.

I created a table with the fields UserID, Division,and Region and entered
each user and the access information. I then created a query linking the
Access Table and the User Table with the join on the UserID. I was going to
tie it to the Login form (having an unbound text box on the form without it
visible and keeping the login form loaded, but not visible). I don't thinkt
this would work for those users with multiple divisions and regions. I am
not sure how to filter the query, that the report is based from, in the
macro. I just don't know if I am headed in the right direction or if there
is a better way to proceed.

I hope I have explained this adequately.

btw, one thing that may help you understand the use of a domain aggregate
function in a macro Condition, is to be sure you understand how a condition
works.

the expression in a Condition column is always an equation (or more than
one, but we won't get into complex expressions), that evaluates to True or
False. think of it as making a statement, like "It's 70 degrees outside
right now." Sometimes that statement is true, sometimes it isn't. if you
write an equation, like
2+2 = 1+3
Access calculates the expression on the left side of the equation (2+2), and
returns 4. then Access calculates the expression on the right side of the
equation (1+3), and returns 4. then it evaluates the expression
4=4
as being a True or False statement. the statement evaluates to True, and so
the macro action runs. of course, hard-coded values are what they are, so 4
will always equal 4. but when you replace a hard-coded value with domain
aggregate function, for instance, then the statement may be True or False,
depending on what the function returns. in the statement

DCount(1, "tblUsers", "[UserName] = '" & [UserName] & "' And [Password] = '"
& [Password] & "'") > 0

Access first runs the DCount() function.

DCount(1, "tblUsers", "[UserName] = '" & [UserName] & "' And [Password] = '"
& [Password] & "'")

if no records that match the criteria are found in the domain (a table, in
this case), then the function returns zero (0). so the equation
0 > 0
is evaluated, and of course it's False. but if one or more records that
match the criteria is found, then the DCount() function counts the matching
records (of course) and returns that count. so the equation
1 > 0
(or 2 > 0, or however many records are found)
is evaluated, and of course any positive number is greater than zero, so the
expression is True. and the macro action runs.

hth

Thank you for the explanation. The D functions are still new to me and I am
trying to learn more and your explanation helped alot. I will give it a go.

i understand that, hon. the expression

DCount(1, "tblUsers", "[UserName] = '" & [UserName] & "' And [Password] = '"
& [Password] & "'") > 0

all goes on one line in the macro Condition column. the expression evaluates
to True if a matching record is found in the table, and evaluates to False
if no record is found. when the condition = True, then the macro action will
run.

one thing to note is that if two users happen to have the same name, and
happen to choose the same password (not likely, but not impossible unless
you've disallowed duplicate values in those fields at the table level), then
the DCount() expression will return True and the macro action will run -
with nothing to stop one user from logging in as the other.

hth


Sorry, I may not have been clear. I do not want to count the records, I
want
to verify that the user has entered the correct username and password into
the login form. If they have, the switchboard will open.

:

try

DCount(1, "tblUsers", "[UserName] = '" & [UserName] & "' And [Password]
= '"
& [Password] & "'") > 0

hth


Hi,

I have a form which the user enters their UserName in an unbound text
box
and Password in another unbound text box. I want the macro to verify
what
was entered in these two text boxes against information in the user
table.
If the UserName and Password match, then the database will open the
switchboard. I have the following condition:

[UserName]=DLookUp("[UserName]","tblUsers") And
[Password]=DLookUp("[Password]","tblUsers")

The macro is only reading the first row of data in the UserTable.
When I
enter that UserName and Password, the Switchboard opens. If I enter
another
UserName and Password that have been entered in the table, it does not
work.

Please help!!

 

[tina]

well, i think the issue here is not really using the macro, it's how to
filter the report data correctly. i have no doubt it can be done, but i'm
coming up blank. suggest you keep an eye on this thread for maybe a day or
so; if nobody else comes to the rescue you might want to start a new thread
in the microsoft.public.access.reports NG. make sure you explain the setup
clearly (don't just refer folks back to this thread), and include the fact
that you're using macros.

hth

Great information. I have another question about another macro. I created
the login form and it works great. (Thanks again) Now I am stuck on another
macro.

I have users who will need to run reports based on the Division and/or
Division and Region they are assigned to. There are users who are assigned
to multiple Divisions and/or Regions. I want them to click on the report
menu item and have the report run automatically for the division(s)/region(s)
they are assigned to. They currently have to enter their password for each
report and the macro has each password and the filter criteria for each
person. This is very cumbersome for the user and maintaining when
adding/deleting users. I just want them to be able to click the button and
have the report run.

I created a table with the fields UserID, Division,and Region and entered
each user and the access information. I then created a query linking the
Access Table and the User Table with the join on the UserID. I was going to
tie it to the Login form (having an unbound text box on the form without it
visible and keeping the login form loaded, but not visible). I don't thinkt
this would work for those users with multiple divisions and regions. I am
not sure how to filter the query, that the report is based from, in the
macro. I just don't know if I am headed in the right direction or if there
is a better way to proceed.

I hope I have explained this adequately.

btw, one thing that may help you understand the use of a domain aggregate
function in a macro Condition, is to be sure you understand how a condition
works.

the expression in a Condition column is always an equation (or more than
one, but we won't get into complex expressions), that evaluates to True or
False. think of it as making a statement, like "It's 70 degrees outside
right now." Sometimes that statement is true, sometimes it isn't. if you
write an equation, like
2+2 = 1+3
Access calculates the expression on the left side of the equation (2+2), and
returns 4. then Access calculates the expression on the right side of the
equation (1+3), and returns 4. then it evaluates the expression
4=4
as being a True or False statement. the statement evaluates to True, and so
the macro action runs. of course, hard-coded values are what they are, so 4
will always equal 4. but when you replace a hard-coded value with domain
aggregate function, for instance, then the statement may be True or False,
depending on what the function returns. in the statement

DCount(1, "tblUsers", "[UserName] = '" & [UserName] & "' And [Password] = '"
& [Password] & "'") > 0

Access first runs the DCount() function.

DCount(1, "tblUsers", "[UserName] = '" & [UserName] & "' And [Password] = '"
& [Password] & "'")

if no records that match the criteria are found in the domain (a table, in
this case), then the function returns zero (0). so the equation
0 > 0
is evaluated, and of course it's False. but if one or more records that
match the criteria is found, then the DCount() function counts the matching
records (of course) and returns that count. so the equation
1 > 0
(or 2 > 0, or however many records are found)
is evaluated, and of course any positive number is greater than zero, so the
expression is True. and the macro action runs.

hth

Thank you for the explanation. The D functions are still new to me

and I
am

trying to learn more and your explanation helped alot. I will give it

a
go.

:

i understand that, hon. the expression

DCount(1, "tblUsers", "[UserName] = '" & [UserName] & "' And

[Password]
= '"

& [Password] & "'") > 0

all goes on one line in the macro Condition column. the expression evaluates
to True if a matching record is found in the table, and evaluates to False
if no record is found. when the condition = True, then the macro

action
will

run.

one thing to note is that if two users happen to have the same name, and
happen to choose the same password (not likely, but not impossible unless
you've disallowed duplicate values in those fields at the table

level),
then

the DCount() expression will return True and the macro action will run -
with nothing to stop one user from logging in as the other.

hth


Sorry, I may not have been clear. I do not want to count the

records,
I

want
to verify that the user has entered the correct username and

password
into

the login form. If they have, the switchboard will open.

:

try

DCount(1, "tblUsers", "[UserName] = '" & [UserName] & "' And [Password]
= '"
& [Password] & "'") > 0

hth


Hi,

I have a form which the user enters their UserName in an

unbound
text

box
and Password in another unbound text box. I want the macro to verify
what
was entered in these two text boxes against information in the user
table.
If the UserName and Password match, then the database will

open
the

switchboard. I have the following condition:

[UserName]=DLookUp("[UserName]","tblUsers") And
[Password]=DLookUp("[Password]","tblUsers")

The macro is only reading the first row of data in the UserTable.
When I
enter that UserName and Password, the Switchboard opens. If I enter
another
UserName and Password that have been entered in the table, it

does
not

work.

Please help!!