I have a problem with my access form, it said "You cannot add or change a record because a related record is required in table". I have attached the access file.
View RepliesHi! 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
Currently I keep getting this error: "You cannot add or change a record because a related record is required in table"..My current tables are this:
Primary Table with persons info:
Primary Key - Auto number generated
Name
Address
Email
Phone
I have 4 other tables with use check boxes.
ex:
Table 1 - Geographic locations visited
ID - Auto generated
USA
CANADA
ASIA
ECT...
Table 2 - Languages Spoken
ID - Auto generated
Spanish
Chinese
English
Table 3 - Skills
ID - Auto generated
Hunting
Dance
Singing
Weaving
Is this not a genuine one-to-one relationship table? I mean No two people would have had visited the same places and speak the same language no? I tried to create a one to one relationship with the primary key to the auto generated ID of the child tables but I'm sure that is not how you do it. Also when I try to save the check boxes in my form and I close it and come back it doesnt save and is blank again. Is it because my form gets its information from a query that takes all the information from all the tables.
how I can get this to work properly? Am i to make use of a foreign key? I've read a lot about it online and watched youtube videos but I dont see why I need it here in this case. Is there a way to set the IDs in the child tables to be the ones from the primary table? Or do I have to use a foreign key and manually input the primary ID into them?
Or would it be better to have all these child tables in the primary table and have one large table instead? I just didnt do that because one of them has like 20 checkboxes with cities and locations
Can someone help me out by looking at my database I've been messing with it for 3 days and I can't figure it out.
I have 4 tables each are in a 1 to 1 relationship. From there I have a query that is for every field between the 4 tables. I then have a Tab Control form with 4 tabs and it uses the qryAll I have setup. when I enter on the form I can enter info on all 4 tabs but if I try to go to a different record I get the message "You can't add or change a record because a related record is required in tblScouts". I have tried so many different options. I had even posted yesterday trying to do the same thing but my tab controls had subforms on them. The_Doc_Man was helping me with that issue but I couldn't get that to go either.
Would anyone be willing to look at it for me and explain to me what I was doing wrong?
Rick
I have created three tables, all of them are connected by one-to-one relationship by same field, as you can see in the screenshot. and at the same time I have created three forms for each table. then I brought two forms in one remaining form. so whenever I entered data in first form and click on the next tab in which another form exist, it gives me this error: "you cannot add or change a record because a related record is in table".
View 2 Replies View RelatedHi 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:eek:
Can anybody help????:(
I have a table called tblCompanies. When a company acquires another company, I need a method by which the acquired company's CompanyID (PK) can be updated to the new company's CompanyID (PK). I also need to be able to update all related CompanyIDs (FKs) to the new value in related tables.
In cases in which the new company does not have an existing record, there is no problem: the company name simply gets changed to the new company and the existing CompanyID is maintained. I then use an audit table and Track Changes function to keep track of the company name data and a union query to keep the old names in the selection lists.
The problem is when both companies already have existing records in the table.
So, let's say I have records for Company A and Company B. Company A merges with Company B and Company B is now the main record. What is the best, simplest and easiest way to update the CompanyID (PK) from A to B and change the CompanyID (FK) to the new value in all related tables?
I am envisioning a pop-up form that directs the user to select the new company and then an update query happens behind the scenes... but exactly how does the criteria for the update query get selected and how do all the related tables get updated? My vba skills are pretty basic, will I need extensive coding to do something like this?
Is it possible to open a form to add a child record related to the highlighted record in the subform?
View 2 Replies View RelatedIs it possible to have the control tip show related records from another table?
I've been looking around the internet, and I've seen many examples for showing concatenated fields on current record, but had no luck with what I'm trying to do.
For example.
I have a continuous form, and on it shows top line data. Where a record in this form shows as "Split" it means I have more data related to this record on another table.
Is it possible to show however many fields are related to this record, when I control tip, or mouseover?
I've been playing with
Code:
Me.textbox.ControlTipText = Me.textbox
but was wondering, if this is possible, how I'd reference the other table,
If said column of continuous form is "split"...
lookup related table by ID number
show however many columns of data..
I have a form based on a table which includes the mid field. I want to have a macro that takes the value of the current mid, and makes a new record in a 1-many related table (consisting of record id (auto), mid and trmntdate), paste the mid and insert the current date.For the life of me I cannot get it to work? The process should be something like:copy mid value, add new record to related table, paste value in mid, insert current date in trmntdate, save. I've tried append queries, experimented with copy etc, dabbled blindly with VBA and not got anywhere.
View 14 Replies View RelatedI am currently working on an Access Database that houses our security clearance information. Most of the system is up and running but the most recent form has got me spinning my wheels. I have a Word User Form that users will download and complete, once completed the macros will automatically send us the document to be added to our database. Most of this is working the problem is that this portion of the database has multiple related tables and at any given time a user may require multiple records in the related tables. I have created the code to copy most of the information but am getting stuck adding a new record on the sub-form when multiple items are required. Here is a breakdown of the scenario
Word Doc Table 1 = Basic Organization Info
Word Doc Table 2+ = Sites to Visit (There could be more than 1 table added here)
Word Doc Table 3+ = People to go on site (this might not be the third table based on user interaction for sites)
So far I can get Table 1 and Table 2 data but if there are more than 1 site I cant seem to get the system to create a new record on the related table it is creating a new record on the main form. Here are the lines I used to try to create the related record..
Me.RFV___Agencies1.SetFocus
DoCmd.GoToRecord , , acNewRec
When I put just the above code on a button it seems to have worked as the sub-form showed an additional record was created but when using this on my macro the sub-form is not taking the focus for some reason.
I have a database set thus
Clients (the main form)
ClientsID (PK)
blah blah
Information and Referral (a check box)
blah blah
blah blah
ClientIR (the form that opens up when I & R is checked)
IR ID (PK)
ClientsID (FK to the above form)
Requests (a lookup referencing to IRCategory)
The code is set up so when the I R box is checked, form will open, and unchecking it will delete the I R record of that Client.
The problem is when the IR Box is already open, the request has been selected, I cannot save it as Access says a record is required in the Clients form. There are only two buttons, one goes on to next requests (one client can have more than one requests), other saves and closes.
The Client form is already coded so it will save the Client's record before opening the IR Form, and either button will save the IR record as well.
I had referential intergrity turned on. Turning it off only gave me weird results (I was able to input records, but Access didn't autofill the ClientID in the IR records, and looking at Clients returns a blank IR record)
What am I doing wrong?
Thanks
I have 2 tables.
1- customers table with 2 fields : customername,customerno
2-conversationstable with 4 fields: date,customername,customerno,details
The conversations table is for keeping memo of telephone conversations with the customers.
I built a simple form deriving from the conversations table. And added to it a combobox with 2 columns from customers table to select the customername an customerno for the form.
While deleting the record in the conversations table,through the form, I saw that the customername and customerno in the customers table of that particular customer record are deleted also. I made no links between both tables.
I wonder why that happened.
Three tables:
Employee, Sessions, EmployeeSessions.
Many sessions can have many employees - thus the joining table has been included.
When trying to delete an employee from the database using a form, I encounter the error:
The record cannot be deleted or changed because table 'tblEmployeeSessions' includes related records
Is there a problem with my table relationship structure? Or is it 'correct' that as the employee is supervising a session he/she cannot be deleted as this would interfere and maybe mess up the session record?
Hey guys,
OK, your gonna have to bare with me a little bit as its hard to explain and if any VB is given please add a few annotations as I have to explain everything i do in a report (doest have to be too detailed, just to make the code understandable :) ) and if it needs better clarification feel free to ask :), but basically, I have the following relationship set up:
http://img512.imageshack.us/img512/3246/relationnshipswd9.jpg
At the moment, because of the way it is set up, I cannot create a record in the transactions table unless an income record is given for it (because tb_income (one) to tbl_transactions (many)) but the way I want to work is as follows:
If you have a look at the tables tbl_transactions and tbl_income and their link. The way I want the system to work is when a new transaction is made, a new income record in the "tbl_income" table would be made with the date (in tbl_income) being the date at that particular time and all transactions created on the same date would all go in the subdatasheet for that one record created for that date; and if another transaction is made on an alternate date (say 00:00am of the next day) another income record would be created automatically with the date being of that particular day etc.
My other problem im facing is that everytime there is a transaction created, I want the stock level(s) field of products in that particular transaction to be decreased by the quantity purchased of that product but i have no idea how to do so
Any help?
Thanks a lot in advance!
Daniel
Ok my problem is this......
I have a form with a field "Property Status" on it. It has 4 possible values -
"C - SHELTERED (with warden charge)";"H - SHELTERED (No warden charge)";"J - WHEELCHAIR SHELTERED (With warden charge)";"M - WHEELCHAIR SHELTERED (No warden charge)";"X - DISCONNECTED"
When the value "X - DISCONNECTED" is selected in the form I want the record to be removed from its existing table and sent to a new table which keeps all the "X - DISCONNECTED" records together.
Any ideas would be greatly appreciated. How would this be coded?
I am new to access 2010. I need to change the primary key in a table ("stations", Primary key is "station number"), because future entries will result in double entries for this primary key.
I have another table ("species&stations") which has records of Species for each "station number".
So I created a new Primary key for the "stations" table, now called "species id", being a bit better in defining a unique station (as it contains the year and the species number, i.e. "451_2010")
Now the table "species&stations" references/(is in relationship with) the old primary key, identifying a station number for each record.
How do I get it to recognise the new primary key from the "stations" table, and keeping the records (over 1000...) with all there info.
Approach so far: I tried to create a new field in "species&stations", which should look up the new primary key from "stations" and then autofill the field. i did not manage this though
I attached a screenshot to make the relationships more clear!
What i want to do is if a user forgets to put lets say Address or Zip
Will not allow user to save record but instead will highlight the text boxs
that are required. Once data is put in will allow user to save record.
Any ideas
I have 3 forms.
I open the very first one (main form). From there, I wrote a little VBA in the 'Add Record' button that if a certain checkbox in that form wasn't checked when I click 'Add Record', it would open up the 2nd form.
The problem is, when I open up the 2nd form and enter info, the info is all put into a new record.
I want the 2nd form's info to go into the same record as the main form. It's all related.
How do I do that?
Note:I built the 2nd and 3rd form from queries that had parameter querying in them...the parameters are frmo the main form.
One to Many tables:
tbl_Doctors - Main Table : PK is doc_ID
tbl_Patients - Related Table : PK is pat_ID and 'related key' is docpat_ID
Dr. John has 3 Patients
Dr. Sam has 7 Patients
Dr. Andrew has "No Patients" IE. NULL
The (totals)Query:
Group by doc_ID (tbl_Doctors)
Count of pat_ID (tbl_Patients)
It works but ofcourse it does not include Dr. Andrew - as it shouldn't, because there is no related record in tbl_Patients.
What must the WHERE statement be to include Dr Andrews' number of Patients and display it as "0" (zero) ?
I have a transaction form and there is at least two fields I need to make sure have been entered before the record is saved.
I have no problem with text or numeric fields but I can't seem to be able to check the contents of a drop down field.
What is the best method to use to make sure a drop down has been selected by the user and contains a value.
I have temporarily used a default value in the drop down but that's not really what I want.
Hi i have a field which is a date/time field and its format is short date which is xx/xx/xxxx. I want to ask if there is any way i can add a validation rule for only the year to be larger than 1980???
View 2 Replies View RelatedI have a simple relational database with the proper linking tables, etc. I am normalized through BCNF. I have a table that will track tasking details, however, more than one person is assigned the tasking. How do i add to my form the ability to add additional people to the tasking?
If I drag and drop the name field then it is a one name for one box deal. I want to create a combo box that uses a query to prepoulate multiple elgible names (already built) and then you can select the person you want. Afterwards I want the task assigner to dynamically add additional people to the task with no true limit as the group can be as small as one and as large as thirty.
I attached a small screenshot of the basic table structure. I need to be able to add multiple SME's to the one tasking...
I have created a Form based from a Table. On one of the fields, I need to be required inside the Form before moving to the next record of that form. I can not make the required field in the table, because I have to append information day to day.
Also, the required field is a combo box option that is limit to the list.
What kind of Code I can do inside the form of the specific field to make it required before clicking onto the next record. It does not matter if the processor closes out of the form before updating, only if moving to the next record.
In order to arrive at the required solution I have to perform a calculation, using data from each successive table record (in chronological order). The result of each calculation must then be used to arrive at the result for the next calculation.
In other words I have to update a variable based on data from each record, sorted in chronological order, and use the final result to populate a field on a form.
Is it time to break out my copy of "VBA For Dummies" and start learning how to use Recordsets?
Hi,
Hope someone can help me out on this one. Searched the forum but can’t find anything to help me out.
I have two tables:
tblCustomers that contains unique customer records (names addresses etc.)
and
tblComments that contains comment data for the customers
Tables are joined one to many tblCustomers -> tblComments on account_ref
tblComments can contain several records per customer, all are datestamped.
I want just the latest datestamped comment record per customer, just can’t seem to work the logic on this one. Must be so simple but can’t fathom it.:confused:
Ideally, I’m producing a report of Customer data (Name, address, contacts etc) + the last comment record for each customer. Have started with a simple query, but have hit an impasse. Have tried sorting, group by, top 1, distinct etc, but don’t seem to be getting anywhere.:mad:
If someone can point me in the right direction, and give me a starter for 10 I’d be much appreciated.