mrsgwen90
37 New Member
i have an access database with some vba code behind it. i have a table called "Files" that holds some records and i have 2 forms. one form opens a new file and the other form closes the file. When i close the file the record should be updated with a "closed" status. But the problem is i keep getting the error: "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." then it gives the option to copy to clipboard or drop changes. Any help will be greatly appreciated.
Here is my code:
Expand|Select|Wrap|Line Numbers
- PrivateSubcmdCloseFile_Click()
- OnErrorGoToErr_cmdCloseFile_Click
- DimResult
- DimrstAsADODB.Recordset
- DimadocmdAsADODB.Command
- Setadocmd=NewADODB.Command
- Setrst=NewADODB.Recordset
- Withadocmd
- .ActiveConnection=CurrentProject.Connection
- .CommandType=adCmdText
- .CommandText="Select*fromdbo_tbl_FileswhereStatus='closed'andFileNo="&Me.FileNo
- EndWith
- rst.CursorLocation=adUseClient
- rst.Openadocmd,,adOpenStatic,adLockReadOnly
- Ifrst.RecordCount>0Then
- MsgBox"ThisFilehasalreadybeenclosed.",vbCritical,"FileNoConfirmation"
- Result=vbNo
- DoCmd.Close
- DoCmd.OpenForm"LegalFilesMain"
- Else
- Result=MsgBox("AreyousureyouwanttocloseFileNo."+Str$(Me.FileNo)+"?",vbYesNo,"AddFile")
- IfResult=VbMsgBoxResult.vbNoThen
- Me.Room=""
- Me.BoxNo=""
- Me.DateClosed=""
- DoCmd.Close
- DoCmd.OpenForm"LegalFilesMain"
- EndIf
- EndIf
- rst.Close
- Setrst=Nothing
- Setadocmd=Nothing
- IfResult=vbNoThen
- ExitSub
- EndIf
- DimrstFileAsNewADODB.Recordset
- DimfldAsADODB.Field
- DimstrFieldAsString
- DimsqlstmtAsString
- sqlstmt="SELECT*FROMdbo_tbl_FilesWHEREFileNo="&Me.FileNo
- rstFile.Opensqlstmt,CurrentProject.Connection,adOpenKeyset,adLockOptimistic
- rstFile!Status="Closed"
- rstFile.Update
- rstFile.Close
- MsgBox"FileSuccessfullyClosed."
- Msg="Doyouwanttocloseanotherfile?"
- Response=MsgBox(Msg,vbYesNo)
- IfResponse=vbYesThen
- DoCmd.Save
- DoCmd.Close
- DoCmd.OpenForm"CloseFile"
- Else
- DoCmd.Close
- DoCmd.OpenForm"LegalFilesMain"
- EndIf
- Exit_cmdCloseFile_Click:
- ExitSub
- Err_cmdCloseFile_Click:
- MsgBoxErr.Description
- ResumeExit_cmdCloseFile_Click
- EndSub
Feb 2 '11 #1
Subscribe Reply
2 2167 
Stewart Ross 2,545 Recognized Expert Moderator Specialist
Is either form bound to the recordset you are updating? If so the error message could well be indicating that you need to save any changes made in the bound version of the recordset before you make changes using the ADO recordset in code.
There are also known issues with accessing SQL Server data via Access which cause the same error message (see for example this MS knowledgbase entry).
If neither of these circ*mstances apply, could you set a breakpoint and step through the code one line at a time to see where the error is occurring? There is no obvious problem with your recordset update.
(As an aside, I note that there is an unnecessary DoCmd.Save at line 69 which would save the form, not the current record!)
-Stewart
Feb 6 '11 #2
reply
NeoPa 32,563 Recognized Expert Moderator MVP
If both forms are interacting with the Files table, then they will clash with each other (unless you somehow ensure only one is ever active at a single time).
My guess is that you should have a single form that manages the data in the table, and not two that can interfere with each other.
Good luck.
Feb 6 '11 #3
reply
Sign in to post your reply or Sign up for a free account.
Similar topics
| 1 1882 Multiple Update - Record Locking advice by: oldandgrey |last post by: I'm looking at producing an application that will allow multiple users to order multiple items/ parts from what would effectively be an online store. These users could be ordering several hundred items/ parts at a time, each item/ part ordered representing one box or several hundred. Each part record will require the "total stock" to be... MySQL Database |
| 1 1813 Why does this not update record anyone? by: toedipper |last post by: Hello, PHP4 and MySql I have the code below, a mixture of handcoded and Dreamweaver genaratd php code. Basically it's an update record form - I load the values from a db and bind text boxes etc. This works ok. But I also have an update action - if a user changes any of the text box values then he can click update and the code should... PHP |
| 4 2193 Not update record using Navigator in MS Access by: James P. |last post by: Hello there, I have a bound-form using Navigator so that the user can move back and forth to update record in the form. Every time a record was modified and the user clicks the Navigator to either move backward or forward to the next record, a message is popped up asking the user to confirm the change. If the user clicks "Yes", the record... Microsoft Access / VBA |
| 2 21340 SQL UPDATE record with Image field by: Tinius |last post by: I am trying to update a field of type IMAGE. I am able to do this by deleting the record and then INSERTing a new record as shown below in the commented section. But I really want to do this with an UPDATE command as shown below in the un-commented code. I assume this is possible. The image is passed as a byte array called 'content'. I... C# / C Sharp |
| 1 1409 Update record ? by: Daniel |last post by: Does someone know how to update record in ASP.NET ? Because i face a problem after updating the record in SQLServer database. The problem is : I do update to a record's field called DateIn. After updating the whole DateIn column field will also same value as the updated value ? Does any one know why ?? Your help will be appreciated. ... ASP.NET |
| 2 3226 Binding Manager (update record problem) by: Agnes |last post by: Binding Manager (update problem) by agnescheng I use binding Manager to bind the textbox, I got a problem on update record. E.g Me.txtUserId.Text = "PETER" bm.current.item("userid") = Me.txtUserId.Text. if ds.haschanges() then .................... .....acceptchanges() messagebox.show("Save is completed") end if................ I found... Visual Basic .NET |
| 1 1484 How to select row from gridView and update record in database using Ajax by: ashish ranjan |last post by: Hi there , I m in great need to know: How to select row from grid and update record in database using Ajax. u can use c#,.net and java script. Thanks in advance, Ashish Ranjan .NET Framework |
| 12 2484
Update record of two table by: ranjana1980 |last post by: How to update record of two tables when in Table A RegNo is primary key and in Table B RegNo is foreign Key and i like to update where A.RegNo=B.RegNo Microsoft SQL Server |
| 1 1807
How to update record to last record in database using vb.net by: Kosal |last post by: Dear Sir/Madam I would like to Update record to last record in database that unkown last record please advice. thanks Best Regard .NET Framework |
| 1 2014 how to update record with 2 tables? by: dreamy |last post by: i have 2 tables, 1. account -id, -accounttypeid, -name 2. accounttype -accounttypeid, -type. PHP |
| 7453
What is ONU? 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, well explore What is ONU, What Is Router, ONU & Routers main... General |
| 7647
Problem With Comparison Operator <=> in G++ 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. ... C / C++ |
| 7802
Maximizing Business Potential: The Nexus of Website Design and Digital Marketing by: jinu1996 |last post by: In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that... Online Marketing |
| 1 7410 The easy way to turn off automatic updates for Windows 10/11 by: Hystou |last post by: Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For... Windows Server |
| 1 5318
Access Europe - Using VBA to create a class based on a table - Wed 1 May 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... Microsoft Access / VBA |
| 3441 Trying to create a lan-to-lan vpn between two differents networks by: TSSRALBI |last post by: Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in... Networking - Hardware / Configuration |
| 3436 Windows Forms - .Net 8.0 by: adsilva |last post by: A Windows Forms form does not have the event Unload, like VB6. What one acts like? Visual Basic .NET |
| 1 1865 transfer the data from one system to another through ip address 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 C# / C Sharp |
| 1 1007
How to add payments to a PHP MySQL app. by: muto222 |last post by: How can i add a mobile payment intergratation into php mysql website. PHP |
