Tables :: Two Part Primary Key With One Null Value
Jun 25, 2013
I 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 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 ?
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.
I have a calculated field (Bhours) with the following IIf function:
=IIf(Fields!BDate.Value<=Parameters!Pdate.Value and Fields!EDate.Value>Parameters!Pdate.Value,Fields!H ours.Value,0)
It works perfectly, but I don't like the fact that it has a zero for the false part as I like the field to the blanc instead of filled with a zero.I can't use a space as I need to calculate with this field later on. I tried using "", but then the field displays #error".
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 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?
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.
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()
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.
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 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.
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 what I think is a difficult problem to overcome...
I am designing a form to create an invoice. The user will select a workstream and a date range in form frmInvByHrs. Within this I want two sub-forms, one is frmInvByHrsTsht and the other is frmInvByHrsBill. I want the first one to display all the staff and their hours done, and the second one to be in data entry mode where you can enter the hours you want to bill. Each sub-form is based on a separate query.
Is it possible to do this? ie. to have one sub-form in data entry mode, and the other not? It seems to me that the data entry mode is controlled by the MAIN form regardless of the sub-form settings!
If this is not possible, do you know how I can acheive this?
I am pritty new to access and have been tasked with creating a database ! I have two tables one called main which has 26,000 claims (listed under there part number). The other table called part numbers is a list of all the part numbers in the main table but with no duplicates (126 part numbers) I need to somehow count how many times a part number apears in the main table. Maybe display a count in the part number table as a extra field ? then i could put this field into the form i created to browse the parts numbers (to show how many times the part number appears in the main table)
Hello everyone. I'm pretty new to access and need some advice on table structure for a new project I've been given.
One of our customers sends us an Excel spreadsheet each week containing their order. Currently, someone formats the spreadsheet, prints it out, and manually picks out the products we need to ship. I want to import this into an Access table. Basically, the sheet with some data examples looks something like this:
There are usually 30-40 records on this list. The COMPONENT field is actually a part kit. We have tables in a Excel sheet that list the parts in the kit as well as the quantity. For example.
What I basically want to do is run a report based on a query where each page has a contract number (one for each record) with matching ship date and location information and a list of parts needed on that kit.
I'm a little confused by the COMPONENT field linking to more than one part. In the little experience I've had with Access, one record was always linked to just one other record in another table. Also, I want the Contract Number, Location, Ship Date, and Kit # show up once at the top and the complete kit show up below.
I would appreciate any suggestions as to hot to set up my tables. We have about 30 different kits. Should I have a separate table for each or a huge table? Also how will I make relationships between the main table and the part table/tables? Am I going to have to have the kit # as a field in the part table and list it for every part/quantity record? I want to just list the description information (location, ship date, etc...) only once while listing all the parts and quantities for the kit.
I've only been using Access for a short time, so this might be a really simple question. I just need to be pointed in the right direction, so I can set the system up the right way. Any information would be greatly appreciated.
Could you answer a diabolical question? I am trying to track the changes of a record in a table when I have imported a text file.
I will import a txt file everyday and to that end I have wrote the code for the insert of new records into customer table and customer history tables and then the updates into the customer table.
However I will only be able to capture the updates in tables as there will be multiples. I understand that Jet can't capture the updates at the table level (no triggers).
So the diabolical question, is it possible to capture the updates only in tables using code??
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.
As you see its a composite key. Now each time a new record is created i want the ConfNo to autoincrement within its ConfYear. I used a byte as its not usually more than 4. Autonumber does not work as part of a composite key i found out.
2014 1 2014 2 2014 3 etc
And revert to 1 when ConfYear input changes to 2015
Is there a simple way to do this with say, a macro, or is the only way with VBA and DAO?
I am creating a database that tracks the selling of products amongst other things.
The user will enter in an order and may delay invoicing until the customer approves the quote - at which time the order is turned into an invoice.
In the transactions table the OrderID, ProductID and CustomerID constitute a composite key.
I want to be able to view the order and change it by either deleting or adding ordered items (obviously prior to invoicing) but because the ProductID is part of the composite key I cannot delete a line item.
Perhaps the solution is to remove ProductID from the index or is there a better way?
