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

[kiki88]

Hello,

I have been struggling over this for days and have no idea what is wrong. 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.

Can someone tell me 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

Thank you!
​

 

[mahenkj2]

You must need to consider review your tables.

You do not need to make many fields for geographic locations visited, skills and languages etc. You may have a table for locations, skills and language.

Then, in another table (say main table), you bring data from these four tables by adding foreign key in main table with one to many relationships.

Like

Locations
LocationID
Location (this will be one field and you may fill with USA,Canada,UK etc as per requirement)

Languages
LanguageID
Languages

MainTable
ID
Foreinkey for PersonID
LocationFK
LanguageFK
etc...

Just give a try, before going for form, better to check this.

 

[kiki88]

Hi thanks for your reply.

I cant just make it an empty text field and type it in myself because I need to sort it later for a report to pull all those people who speak Spanish and can sing in a list of preset locations ect, All those other tables have present values to select from. Would you not use check boxes then either?

This might be a silly question. But I created new number fields in the main table: LocationFK, LangFK and I dragged the LangID and LocationID to it. However when I go to the main table. It is still empty?

Also after doing what you suggested I tried entering things into the form but it still doesn't save. Or am I still missing a big piece in this problem? Now the relationships are one-to-many. The Languages and Locations tables are now just fields such as: Spanish, English with checkboxes. Is this current set up still not correct?

Im just generally confused on what else I need to do because I need to pull this data to repopulate in my form and have the ability to change and edit there. Also now when I try to make a query of everything in the tables the query is empty?

 

[mahenkj2]

Can you post a sample dB with what you did with your expected results.

 

[kiki88]

Hello thank you so much for your help so far!!! I played around with the database and did the things you said. I "think" it works now but would you mind taking a look to make sure my relationships and structures are correct before I enter tons of information please?

Basically I just want:

To enter a person and their contact information, what languages they speak, and what skills they may have. I want to be able to just check them in the form and go back and edit them if needed. The example is just a very basic view of it there are tons of other check boxe and tables.

Thank you!!!

 

[boblarson]

Your problem is you have 3 tables and one form. The Contact should be on a main form and the languages on a subform and the skills on another subform on the contacts form. They don't all go togther in one form.

 

[kiki88]

Ok I added the subforms as you suggested but now it doesnt look right and show the check boxes for everything not just the person? Am I not utilizing the sub forms correctly? Also is there a way to make it not look like a scroll able box and fit in with the form?

 

[boblarson]

First thing is that your main form should only have ONE table as its record source - CONTACTS.

Next, your other table structure needs work and I'll try to get to that later. But I have to go home now and won't be available until later tonight.

 

[kiki88]

If I did not use sub forms for the skills or languages just for the sake to make the form look better. Would that be okay? I mean is there any major structural problems that might occur later besides the fact that it is not the best that it could be?

I basically just want a large clean looking form with the checkboxes when i search for each contact person and be able to edit their data when need be. Im just afraid if I dont you the sub forms as you suggested that there will be some relationship errors or the data being pulled would be for someone else or things would not save properly.

thanks

 

[boblarson]

If I did not use sub forms for the skills or languages just for the sake to make the form look better. Would that be okay?

No, it would not be okay. But you can format things so it doesn't look so bad.

I mean is there any major structural problems that might occur later besides the fact that it is not the best that it could be?

Yes, there are.

I basically just want a large clean looking form with the checkboxes when i search for each contact person and be able to edit their data when need be. Im just afraid if I dont you the sub forms as you suggested that there will be some relationship errors or the data being pulled would be for someone else or things would not save properly.

Better to set it up properly to begin with than to have to do "band-aid" fixes later. We can help you get it close to what you are asking for. Now, that being said, with code you can almost do anything but if you are not highly experienced with VBA I would suggest taking things slowly and not get to caught up in what it looks like when starting out. The table structure is the most important part because that will also determine how useful the data will be later and how difficult it will be to get the data back out in a meaningful way.


Oh, and by the way. This form has a subform on it. Can you tell where?

 

[kiki88]

I dont really see the sub form in that?

Could you please show me what I did wrong visually on my sample database or is there a tutorial, sample, or video you can direct me to so I can get a better idea on what I'm doing wrong and how to achieve what I would want to achieve? I get a since of things better if I can visually see it.

I've been playing with this for awhile now after watching some videos and reading other peoples posts but got nowhere.

Thank you!

 

[mahenkj2]

Also is there a way to make it not look like a scroll able box and fit in with the form?

In the design view of your Input form, adjust the footer size by dragging it smaller. Also, Optimize other dimensions in detail and header section.

 

[kiki88]

Hi I was wondering if someone could tell me what is wrong with my table structure still? I've ask around but never got an answer still.