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

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

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.

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.

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.

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

...are you quite sure you have your master/child linking fields set correctly for the sub forms?

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.

<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

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.

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

^
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

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.

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

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.

Nick,

What do you mean by "Tabs"

OK, I see what the "Tabs" are now.

Samples of forms the company wants in this file for me to use @ home
Instruments.mdb

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!

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