Can I disallow entry of duplicate primary key on form?

I'm not sure exactly how to best explain this, bet here it goes...

If I add a new record on a form and enter a duplicate SSN (the primary key), I want to be prompted that that SSN already exists once the user tabs away from the field. However, Access will accept the entry and let the user continue to enter data, and will only prompt what it's a duplicate when the record is saved or exited.

Can Access notify the user of the duplicate earlier?

Post your answer or comment

comments powered by Disqus

I build a private company orders/customer info.
In the customer info area, i want to add a subform of computer information. Meaning, every company usually has more than 1 computer, so I want to add that as a subform later on in my customer form.
The problem i am having goes back to the table.
I created a table called Customers & CompInfo.
In Customers i have all the relevant information I need and I also call a CompID (number) that comes from CompInfo table.
In CompInfo, I have CompID & CustomerID (which suppose to come from customers table). My problem is, I can only put a primary key on the CompID and not on the CustomerID.
In my relationships, I made CompID from CompInfo relationship to CompID in Customers table.
I dont know if they makes sense.
When i eventually added a subform called CompInfo subform to Customers form, it worked fine.
...But when I go back to tables and open the CompInfo table, the + sign show show me the customer that is related to whichever particular CompID. Yet, the + sign only shows me blank fields.
What am I doing wrong?

I am using a data entry form to enter new data into one of the tables.How can we prevent entry of duplicate records? Duplicacy shall be checked on the first two fields only.
Please help.


I have a DB where I want text entry of the primary key to adhere to a certain format.
I'm already using a mask of >LL000000 to force two capital letter and 6 numbers.
Is there any way I can force extra restrictions, by making for example the first 3 characters to have to be AB1, thus making every entry follow format:

(FYI: Access 2003)

Hi Guys,

I have imported a supplier table from an excel spreadsheet to access. However , i wish to check if there are duplicate entries of the primary key (supplierId). I have tried using a query but to no avail.

Any help would be greatly appreciated,


OK, when I manually run an import of a text file to MS Access, the wizard runs me through some steps. So, I save this as an Import specification "IS-TEST" under the advanced settings within the Import Wizard. I make the asumption that these "specs" have been saved. But....

when I try to run the import specification in my code, it doesn't do the same thing as it does when i run it manually. I want to have Access add a primary key, like it does when I manually run it through the import wizard....

how can I have access add a primary key field to my import automatically?


Hi! I have a table that the primary key is autonumeric. I trying to do a form to add data to tnis table... but now i erase som rows of the table, and now the primary key jump to values. what can i do to restart the primary key to have the correct order without jumpping number? please


I hope I am posting in the correct place if not I send my apologies.

I am attempting to create a a data base to track commercial information. This information is tracked on several tables. We have an opportunity reference number which I want to be the primary key on every table.

My question is, when I enter the opp ref number in the form for the first table. How can I link it so that, that opp ref number is immediately filled into that field on every table, but in the same order so all the data will relate to eachother?

Kind Regards


Hi, Before i even open a form I have exported data from another program into the main table and one other table (5 in total). I need some way of looking at the last record in the main table and setting the primary key of three other tables equal to this one...This is what i have been working with at the moment, though movelast is giving error on compile:

rivate Sub Form_Load()

Dim db As DAO.Database
Dim RS As DAO.Recordset
Set db = CurrentDb()

Set RS = db.OpenRecordset("Patient_Information", dbOpenDynaset)
Set LastPatientRecord = RS.MoveLast

Set RS = db.OpenRecordset("Test1", dbOpenDynaset)
RS![HospitalNumber] = LastPatientRecord

...then i would repeat this for two other tables
End Sub

any advice wold be appreciated!!

tan :-)

Can I create many of chart in one pivot chart in access using more than one table?


I've just begun using microsoft access and would like to create a primary key on a table of data that has been sorted alphabetically. However, when I try to create this key (designview -> auto number-> increment) it autonumbers for the column the way it was before i sorted it. Is there a known way of doing this?


