Write Conflict: Form / Subform

[crystal]

I've checked the threads but haven't been able to come up with a
solution to my issue. Help......

I have a simple form based on a table.
Within the form is a subform that is also, through a Q, based on the
same table.
The code:

Private Sub Select_BeforeUpdate(Cancel As Integer)
On Error GoTo resetselect_Err

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE SHOWS SET SHOWS.[Select] = No; ", -1
DoCmd.RunCommand acCmdRefresh
DoCmd.ShowAllRecords
DoCmd.SetWarnings True
resetselect_Exit:
Exit Sub

resetselect_Err:
MsgBox Error$
Resume resetselect_Exit

End Sub

I understand WHY I am getting the write conflict but have not been able
to find a workaround.

The form works exactly as it should. It is basically a series of check
boxes. If you check 1, all other checks are set to null (or -1). I
only get the write conflict when I uncheck a box that is already
checked. It's not a huge issue but will be to the novice user.

I've tried adding DoCmd.RepaintObject acForm, "SHOWS" (as well as
removing the error lines, yeah I know) but the problem is innate.

Any thoughts.....
Thanks, C~

[Allen Browne]

There's a range of issues here.

1. Is Select a bound control?
If it is a bound control, the code fires when you change the value.
That means an edit is in progress.
At this point, you are executing a query that changes the data in the table.
That's 2 writes at once: it has to give a write conflict.

If Select is not a bound control, you could just handle the case where an
edit is in progress by adding this line before your DoCmd lines:
If Me.Dirty Then Me.Dirty = False

2. Select is a reserved word in JET.
It is likely to confuse Access and give you problems.
There's a utility here that contains a table named tblBadWord:
Database Issue Checker
at:
http://allenbrowne.com/AppIssueChecker.html
The utility will check your database for these words (and a dozen other
problems if you wish.)

3. Form and subform bound to same table.
This is a completely separate, second reason why you might get write
conflicts. Particularly if there are any memo fields, the chance of a
conflict is high.

But there are other problems with this as well. When you create a new record
in the subform, the field(s)/control(s) named in the subform control's
LinkChildFields property inherit their values from the field(s)/control(s)
named in LinkMasterFields. Typically, Access will assign the primary key
field to these properties. So if the main form is not at a new record, the
subform will try to reuse the same primary key value as the record in the
main form.

If that's not bad enough, if the field is an AutoNumber, this failed write
actually resets the Seed of the AutoNumber to 1 more than the value in the
main form. So, unless the main form happened to be at the last record, the
table is now damaged, and will not accept *any* new records! Not just in
this form, but anywhere - even typing directly into the table - the table is
shot. This happens in all versions of Access from 2000 onwards - even fully
patched, and even in the 2007 beta. More info on this issue:
Fixing AutoNumbers when Access assigns negatives or duplicates
at:
http://allenbrowne.com/ser-40.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<cr*****@cinstall.com> wrote in message
news:11**********************@b68g2000cwa.googlegr oups.com...

I've checked the threads but haven't been able to come up with a
solution to my issue. Help......

I have a simple form based on a table.
Within the form is a subform that is also, through a Q, based on the
same table.
The code:

Private Sub Select_BeforeUpdate(Cancel As Integer)
On Error GoTo resetselect_Err

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE SHOWS SET SHOWS.[Select] = No; ", -1
DoCmd.RunCommand acCmdRefresh
DoCmd.ShowAllRecords
DoCmd.SetWarnings True
resetselect_Exit:
Exit Sub

resetselect_Err:
MsgBox Error$
Resume resetselect_Exit

End Sub

I understand WHY I am getting the write conflict but have not been able
to find a workaround.

The form works exactly as it should. It is basically a series of check
boxes. If you check 1, all other checks are set to null (or -1). I
only get the write conflict when I uncheck a box that is already
checked. It's not a huge issue but will be to the novice user.

I've tried adding DoCmd.RepaintObject acForm, "SHOWS" (as well as
removing the error lines, yeah I know) but the problem is innate.

Any thoughts.....
Thanks, C~

[crystal]

Allen Browne wrote:

There's a range of issues here.
1. Is Select a bound control?
Unfortunately, yes. The checkbox is a yes/no field from the SHOWS
table.
I also tried using the Me.Dirty but it then will not allow me to check
any selection at all. It does however solve the write conflict. ;->
2. Select is a reserved word in JET.
Will run the checker. Thanks for heads up.
http://allenbrowne.com/AppIssueChecker.html
The utility will check your database for these words (and a dozen other
problems if you wish.)

