I 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 RepliesI 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?
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.
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.
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.
Thanks
I want to use my own primary key but I keep getting this message. Index or primary key cannot contain a null value.
View 14 Replies View Relatedi 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 recently acquired a database that is already established. When entering new date the primary key is null. I've looked at the properties and the best I can figure is it has something to do with a missing property called New Values.
View 12 Replies View RelatedI 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]....
Hey guys,
Anyone know how to go about catching the "Index or primary key cannot contain a Null value." message box and perhaps showing a custom message instead?
Thanks in advance,
Bob
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]
Hey Guys
I'm having abit of bother with a database I'm fooling around with.
Basically it has 4 main tables at the moment.
Personal Details
Participant ID (Autonumber)
etc
Training Details
Participant ID (Number)
Attendance ID (Text Field) Auto generated by expression =[Participant ID]&[Course Code]&[Level Code]
etc
Attendance Records
Attendance ID (Text Field)
Review ID (Text Field) Auto generated by expression =[Attendance ID]&[Month]
Reviews
Review ID (Text Field)
etc
These tables are linked by standard one to many relationships. (http://www.flamingbird.com/relationship.png)
Everything worked fine until I decided to Auto Generate the Attendance IDs and Review IDs to save confusion amongst those administering it.
Now it reports when I try to add a new record to the tables with the Auto generated IDs that:-
Index or Primary Key cannot contain a null value.
It appears the auto generated ID's are being mistaken for NULL values!
Anyone any ideas as to why this is happening? This is really my first time trying anything this complex, every other database I've done relied heavily on the data inputer.
Oh and its Office 2003, but the database is in 2000 format cos not all the office has office 2003 just the training end.
Many thanks
Dazzy
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.
I'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 4 supplier tables with identical field names but different databases in sql.I want to have them all in one table and only want the information for reference i do not want to edit any of the data.I need to create a new primary key number for the complete table. The data will need to be refreshed as the data comes from MMS Sage looking at company PLsuppliers.
I have tried a linked SQL union view- but this has no primary key.I need to keep the Primary key consistent as the data saved in other tables will use this field.The overall goal is creating a Purchase order system and this list will be my complete supplier list.
Hi,
I need to create a table with 2 sets of different information both referring to job types. the problem is that i need the autonumber which created the job number to scale together. so both tables have a primary key of "job number" and i want the autonumber to only ever use 1 number in both tables.
e.g.
job type 1 - autonumbers - 1,2,3,4,6,7,9,10
job type 2 - autonumbers - 5,8,11,12
is there anyway of achieving this?
Any help greatly appreciated
I have mapped everything out and built it in Access (but haven't entered any data yet). My question relates to structure. The data is based on artifacts, each piece is recorded with an automatically generated primary key in the main table. There are a number of features (type, decoration etc.) that are documented in related tables (included in/linked to the main table with foreign keys). Aside from these (the features I will query on), each piece has a number of characteristics to be recorded (size, colour etc.). Because of form/table size, I have broken the information into three tables (a main table with ID details, a second with Sizing and Dimensions, and a third with Materials etc.).
My question is: Can the main ID primary key from the main ID table also be used as the primary key in the 2 other basic information tables, with these 2 linked to the main table in one-to-one relationships (so it's an auto-number in the main table and an integer in the other two). This to me would be much easier to navigate than to have one long giant table with all these basic details.
I have created a main data entry form with a "navigation form". 5 tabs/pages. Tab/Page 1 = main ID. 2 and 3 are the two broken apart tables mentioned above. 4 and 5 are forms based on related tables that have their own subforms included. Both 4 and 5 are based on tables in which the main ID primary key is entered as a foreign key (in one-to-many relationships with the main table). Since this was done with a navigation form (instead of adding each of the 4 as subforms), will it still allow me to enter all relevant details for one piece into each tab/page, and save it all together automatically (whenever I enter a new piece into the main ID form)? Will the primary key be automatically generated throughout, or do I have to enter it manually in each tab/page?
My Access 2010 skill level is intermediate.I have a table with 4 fields.
EmployeeID (Primary Key)
LastName
FirstName
SupervisorID
The problem I have is that in the Supervisor field, it identifies the supervisor by his/her EmployeeID. I would like to create a query or merge tables so that the SupervisorID can look for a matching value in the EmployeeID field and return the last name/first name.
Composite Primary Key in Ms Access 2010. I shall try and explain my tables in detail. So my first table is "Client Database" where the field "Buyer" is primary key. This is connected to my next table "Project Database" which has fields such as Work Order #, PO #, PO Date , Buyer etc where Work Order # is the primary key. Finally "Projects Database" is connected to Order information. Now the problem is until now, I always had 1 PO # for each Work Order #. However now I am facing a situation where my client has clubbed multiple PO #'s for the same Work Order #. I need to trigger the Primary Key Violation only if both the Work Order # and the PO # are the same.
View 8 Replies View RelatedI have constructed a DB for work to record accidents, however I am running into problems. On each form I have a button that Saves the data closes the current form and opens the next form, depending on what data was entered. I have this ruuning fine using this code,
Private Sub SaveRecord_Click()
Dim NextForm As String
If Me.Status = "Pupil / Child" Then
NextForm = "StudentDetail"
Else
NextForm = "EmployerDetails"
End If
DoCmd.save
DoCmd.Close
DoCmd.OpenForm NextForm
End Sub
Private Sub SaveRecord_DblClick(Cancel As Integer)
Dim NextForm As String
If Me.Status = "Pupil / Child" Then
NextForm = "StudentDetail"
Else
NextForm = "EmployerDetails"
End If
DoCmd.save
DoCmd.Close
DoCmd.OpenForm NextForm
End Sub
The primary key of this form is "PersonKey" and the two forms taht could open, each have"PErsonKey" as a foriegn key. However the key does not get brought across into the next form and the save function does not appear to work on all forms... :confused:
I put another button on this form using the control button wizard, and it gave me this code,
Private Sub Command23_Click()
On Error GoTo Err_Command23_Click
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Exit_Command23_Click:
Exit Sub
Err_Command23_Click:
MsgBox Err.Description
Resume Exit_Command23_Click
End Sub
However when I run this from the form, it tells me that the "PersonKey" field needs a value.... :confused:
I have three tables.
EAch has a field called ID that is an Autonumber that is the primary key.
When I enter data via a form each record has a different ID across the three tables.
Does this mean they are not linking up correctly?
I have attached my databaset if that helps.
Thanks.
Hi guys,
I am having problem with a query.
I have to tables. One named wire and the other named Input.
As my title says there is not unique ID for the tables to relate.. the only thing to relate is one ID where is not unique for all the rows
In the Wire Table I have 3 rows using the same ID, lets say 123.
In the Input Table I have 1 row using the same ID as above, i.e. 123
I need to select a couple of fields from Wire Table and 1 field from Input Table
The only relationship I have is the ID 123.... since the ID in Wire Table is repeated in 3 rows.... I would like to use the same value from the Input Table to be the same for the 3 rows in the other table.. Below is an example:
Wire Table:
ID Group
123 A
123 B
123 C
Input Table
ID Description
123 Analog
My wanted result would be
Group Description
A Analog
B Analog
C Analog
I tried using the Left join or Join function, but somehow the resulting table has many duplicates of the rows...such as:
Group Description
A Analog
A Analog
A Analog
A Analog
A Analog
B Analog
B Analog
B Analog
B Analog
...
Any Idea on whats going on and how can i solve this problem????
Thanks in advance. Your Help is much appreciated
In a many-to-many relation, one create joint table and use two columns of both ID's in a Composite way. This what the text books recommend.I had a Form/subform to view the relation between the two table. I tried to add to the subform a new relation Access did not allow me to do it. I can go to the joint table in datasheet view and ADD to my heart content observing the unique condition.
If I change the design of the joint table to auto as PK and the two ID's with no PK in either, I can update from the subform .WHY the Composite Keys do not allow me to update the joint table by adding new records in the subform ???