I'm using a split form in Access 2007. The bottom part of the form shows the table that is linked to the form. When the user clicks on one line in the table, I want to update that specific line. in order to do that I need to grab the primary key for that line to use in my code. What's the syntax for referring to the key specific to that line?

Many's giving me a headache


I make a table using VBA, and I want to put a primary key on a field using code

Any ideas? Ive trawled the web with no joy.

M :-)

This is an annoying little problem and I was wondering if anyone could lend any insight as to how to solve it. I am using Access 2007.

Create a blank form and go to form view. Press the backspace key. The computer will beep and you probably won't even notice but the status bar will quickly flash a message saying:

"Records can't be deleted with this form"

Well obviously. To stop the warning you can set the form's Key Preview to true and then capture the KeyCode in the KeyPress of the form, set it to zero and problem solved. But not quite.....

I am using an ActiveX control on the form. If I am editing a cell in the ActiveX control and press the backspace key the above problem happens because the key pressed is getting passed on down the chain to the form. If I use the work around I mentioned in the previous paragraph I cannot then delete a character from a cell in the ActiveX control because I have stopped the backspace character being passed to it.

Basically I'm stumped. I have tried all manner of things to solve this *beeping* problem but to no avail. I have even contact the support people for the ActiveX control and they are even stumped and asked me to inform them if I come up with an answer.

I tried putting the form onto a form in Access 2003 and it works fine without the beeps. So I definitely think it has something to do with Access 2007. Is there a way to disable the default behaviour of the backspace key on a form?

Can anyone please help?



Can I create a permanent filter by using the Form's Attribute “Filter” so the records returned only show a specific value?

I have tried this but it didn’t work for me.


Cash_Record is the table Current_Assignment is both the Table’s Field name and the Form Control name, 2 is the value I want to return.

I need to keep the records viewable as well as editable in the nice forms and not return them in Datasheets.

Is there a permanent script I can put in the form somewhere that will limit the returned records?


I have a database in Access 2000 that was written somewhat hastily. I would like to make some specific repairs.

There are 2 linked tables.
1) tblContacts (containing contact info including street address without city, state, and zipcode)
2) tblTowns (containing city, state, zipcode)

The relationship one (tblContacts) to many (tblTowns). Primary key in tblTowns is the name of the city.

Problems that arise: When a new contact is from a city that has the same name as a city in another state. I will get duplicate reports since city X is now a duplicated primary key.

I would like to create a new primary key maybe an autonumber generated key in tblTowns. Is there a standard methodology of handling this type of a change. If I just add the ID column to tblTowns, then I need to populate the foreign key field in is that best done?

Hope the question is not to open ended. I'd appreciate any help that can be offered.

I would be most appreciative to any one who can figure this out. I have a one table DB that has about 3100 Rows and 30 Columns. (All info I had to import from excel)

Its a database that tracks the domain name status, expiration, cost, etc. of my companys domain holdings. About 700 of the entries are the same domain name with different columns filled in. For example, one domain , listed twice, has cost and expiration, but no status. The second entry of the same domain has status, but not cost and expiration.

Can any one come up with a query that would delete the duplicate entry with the end result being one full row with all of the desired information?


I am very new to MS Access Forms and scripting. I am trying to write a simple VBS script to check to see if a Primary Key has been used Before Update. If it has then a message box lets the person know and Undo the form field and the person can verify that there is not a keying error on their part before continuing creating records.

The script pops up a message no matter what number is used in the field.

MS Access 2003

The table is named “Cash_Record”
The Primary Key field is named “CR_CCN” (Primary Key)
The Form is named “Finance New”
The form field is named “CR_CCN”
(Format General = Number, Decimal Places = 0, Control Source = CR_CCN [name from table field], Default Value = None)

The Code I have is:
Private Sub CR_CCN_BeforeUpdate(Cancel As Integer)

