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

Posted on 2011-05-04
Medium Priority
Last Modified: 2012-05-11
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.

Question by:James Coats
  • 4
  • 2
  • 2
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35692137
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, ...it 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.

LVL 26

Accepted Solution

Nick67 earned 2000 total points
ID: 35692218

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 ***

Author Closing Comment

by:James Coats
ID: 35692838

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.
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

LVL 26

Expert Comment

ID: 35693002
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.

Author Comment

by:James Coats
ID: 35693187

Thanks again for the extra effort. I wish I had more points to give.
LVL 26

Expert Comment

ID: 35693225
No biggie
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35693673
<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.


LVL 26

Expert Comment

ID: 35693731
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question