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

Posted on 2011-05-04
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
    LVL 74

    Expert Comment

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

    LVL 26

    Accepted Solution


    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

    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.
    LVL 26

    Expert Comment

    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

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

    Expert Comment

    No biggie
    LVL 74

    Expert Comment

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

    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    This article describes some very basic things about SQL Server filegroups.
    Creating and Managing Databases with phpMyAdmin in cPanel.
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now