I am trying to add a "T" in front of an article number and write this new number to a table.Access cannot find the new Article strArt because it does not exist. When I put a msgbox after "if .nomatch then" it correctly shows the message. Yet it refuses to create the new record because of a key/index violation. Apart from an index on the recordkey, it has an index on Artno, both indexes do not (obviously) want duplicate values.
Here is the code:
Private Sub btnCopyT_Click()
Dim db As Database
Dim rstArt As Recordset
Dim strArt As String
Set db = CurrentDb
strArt = "T" & Me.frmNomenclatuurSubfrm.Form.Artno
I have 33,099 records in a query, that i'm importing into a table. (don't bother witht he semantics, it's from a linked dbf file) The table does not have a primary key. Given Three Fields (out of 74):
Item_ID Title AltTitle
With the table populated with all the records, I highlighted those three fields in Design View of the table, and told told access to make all three of them the Primary key. Upon attempting to save the table, I got an error message saying that data in the table violated the primary key unique fields rule or what not. So I wanted to make a query to determine where the error occurred. I could not off the top of my head figure out how to select only the duplicated records in a table, so instead, i figured if they violated the Primary Key unique field rule, there should be duplicate entries. so I did this:
select distinctrow item_id, title, alttitle from tbl_Table;
I got 33, 099 records returned on the DISTINCTROW. Strange as that was, I deleted all the records from the table, set the primary key as I wanted it, and then repopulated the table via my sql insert into commands. This time the table reports only 33,093 records, meaning 6 records somehow violate the primary key unique index, but don't violate a DISTINCTROW call. How can i find them to determine how they are violating the primary key unique index? thanks Jaeden "Sifo Dyas" al'Raec Ruiner
I created a subforum with an updateable query, and this works. I can view the results of the query, modify them and add new records.
However I shortly realized that once I deleted a record (either by some VBA code, or by SQL) that I could not re-add the same record through the subform.
I've made sure that the record is deleted, so there is no duplication of the key. I've made sure that Index is Duplicates OK, so there is no duplication issues there.
Hi - it's been a while since I did much access and am trying to help someone out and have got into a pickle! A quick job has turned out to be a lot more complicated! Plus I'm having to use *spit* Acc 2k.
When I go into frmHighCostDrug (from the "add new drug episode" button on the frmPatient) I often get the message "The changes you requested were not successful because they would create duplicate values in the index, primary key, relationship etc etc."
Now, I didn't think I'd forgotten that much. I've got primary keys, values in the table set to Null, relationships set and ref integrity set and cannot for the life of me see why this is happening.
I know though, that one of you will say "oh it's that" and point it out and I'm going to feel completely stupid, but at the moment, I don't mind! I really can't figure it out.
I've attached db - it's in Acc2k and I tried to do a copy Acc'97 for those who don't have it but it caused no end of problems. Came up with messages that this form contains data that Access doesn't support!!!! ??? :confused: If I can sort it I will.
This is a real mind bender. I am running a2k. I am merging two databases. table1 is in the backend database. table2 is linked to the second database. I run an append query to add table2 entries to table1. The append fails for 96 recs saying key violation. Turns out I can rerun the append if I drop the zip code field. I then try to manually change the zip code and receive the duplicate values in index, primary key, or relationship.
I ran a compact & repair the databases, still no go. I can't edit the value of the zip code. I import the table to a new database. Still can't change the zip code. I drop MANY of the indexes. Even the index on the zip code field. Run compact&repair. still getting the index message. I even re-imported to a new db again. Still same error message.
I may start again by importing JUST these two tables to a new blank db and see if I get the same issues. I'll keep you posted...but maybe there is someone out there who has seen this error before.
fyi-if I enter a new record, the autonumber field correctly increments to the next available autonumber field. (I say this because of another thread on this matter w/ autonumber fields and bug in A2003)
I was using the following code on a field (ItemCode) that was indexed to prevent duplication of records. The intent is that the user will get an error message that a duplicate exists before they enter all the data for the record and get the built in error message that Access 2003 provides when an index violation has occurred.
Code: Private Sub ItemCode_BeforeUpdate(Cancel As Integer) Dim Answer As Variant Answer = DLookup("[ItemCode]", "tblQuestions", "[ItemCode] = '" & Me.ItemCode & "'") If Not IsNull(Answer) Then MsgBox "Item Code already exists" & vbCrLf & "Please enter unique Item Code.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate" Cancel = True Me.ItemCode.Undo Else: End If End Sub
Now, the index for this is based on two fields (ItemCode and Question Group). I would like to display the same message before update but don't know how to include the second field in the syntax.
After a lot of reading and consolidating VBA codes for audit trail.How My Audit Trail Works..A module was made for a function named as "Changes" .Then inserted into before update event of a form where I will do the editing of the records.Then I made a table named as Audit.Inside this table I made all the fields I needed such as:
*AuditRecordId[autonumbered] *FormName[The name of the form for editing] *Index[The record ID of the record being edited] *ControlName[The Field being edited] *DateChanged[Date change was done] *TimeChanged[Time change was done] *PriorInfo[for the old value of data being changed] *NewInfo[For the new value of data changed] *CurrentUser[The user base on log in form that was set to Global into another module] *Reason[The reason for changing for future references]
And Here is the Function Code:
Code:
Option Compare Database Option Explicit Function Changes() Dim db As Database Dim rs As Recordset Dim strSQL As String Dim strCtl As String Dim strReason As String
[code]....
This audit trail function is valid only for one(1) form, due to the limitation of
Code: Screen.ActiveForm.Controls("SUP ID").Value
where "SUP ID" is the primary key of the record being updated/Change, so if there are Five(5) tables that needs audit trail, there will be also Five(5) forms, as well as Five(5) Function Changes namely; Changes(), Changes1(),Changes2(),etc... because all the table do have their own sets of primary Key.
Is there a shortcut, in such a way that the "rs!Index" will automatically return a value, equivalent to the Primary Key/Record Id of the record being updated/change, given that there are different updating forms for each table to be updated?
Hello I am new to this forum. I am trying to use the append query on two tables that are identical in data type, from TblA to TblB, but cannot append due to key violations for each row.
Both TblA and TablB have an autonumber primary key, although TblA does not need to have a primary key. Any suggestions as to what I should be looking for first?
I am new user of access and have encounted a key violation from an append query. I'm trying to update a master mail-out list and am having a key violation that doesn't add some of my records.
Here is the SQL vew:
Also, I've checked the properties for both tables and they match, also I have "Allow zero length" set to yes.
The table that it's being inserted into, Loan, has an Autonumber primary key (not included in the INSERT script) and a number of other fields that have no zero-length restrictions, no "Required" fields set to Yes and no Indexes. The table in question is also completely empty, there are no rows (they have all been deleted) - so I have no idea why I'm getting that "Microsoft can't append all the rows in the append query" error. It says its a key violation but I really can't see how its possible.
Wow, this thing is annoying me. I give up! I've attached the database for anyone here to have a look at. I promise there's no nasty code on it, although you should be able to see my code and hopefully pick the problem if you disable macros anyway.
I use VBA to prompt users to enter their staff number, the member's number and the book ID. The same VBA checks to make sure that it's a valid number that they're entering, that's it's actually present in the table it's being referenced from. (Command0 button. Command1 is to return an item)
It then takes these values, the current date and another date variable and inserts into the LOAN table. The loan table has enforced referential relationships with the book, member and staff tables.
All the fields in Loan (except for the autonumber PK) are not required, and have no validation formulas, zero-length is permitted where applicable.
I KNOW that the numbers being inserted are in the related tables! They're the same data type - long integers, and the related tables' primary keys are not autonumbers.
So why am I still getting a key constraint violation??
Can someone please help me??
Correction: I'm trying to attach the database, but it's too big, even zipped. Why isn't RAR accepted? Anyway, the file is hosted here: http://jellopy.com/files/newdb.zip
Hey, I am getting a primary key violation when I try to run an update query.
My primary key is a combination of two fields, ScheduleID and SchedulePage. That way for each schedule I can only have one Page 1, one Page 2, etc.
When I want to insert a page (say a new Page 2), I need to update the table so that Page 2 becomes Page 3, Page 3 becomes Page 4, and so on.
The problem is, since it starts at the bottom, when I tell it to increase the page number by one, it's conflicting with the primary key of the next record.
Any ideas? BTW, if the solution has to do with sorting, I need to make sure it's something that always defaults back to the correct sort, since users may be able to change the sort and accidentally save it. Plus, I'll need to do the same thing in reverse (delete a page).
when i import i have records deleted due to key violation, how can i get these deleted records to an error table so i know that the right record was deleted?
Hi, I have a form that consists several buttons. One is to delete a table A, then add records to several tables and eventually it will do a join and insert records to table A, and display a report. It always works while all tables reside in the same Access database. Now we are trying to migrate to SQL server but not ready to get rid of Access yet. So we exported all tables to SQL server and created linked tables. We can open tables directly from Access without any problem. It shows the contents as the SQL database But when we tried to open the form and run the same button as before, we got an error:
Microsoft Access can't delete 0 record(s) in the delete query due to key violations and n record(s) due to lock violations.
We have no idea why this happens. Most of the time, it worked the first time when we clicked on the button. But we got the message when we clicked on the same button the second time. If we clicked "Yes" then it will append new records. There will be duplicate records since it doesn't delete the existing ones.
Did any of you encounter a similar problem before? Thanks in advance!
The front end of the database has a form for each table. Once you create an Audit in table 1 you can create multiple Processes underneath this one audit. Underneath a process you can create multiple Risks...... hence a hierarchy.
I am trying to append data to table 4 - controls. I have copied the format exactly into excel. I have also prepopulated the Audit, Process and Risk data in the required tables.
The reason I want this functionality is to enable team members to populate control information (oftentimes up to 25) in excel and then upload them easily into the database.
The macro I have created will work when created a new table. but when I point it at the existing table 4 - Controls I get the Key value violation error.
I need to copy over subform contents, for which I use an Append Query (actually, I use its SQL in VBA code to pass it the right parameters).
Now, naturally, I ran into Key Violations, because taking 50 sub-records from the original they would have their own AutoNumber Keys (ID's) and I can't append them into the same table.
My question: how do I copy over the same records (appending FROM and TO the same table) but cause the table to insert AutoNumbers for the records being appended?
- I have a module which runs queries on linked sales spreadsheets, to merge them in to one Access table. - To prevent duplication of sales, the primary key merges the sales record and item number fields.
Today, it's found 103 key duplication errors, which is fine. But it's still adding data to the table. The data seems to be fields which aren't even mentioned in the query. It only does this when the query is ran from VBA.
I am trying to change the standard input mask violation error message to a personalised one. I have found this code:
Private Sub Form_Error(DataErr As Integer, Response As Integer) Const INPUTMASK_VIOLATION = 2279 If DataErr = INPUTMASK_VIOLATION Then MsgBox "There was an input mask violation in the field no!!" Response = acDataErrContinue End If End Sub
However, i would like to change the message for a number of different text boxes. and i don't know how to isolate each one, and give each one a different message?
I am building an access database for my college project and I essentially have a quotation form that when I click a button 'Convert to Invoice' it creates a new record in the invoice table and then creates new records in the invoice details table which match the quotation details table. This is working as it should but for only the first 2 customers in my customer table?
On the quote form I have a combo box which is linked to the customer table and updates the quote table based on the selection. If I select customer 1 or 2 and click 'convert to invoice' it works and opens an invoice form based on the inserted data however if I select any other customer it returns an error that the record wasn't added to the table due to key violations?
As far as I can tell I am not trying to update the primary keys in the Invoice Table or the Invoice Details Tables.
No records are being imported. I am getting a validation rule violation but I don't have any rules. The forename and surname are straightforward text boxes and the ID is an autonumber.
Code:
INSERT INTO table_candidate ( Cand_ID, Cand_forename, Cand_surname ) SELECT candidates.Cand_ID, candidates.Cand_forename, candidates.Cand_surname FROM candidates;
1. Is your append query trying to assign values to the primary key field? Could that be the source of the duplicate?
Yes but there are no duplicates
2. Do you have any other fields that are "Indexed: No Duplicates"? Any compound indexes?
No
3. Is the query assigning values to a field that is a foreign key to another table? Is it possible that these values do not match the values in that other table?
No. All child tables are empty.
4. Is there a validation rule on the table itself?
What this means.
5. Does the query attempt to assign a string with no characters in it (as opposed to a Null value) to a text field that has its Allow Zero Length property set to No?
Both fields are text and all records contain information
Required = No Zero length = Yes
6. Is there a Default Value in one of the fields that is NOT being assigned by the query? For example, a foreign key with zero as the Default Value?
No
7. Is there data that is outside the range a field can accept, e.g. an integer larger than 32767, or a Null to a Yes/No field?
The length of the text in each record is not greater than the set limit
8. Is one of the query fields arriving in a format that Access is not understanding, e.g. for a date, or for a currency?
I have a form based on a query and have written code to display a msgbox if a duplicate entry is inputted in the NHS_Number field in the form. I have also added code "me.undo" to clear the form so that it is not saved. I am looking for a piece of code that will display the duplicate record. Here is my code thus far:
Private Sub NHs_Number_BeforeUpdate(Cancel As Integer) Dim dbs As Database Dim rst As DAO.Recordset Dim x As Integer
I have a tblManualRewardIncident in there I have 3 fields I want to have my staff in put these rewards via a frmRewardIncidentEntry However im concerned over another staff member in putting the same data so I want the form to highlight on update that a duplicate is there already so cancel the data entry.
The 3 fields that need to check and match are StrataID , DateOfIncident , EndDateOfIncident these are all in the tblManualRewardIncident and appear as buttons in the form frmRewardIncidentEntry the buttons are as follows cboStrataID , txtDateOfIncident , txtEndDateOfIncident
I want the VBA to run when I CLICK another button on same form cboSQCDP..All 3 fields must match exactly to flag a issue.
StrataID , DateOfIncident , EndDateOfIncident from tblManualRewardIncident
If it doesn't find a match continue and add the inputs in these boxes to the table if it finds a match flag a message and delete the data from the form.
