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?


Sponsored Links:



Hi,

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.
regards
bijon




Hi,

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:
AB1

(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,

Aidan




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?

Thanks,
Gary




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




Hello

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

Craig




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.AddNew
RS![HospitalNumber] = LastPatientRecord
RS.Update

...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?




Hi,

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?

Thanks




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 thanks.....it's giving me a headache




Guys,

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?

Cheers

Nick




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.Current_Assignment=2))

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?




Hello,

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 tblContacs...how 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?

Thanks!




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
Me.Undo

'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
Orders
Customers
Principals (Suppliers)
Employees

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.

David


BTW, this thread is an offshoot of a previous thread at http://www.access-programmers.co.uk/forums/showthread.php?t=186837. 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.