I don't understand this msg box

[jinglin]

I have created a updated query (UPDATE ELAW SET ELAW.ChargeOutTo = Null, ELAW.unitCode = Null, ELAW.DateOut = Null
WHERE (((ELAW.FileNoFull)=[Forms]![ChargeOut]![txtFileNoFull])), but somehow when I ran this query from the form ChargeOut. I will get an error message box popped up saying Write Conflict: 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.
Is this sth wrong with my query? or sth else?
Please help!

Thanks

 

[scriverb]

I suspect that the form CloseOut is a bound form to the table and the record being updated is the current record on the form. I am also suspecting that you have modified a field/control on the form and this record is now locked by the system as being edited by yourself. You see when the record is in the process of being edited ACCESS will lock the record so that no other updates/edits take place until the changes are finished and the record is released. You are executing an update query on a locked record as you query is selecting the current record from your form.

Try opening the record without making a change and then run the update.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[jinglin]

You're absolutely right. The record is locked by the system. Opening the record without doing anything still doesn't work. I guess the best way to do it is to use unbound controls on the form. What do you think?
Thanks, Scriverb!

 

[scriverb]

I have setup a similar table, form, and query. If I open the bound form and make a change and run the query before leaving the record I get the same message that you have. If I go to a record and make no changes and run the query I DO NOT get the message. It should be the same for you.

Check out your Tools/Options/Advanced Tab. See what you record locking settings are. Mine is set to No Locks.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[jinglin]

I did as you told me and set to no locks, now I don't get that error msg box, but it didn't delete the values in the fields of ChargeOutTo, DateOut and Unitcode for specified file #.
Thanks again!

 

[scriverb]

Is the query notifying you that it is updating a record? If you have set the warnings to false, please remove that code temporarily so we may see if ACCESS will tell us that the update is working properly. If the record is being updated then the Criteria may be wrong and it is selecting the wrong record.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[jinglin]

Now I set the warning to false and it told me that it updates 0 files. Strange enough, if I ran the query only, it will delete the values for certain fields. so it's sth that's related to the form.
Sorry to bother you so much and I appreciate your help.

 

[scriverb]

The criteria for the query is posted as:

WHERE (((ELAW.FileNoFull)=[Forms]![ChargeOut]![txtFileNoFull]))

Just what records are we selecting here. Are you trying to connect to a particular record only. Or will this WHERE statement edit/update with Null more than one record?

By the way this is no trouble. I enjoy helping out.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[jinglin]

Just edit one record at a time. I have a textbox on the form called txtFileNoFull which is unbound. After I entered the file # in the textbox and press Find btn on the form, it will find matched record and show stored values on the form (such as ChargeOutTo, DateOut, etc), then I need to delete above values except file # from the table. and make the form blank (me.DataEntry=true)for the next search.
Sorry for the confussion!

 

[scriverb]

So you just want to clear the record currently active on the form. Why not just set the controls to blank and save the record. I take it that the form is a Bound form. Only the textbox for the search is unbound. And, the fields that need be cleared are on the form as bound controls.

How about this VBA code:

Me![ChangeOutTo] = Null
Me![unitCode] = Null
Me![DateOut] = Null
Me![txtFileNoFull] = ""
DoCmd.RunCommand acCmdSaveRecord
Me![

Post back with questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[jinglin]

It's almost working except that I got a msg box saying that the ELAW.FileNoFull can not be an empty string. I will figure out that tomorrow if I can.
Again, thanks for working throught with me.

Jinglin

 

[scriverb]

Just change it to:

Me![txtFileNoFull] = Null

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[jinglin]

Hi, good morning!
Set txtFileNoFull =null won't work, it's a primary key. Now I used me.dataentry=true after running DoCmd.RunCommand acCmdSaveRecord, which will clear up the form and wait for next file to be charged out. It seems everything is working now. I really appreaciate your help. Thank you so much.

jinglin

 

[scriverb]

Great. I thought that text box was the one you were using to search for the next record. Sorry about that. Glad that everthing is working for you.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[mc14702]

I am doing something very similar. I have a main form with unbound controls where a user can type in a carton # and search for it. It will bring up all matching records in the unbound subform. Here you can navigate to the record you wish to see (the control source for the controls on the form get set at various spots throughout the code based on the type of record it is). Once you edit the record and save it it is fine, but once you navigate to another record or try to find another carton it gives the write conflict error. I am doing a simple rst.edit or rst.addnew. If I click "Save Changes" on the error box it does save the changes. Let me know if you would like any more detail.

 

[scriverb]

You really should start a new thread with this one. This way others here at TT can participate and benefit from the solution. I will try to respond to your posting a little later.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]