[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MS Access: Forms: Tables accept data without fault / Forms don't see some of the tables

Posted on 2011-05-09
15
Medium Priority
?
374 Views
Last Modified: 2012-05-11
I have a form with subforms on it. The form "frmPWCTools" has the subforms "frmPWCInspections & "frmPWCDimensionVerfication" on it.

The tables associated with it are: "tblTools", "tblInspections",  and "tblDimensionVerification" I have tested the tables and I can enter data into table "tblTools" and that data flows without fault to the other two tables.

My form "frmPWCTools" sees the data from the first child table "tblInspections" but not the second child table "tblDimensionVerification" I used the wizard to create the form. I have looked at the example forms created for me by several individuals on EE yet I cannot seem to get it right. This is really bugging me what the heck am I doing wrong!!

Also if I try to add or change a record I get "You cannot add or change a record because a related record is required in table "tblInspections". This occurs when I try to enter measurements into last part of the form which sends data to the table "tblDimensionVerification".

If I go to the tables themselves I can enter data into all three tables when starting entries in table "tblTools". I don't get why the forms won't work. It doesn't see like it should be this hard unless I am that stupid.

I am working from home today. I am using Access 2010 I just got it. If I can get this db to work I will be able to work from home more often so I am hoping someone can show me what I am doing wrong.

I know how to create forms that only see certain tools and include host of other controls that members of EE have taught me how to do. Is too much being asked for a single form to do??


I can add a lot of controls to forms with what everyone has shown me but this one thing keeps stumping me.

The format of this form is what is being asked for. I will later have to add a results panel to the bottom of each form which shows the Min, Max, Range, Repeatablity, Accuracy, St Dev & Avg of the measurments taken.




Instruments2.mdb
0
Comment
Question by:James Coats
  • 7
  • 3
  • 3
  • +1
15 Comments
 
LVL 42

Accepted Solution

by:
dqmq earned 1100 total points
ID: 35721306
>My form "frmPWCTools" sees the data from the first child table "tblInspections" but not the second child table "tblDimensionVerification

Because tblDimensionVerification is a child of tblInspections.   Therefore, frmPWCDimensionVerfication needs to be a subform of frmPWCInspections, not of frmPWCTools.  Make frmPWCDimensionVerfication a subform of the  frmPWCInspections and the data will show up.

0
 
LVL 8

Expert Comment

by:Andrew_Webster
ID: 35721339
The rule is that a table can populate a subform properly provided one of its fields links to one of the fields on the main form.  This makes the most sense if the subform's linking field is a foreign key that uses the main form's recordsource's primary key.

Main form: Primary key
Subform: Foreign key

Does that help?
0
 
LVL 42

Expert Comment

by:dqmq
ID: 35721430
Above fixes the data entry problem as well.

Table design:
tblTools  --< tblInspections  --<  tblDimensionVerification

Form design:
frmPWCTools-->subform-->frmPWCInspections-->subform frmPWCDimensionVerfication  


Also, I must ask, is the relationship between tblInspections and tblDimensionVerification one to one or one to many.  I mean, how many DimensionVerifications can a tblInspection have?



childmaster.bmp
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:James Coats
ID: 35721552
A single instrument can be inspected up to 4 times a year so I set the relationship as a One-to_many. Is that correct or should it be one-to-one??
0
 
LVL 8

Assisted Solution

by:Andrew_Webster
Andrew_Webster earned 900 total points
ID: 35721661
One-to-many would be correct.

FYI:
One-to-one tends only to be used in very specific circumstances, usually when data is optional.

"One-to-nought or more" is recorded using a one-to-many relationship with the Foreign Key being Nullable.
"One-to at least one or more" is recorded using a one-to-many relationship with the Foreign Key being not Nullable.

"One-to-a specific number" would have to be enforced using code in Access.
0
 

Author Comment

by:James Coats
ID: 35721786
dgmg

Where are you and what are you right clicking to see the "Link Master Fields" and "Link Child Fields" ??

I know about the little black square in the top left corner of the form or subform but I am not seeing what you are showing.

So if I make "frmDimensionVerification" a subform in "frmPWCInspections" which is itself a subform in "frmPWCTools" the data will show up?

Are my primary keys and foriegn keys correctly set up as they are?
0
 

Author Comment

by:James Coats
ID: 35721876
I just tried it as a subform in "frmPWCInspections" and it works just fine all the tables show their information just as I wanted. It does present some design issues but now i clearly understand what I was doing wrong.
0
 

Author Closing Comment

by:James Coats
ID: 35721902
dgmg,

Could you still please show me where you clicked to show the parent / child key links.

Thaks to both of you for you help
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35722146
Man! <grin>

You just seemed bound and DETERMINED to create a form that WILL NOT work!
Maybe this will drive it home:
Open your relationship diagram
tblTools has NO relationship--nor should it--with any of the tables on the far right.
They relate THROUGH tblInspections

So you CAN'T make a form/subform with tblTools and tblDimensionVerification

It's ugly but it works--a form/subform/subform of the subform
0
 

Author Comment

by:James Coats
ID: 35722156
I am just uploading this db for my use later @ work
Instruments2.mdb
0
 

Author Comment

by:James Coats
ID: 35722193
Nick,

I have a real hard head... but I see it now. I had no issues with many of the other great things everyone has taught me but this form thing really took some doing for me to get it.. I was hoping you would not "SEE" this but I guess I knew you would.

I will not have this kind of issue anymore
0
 
LVL 42

Expert Comment

by:dqmq
ID: 35722228
The master child properties are on the data tab of the subform control properties.  By that I do not mean the subform itself, but the subform control which names the subform in it's controlsource.  You need to click on the border of the subform to select the subform control.

I realize the relationship between tools and inspections is 1-many.  What is the relationship between inspections and dimensionverification?  That's the one I was concerned about.  
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35722240
No biggie.
I am having grief uploading to EE today
<Could you still please show me where you clicked to show the parent / child key links.
That required an knack.
Open the form in design view
Open the property window
go to the data tab
Click anywhere BUT on a subform
Click on the subform ONCE

You should then get the properties for the subform object on the main form, and not for the actual form used as a subform

Now, master/child relationships.
Think of them as SQL Where clauses.
'Show me all the stuff on subform WHERE something on the subform = something on the main form.'
Needless to say, both fields HAVE to be in the underlying recordsets of both forms, and the choices have to make sense.

The master/child relation is almost always on PK/FK.
It try and upload the altered sample on the next post.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35722412
0
 

Author Comment

by:James Coats
ID: 35727443
Nick,

Thanks again for the extra work. Nice examples. This is what I really like about EE. You find people who are willing to help and you have the examples saved for you online.

Once I get the final product done and the company is using it I will upload a copy for comments and further suggestions this way if anyone else needs this type of db its here.

I am going to try and go back to sleep. I have to work tomorrow. It's 02:50 PST here. Good night.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this article, we’ll look at how to deploy ProxySQL.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

834 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