Dim SID As String

SID = Me.CR_CCN.Value

'Check Cash_Record table for duplicate CR_CCN
If DCount("CR_CCN", "Cash_Record") > 0 Then

'Undo duplicate entry

'Message box warning of duplication
MsgBox "Warning!! " _
& vbCr & vbCr & "CCN [ " & SID & " ] has already been used!." _
& vbCr & "Please verify the number and try again."

End If

End Sub

Thanks for any help you can give.

I am about to loose it......
For weeks on end I have worked on adapting parts of northwind to an old existing database. After spending months trying to import an excel spreadsheet, removing invoice, inventory and other worthless parts; I am now struggling to just enter a freaking order. This programs is going to cause some property damage if I dont get some guidance. Help!

Have tables
Principals (Suppliers)

All have primary keys - on auto number - indexed no duplicates

I have a form
Order details - which I use to enter information/Orders.
Form is sourced on Orders Tbl
The form has a main section which asigns a new order ID
Sets status
Has Combo box for customers, employees, and text box for email addy and order date.

There is a sub form which is currently sourced on Orders TBL (though I have tired any and every possible version)
Sub form has text boxes for order amount, comm earned and statues
There is a combo box for Principals (Suppliers).

The issue.
When I enter a new record and try to save or close, I am given error message that duplicate values in the index, primary key or relationship. I have deleted relationships, changed primary keys, removed primary keys, etc....... Still get the message.

You can reopen through use of this form a record and change any field, IE Customer, employee, principal (Supplier) etc and it will save with out error.

What is going on? All of the indexs should be unique auto numbered, and dups not allowed, though I have tried to change this field to no avail. Help, guidance or a bullet for computer would be much appericated.

I have an Access 2003 application that contains a table for recording payments received by post. The Primary Key of this Payments table is an autonumber field called PaymentID.

I also run an ASP.NET website that has an SQL Server Payments table, in this case for recording PayPal payments. It also has an autonumber PaymentID PK field (aka an 'identity' column in SQL Server terminology).

My plan is to merge the online payment data with the postal payment data on the Access version of the Payments table to enable various Access forms and reports to show all payments, regardless of source. For this to work, I need to end up with a single PaymentID column as PK.

However, my problem is that at present there is duplication of PaymentID numbers between Access and SQL Server as there is no mechanism in place to prevent this. In other words, there could be an online payment record with a PaymentID of 1234 that is totally unrelated to a postal payment with the same PaymentID. I cannot therefore merge the data without modifying the PKs in some way.

I would be very grateful for any suggestions on how to address this problem.


BTW, this thread is an offshoot of a previous thread at In that previous thread I was asking for guidance on how to implement a specific solution to this problem. However, I now think I was premature in selecting that solution, which is why I have now posted my original problem.

Hi Forum,

I have a table with a multifield primary key, made up of the fields "Plant" and "Code". Plant is not unique and neither is Code, but both combined provide a unique key.

In the VBA code below I have referenced "Plant" (see the part of the code that I have highlighted in magenta).

When I select a record in the Form and run the code it returns a record with the correct Plant, but usually the wrong Code. I realise that this is probably because I require both Plant and Code to provide a unique record for Access to find.
I don't know how to show a multi-field Primary Key in my code.

Can anyone help, please?

Thanks very much.


Public Function MergetoWordQRY()
'This method creates a new document in MS Word
'using Automation
On Error Resume Next
Dim rsCert As Recordset, iTemp As Integer
Dim WordObj As Word.Application
Set rsCert = DBEngine(0).Databases(0).OpenRecordset("tblComplCe rt", dbOpenTable)

rsCert.Index = "PrimaryKey"
rsCert.Seek "=", Forms!frmByClientSig![Plant]

