Multiple form and subforms for 1 table

[Tupacmoche]

I want to edit record that belong to one table but have run into a problem. I have several views of this one table on a form which includes 3 subform and 2 buttons that call up yet another two forms to edit/add data to the single table that they are all connected to.

Now, to the problem. The reason why, I have 3 subforms is to logically separate the data in this table to view. If, it is discovered while moving through the records that an address or anything else is missing in one on these logical views, I have a Edit/Add button that when clicked opens a form that allows the user to edit/add the missing data like an address.

When the save button on this form is click I get the message :

This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made.

Write Conflict.

No one else is using this form when this happens what can I do?

How can this be fixed?:banghead:

 

[isladogs]

Do you have any yes/no fields in your data source table(s). If so, make sure that null values are not allowed and make these have a default value (true or false as appropriate)

 

[Tupacmoche]

No, I do not have yes/no fields.

 

[Tupacmoche]

Does any of the Access Master out there have a solution to this thread? Here is the error message again:

This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made. The form is described in Post 1 of this thread.

 

[isladogs]

Suggest you post the structure of the source table(s) so someone can advise you better

 

[Tupacmoche]

:banghead:There is a single table which has columns for data related to Primary Credit, Joint Credit, and Honoree Credit. Simply put there are three (3) sub-reports that show fields related to the type of credit. The columns for joint and honoree may be missing data that needs to be updated so, I have added a button which when clicked opens a form so that the blank fields can be updated. This is where the "Write Conflict" takes place. All the fields are from a single record that is open in four different forms and it is locked so it does not let me edit it. But is there a way around this?

 

[isladogs]

:banghead:There is a single table which has columns for data related to Primary Credit, Joint Credit, and Honoree Credit. Simply put there are three (3) sub-reports that show fields related to the type of credit. The columns for joint and honoree may be missing data that needs to be updated so, I have added a button which when clicked opens a form so that the blank fields can be updated. This is where the "Write Conflict" takes place. All the fields are from a single record that is open in four different forms and it is locked so it does not let me edit it. But is there a way around this?

You didn't give the datatypes for each field so that doesn't help much
It will save lot of time if you post a stripped down version of your db

 

[Tupacmoche]

All the data types are String

 

[isladogs]

As I said before, please post a stripped down copy of your databae

 

[June7]

Are these subforms placed on Tab Control? Is the main form bound? Are the subforms in Single or Continuous or Datasheet view? A possible solution is one subform (or a standalone form) in Single view then put a Tab Control on that form and arrange controls directly on Tab Control. This can provide the 'logical' separation you want.

 

[missinglinq]

...No one else is using this form when this happens...

No one else is using the Form...but someone else is using the Table...you are...six times!

If you have 1 Main Form, 3 Subforms, and 2 additional Forms that you are opening, all at once and all based on a single Table, you have the Table opened 6 times...and Write Conflicts will never go away!

...The reason why, I have 3 subforms is to logically separate the data in this table to view. How can this be fixed...

Instead of using Subforms, to do this, following June7's suggestion, in Form Design View:

1. Set the Form's RecordSource to your single Table
2. Place a Tabbed Control on your Form
3. Add more Pages (by default, your Tabbed Control will already have 2 Pages) until it has the appropriate number for the 'views' you want
4. Select a Page (the 'insert' will turn black, if a Page is actually selected)
5. On the Ribbon, Click on the Design Tab
6. Go to the 'Tools' section
7. One by one, drag each Field you want to see in this 'view' onto the Page
8. When all Fields have been selected, for this view, select another Page
9. Repeat Step #7

Now you can view the Fields/Controls logically, by moving from one Page to another, and any editing you do will be performed, just as if the Fields were all on a single, standard Form. Also, note that when done like this, all Controls are referred to as if they were on a single, standard Form.

You also have no need for separate Forms for Editing existing Records and/or adding New Records.

Having said that, your setup sounds suspiciously like it is non-normalized. The general rule/consensus is that a Table with more than 20-25 Fields is non-normalized and needs to be broken up into multiple Tables, possibly one each for your three types of credit (don't know your business, so I can't say for sure.)

If you continue to use a single Table, you may want to conditionally Disable and/or Lock Controls not needed for a given credit type.

Linq ;0)>