Block a user to login

[rcoreejes]

Hello all,

I Have a table votes where a user is able to vote for a specific campaign, when the user votes his username and the date and time is logged in the table votes.
After the user voted he is logged out, then displaying the login form for the next user.

At the login form I want to check if a user already voted and if so he should not be able to login again. How can you do this ?



Please could anyone provide me with a solution that does NOT include VB-code. I am doing a project and is a beginner programmer, and I will be assessed in only SQL, Queries and Macro's.

[Dal Jeanis]

1) Create a query that tells you whether the username has been logged in the table. Look at "WHERE EXISTS".

or

2) Modify the existing query that allows the user to log on in the first place. Add a subquery to that which uses "WHERE NOT EXISTS"

http://www.techonthenet.com/sql/exists.php
http://www.everythingaccess.com/tuto...-a-subquery%3F

[rcoreejes]

Hi,

I am struggling. Perhaps you can have a look and give me a step-by-step solution.
thanx.

[Dal Jeanis]

The style you are using is very different from my own. I don't use macros, and wouldn't use your particular methods. For instance, I would be looking up the names during the login procedure and storing them as temp variables. I would not be creating the voting record on open of the form, I would be using the afterupdate event of the dropdown to create the voting record.

BUT, I understand how you did it, and your methods should work, so I can tell you how to accomplish your goal using the same KIND of methods you are already using.

The most important rule of application design is consistency, and making sure to do it a way that you understand. Beause YOU have to debug it when it fails.

Okay, here's the steps:

First, tell Access about the relationships between the tables.

1) On the Database Tools tab, in the Relationships group, click Relationships.

2) To show a table, on the Design tab, in the Relationships group, click Show Table, and then in the Show Table dialog box, add the table that you want.

3) Add at least two tables, Users and Votes. Click on usrsID and drag a line to VotesUser. Accept the one-to-one relationship.

4) You can also add the Candidates table and establish the link between the Votes and the Candidates. Accept the undeterminate relationship. (It should be one candidate to many votes, but it's fine.)

5) Save the changed Layout.


Second, create a query of users who have not voted. Call it "HasNotVoted".

1) On the Create tab, click Query Wizard, click Find Unmatched, click Okay

2) Select table Users, click Next

3) Select table Votes, click Next

4) (verify the relationship), click Next

5) Select the usrsName field ONLY, then click through to save the query and name it "HasNotVoted".


Third Step, create another hidden text box like txtCheckPassword. This one will have a value of 1 when the name in usrsName is present on the query "HasNotVoted", so the user is still eligible to vote.

I'm sure you know how to do that, since you've done it a few times already.

The Control Source of the textbox is going to be something very close to this (this works in debug mode for me):

=NZ(DCount("[usrsName]", "[HasNotVoted]", "[HasNotVoted].[usrsName] = '" & [forms]![Login]![usrsName] & "'"))

Fourth Step, add one more step to your login macro that tests your new textbox for value > 0. If the value is 1, tell the person they have already voted.

You're done.