3. Form and subform bound to same table.
This is a completely separate, second reason why you might get write conflicts. Particularly if there are any memo fields, the chance of a conflict is high.

No memo field in the table.
The Main form is a single form used to input different data and create
a new record. Once the data is in, the subform reflects the added data
in a continuous form to select which show to process. Unfortunately
they are both running, in the end, from the same table.

As for the "new record in the subform and the autonumber, No data
allowed to be added in the subform and the autonumber is taken from the
mainform. Of course, I could be wrong, as always.

New shows are added to the form and a refresh after update adds the new
line to the continuous subform. The Yes/No field is not located on the
mainform, although it is taken from the same table and reflected on the
subform. The only time it becomes an issue is when I uncheck.
Otherwise the code works perfectly and without issue. Hope this helps.

I will run the checker and see what's going on there. I wouldn't have
an issue except it runs perfectly with the only exception of when I
uncheck the box.

Thanks for your help.

C~

[Allen Browne]

For the way you describe this, using the AfterUpdate event of the check box
might work. The save should succeed in that event, unless there are other
issues.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<cr*****@cinstall.com> wrote in message
news:11**********************@g10g2000cwb.googlegr oups.com...

Allen Browne wrote:

There's a range of issues here.
1. Is Select a bound control?

Unfortunately, yes. The checkbox is a yes/no field from the SHOWS
table.
I also tried using the Me.Dirty but it then will not allow me to check
any selection at all. It does however solve the write conflict. ;->

2. Select is a reserved word in JET.

Will run the checker. Thanks for heads up.

http://allenbrowne.com/AppIssueChecker.html
The utility will check your database for these words (and a dozen other
problems if you wish.)

3. Form and subform bound to same table.
This is a completely separate, second reason why you might get write

conflicts. Particularly if there are any memo fields, the chance of

a

conflict is high.

No memo field in the table.
The Main form is a single form used to input different data and create
a new record. Once the data is in, the subform reflects the added data
in a continuous form to select which show to process. Unfortunately
they are both running, in the end, from the same table.

As for the "new record in the subform and the autonumber, No data
allowed to be added in the subform and the autonumber is taken from the
mainform. Of course, I could be wrong, as always.

New shows are added to the form and a refresh after update adds the new
line to the continuous subform. The Yes/No field is not located on the
mainform, although it is taken from the same table and reflected on the
subform. The only time it becomes an issue is when I uncheck.
Otherwise the code works perfectly and without issue. Hope this helps.

I will run the checker and see what's going on there. I wouldn't have
an issue except it runs perfectly with the only exception of when I
uncheck the box.

Thanks for your help.

C~

[crystal]

Allen Browne wrote:

For the way you describe this, using the AfterUpdate event of the check box
might work. The save should succeed in that event, unless there are other
issues.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<cr*****@cinstall.com> wrote in message
news:11**********************@g10g2000cwb.googlegr oups.com...

Allen Browne wrote:

There's a range of issues here.
1. Is Select a bound control?

Unfortunately, yes. The checkbox is a yes/no field from the SHOWS
table.
I also tried using the Me.Dirty but it then will not allow me to check
any selection at all. It does however solve the write conflict. ;->

2. Select is a reserved word in JET.

Will run the checker. Thanks for heads up.

http://allenbrowne.com/AppIssueChecker.html
The utility will check your database for these words (and a dozen other
problems if you wish.)

3. Form and subform bound to same table.
This is a completely separate, second reason why you might get write

conflicts. Particularly if there are any memo fields, the chance of

a

conflict is high.

No memo field in the table.
The Main form is a single form used to input different data and create
a new record. Once the data is in, the subform reflects the added data
in a continuous form to select which show to process. Unfortunately
they are both running, in the end, from the same table.

As for the "new record in the subform and the autonumber, No data
allowed to be added in the subform and the autonumber is taken from the
mainform. Of course, I could be wrong, as always.

New shows are added to the form and a refresh after update adds the new
line to the continuous subform. The Yes/No field is not located on the
mainform, although it is taken from the same table and reflected on the
subform. The only time it becomes an issue is when I uncheck.
Otherwise the code works perfectly and without issue. Hope this helps.

I will run the checker and see what's going on there. I wouldn't have
an issue except it runs perfectly with the only exception of when I
uncheck the box.

Thanks for your help.

C~

Oh, the agony. Seems I have a lot of "issues" all with "Jet reserved
words". Thank you so much for the heads up. Very nice utility. I
don't know if this will solve my write conflict problem but I
definately have some cleanup to do.

Thanks again, C~