If rsCert.NoMatch Then
MsgBox "Invalid record dude", vbOKOnly
Exit Function
End If
DoCmd.Hourglass True
Set WordObj = GetObject(, "Word.Application")
If Err.Number 0 Then
Set WordObj = CreateObject("Word.Application")
End If
WordObj.Visible = True
WordObj.Documents.Add ("C:Access AutomationTest.doc")
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="Plant"
WordObj.Selection.TypeText rsCert![Plant]
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="Area"
WordObj.Selection.TypeText rsCert![Area]
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="Code"
WordObj.Selection.TypeText rsCert!

WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="PrjSig"
WordObj.Selection.TypeText rsCert![ProjSignatory]
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="Desc"
WordObj.Selection.TypeText rsCert![Description]
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="Act1"
WordObj.Selection.TypeText rsCert![Action1]
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="Act2"
WordObj.Selection.TypeText rsCert![Action2]
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="Doc1"
WordObj.Selection.TypeText rsCert![Doc1]
Set WordObj = Nothing
DoCmd.Hourglass False
Exit Function
Set WordObj = Nothing
Exit Function
End Function


is it possible to change the default primary key error message that appears when a duplication of the primary key is made in a form?? in other words how can i make those error messages more user friendly??

thank you

hey guys. i have a field by the name of ISSUE. i am using this afterupdate event procedure
which is as follow:

Private Sub ISSUE_AfterUpdate()
If Not IsNull(ISSUE) Then
ISSUE = Format(ISSUE, "000000")
End If
End Sub

if a user enter like (23) in my ISSUE field my procedure will run and it will replace
the number by (000023) to make it a six digit. i wanted to create a primary key so then a user cannot enter the same number again
or cannot duplicate a record but i cannot because primary key cannot take a null value. Can you all tell me anything to add in my procedure so that once the number is entered like (23)
another user cannot enter (23) again in the record.

I have been asked to update this database with new records. the problem is i have been given records that have two sof the same values for the prinmary key.

Som i decided to remove the primary key. this meant removing the relationship all ok.

But when i tried to put the relationship back agin it would not enforce referential integrity. It vchanged from a one-to-many to an inderminate relationship. I want to enforce referential integrity so i put the primary key back obviously having to get rid of the duplicate record values.

Is there a way i can get around the inderminate problem above?

Hi everyone,

Hopefully someone can help me.

In Table 1 - I have a set of contracts that have a unique number. However, although the primary key is the contract number - these numbers can be duplicated as long as their start and end dates don't overlap. So my question is, how do I make the Primary key the contract number plus the start and end date? Is this possible?

I know I can select multiple fields for the primary key, but this has a problem...

Here are 3 examples of how Table 1 could be constructed and the Table outline...

Table 1
Field 1: Contract Number
Field 2: Contract Start
Field 3: Contract End
Field 4: Contract Details

Contract Number/Contract Start/Contract End/Contract Details

001/01-Apr-07/31-May-07/Items Cost £1
001/01-Jun-07/31-Jul-07/Items Cost £2
001/01-May-07/30-Jun-07/Items Cost £1

Now, if I select Contract Number/Contract Start/Contract End as the primary fields, the above would not create any conflicts. However, the third record overlaps the first two records date barriers - and I don't want that to be possible.

Any new record's start date MUST be after any existing record with the same Contract Number's End Date. Also, any new record's end date MUST be before any existing record with the same Contract Number's Start Date.

In Table 2 - I have a list of orders that relate to Table 1. i.e. Each order has a contract it relates to. How do I make sure that in a query, the order number picks up the correct contract for the corresponding date.

For example...

Table 1
Field 1: Contract Number
Field 2: Contract Start
Field 3: Contract End
Field 4: Contract Details

Table 2
Field 1: Order Number
Field 2: Order Date
Field 3: Contract Number

My query would pick up Order Number, Order Date, Contract Number, Contract Details.

I think what I'm looking for is validations in the fields. But I'm not entirely sure how to implement those.

I hope I'm making sense...

Thanks again for any assistance,


Not finding an answer? Try a Google search.