Error Message Form & Subform. HELP! PLease

[Bickertk]

I have created a form with 2 subforms made from forms from three tables.

The first table has a primary key and the others do not .

From the main table I have a name combobox linked by ID to the first subform. this looks up the second table and enters data that never changes for viewing.

The third subform I hope will allow data entry, .i.e other journeys not in the lookup list.

However, when I click the name and the viewing data changes I cannot close the form without an error message. This reads:

"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship."

With the name and first sunform I just want the data to show for viewing not change in the table the only place the information will change is in the third subform. However, I get this message even when I have not entered date in the third form.

Can anyone help.

Regards

 

[thornmastr]

I am trying to spec out your question and it keeps generating questions; so let’s begin with that and drill down.

You have a main form linked to a main table. The main table has a primary key which is related to your joining tables as forward keys so that, you can reference records from table 2 and table 3 with the primary key of table 1.

You have two other forms bound to table 2 and table 3 respectively and these two forms are sub forms both contained on the master form.

Questions:
1. Form 2 is contained within a child control on the main form? You are using a master child relationship of primary key to foreign key between these two forms?
2. form 3 is contained within a child control on the main form, or is it contained as a subform of form 2, or, is form 3 used as an entirely different form called from the foreign key on the first sub form (form 2)?

Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[Bickertk]

Hi Robert

Thanks for your questions re my problem I will try to answer them in the same order.

1)Form two is contained within the child control of the main form and I am using a primary key to foreign key between the forms although the second form is indexed with no duplicate allowed as it just looks up a table.

2)Form 3 is contained within the child control on the main form as evrything has to run from a drivers name.

What I want is to put in the drivers name on form one which is in a table with his name and hours of work, i.e. start and finsh time. this is linked to another table by ID which holds journey numbers and destinations.

I want to put in the name which then automatically puts in the worktimes from table one and the journey numbers and destinations from table 2.

My third table is called a debrief table where the actual times of the journeys and other information can be put. this is also linked by ID.


So, I want to put in the name, Journey's and time automatically fill in and then in the third form I want to be able to put in information which will go into the thrid table linking by ID. The I can look up the set with the actual in a report.

Hence my subforms. But although form 1 puts in the info I want whem I come to closing it I get the message from my earlier note.

I hope I have explained this better and not confused you more.

Can you help?

 

[thornmastr]

After reading your further explanation, I realized I had an equivalent situation right after trashing A95 and really getting into A97 and loving it, but, it did throw this error in my face while staring at a deadline and I even got it once with A2K2.

I will spare you all the aggrevation of searching through perfectly good relationships and trying to find a unique index that should not be unique.

It did generate a number of interesting calls to MS and finally we found a solution which on first glance seems to have nothing to do with the problem, but, given that you do not have a key conflict, refresh or requery the child container prior to your update operation, ie,

Me.child0.form.refresh where child0 is your child container.

A requery on the child container will work also, but refresh is a bit less work for Access to deal with.

Somewhere, I have the explanation from MS why this works, but all my notes are packed up waiting on the movers.

I didn’t expect it to work either, but it did.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[Bickertk]

Hi Robert

Thanks for your help that worked although I would never have thought of it.

Regards

 

[DiamondDust]

Are you opening up any of the forms with some kind of an add New Record clause or are you adding any new records in any of the forms? If so this might be the problem you are trying to add record where the Primary Keys are the Same as another record in the table. You cannot add duplicate values for a primary key value.