Access Login System - DCount using 2 Tables? (1 Viewer)

[wlc-cw]

Hello,

I'm a college student currently studying a Database Design unit, so I apologise for being clueless in this post.

I've watched a YouTube video on how to create a simple login system using an If DCount Macro.

I have the following 2 (simplified) tables:

Staff

Staff_ID​	Staff_Number​	First Name​	Last Name​	Address​	Phone Number​
1​	101262​	Amelia​	Smith​	12 Somerview Walk​	555111​
2​	103951​	Harold​	Jenkins​	46 Oldham Avenue​	​

Account

Account_ID​	Staff_ID​	Staff_Number​	Password​
1​	1​	101262​	pass123​
2​	2​	103591​	pass321​

I have the following Macro that I acquired from the aforementioned YouTube video:

If(DCount("Staff_Number","Account","Staff_Number=[UName] And Password=[Pwd]"))>0

This macro checks the data entered in the UName and Pwd text boxes against a Staff_Number (username) and Password combination in the Account table to determine a successful login.

However, as part of normalisation rules, I don't want to have the Staff_Number attribute in the Account table. I want DCount to use the Staff_ID (foreign key in the Account table) to get the Staff_Number from the Staff table and check it against the data entered in the UName text box. For the Password, I want it to check the Password in the Account table against the data entered in the Pwd text box (as it's already doing in the above If statement).

I've tried the following, which is a lazy attempt (as it tries to check user-input against data in two different tables) and doesn't get rejected by the Macro, but it results in a failed login attempt:

If(DCount("Staff_Number","Staff","Staff_Number=[UName]") And DCount("Password","Account","Password=[Pwd]"))>0

I've tried learning DCount and DLookup online but I'm just so confused by the various examples and complex ways in which it can be used. I have no idea how to implement what I'm trying to achieve. I wanted some authenticity (i.e. getting the Staff_Number by checking it against the Staff_ID in the Account table [then finding it in the Staff table] and finally checking the password against the same record).

I appreciate in advance any time taken to help me understand how this can be achieved.

 

[June7]

I don't use macros, only VBA.

That DCount() can't work because the control references are between quote marks. Unless you include full form/control path reference, must concatenate this variable input. Also, the password count (or lookup) needs the StaffID in criteria. So you first need a DLookup on Staff table to pull that.

If a staff member can have only one account, why not just have one table?

 

[The_Doc_Man]

I'm not sure because your explanation seemed to eliminate the easiest way to get everything together in one record.

