Form & Subforms producing separate records??

[philelpko]

Good Morning all,

I have a main form with 3 subforms. All of the input data on all the forms is stored in the same table.

I started getting the message "the changes you requested to the table were not successful because they would create duplicate values in the index".

I decided to check that the form was trying to make more than one record on the same ID (Auto Number with no Duplicates allowed) and that windows wasnt lying.

After changing the "link child field" and "Link Master Fields" to a temporary field which allowed duplicates for each record to be added, instead of all the info going into the one record, it breaks it up into 4 separate records (one for the main form and one for each of the subforms), hence the error message earlier.

Is there anyway to force all of the data from all 3 subforms onto the same record as the main form? I don’t understand why it doesn’t already if the “Link Master/Child Field” is setup?

Any pointers are most welcome!

[MMcCarthy]

I don't understand your structure. Why are you using subforms if all the records are in one table and why are all the records in one table?

Good Morning all,

I have a main form with 3 subforms. All of the input data on all the forms is stored in the same table.

I started getting the message "the changes you requested to the table were not successful because they would create duplicate values in the index".

I decided to check that the form was trying to make more than one record on the same ID (Auto Number with no Duplicates allowed) and that windows wasnt lying.

After changing the "link child field" and "Link Master Fields" to a temporary field which allowed duplicates for each record to be added, instead of all the info going into the one record, it breaks it up into 4 separate records (one for the main form and one for each of the subforms), hence the error message earlier.

Is there anyway to force all of the data from all 3 subforms onto the same record as the main form? I don’t understand why it doesn’t already if the “Link Master/Child Field” is setup?

Any pointers are most welcome!

[philelpko]

The project started off simple but then people decided that other things were needed etc.

Originally there was one form/one table. As people added requirementsand more visible (user input) data was needed, I decided to create 3 subforms and hide them behind each other on the main form as there would be enough room for all the information on one subform at a time. (Code works nicely so only one is visible at any one time)

This is how I ended up with 3 subforms on the same table. I take it from your comment this really wasnt a good path to go down?

Would it be better for me to start from scratch with 4 separate table now that I know the 'new' requirements?

Cheers

I don't understand your structure. Why are you using subforms if all the records are in one table and why are all the records in one table?

[MMcCarthy]

Would it be better for me to start from scratch with 4 separate table now that I know the 'new' requirements?

Cheers

The short answer is yes. Have a look at the following website.

http://support.microso ft.com/kb/209534/

Mary

[philelpko]

Cheers!! I think I suspected that I had gone too far adrift but didnt want to admit it as it would mean more work.

Thanks again

The short answer is yes. Have a look at the following website.

http://support.microso ft.com/kb/209534/

Mary

[MSeda]

First and foremost examine your structure as mmcarthy advised. If you find you still want to use multiple subforms like on a tab control to enter data into a table try this.

The Main Form and Subforms Allow Additions & Allow Edits are all set to true. The Main form’s Data Entry is set to true and Subforms’ false.
All of the subforms are linked to the Main Form by the Primary Key, in this case ClientCode.

A textbox bound to ClientCode is located on the main form. And contains the following code (In my Form Users Choose a ClientCode, if yours is automatically select like an autonumber there would need to be some slight modifications to the design so that this code runs when a new record is initiated.):

Private Sub ClientCode_Afte rUpdate()

Me.Form.AllowAd ditions = False
DoCmd.Requery "SubformShippin g"
DoCmd.Requery "SubformBilling "
DoCmd.Requery "SubformPrefere nces"

End Sub

This sets the subforms to the record that was just created.

[NeoPa]

That seems quite clever.
Does it add the subforms' data to the table when a record (from the main form) is written?

[MSeda]

The user enters a ClientCode on the main form first and then enters the data in the subforms which are bound directly to the table. Basically the main form creates a record that is blank except for the primary key and then once subforms are requeried the user enters the data directly into the newly created record.
It seems that since the subforms a linked via the primary key none of this should be neccessary but as philelpko says the subforms will try to enter their data into their own new records despite the fact that their respective data entry properties are set to false.

I should say that this method requires that any fields that are required by the table properties be on the main form and completed before the code runs.
Because of this I've acctually abandonded this particular method in my own project and have users enter data into entirely unbound forms. Data is not entered until they click an "enter" button which executes an event procedure that validates all of the data before inserting the record via an sql statement. I've found this to be the most effective way to prevent entry errors.