?
Solved

MS Access: Form accepts data shows correct ID but give error when moving to next record

Posted on 2011-05-05
20
Medium Priority
?
279 Views
Last Modified: 2012-05-11
The following form “frmTools_Dimensional” seems to be properly set up but is giving the following error after I have entered data into the subfrmInspectionChecks and subfrmDimensional. At first the two subforms accept the data and give the correct tool ID (primary key of the table tools) but when I try to navigate to the next record I get the following error:

“You cannot add or change a record because a related record is required in the table ‘tblInspections’.

Look at the tablerelationships and you can see that they are correctly related. I don’t understand why I am getting this error.

This form also only see tools that are “Dimensional”

Ignore the other tables they will later be connected in the exact same manner but right now focus only on the three tables:

“tblTools”, “tblInspections” and “tblDimensionVerification”

Can someone take a look please.

Measuring-Instruments-5-04-11Rev.mdb
0
Comment
Question by:James Coats
  • 11
  • 4
  • 3
  • +1
20 Comments
 
LVL 49

Expert Comment

by:Dale Fye
ID: 35698489
I'm unable to download your app due to corporate policy.  However, the error you mention occurs because you have a referential integrity relationship defined, and you are attempting to add a record in a subform before the record in the main form has been saved.

You might want to try using the subform controls "enter" event to save the parent record.  It might look like:

Private Sub subfrmInspectionChecks_Enter

    If me.newrecord and me.Dirty then me.dirty = false

End sub

0
 

Author Comment

by:James Coats
ID: 35698768
I do have referential integrity defined. The record in the parent table already exists. The record in the first subform Inspection Checks exists the Tool ID (PK form table Tools) date checked is there, Checked By the person is present as is General Condition and comments.

There is no data in the sub form Dimensional below it does however have the correct tool ID (PK for table tools) it will allow me to enter in dimensional checks but then I cannot move on the the next record as I get that error. Prior to entering data in that subform I can page through the records with no problem.

I guess technically I trying to change a existing record.

I looked at the events panel but did not see an "enter" event control to look at. I saw Before insert, after insert, before update, after update and quite a few more but no "enter" event.
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 35698861
I'll try to take a look at your app from home this evening, if none of the other experts resolve your problem before then.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:James Coats
ID: 35699099
Thanks Fyed,

I am still working on it. I just tried to add a new record and a new Tool ID number was sent to all three tables and was displayed in the form.

When I finished the entries and tried to nav on to the next record I got the same error. I closed the form and reopened it. The record was in the table "tblTools" and in the table "tblInspections" but not in the table "tblDimensionVerification" where the results of the inspection check is susposed to go. The new record was there and displayed in the form but only in the Tools and Inspection section of the form it was not displayed in the Dimension section of the form.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35699240
This seems to be a continuation of your previous question.

Again, why not test this with the tables only first *before* building any forms?

The issue is exactly what fyed stated.
Somehow you are trying to create a child record without having first created the Parent record.
(ex.: Trying to create an Order for a Customer that does not exist.)

In a traditional Main /subform data entry sequence, you will open the main form to the record you need.
Then enter the data for the child record in the subform(s).

It may be here that you are trying to first create the child record(s).

In any event, ...I could not reproduce this issue.
Can you give us an exact sample scenario that we can try, that will trigger this issue?

Also I do not see a subform named "subfrmInspectionChecks " or "subfrmDimensional" on your main form?
Did you post the correct sample?

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35699268
...are you quite sure you have your master/child linking fields set correctly for the sub forms?
0
 

Author Comment

by:James Coats
ID: 35699581
OK, yes this is a continuation of yesterday. This is the correct file. I may not be naming things correctly.

However go to the form "frmTools_Dimensional" open it live. On the left side you will see Tool ID, Tool Name, Type of Device ect.

On the left you will see on top Tool ID, Date Checked, Checked By, General Condition ect.

Under this you will see a place you can enter measurements: .125, .250, .500 & 1.0

You can change data in the first two areas but you cannot add or change data in the last area of the form that is how you get the error I am seeing. Are you saying you get no error? And can use all of the form?

I did test out the tables first. I was able to make the connections you see in relationships that each had "Enforce Referential Integrity". I did not get any errors enabling this feature but I did not test it out with data which I now see I should have. I thought it would not allow this unless there was a problem with the relationships.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35701756
<The following form “frmTools_Dimensional” seems to be properly set up>

It is NOT properly set up!
tblInspections is your primary table.
Any of your form/subform setup that do not have the data from tblInspections as the main form aren't going to work when you try to enter new data.
frmTools_Deflection isn't going to work either, for the same reason

