Running query with no lock

seeker63a

Registered User.
Local time
Today, 15:42
Joined
Mar 30, 2014
Messages
16
I am running delete and update queries using docmd.runsql. I am finding that particularly after the update query is ran the record is locked and other field in that record can not be updated via a form without a warning saying another user has made changes to the record. To be more specific -- I have a vendor table which has fields vendorname, vendorstreet, vendorcity, vendoraddress. A form is used to place a street address to the vendorname. onexit of the street textbox an update query is ran using DoCmd.RunSql updateing the vendoraddress field to an 'X'. When I put the city in and then close the form this is when the error occurs stating that another user has made changes to the record. My thought is that the update query has not unlocked the record. Any suggestions of a better way to accomplish this is appreciated. The customer wants the X to be placed in the field without his manually placing it in the field. Thanks.
 
No one answered this for a while, so let me ask a question to get things rolling:

This code us used to update a SQL Server Linked Table. Would you compair it to the code you are using? (your SQL Statement will be diffrent)
Code:
10    strSQL = "UPDATE ED_EMTank SET ED_EMTank.Efficiency = " & Efficiency & ", ED_EMTank.UpdatedDt = Now() "
      'strSQL = strSQL & " FROM ED_EMTank "
20    strSQL = strSQL & " WHERE (((ED_EMTank.ID_Wells)= " & ID_Wells & " ) AND ((DateSerial([CalYear],[Mo],1)) Between #" & StartMonth & "/1/" & StartYear & "# And #" & EndMonth & "/1/" & EndYear & "#)) "
      'strSQL = strSQL & " ORDER BY DateSerial([CalYear],[Mo],1);"
30    Debug.Print strSQL
40    DoCmd.SetWarnings False
50    DoCmd.RunSQL strSQL
60    DoCmd.SetWarnings True
70    Debug.Print "Error after run sql " & Err.Number

2nd question - when running your update, is there an open form that has the record locked?
 

Users who are viewing this thread

Back
Top Bottom