MS Access: Forms / Table Relationships form won't accept data

The form “frmTools_Dimensional” works perfect. The new form that is going to be similar is called “frmTools_Deflection”. It is close to what is needed.

However when you try to enter data into Date of Inspection Checks you get an error saying “You cannot add or change a record because a related record is required in table “tblDimensionVerification”

I have adjusted the table relationships several times with no improvements. Some how the sub form “subfrmInspectionChecks” is pointing to “tblDimensionVerification”.

I am trying to create the same table relationships between for “frmTools_Deflection” as exists for ‘frmTools_Dimensional.

Take a look at the table relationships

Can someone take a look at this please.

James CoatsComputer Info. Sys. StudentAsked:
Who is Participating?
Nick67Connect With a Mentor Commented:

Looking at your data, it's clear that InspectionPK is the linchpin of it all.
Tools get chosen to for inspection
That inspection has various possible permutations depending on the tool.
I've fixed your realtionships

***It means that your forms are messed up***

You have to FIRST create an InspectionPK entry and THEN open forms to enter the various results.
Your forms aren't structured that way yet.

Take the altered sample, and build a form that
a) Creates an inspection
b) Has buttons to open your various other forms to enter results.

You will probably need  a bit of a rethink on the forms/subforms

You were thinking, maybe, that the tool was the linchpin, but it's not.
Each inspection is unique, a tool and various measurments.

Let me know how it goes
***I know your forms are broken, but your relationships are what they need to be.
You may need to delete some of those forms and start them over ***
Jeffrey CoachmanMIS LiasonCommented:
You will really have to explain the relationship between all of these tables.

Without any "Context" it is difficult to just look at your tables and determine what might be wrong.
For example, what's going on with the keys and their names in your tables?
Some are duplicated in other tables, some are linked to a field with a different name, some are PK's and some are not, some have referential Integrity and some do not?

And what of the other tables...?

It may also be that your forms or their recordsources are not correct, is not even clear if the tables are properly normalized for what you are trying to accomplish.

On note:
Make sure the data and the design is solid by testing various scenarios with the *Tables only*, ...before creating any forms.

If the data is not "working right" in the tables by themselves, then why complicate things by creating forms on top of this?

Perhaps I am missing something in all of this, so let's see what some other Experts post.

James CoatsComputer Info. Sys. StudentAuthor Commented:

Thanks, your relationship change makes sense. However sh** I don't really want to do the forms over but I REALLY need the training. I have a lot of code and examples for what I want to do. Please keep an eye on me as I will likely have some form questions.


Sorry I should have said pay attention to only the tables with the connections. I knew the others needed changes and I have gone through a normalization on what the tables will have though I am sure there is room for improvement on that.

I liked your suggestion on using the tables first for testing and then develop the forms. I will do that from now on.

Nick did the most on this so I hope you don't mind my giving him all the points. I'll have more form questions later on.

Thanks to both of you.
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

You don't necessarily have to throw out the forms in their entirety, but they need a rethink
frmToolsDeflection has a subform with the stuff from tblInspections on it.

That's backward

The tblInspections form will be the main one, and the other stuff will be subforms
A tool subform, perhaps, read only just to display the tool info so the end user doesn't get confused

Check the sample now, for some idea of the rethink.
James CoatsComputer Info. Sys. StudentAuthor Commented:

Thanks again for the extra effort. I wish I had more points to give.
No biggie
Jeffrey CoachmanMIS LiasonCommented:
<Nick did the most on this so I hope you don't mind my giving him all the points. I'll have more form questions later on.>

I have enough points, so I won't cry over this one Q.

Besides, I think Nick is gunning for the "rookie of the ____", title (and has been posting some pretty impressive solutions), so he could use the points...

Remember that you can assign partial points as well, so if Nick did most (90%) of the work and I did 10%, you can split the points accordingly.


Nick is just bored.
He is also wondering what his pay-grade could be

And he owes this site, since much of what he learned came from here
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.