Form Reporting Null Value In Primary Field
Aug 7, 2005
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
View Replies
ADVERTISEMENT
Nov 30, 2012
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.
View 1 Replies
View Related
Jul 26, 2006
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
View 5 Replies
View Related
Sep 23, 2011
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 Related
Mar 12, 2006
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
View 8 Replies
View Related
Aug 28, 2013
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 14 Replies
View Related
Jun 5, 2015
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 Related
Nov 5, 2012
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]....
View 5 Replies
View Related
Jul 21, 2006
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
View 2 Replies
View Related
Oct 7, 2004
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!
View 6 Replies
View Related
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?
View 5 Replies
View Related
May 16, 2014
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.
View 8 Replies
View Related
Nov 11, 2014
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.
View 14 Replies
View Related
Apr 22, 2012
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]
View 2 Replies
View Related
Mar 6, 2014
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.
View 9 Replies
View Related
Jun 19, 2013
Using Access 2007.
I have a table "StoreInfo" that stores the store number, store address, etc as text fields. I then have created a query gathering this data, specifically store number and address as separate fields. When I run the query, the store number and address are correct. When I use that same query in a report, I have one specific store where all data appears on the report correctly except the store number. The store number is "007" but appears on the report as "137". I was able to correct the problem by changing the store number to eliminate the leading 0's.
Since the field is a text field, I just assumed I would not have any sort of problem with the leading 0's. Once I changed the store number to 107, the problem disappeared.
View 3 Replies
View Related
Sep 23, 2014
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?
View 3 Replies
View Related
Nov 16, 2007
I think the title pretty much sums it up....
I have a query where data is first sorted by user input; first field's criteria: [fieldname], then by another field's criteria: Is Null.
I know there are records containing null values in the second field, as I have run a select query with the criteria: Like "*", to make sure they are null, and not zero-length-strings.
The query is refusing to return any results...
Any ideas?
View 10 Replies
View Related
Dec 6, 2012
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.
View 6 Replies
View Related
Jun 10, 2013
Trying to run a query using criteria to populate the query by looking at information from a field on a form, if from is closed I need that criteria to look at the table and return all date in table.
View 14 Replies
View Related
Mar 27, 2014
I have the following code that I need to modify:
Code:
Private Sub ChargeReport_Click()
On Error GoTo Err_ChargeReport_Click
Dim stDocName As String
stDocName = "Charges_Report"
DoCmd.SendObject acReport, stDocName, acFormatPDF, , , , "Charge Sheet"
[Code] ....
Currently, this code opens an input box that accepts the ID number for a particular record. Then it attaches a specific report for the selected record to an email. It works fine for this purpose.
I want to modify it so that it attaches one of several different reports depending upon the value of a [Staff_ID] field in the selected record.
I've tried a number of different solutions using an InputBox to get the record ID along with an If/ElseIf/Else construct that evaluates the [Staff_ID] field in order to determine which report to attach to the email, but I cannot find my error.
View 4 Replies
View Related
Aug 30, 2013
I'm using a combobox to select a record based off my primary key field, and then a separate button to print that particular record.
When the form loads, it has the first record selected but the combobox is empty so that if I press my button it will still print the first record.
when I press the combobox my list of entrys in the primary key field shows up and the combobox is no longer blank.
How do I show the first primary key field in the combobox when the form loads?
View 6 Replies
View Related
Aug 8, 2014
I have a split form on my database where I have my combo boxes bound to a primary key but they display the description field so it's much easier to understand. Is there a way to do this with option groups? In other words, I would like the datasheet part to display the option group labels rather than the values so it is easier to understand.
View 1 Replies
View Related
Sep 22, 2014
I have a form called PAF_Assignment and on this form there are many textboxes for editing. After all fields are entered, the user clicks on a button and this button populates the PAF_Issued_Date field with the actual date.
Then I want to disable (enable=False) all fields on this form once there is a date on PAF_Issued_Date as I don't want any user to make any changes after submitting it, although the user would still be able to read the information submitted.
I have tried the following...
PAF_Assignment Form AfterUpdate Event
If Me.PAF_Issued_Date=Not null Then
Me.FieldName1.Enabled=False
Me.FieldName2.Enabled=False
Me.FieldName3.Enabled=False
[Code] ......
However this is not working, there is no error message or anything but the fields remain enabled with the date...
View 3 Replies
View Related
May 3, 2015
The code below always displays opens form even when field being tested is null.
Private Sub Assign_Classes_Click()
On Error GoTo Err_Assign_Classes_Click
Dim stDocName As String
Dim msgboxstring As String
Dim stLinkCriteria As String
Me.name_found.Requery
[code]....
View 14 Replies
View Related
Sep 15, 2013
I've been trying to populate a field in a form with information, but only if the field is blank.
What I am trying to do is this.
I have a continuous form displaying all of our principals, however when our principals are on leave I want to populate the field with the "acting principal". I have set it up so the field grabs the acting principal first but it is blank for several schools. For these schools where there there is no "acting principal" I want it to grab the substantive principal automatically and populate the field.
I have tried Is Null, Is Not Null, NZ, IF, Where and Then statements but to no avail.
View 10 Replies
View Related
