473,724 Members | 2,277 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Write Conflict: Form / Subform

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_BeforeUp date(Cancel As Integer)
On Error GoTo resetselect_Err

DoCmd.SetWarnin gs False
DoCmd.RunSQL "UPDATE SHOWS SET SHOWS.[Select] = No; ", -1
DoCmd.RunComman d acCmdRefresh
DoCmd.ShowAllRe cords
DoCmd.SetWarnin gs True
resetselect_Exi t:
Exit Sub

resetselect_Err :
MsgBox Error$
Resume resetselect_Exi t

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.RepaintOb ject acForm, "SHOWS" (as well as
removing the error lines, yeah I know) but the problem is innate.

Any thoughts.....
Thanks, C~

Jun 24 '06 #1
4 8503
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 LinkMasterField s. 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*****@cinsta ll.com> wrote in message
news:11******** **************@ b68g2000cwa.goo glegroups.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_BeforeUp date(Cancel As Integer)
On Error GoTo resetselect_Err

DoCmd.SetWarnin gs False
DoCmd.RunSQL "UPDATE SHOWS SET SHOWS.[Select] = No; ", -1
DoCmd.RunComman d acCmdRefresh
DoCmd.ShowAllRe cords
DoCmd.SetWarnin gs True
resetselect_Exi t:
Exit Sub

resetselect_Err :
MsgBox Error$
Resume resetselect_Exi t

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.RepaintOb ject acForm, "SHOWS" (as well as
removing the error lines, yeah I know) but the problem is innate.

Any thoughts.....
Thanks, C~

Jun 24 '06 #2

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~

Jun 24 '06 #3
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*****@cinsta ll.com> wrote in message
news:11******** **************@ g10g2000cwb.goo glegroups.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~

Jun 24 '06 #4

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*****@cinsta ll.com> wrote in message
news:11******** **************@ g10g2000cwb.goo glegroups.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~

Jun 26 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1359
by: Not Me | last post by:
Hi, I'm experiencing a lot of 'write conflict' errors from a certain subform in my access database linked to sql server tables. I've heard in the past, and successfully avoided the problem by making bit values in the table default to 0. This however, isn't working! I don't alter every field in the table from the subform, so have also tried making other bit fields hidden, but default to 0.
1
3084
by: shumaker | last post by:
I've loked over older messages on this, but haven't found a solution. I have a datasheet of records, and each record has a subform that can be viewed with the expand '+' symbol. The subform simply shows addition fields of the record it is expanded from. I used the subform control wizard to tell it how to link the records when creating the subform. There is only one control in the subform that the users can edit, a textbox. All the...
0
1429
by: manning_news | last post by:
Using A2K. I've got a form for clients that has a subform that is a cartesian product between client ID and a lookup table for types of income. So each client has the exact same number of income type records. If the user changes the name of an income type for a client then I want the change to happen automatically for all clients. I have a one-to-many relationship set up between the client table and the lookup table with cascading...
3
3904
by: rivka.howley | last post by:
I recently added some code to the BeforeUpdate event of a text box on a form. The code uses the new value in the text box to recalculate some values in another table, which is shown in a subform on this form. Since I've added the code, I get a Write Conflict error - "This record has been changed by another user..." This typically does not happen the first time I edit this text box after I open the form, but on every subsequent time. ...
5
4730
by: Simon | last post by:
Dear reader, I have two Forms they are both working with dada from the same tables. By typing in some changes in Form-B the changes are also visible in Form-A. There is no record lock set.
1
2447
by: lorirobn | last post by:
Hi, I have a query that I have been using as a record source for a form with no problems. I just created a new "addnew" form, and added 20 records to the table with this form. The problem I now have is I cannot modify data on these new records using my query. The query's sql is: SELECT tblSpaceUse.* FROM tblSpaceUse; The error I get is: "Write Conflict -
1
4026
by: S.Dickson | last post by:
I have a database with access as front end and Mysql as back end. I am gettting the following 'Write Conflict' Error. when i am on my order form, This form does has a subform where i enter all the products that are ordered. Could any one let me know what could cause this problem. I am the only one using the database as its still in the build stage Thanks
6
4304
by: jpatchak | last post by:
Hello, I have a main form with one subform. I have a command button on the main form to delete the current record. Below is my code. The tables on which the main form and subform are based cascade deletions and updates, so getting rid of the record in the table for the main form should delete any corresponding records in the table for the subform. Everything appears to be working correctly in this code from the standpoint of the tables. ...
1
1707
by: kjworm | last post by:
Greetings all, I have a main form that does not have a record source. On that main form is a combobox that looks up values from the main table and is based off of a stored query. I also have a subform on the main form that is based on a different stored query. If a user picks an item from the dropdown, then manipulates the data via a popup form, it is then added on the popup close and will appear on the subform. I also have a button to...
0
8868
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8741
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9388
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9090
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8062
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6685
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4764
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3209
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2149
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.