WRITING records which had been used by others

[rickyfong]

Sorry that my Access is a Chinese version and I could only manage to explain what's my problem and the error message complained by Access!!

Firstly, I create a form and sub-form relationship of which the sub-form linked with a combobox on main form. When user wondering in the combobox, the subform could display relavant records related to the invoice number selected in the combobox.

In the subform, records got a YES/NO field (named CHECKAMT) and when I used the following coding to update records in the subform, a message something liked saying the record had been used by another user, you may overwrite those records, copy those records to another place or undo and quit!!


Set TRANS2 = db.OpenRecordset("select * " & " from MSQUARE WHERE INVNO = '" & Me.INVONUM & "'", dbOpenDynaset)
TRANS2.MoveFirst
Do While Not TRANS2.EOF
TRANS2.Edit
TRANS2![AMOUNT] = Null
TRANS2![checkAMT] = -1
TRANS2.UPDATE
TRANS2.MoveFirst
LOOP
:
:
:

How can I resolved this problem even though user may agree to overwrite those records and it still can work in this way??? Thanks a lot!!

 

[arnelgp]

Do While Not TRANS2.EOF
TRANS2.Edit
TRANS2![AMOUNT] = Null
TRANS2![checkAMT] = -1
TRANS2.UPDATE
TRANS2.MoveFirst 'should be .MoveNext
LOOP

or you can use this:

dim rs as Dao.Recordset
set rs=Me![yourSubformHere]!Form.RecordsetClone

with rs
.MoveFirst

while not .Eof
.Edit
![AMOUNT] = Null
![checkAMT] = -1
.Update
.MoveNext
Wend
End With
set rs=Nothing

 

[JHB]

Use an update query for update them all at once, instead of cycle through a recordset.
http://www.fmsinc.com/microsoftaccess/query/snytax/update-query.html

 

[rickyfong]

Sorry ! Typing mistake! It should be MOVE.NEXT in my VBA!!

 

[rickyfong]

Firstly, I had used update query to work but I found out the same problem appeared. Then I switched to use rescordset and the same still pretained! My doubt is why it kept on saying the recorded had been opened by other users but in face, I was the only user to work with all the database for the present moment!!

 

[rickyfong]

Is there any way or statement should as MSGBOX to tell which user had opened the table or recordset?? I don't think other user can link to my database for the time being! Is that, the Main form had a link with the subform which meant when all record displayed is a sense of user opened those record. Using recordset or update query to force disappeared to change is in the sense of another user to change the displayed records!!??

 

[Galaxiom]

The "other user" is you in your bound form.

You can avoid this by using a Snapshot RecordsetType for the form. You would need to Refresh the form to get the values changed by the query.

 

[JHB]

I think you are the one (the other user) which lock it.
Can you manually change the record(s) in the subform without problem?

 

[rickyfong]

Yes! Manually change did not have any problem!!

 

[rickyfong]

I still don't have any idea to fix it! Any further idea?? Thanks!

 

[Galaxiom]

The "idea" was in the last two responses (mine and JHB's). The "other user" is the form. You have two recordsets open at the same, one for the form and the other you are processing in a query or recordset. They are interfering with each other.

You have three options. Process the data inside the form, close the form while you do the update or use a Snapshot RecordsetType for the form. The Snapshot will not interfere with the update but neither will it show the changes until a Refresh of the form.

 

[rickyfong]

Thanks Galaxiom. Option 2 is not result that I want. In fact, I don't know how to do the option 1 automatically using VBA, but only manually by user input! Option 3 may be but I still don't fully get the idea nor skill to finish after wondering many posts in this stie! Just want to know if I kept the process as present, 1) is there problem to the database provided that users just required to do a confirmation response or 2) is that any statment to by pass this system warning ?? Thanks!!

 

[Galaxiom]

The easiest solution for you would be to loop through the form's own recordset.
Replace the first line of the code in your original post with:

Set TRANS2 = Me.RecordsetClone

 

[rickyfong]

I have changed the statement to Set TRANS2 = Me.RecordsetClone, but the effect is the same as before. Do I still miss something?? Thanks!

 

[Galaxiom]

Can you post a version of your database with a few records of anonymous data?

 

[rickyfong]

MY database is a Chinese version. I am not sure whether this is the problem, let me try my best to explain further. FIrstly, I got a form/subform relationship with a combo box choosing invoice number for subform to display records belonged to that invoice. In the subform recordset, I got a Yes/NO field which when user triggered that field the VBA statement working!!

Set TRANS2 = db.OpenRecordset("select * " & " from MSQUARE WHERE INVNO = '" & Me.INVONUM & "'", dbOpenDynaset)
TRANS2.MoveFirst
Do While Not TRANS2.EOF
TRANS2.Edit
TRANS2![AMOUNT] = Null
TRANS2![checkAMT] = -1
TRANS2.UPDATE
TRANS2.Movenext
LOOP
:
:
:

 

[Galaxiom]

Your form structure sounds fairly normal and I would expect it to work.

Are there other forms or queries open with the same records? If there are no other conflicting objects, perhaps the form is corrupted. Maybe try recreating the form?

Although your database content is in Chinese and we won't be able to read the characters it might still open for us so we can see the structure.

Is the VBA code in Chinese too?

 

[rickyfong]

It's pity that some filenames and field names are in chinese though VBA are still in English. If I am luck that you all can open the database, please click the WORKSQUARE to trigger the form/subform, in the middle of the subform (the first YES/NO column), if you click the YES/NO , the warning message will appear!! Thanks a lot!!