If it is permitted in your unit, consider an INNER JOIN between the two tables, which share the common field Staff_ID. Then you can create a simple SELECT query that contains ALL of the relevant fields and can do a DCount or DLookup on the query rather than on two tables (which you can't do in Domain Aggregate functions anyway... only allows one domain at a time.

 

[theDBguy]

Hi. Welcome to AWF!

One way to remove the staff number from the account table and still be able to use it for your login DCount is to use a query for the DCount instead of a table.

 

[plog]

If(DCount("Staff_Number","Staff","Staff_Number=[UName]") And DCount("Password","Account","Password=[Pwd]"))>0

First, It makes no sense that the username and password are stored in separate tables. They should have a 1-1 correspondence (a username can only have 1 password, Right?). I fail to see the purpose of your Account table. All its data should just be stored in Staff.

Second, don't use macros. Use VBA, it's easier to debug to see where you are going wrong. Make a button the user clicks on your form and then work inside that Onclick event to write code.

That's the system errors you have. Onto the syntax issue. The last argument of a DCount is the criteria string. Inside that criteria string you are bnuilding a comparison (you are using the equal sign to see if 2 things are equal). Since the datatype of the field you are using on the left side of the equal sign is a string, you must enclose the right side of the equal sign in quote marks. You are already using double quotes to build the whole criteria string, so on the inside you must use single quotes, like so:

"Password='pass123'"

That's how it should look if you always checked against 'pass123'. But you are testing against user input which is a variable so you must insert it by escaping out of your string like so:

"Password='" & Forms!UserLogon!InputPassword & "'"

 

[wlc-cw]

Are you building macro or VBA?

That DCount() can't work because the control references are between quote marks. Unless you include full form/control path reference, must concatenate this variable input. In VBA:

If DCount("*","Staff","Staff_Number='" & [UName] & "'") > 0 And DCount("*","Account","Password='" & [Pwd] & "'") > 0 Then

Thank you for your reply.

I think I'm building a Macro using VBA in the Macro Builder feature of Access.

I unfortunately wrote the first code exactly as it was shown in the YouTube video (tweaked to my own table and attribute names) and it surprisingly did work (when the Staff Number was in the Account table).

Can I kindly ask why your suggested VBA includes a "*" prior to the expression?
Finally, I noticed your VBA doesn't use the Staff_ID to find the Staff_Number, therefore I assume it would accept any Staff_Number (username) and password combination from all records?

 

[wlc-cw]

I'm not sure because your explanation seemed to eliminate the easiest way to get everything together in one record.

If it is permitted in your unit, consider an INNER JOIN between the two tables, which share the common field Staff_ID. Then you can create a simple SELECT query that contains ALL of the relevant fields and can do a DCount or DLookup on the query rather than on two tables (which you can't do in Domain Aggregate functions anyway... only allows one domain at a time.

Thank you for your reply and insight.

I completely agree, but I came to realise that an outsourced Database Admin (who would need his own login details, who would also have administrative privileges of which I've not included in this example due to it being unnecessary) would not be a Staff member. Therefore, a separate Accounts table in a relational database would allow Staff members, Database Admins and other potential experts to access the database forms / reports / tables / queries / etc., without admins or other types of users having to be added as Staff in the Staff table (when they don't work there). I thought I could link the two tables and get the Staff_Number from the Staff table by using the foreign key Staff_ID located in the Accounts table.

Apologies for the long, complicated explanation!

Regardless, I will definitely consider researching and implementing your recommendation as I didn't know of this potential solution.

 

[June7]

I edited my previous post after you read it.

It is not necessary to reference a field when counting records so the * wildcard is sufficient.

 

[wlc-cw]

If(DCount("Staff_Number","Staff","Staff_Number=[UName]") And DCount("Password","Account","Password=[Pwd]"))>0

First, It makes no sense that the username and password are stored in separate tables. They should have a 1-1 correspondence (a username can only have 1 password, Right?). I fail to see the purpose of your Account table. All its data should just be stored in Staff.

Second, don't use macros. Use VBA, it's easier to debug to see where you are going wrong. Make a button the user clicks on your form and then work inside that Onclick event to write code.

That's the system errors you have. Onto the syntax issue. The last argument of a DCount is the criteria string. Inside that criteria string you are building a comparison (you are using the equal sign to see if 2 things are equal). Since the datatype of the field you are using on the left side of the equal sign is a string, you must enclose the right side of the equal sign in quote marks. You are already using double quotes to build the whole criteria string, so on the inside you must use single quotes, like so:

"Password='pass123'"

That's how it should look if you always checked against 'pass123'. But you are testing against user input which is a variable so you must insert it by escaping out of your string like so:

"Password='" & Forms!UserLogon!InputPassword & "'"

Thank you for your reply and insight.

I completely agree that it makes no sense. But I'm battling with the issue that the Database Admin is outsourced, so therefore not a Staff member. As a result, I thought it would be simpler (and wiser) to have a separate Account table in which Staff members, and outsourced admins, can access the database. For staff members, the user-input Staff Number (UName) could be matched by using the foreign key Staff_ID in the Account table to find the corresponding Staff_Number in the Staff table. For admins, the word "admin1" or "admin2" would replace a numerical Staff_ID and since it doesn't exist in the Staff table, the DCount would accept it from the Account table.

I can certainly imagine that VBA is better to use, but sadly I'm clueless with it so the Macro Builder helps me build the expression in a way I can follow/understand. Perhaps I may learn VBA once I learn the basics through the Macro builder.

For the final part of your message - thank you, I did not know this. I'm surprised my example worked when the Staff_Number was in the Accounts table (for testing). This new information will be beneficial in later use, I appreciate it very much.