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.
<crystal@cinstall.com> wrote in message
news:1151110813.898543.227420@b68g2000cwa.googlegr oups.com...[color=blue]
> 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~[/color]
