"You cannot add or change a record . . . . "

Hi,

I have the error message:

"You cannot add or change a record because a related record
is required in table "Service" "

(Service is a subform off my main form.
I have a tab control, with two tabs. The second tab is for the service details form. The first tab is the actual "Service" form.

I start a new service ticket in the "Service" form, and tab over to enter the details. It links to the service ticket# (Primary key) just fine, but when I enter any data, and hit enter, I get the error message.

Even the underlying Service table is properly updated with the PK.

Please help,

Jim


Post your answer or comment

comments powered by Disqus
You cannot add or change a record because a record is required in .

Simple question (simple answer too I hope). How can I trap this referential integrity error?

I would like to create a more user friendly message.

I have a 2 tables (tblCase and tblCaseDetail) linked via CaseID. CaseID is the Primary Key in tblCase and a foreign key in tblCaseDetail. Referential integrity is enforced and there are currently no records in tblCaseDetail. I have a form (frmCase) that is used to display data from tblCase. On frmCase I have a command button that is used to open a form (frmCaseDetail) and add data to tblCaseDetail. I keep getting the following error when I try to add data though: You cannot add or change a record because a related record is required in table 'tblCase'. I'm not sure what the issue is. I have the foreign key field set to default to the value of primary key from whatever record is open on frmCase. I purposely made the foreign key field visible on frmCaseDetail so that I could make sure it was defaulting correctly and therefore I can see the Primary Key value of the record it says doesn't exist. So, obviously the record it's saying doesn't exist DOES exist. What am I missing here?

Hi! Please help!!!

I'm currently building a bookings database and have encountered an alert message that I cant seem to rectify -

"You cannot add or change a record because a related record is required in the table 'Booking Details'"

Basically - I have a 'Customer Database' form that is linked (via command button) to a 'Booking Details' form. Within 'Booking Details' I have 2 sub forms - 'Booking Quote' and 'Booking Payments'. Both subforms are linked to the 'Booking Details' form by the 'booking ref' field with RI.

I have no problem updating information in the 'Booking Quote' subform, but when I try to add information to 'Booking Payments' it states the above message.

Can anyone please advise as to how I can prevent this happening? I'm slowly losing my mind....!!!

Many thanks,

Stacey

Hey,

I'm using Access 2000 with several tables. I'm trying to change a form that allows user to enter time worked on a specific project. Once they select the Project, in another combo box they select the customer of that project. Upon submitting I get the error, 'You cannot add or change a record because a related record is required in tblTasks'.

The customer combo box is bound to the Customer and the row source is the project table, since every project is linked to one customer. Table tasks contains the task that can be done for the customers. I've checked the relationships amongst the tables and I think I'm got everything present. I also made sure I didn't have any autonumber fields linked together.

What am I missing????

Can someone please help me.

I have created a basic database for entering surgical data for a medical practice. A picture of the Relationships are attached. When I create a form I get the following message: ""You Cannot Add or Change a Record because a Related Record is Required in tblSurgicalID".

What am I doing wrong here? I have tried everything! PLEASE HELP!!!

hello

I am getting this msg "You cannot add or change a record because a related record is required in table employer" and I dont know how to fix it.

I have 2 table:
placeofservice: primary key: vol_id, name_of_employer,city, name_of_branch, start_date and another field which is not key: end_date

Second table: employer: primary key: name_of_emp, city_emp, branch_name and other fields which are not key.

I did a 1:N relationship between the tables on fields: name_of_emp, city_emp, branch_name. (one employer can have multiple records in placeofservice).
and also i enforce RI.

when i try to add a value to placeofservice , in a subform, i get the error msg above. if i enter a record manually to placeofservice (through edit of placeofservice table) it works fine.

Please help,
Thanks in advance, Elad

I am getting the following error message on my database:

You cannot add or change a record because a related record is required in table 'MolTable'

I have a table called MolTable and a table called AssayTable. One MolTable record will be linked to several AssayTable records. However, one AssayTable record will be linked to exactly one MolTable record. I also have a main form for the MolTable data that contains a subform for AssayTable data. When I try to add a new record to the assay table, I get the above error message. There already are several AssayTable records for each MolTable record. I was simply trying to add an additional AssayTable record to an existing MolTable record. Compound ID is a field in both tables that links them together. CoumpoundID is not the key field in either table however. Any ideas?

Thanks!
Dina

Hey guys ive been looking online and seeing a lot of comments about cascading deletes, Relationships and enforcing referential Integrity. However i still cannot find my error where i have went wrong, hopefully someone out there is quicker than me, by the way im new in Access. So here is a image of my relationships,
So basically i have many tables main table is the Header_Generator which contains HG_ID and links it to all the the other tables, so my main goal was if i deleted a HG_ID it will delete the records related to this field everywhere. Saying that i had created two forms a parent form contains the HG_ID creation and the child form which contains the HEADER_ID, so when im in the parent form and i decide to manual delete this record and redirect to another form completely i get no error message and everything is working as suppose too. However i created a button to this manual step however since i've created this i get the error message i mentioned before
Code: You cannot add or change a record because a record is required in header_generator if i avoid the message i can see the code does work, its just that error message so annoying, so this is my coding i have in VBA


Code: 'Delete current data and redirect to home screen. Private Sub DeleteEntry_Click() 'Promt strPrompt = "All windows created will be delete, are you sure you want to delete this entry?" 'Dialog's Title strTitle = "Warning" 'Display MessageBox iRet = MsgBox(strPrompt, vbYesNo + vbCritical, strTitle) If iRet = 6 Then Me.USER.SetFocus DoCmd.RunCommand acCmdSelectRecord DoCmd.RunCommand acCmdDeleteRecord DoCmd.Requery DoCmd.RunCommand acCmdSaveRecord DoCmd.Close DoCmd.OpenForm "HOME", OpenArgs:=Permissions & "|" & UserName & "" Exit Sub End If End Sub Overall i might be a lost case so help a brother out!!! Thank you!

Hi, I really need help with a form and a subform.

The main form is the WorkOrder. I select the FleetID from the dropdown list and this creates a WorkOrderID number.
Then I enter the ServiceTypeID number which brings up a list in the subform of all the ServiceItems that belong to the ServiceTypeID.

My problem is when I select on the checkbox to mark a Service Item as Completed I get an error message saying “You cannot add or change a record because a related record is required in table “tblServiceItemsID””

I’ve tried various ways of changing my relationship structure, my queries and form and this is the closest I have ever got to getting the form to do what I want.

Can someone shed some light on what the problem is, and how may I resolve it please….

Attached are screenshots of the relationships, queries and form.

Hi all

I have a form 'Paper' with a combobox 'Submitting author' and also a subform 'Author'. When a new record is created, the user should select the names of the authors in the subform, and then select the submitting author in the combo box (a query selects the authors of the paper in the combo box).

The problem is that, when I try to enter the name of the authors in the subform, the following error appears: "you cannot add or change a record because a related record is required in table 'Person New' ".

If I type something in the combo box, then it allows to add the name of the authors.

Help is really welcome.
I'm quite a naive user of Access, so please be gentle.
Thanks in advance

Hello,

I have a bit of a snag, a "you cannot add or change a record because a related record is required in table";

I have two tables, one that's called "custinfo", and the other "discounts".
Each table has "cfID" as an autonumber field, and both are primary keys in each table.

I have a field in "discounts": "name2" which I filled with numbers, and then used the lookup wizard to display the name of each customer (the field is "clname" in the "custinfo" table). Thus, a one to many relationship between "cfID" in "custinfo", and "name2" in "discounts". So far so good.

Now, when I create a form with fields from both tables, and I try to create a new record, access gives me "you cannot add or change a record because a related record is required in table"..

Can anybody help me out? thanks.

Hello,
I have a database tracking invoice from diff vendors.I have 2 tables which have one to one relationship between them both of the tables have id which is set on autonumber.
I have a form that updates both the tables simultaneously today as I was trying to add a new record through the form I am getting this error "You cannot add or change record because a related record in required in the table".
I have no idea how to resolve this issue today all I did is added a new vendor in to my vendor combo box in the form and that is when this error started to appear.
What is happening is one of my table is getting updated but the other other one is not also the records numbers are also matching.
Please any advice or suggestions is more than welcomed.

Thanks,
Roger

Hi!

I have a master-detail (patient and its consultations) form, the consultation table used to be implemented as a subform just below the patient data and all works fine. But I figured out that it's better that i'll just have a button on the patient form that calls the consultations form. I have done it thru the wizard. The problem is when I started inserting data from the consultation table it says "You cannot add or update a record because a related record is required in table 'patient'. I don't know what I have done wrong or what property on the subform has changed . Please help.

Thanks,
skid

Hi everyone,
I am using Access 2007 and I have a database with 2 tables. The original table has a primary key field designated. I have a relationship between that table and a second table and that relationship has "enforce referential integrity" checked.

In the secondary table the field that the relationship is bound to is not a primary key but has the same field properties.

I created a new record in the original table and created the primary key of "T-124". When I attempted to add the same data to the field in the second table, I recieved the following error message: "You cannot add or change a record because a related record is required" in the other table.

I did create the record in the other table

Can anybody help????

You cannot add or change a record because a related record is required in table < name >. ( Error 3201 )

I got the problem when add data into sub form of Order Entry.
Attached is my Access file in the below link:
http://rapidshare.com/files/13679454...Party.mdb.html

Could anyone help me to fix the problems on it. thanks.
You can send me the file to my email:
lee-ng@hotmail.com or ricky_nglee@yahoo.com.hk

many thanks

Up until now I've only used Referential Integrity with tables that store such things as patient / staff information fed from subforms. After a course and reading stuff on the forum I've added it to every table, but this is causing me problems and think perhaps I don't need it here, but when I think about it I probably do, ie if a field from the lookup table were to be changed, then it does need to update, although wouldn't this do this automatically? As you may tell I am confused!

Problem: My main table is tblEpisode and I have various lookup tables. On entering data in my form, I get to a certain field then get an error message:

You can't add or change a record because a related record is required in table tblElectrodeMF.

If I then delete the RI between tblEpisode and tblElectrodeMF, it would only keep on going with error messages through all the lookup tables, until I had deleted all the RI from every one.

I did do a search on this on the forum and didn't come up with anything to help with this particular problem, although on reading Cannot add or change record I did add code to save the record OnOpen.

Can anyone help please?

Hi,
I have a Form [Quotes] and a Subform [QuoteDetails]. In my form I created a dropbox with an AfterUpdate event to append new records in the [QuoteDetails] table automatically. The problem is that since my code creates the records directly in the table without going through the form, there is no record created at the parent level, the [Quotes] table. Therefore my record in the child table [QuoteDetails] cannot be created.

The error is: Run-time error '3201':
You cannot add or change a record because a related record is required in table 'Quotes'

Any work around?

I'm setting up a college database.
On my enrolment form i have the following fields

Enrolment_ID
Student_ID
Course_ID

I'm receiving the error in my Student_ID field.
It's telling me a related record is required in my Student Table.

The drop down shows the student name/date of birth/Education number

But if i set it to just show the ID it works fine, is there a work around for this as it will make life a lot easier for the dropdown to display the student names rather than the ID?


I forgot to mention,
The dropdown is based on a query i have of accepted students

I have entered the following code to the "Not in List" event of my combo box.

Private Sub Combo74_NotInList(NewData As String, Response As Integer)
On Error GoTo Combo74_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The ground " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Ground")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tblGround([GroundName]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new ground has been added to the list." _
, vbInformation, "Ground"
Response = acDataErrAdded
Else
MsgBox "Please choose a ground from the list." _
, vbInformation, "Ground"
Response = acDataErrContinue
End If
Combo74_NotInList_Exit:
Exit Sub
Combo74_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume Combo74_NotInList_Exit
End Sub

But when the code has fininshed running I get the following message:

"You cannot add or change a record because a related record is reqired in 'tblTeams'.

I know what this is, it is telling me that one of the fields is empty, is there anyway of stopping this?

I mean... when the code has fininshed the curser goes to the next combo box instead of trying to add the record before it is complete?

Many Thanks
Benn

hi all,

I am getting an error message "you cannot add or change a record because a related record is required in table 'personal details'.

What is happening is I have a main client form with command buttons that run other related froms that store information on client details such as attendance. When trying to save the form I get the above error. Any ideas anyone.

Regards

AB

Access 2010 here. Designing a database for parts inspection and quality control.

I have a peculiar issue with checkboxes in subforms. The database structure is like this:

One Main table, that is the record source of the main form and includes general inspection information. Three child tables that are in a one-to-one primary key relationship with the main table. Each child table is the record source of a unique subform. Each child table/suform deals with a certain type of inspection.

The user selects an option box (three options for three types of inspections, AQL, REW, and STD) that makes visible one subform, while hiding the other two. This option box selection also purges possible duplicate entries using "Db.Execute," and finally, this option box sets the inspection type in the main table.


	Code:
	    'Example code for an AQL selection, uneeded record deletion part
    If Me.InspectionType = "AQL" Then
    CurrentDb.Execute "DELETE * FROM REWDB WHERE REWID = " & Me.MAINID, dbFailOnError
    IEF_REWEntryForm.Requery

    CurrentDb.Execute "DELETE * FROM STDDB WHERE STDID = " & Me.MAINID, dbFailOnError
    IEF_STDEntryForm.Requery
    End If

The idea behind the form layout/control is to be able to actively display and, if needed, modify all the old records from the main form, so DataEntry is turned off to display the older records, and turned on for entering new records under Form_Current. Form_Current also controls all the display for the older records to echo what is in the databases and re-display what the user has previously entered. (The attached image may help decipher the functionality of the database.)

The problem comes if/when the user decides to modify old records, the only thing they cannot modify, without a "3201" error "You cannot add or change a record because a related record is required in table "MainTblName"," is a checkbox in one of the subforms that controls a field in that subform's child table through VBA. It's events are quite simple and operate on the subform's table alone.


	Code:
	    'The user has selected the Pass option for the AQL disposition
    Private Sub CheckPASS_GotFocus()

    Me.AQLDisposition = "PASS"

    End Sub

    'The user has selected the Fail option for the AQL disposition
    Private Sub CheckFAIL_GotFocus()

    Me.AQLDisposition = "FAIL"

    End Sub

I'm not sure if the Option group actively deleting records in the unused subforms is causing issues with record "alignment" or if the re-display of the check box doesn't "attach" itself back to the subform's database. This is one of the last hurdles I need to overcome with this database, and it seems like it could be a simple fix If I could pinpoint the problem.

I know this is quite complex and difficult to abstract well, hence why I am here, but are there any ideas or suggestions?

Thank you!

I have form which uses VBA to manually insert a record into a database when a command buttons is clicked.

The table I am inserting the record into is an enrolments table. Each enrolment must include a student ID, a course code and course start date for the course. The course code and start date form a composite key in a courses table.

The code I have worked fine and INSERT's the record - no problem. However, earlier I added a couple of new courses to the courses database and attempted to use the enrolment form to add an enrolment for the course. I was confronted with the following runtime error:

You cannot add or change a record because a related record is required in table 'courses'.

Now this error would make a lot more sense if the course did not exist in the courses database; but it does. Granted, adding the record manually is successful. I then decided to find out a common factor for all the courses which I could not add enrolments for.

To my utter dismay it turns out my VBA code will only allow me intert a record for a course which started in the past. I have spent the last two hours trying to figure out why, to no avail - I am at my wits end.

The VBA code is as follows:

	Code:
	Private Sub cmdConfirm_Click()
    Dim StudentId As Long
    Dim CourseCode As String
    Dim StartDate As Date
    Dim enrolmentDate As Date
    Dim amount As Single
    Dim status As String * 1
    
    Dim conn As ADODB.Connection
    
    Set conn = Application.CurrentProject.Connection
    
    status = "P"
    enrolmentDate = Date
        
    StudentId = Parent!step1!studentdisplay!StudentId
    
    With Parent!step2!foundcourses
        CourseCode = !CourseCode
        StartDate = !StartDate
        amount = !CoursePrice
    End With
    
    strSql = "INSERT INTO enrolments (StudentId, CourseCode, StartDate, EnrolmentDate,"
    strSql = strSql & " Amount, Status) VALUES (" & StudentId & ",'" & CourseCode & "',#"
    strSql = strSql & StartDate & "#,#" & enrolmentDate & "#," & amount & ",'" & status
    strSql = strSql & "');"
    
    conn.Execute strSql
End Sub

If anyone can help I would be most grateful. Maybe I am missing something simple here, maybe access uses different SQL to other DBMS's.

Thanks in advance.

Ok so I have a huge update that runs and pulls guys information from a web site and updates the tables accordingly. Cough... It takes way to long to do this.

So I wanted to transfer the tables from the back end temporaraly to the front end to get to the data faster.

The problem is when I try to create the relationships for the transfered tables it still thinks the tables are linked tables even though they are not...

Errr is 3201 You cannot add or change a record becuase a related record is required in table *whatever*
I have visually verified that the linked tables are gone and the local tables are there.

Anyone have a clue or a guess?

Maybe a better way than transfering the tables I dunno just thoughts.

Thanks,

TheChazm

I'm a relative beginner and am in need of clarafication on an error message I'm getting.

Two of the tables in my database are called Requests and Status,
They are linked together.The primary key of the Staus Tbl (Status_ID)(Autonumber) is the Foriegn key in the Requests Tbl (Number).

When I try to add a record on a form to the Requests table I get the error message

You cannot add or change a record because a related record is required in table 'Status'

I know this must be simple so could someone tell me what I'm doing wrong and how I can get around it?

All I really need is to have one of the status eg (Proposed) stored with the Request Record from the Status tbl.

Can someone please help me in the right direction?


Not finding an answer? Try a Google search.