I am building a database to capture monthly statistics on a number of items. I want to ensure that users don't enter statistics for the same item for the same reporting period. I found the following instruction, but can't make it work:
It suggests that I create multiple primary keys in the table
When I do it, it comes back with an error: Index or primary key cannot contain a null value.
I am trying to create a one-to-many relationship between these two tables. I want to be able to access the 3 fields on the [Processors] table within reports based on [AllItems]. [AllItems] is a listing of account activity where the [AccountNumber] repeats. I have every field set as the "Primary Key" on [AllItems] as that is the only way to avoid importing duplicate data. I am getting the error: "no unique index found for the referenced field of the primary table"
View 3 Replies View RelatedI've designed a DB in access which has a BackEnd and 2 FrontEnds (one person insert all the records and the others just keep inserting infos till the process is finished.The DB has 12 tables and we used it for about 6 months without having any trouble but recently (2 weeks ago) i've add 3 new tables and then related them to one table that already exist.
The DB was running smoothly for a week after the changes but last monday (09/15) the "Record is deleted" appeared. I've compacted and repaired an the following errors descriptions appeared:
ErrorCode: -1017
ErrorDescription: Record is deleted.
ErrorTable: tblFatura
ErrorCode: -1053
ErrorDescription: Index or primary key cannot contain a Null value.
ErrorTable: tblFatura
ErrorCode: -1630
ErrorDescription: You cannot add or change a record because a related record is required in table 'TblExpense'.
ErrorTable: tblFatura
I've restored the file via IT using the Backup2 days before the error occurred but after 30 minutes the same error appeared! I dont know if it is related to the new tables that i have add or no?
i have this form in microsoft Access which contains the fields refno name cpr and o combo box to choose the payment and add button , refno is the primary key....
when i open the form the ref no automatically appears and when i fill in the fields and choose one of the chosses from the combo box i get this message error Index or primary key cannot contain a null value
where is the eroor exactly?
any help plz
thankx
I am currently trying to insert a record into my database. The fields in my table are RecordID, BillNumber, CongresspersonID, and Vote. RecordID is the primary key and its data type is Autonumber. It says the primary key can't be null, but it's not! It wouldn't let me upload the database because it says it's too big. This is my code:
PrivateSub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.ClickcongressCommand.CommandText = "Insert into votingRecord(BillNumber, CongresspersonID, Vote) Values('" & ComboBox1.Text & "', '" & TextBox1.Text & "', '" & ComboBox2.Text & "')"
Dim numOfRecordsChanged AsInteger = congressCommand.ExecuteNonQuery()
[code]....
I am trying a to build a slot booking database in which users will be able to book slots (ranging from 1-30) on a particular day for a specific site (location).
When trying to build the relationship between slot in tbl_available and slot in tbl_appointment i get the following error "No unique index found for the referenced field of the primary table" the same error pops up when trying to build a relationship between site in tbl_available and site in tbl_appointment.
I need both relationships to be 1 to many.
I have a form with a tab control. On the 2nd tab, I have a subform. This subform is linked to the Primary key of the Mainform. They both use the same query. when this form loads, it goes to a new record. I can update that record and go to the 2nd tab to enter more information for that record, and it works fine, however, if i then go from an existing record to a new record, i can enter all the information on the first tab, but when i go to the 2nd tab, i get Index or Primary key cannot contain null value. If i go back to the first tab, then right back to the 2nd tab, everything works. Why is it doing this?? please help urgent. here's my code:
Tabcontrol On change:
Code:On Error GoTo Err_TabCtl0_ChangeIf IsNull(VerificationNo) ThenElseDoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70Me.RefreshEnd IfExit_TabCtl0_Change:Exit SubErr_TabCtl0_Change:MsgBox Err.DescriptionResume Exit_TabCtl0_ChangeEnd Sub
Create new On Click:
Code:On Error GoTo Err_NewVerification_ClickIf IsNull(VerificationNo) ThenElseDoCmd.GoToRecord , , acNewRecInboundQuestions!lblQ4bExplain.Visible = FalseInboundQuestions!Q4bExplain.Visible = FalseInboundQuestions!Q5Explain.Visible = FalseInboundQuestions!lblQ5Explain.Visible = FalseInboundQuestions!txtQ10.Visible = FalseInboundQuestions!Q10.Visible = FalseInboundQuestions!lblPrevPlan.Visible = FalseInboundQuestions!PrevPlan = FalseInboundQuestions!Page1.SetFocusVerificationNo.SetFocusNewVerification.Enabled = FalseEnd IfExit_NewVerification_Click:Exit SubErr_NewVerification_Click:MsgBox Err.DescriptionResume Exit_NewVerification_ClickEnd Sub
I think maybe what i need is an event on my subform that navigates the record on the subform to the same record that is on my main form. I think my subform is staying on a new record. Please help me ASAP!
I am running up against an error "Index or Primary Key Cannot Contain Null Value". In the dialog box it doesn't give the RTE number just the description, OK & Help buttons. After some searching the description matches RTE 3058.
I have identified why it's happening and through a process of elimination it's happening in the Before Update event of a subform. I have tried to catch it in an error handler so I can have a bespoke dialog box to inform the user exactly why it's happened and then undo, but it just goes straight to the dialog box.
How I can catch this?
Access 2010.
I have a .csv file that I am trying to import via:
External Data > Text File
My Delimiter is a | . My Text Qualifier is a "
I keep getting the error Index Or Primary Key cannot contain a null value.
However, I have manually gone through and removed all Indexes. There is also no primary key defined:
1. Is Access trying to identify a primary key for me?
2. Since I have not defined a primary key how can there be a null value in it?
I have tried to:
1. Add an additional AutoNumber Field (Indexed=True, No Duplicates) but this did not seem to fix the issue.
2. To Not import the fields that I suspect are being categorized as a primary key.
I'm trying to record a payment, but I keep getting this error upon completion of my form:
Index or primary key cannot contain a null value.
I've checked the data types and relationships and they seem to be fine.
Here are links to my Access files (2007 & 2003)for your reference:
[URL]
I've inherited a rather messy database which and I need to split it in order for us all to be able to enter data at the same time without problems however I get the following message..Index or primary key cannot contain a Null value..It happens at the table that contains the majority of the data (typical) but I cannot understand what could be causing it. I've check Null Primary Key field and removed all of the "required" statuses out of the fields but still no luck.
I tested on a backup database from a couple of days ago and it worked. Only difference is I've added a few bits and bobs since then and 1 field in the table it's stalling on but this field doesn't contain any null values either and I've tried deleting that field in my test database but made no difference.
Hi, I have 7 tables in my database and 1 form corresponding to all the fields in these tables (linked by a query by recordsource). My problem is that the form will only show a record if ALL 7 tables have manually had the primary key entered (not good when i have information that needs to be added at different times). How can I make it such that if I create a new record on the form that all primary key fields will be updated, and this record will be present every time i open my the form??
Cheers
Tania :o
Hi,
I'm not sure where to post this, maybe VBA or Forms would be better, but I think it's fundamentally an index problem, so I'm posting here. I've searched both the net and here, but most of the questions are about preventing duplicate records.
I want to prevent duplicate records but save changes to existing records.
I have a form based on a table that has a 3-field-unique-index (but not the primary index, which is autonumber). I've written a function to check for existing records with the given combination of the three fields, as long as all have data (it bails if any are null). I'm calling the function from BeforeUpdate for each of the textbox controls. I was also calling it from the form's BeforeUpdate, but commented it out as it seemed redundant. I have other code there verifying that the user wants to keep the changes.
I'm trying to save the users from getting all the way to the end before discovering there's a problem. To test it, I added then deleted one character from one of the three relevant fields in an existing record, and bing, up pops my alert that I'm violating the index. Then I can't leave the field, I'm stuck there because I can't save the record.
I want it set up so that other fields in the record can be changed/updated AND so that a new record for the same person can't be entered (a dupe).
Oh, and I'm running into all kinds of problems with acCmdSaveRecord not being available. I feel like an idiot that I haven't been able to make sense of the posts about that. I can just comment it out and let Access save the record when it closes the form. But my bosses really want the users prompted about saving changes.
Any thoughts or suggestions or insights greatly appreciated. I really tried to search this out, so any tips for better searching are also appreciated.
Here's the code for the function:
Public Function CheckForClientDupes()
Dim response As Variant
Dim strFamID, strLName, strFName As String
Dim strFilter As String
If IsNull(Me!FamilyIDNo) Or IsNull(Me!strLastName) Or IsNull(Me!strFirstName) Then
Exit Function
End If
strFilter = "[strFamilyIDNo] = """ & Me!FamilyIDNo & """ And " & "[strLastName] = """ & Me!strLastName & """ And " & "[strFirstName] = """ & Me!strFirstName & """"
If DCount("*", "tblClients", strFilter) > 0 Then
response = MsgBox("There is already a client with this combination of FamilyID, Last and First names in this database. Would you like to Continue Anyway (Yes) or cancel data entry and Erase Your Changes (No)?", vbYesNo, "Duplicate Client Alert")
If response = vbYes Then
' DoCmd.RunCommand acCmdSaveRecord
Else
response = MsgBox("You have chosen to cancel your changes. Your changes will be erased.", vbOKCancel + vbQuestion, "Data Entry Cancelled")
If response = vbOK Then
Me.Undo
Else
Exit Function
End If
End If
End If
' Me.FamilyIDNo.SetFocus
End Function
To prevent duplicate records, I use multiple fields indexes, which worked fine until now. I learned that each "empty" fields are consider unique by Access, so not the best in an index to prevent duplicate records. I managed to have it worked using the default value property to give each "empty" records the same value.
Now my problem is that I have a date field which is optional, but I need to use it as a unique identifier in an index. I could again use a default value, but since it has to be a date, I find it confusing. I would rather have a default value of "No date specified" or "-" or even better, "". Unfortunately none of those works with date fields.
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?
I have a database in Excel that contains farmers who can be identified by a Unique identifier: MZ-01-0001. The registration details for these farmers are not always up to date. Now when I try to append the data for the farmer with MZ-01-0001; with the primary key set on the Unique identifier, Access does not want to do it since there is already a record in the system. When I remove the primary key it doubles the record, which is also not what I want. So I am wondering what I need to do to append the record while keeping the primary key intact.
View 4 Replies View RelatedI am importing data from excel and I want to put a primary key on a column but it doesnt let me saying "primary key cant contain a Null value", coz I have a "NO" or "O" value. But i dont understand why is it a problem as I have specified the field as text not number.Also is it necessary for the table to have a key ?
View 14 Replies View RelatedI have just inherited a database that currently has no primary key set up. I wanted to add a two part primary key, but have run into a couple problems. The purpose of the database is to keep track of parts that have come back for repair. I wanted to set the key up to be a combination of the call number and the part serial number. I should always have a serial number, but the serial number can be in the table more than once, as it could have needed repaired more than once. I will normally have a call number for each record.
However, the call number could be repeated more than once, if more than one part came in on the call. The combination between call number and serial number should always be unique (each time a part comes in, it should be on a different call). My problem is arising, when a part is just pulled off the shelf of the factory and sent in for an upgrade. In a case like that, there is no call number. However, a serial number will never come in more than once without a call number.
So the combination of call number and serial number should still remain unique. When I try to set this up in the database, it tells me that the primary key cannot contain a null value (referring to the times that a serial number comes in without a call number). I want to get this set up so that the people entering data in the database will not be able to enter the call number, serial number combination more than once. How can I do this if one part of the primary key can be null?
1. I have a database (see attached) with three tables all of them with the same fields. The first three are numbers (InCo_No, Proto_No, Year_No). Each of these fields (numbers) can be the same in the other table(eg. Year_No), but the combination of the three cannot be.
How can I prevent the entry of a duplicate combination of these three fields?
2. I want to have a form to fill the three tables separately, depending the values in the other fields.
How can I do this?
Access 2010..One organization that we work with provides us with a block of numbers for each of the two types of contract products we order from them; we do order non-contract stuff from them also.The block of numbers are the same (i.e. 20000 to 30000 this year) for each of the two products. This means that each product can have the number 20000, for example. We call this the Tracking Number. If it is one of these products, we need to select the Contract Number.
For all other one off orders we have with them, we assign our own Tracking Number starting with 00001. This Tracking Number cannot duplicate unless it is one of the aforementioned two products.Both the Tracking Number and Contract Number are in the same table. The user selects the Contract Number from a form (connected to the Contract Number table that has all the details on the contract) and the Contract Number is populated in the same table that has the Tracking Number.Each order must have a Tracking Number (no null)..Not all orders need a Contract Number (null okay).The Tracking Number and Contract Number combination cannot duplicate.I tried setting the primary keys to more than one field in the table, but they cannot have null values.
If not... I have been working on Plan B.... an AfterUpdate on the form (either the form or a field... don't know yet) that looks at a query that only has results if there are duplicate values.
I have a beginner Question....How can I set an unique Index on a field in a table if all the four fields are using duplicate values?
View 3 Replies View RelatedCan't figure out why I can't create a one to many relationship between two tables:
TableA uses a composite key as its primery key (field1, field2).
The table has a unique index comprised of these keys. The index even has a name. The table also doesn't contain any duplicate information, so the fields comprising my Primary Key are unique. The table I'm joining tableA is unpopulated at this time.
TableA
FIELD1 FIELD2
99999 ABCDEFG
99999 HIJKLMNO
Any help will be greatly appreciated.
Okay, anyone have an idea what's happening here?
It seems like it -should- work to me but it's not which leaves me to wonder where I'm missing something. :confused:
Any help'd be appreciated. :D
Thanks,
~Chad
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.
If anyone's got any ideas, I'd be v grateful!
Tnx
Ally
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.