0

I created "Customer" table and created a form to mimic the registration process.
Upon clicking on "Register" button I want to reject the process of registration if email and password combination are already exist. Otherwise save the record.

Is it possible to implement this in Microsoft Access?

Improve this question
3
  • 1
    You need to assign logic to the form, which means you will have to use a macro, which means VBA. Sorry for the bad news.
    – user3463
    Oct 28, 2012 at 21:08
  • @RandolphWest I edited my post to remove the VB restriction. Would please help me with how to implement that ? I'm not looking for code to be sure, but I'm not familiar with Access and VB
    – Chiron
    Oct 28, 2012 at 21:24
  • I'm so out of it with Access at the moment. What I'd do is create a query that checks whether the record exists, as well as one that inserts it (two separate queries, using the query builder if you like). Then a macro that runs the first, and if it doesn't return any values, run the second (using an If-Then statement). That's the simplest way I can think of, off the top of my head.
    – user3463
    Oct 28, 2012 at 21:26

1 Answer 1

Reset to default
0

Create a unique constraint on the two columns together, this will cause Access to reject duplicates as violation of the unique constraint. What you do with the error will be up to your form. There's a SO answer here:

https://stackoverflow.com/questions/2127698/can-we-create-multicolumn-unique-indexes-on-ms-access-databases

The pertinent answer:

Open the table in design view in MS Access, select the three columns that you want to make into the unique index, and then click the little key on the toolbar. You cannot have null values in a primary key (set). https://stackoverflow.com/users/2548/remou

Edit since you already have a PK in place and probably don't want to create a new composite PK to replace it, but this is the other option:

Anyway, here's how you create a multi-column unique index on an MS access database. 1.Open the table in design mode, and Design, select Indexes. 2.Create a new row and enter a value in the Index Name cell, 3.Choose the first column from the drop down menu. 4.Add a new row and leave the Index Name cell blank. 5.Choose the second column, and so on. https://stackoverflow.com/users/47775/nbolton

Other (and bad) option is to query the database first and then not to insert, but that leaves the door open to potential referential integrity issues at a later date.

As a side note though; are you sure you want to insert for every combination of username(email)/password that is unique? This might cause problems for users that forget their password ending up with duplicate accounts. You might want to think of some other composite identifier (e.g. handle + password are unique AND email is unique to the DB. Or just simply have the e-mail address be unique. I don't know enough about your problem set - but it's something to think about.

Improve this answer
2
  • You mean specifying the three columns as a primary key? The table already has customer_id PK column
    – Chiron
    Oct 29, 2012 at 9:03
  • Since you already have a PK, use a unique constraint on an index (other of the answers in the SO link). So you'll have a PK but an additional index with a unique constraint that has the other two columns.
    – iivel
    Oct 29, 2012 at 15:13

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .