Lock violation even though all forms have been closed and I am chaning the value to be updated.

[btrini10]

Hello,

I am running the below code and getting lock violations even though I am closing all forms prior to the update statement.

When I grab the code from the Immediate window and run it as a Query I do not get the lock violations.

Not understanding the difference.

Dim strSQL As String
Dim temp_CRAS_CAR_Id As Double
Dim Tmp_Testing_Start_Date As Date

temp_CRAS_CAR_Id = Me.CRAS_Car_id
Tmp_Testing_Start_Date = Me.Actual_Testing_Start_Date

DoCmd.Close acForm, "Frm_Reviews", acSaveYes
DoCmd.Close acForm, "Frm_User_Dashboard"

strSQL = "Update tbl_Review_Main SET Actual_Testing_Start_Date = #" & Tmp_Testing_Start_Date & "# Where CRAS_CAR_Id = " & temp_CRAS_CAR_Id & ""
Debug.Print strSQL
DoCmd.RunSQL strSQL


strSQL = "UPDATE tbl_Review_Plan INNER JOIN q_Review_Plan1 ON (tbl_Review_Plan.Step_Order = q_Review_Plan1.Step_Order) AND (tbl_Review_Plan.CRAS_Car_id = q_Review_Plan1.CRAS_Car_id) SET tbl_Review_Plan.Planned_Date_On_Actual = [q_Review_Plan1]![Planned_Date_On_Actual]" _
& " WHERE tbl_Review_Plan.CRAS_Car_id= " & temp_CRAS_CAR_Id & ";"
Debug.Print strSQL
DoCmd.RunSQL strSQL

DoCmd.OpenForm "Frm_User_Dashboard", acNormal
DoCmd.OpenForm "Frm_Reviews", acNormal, WhereCondition:="[CRAS_Car_id]= " & temp_CRAS_CAR_Id

 

[Darrylles]

OK bit late, but have ANY of these records been edited in the forms that you describe above?

If so, then perhaps some are 'DIRTY'.

I won't re-invent the wheel - look at this...

https://answers.microsoft.com/en-us...y/9c485cca-e5ad-4c01-8eaf-534e27ac3e82?auth=1

ATB,

Darrylle

 

[MajP]

I am wondering if this a timing issue. You are running and update query and then opening a form with a where clause while that query could be running. To test that put a msgbox after the runSql, or code out the opening of the forms. I do not think doevents would help in this case but you can try that. If it has to do with opening the forms you can put a delay timer if needed.