frmInspectionChecks and frmInspectionChecks2 work
They ARE setup properly
0
 

Author Comment

by:James Coats
ID: 35701967
Yes you are partly correct. frmTools_Dimensional does not work because in the Inspections table there is a lookup for "CheckedBy" that is pointed at a Display Combo Box and a Row Source Type "Table / Query" all my forms are looking for a value list which is what I changed it to on my side.

This is why it was giving the error above. I also had deleated three records. I had replaced one of them and had it working but have since had some more trouble trying add the records back. It is the ID's or rather the PK that had it where it was working for me.

It is records in the Inspection table that are not in the DimensionVerification table. The records in question start in the Tools table and are records (Tool_PK) 11, 12 & 16. If I can put these records back into the Inspections table and the DimensionVerifications table everything will work.

Could you please take a look at that part for me?? On my end it was working on one record but I had trouble with the others.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35701996
It'll be a bit.

But those forms are fundamentally wrong.\
Their master/child relationships are bass-ackward

I finish creating ones that will work
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35702020
^
As Nick points out, the tables were not set up properly.

If the tables are set up incorrectly, then why create any forms?  ...They will be wrong as well.

*Again* Make sure all of this works in the Tables first.
Worry about forms only when your table design is confirmed.

I can't stress this enough...

If it were me I would dump all of the forms then post questions regarding the design and relationships of your tables.
It seems as though you are having trouble with relating your tables...
How much do you know about Table design, Normalization and Relationships?

JeffCoachman
0
 

Author Comment

by:James Coats
ID: 35702348
I really did not know that much at all but I have learned a lot in this excerise. One of the errors I kept getting was pointing at the Inspections table saying it was looking for a query which is how I found that table issue with "Checked By" in the inspections table.

Let me see if I understand, The table "tblInspections" is set up as the parent table it currently has a one to many relationship with all the other tables except for the table "tblTools" with which it has a many to one relationship. It does not look like the table "tblTools" is a child table... what would you call its relationship with "tblInspections" A father table ? with tblInspections the mother table? don't laugh too hard I am trying to understand this.

Refeerential Integrity is on for all tables and will work as I put records into the tables I get no errors now.

I am still getting errors when I use the form but I think I just need to start over with that but the form "frmTools_dimensional" only sees the dimensional tools and displays the information the way everybody whats to see it. It is very important that the new pages that are set up only see tools related to Dimensional, Deflection, Moisture Content, Temperature and such. A page for each will have to be built from the example that you guys are helping me with. If I have a good example I can do the others. Thanks for not getting upset at my being a novice at this.

If I just deleate the subform "subfrmDimesional" and rebuild it back into the form I am guess it should work now. I am going to try this after I see what you all have to say.

0
 
LVL 26

Accepted Solution

by:
Nick67 earned 2000 total points
ID: 35703094
tblTools is a parent table.
Each tool can be inspected many, many times
Each inspection could--but operationally doesn't, I don't think--be done on many tools

Check this out

Normally, tabs are despicable-- but I'll make an exception here
Instruments.mdb
0
 

Author Comment

by:James Coats
ID: 35706031
I am just uploding this file for me to work on this weekend using your example above. I will award points in a few minutes
Measuring-Instruments-5-06-11Rev.mdb
0
 

Author Closing Comment

by:James Coats
ID: 35706036
Thank you this is a great example for me to follow. I have several other similar db to make. The guy who normally does this quit because our company pays so well.

Your telling me to get the tables working first really helped and this working example will go far in teaching me how to do this. Thanks again.
0
 

Author Comment

by:James Coats
ID: 35706233
Nick,

What do you mean by "Tabs"
0
 

Author Comment

by:James Coats
ID: 35706307
OK, I see what the "Tabs" are now.
0
 

Author Comment

by:James Coats
ID: 35706672
Samples of forms the company wants in this file for me to use @ home
Instruments.mdb
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35707085
If you want more help with this, just post into this question, and I'll answer your inquiries
Are you up against things like company required naming conventions?
And your data: while I set up everything as forms in single record mode, it's easy enough to create continuous subforms, too
You just have choose tabular rather than column in the wizard.

I see you have 'long binary data'
Are you storing pictures, images or documents?
OLE fields are of the devil.

Post a question about the best way to store such things in Access, and you'll see the replies!
0
 

Author Comment

by:James Coats
ID: 35707194
Those are just company emblems for the forms. I can choose to use them or not most likely I won't .

Thanks again for the offer of help. I will likely post some new questions but your help has made a big difference. The naming conventions are up to me I can choose so long as it works.

At the moment I have to run to do some QC checks here and won't be around for a couple of hours.

Sam
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

615 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