Getting lock violations even though all forms have been closed and the new data is different from the exisitng data to be updated

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

that is because you are using the keyboard to interact;  equivalent is DoEvents in VBA

instead of using  DoCmd.RunSQL, better to use db.Execute -- which means you should use a database object variable

   dim db as dao.database
   set db = currentdb 'this will need to be released

Select allOpen in new window

after the forms are closed, refresh the tables so all processes can see what is latest, and DoEvents to make that happen now (which may need to be repeated after SQL runs for newly opened forms to see the latest -- first check if that is necessary). then run the SQL statement

   db.tabledefs.refresh
   DoEvents
   db.Execute strSQL

Select allOpen in new window

in the exit code:

   set db = nothing

Select allOpen in new window

although I have not included it ... error handlers are good to add. Here is a short video on error handling. There are 2 other videos in this series if you wish to know more.
basic error handling code for VBA (3:48)
https://www.experts-exchange.com/videos/1478/Excel-Error-Handling-Part-1-Basic-Concepts.html

Hey Crystal,  thank you so much for the suggestions.  The first update statement worked great however the second is not updating.

The second statement is updating a table based on a query that uses the data updated in the first update statement.  Again, when I do it via a query it works.

I have tried adding the  db.tabledefs.refresh and   DoEvents before executing the SQL code but that does not seem to do the trick.

Is the second update statement not seeing the updated table from the first statement?

Thank you again for your help.

-Brian-

you're welcome
put the refresh and DoEvents statements before EACH running SQL statement that is not seeing what it should.

if there are quueries with problems, do db.querydefs.refresh too

the tables and queries should be closed -- or other things also need to happen

Hey Crystal,

My second Execute statement does not seem to be picking up the correct data even though I "Refresh" just before it.

Is there anything else I may be missing.  Running the Update manually does work.

Below is the current code

Thanks

Dim db As dao.Database
Dim strSQL As String, strSQL1 As String
Dim temp_CRAS_CAR_Id As Double
Dim Tmp_Testing_Start_Date As Date

Set db = CurrentDb 'this will need to be released

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"

db.TableDefs.Refresh
DoEvents

strSQL = "Update tbl_Review_Main SET Actual_Testing_Start_Date = #" & Tmp_Testing_Start_Date & "# Where CRAS_CAR_Id = " & temp_CRAS_CAR_Id & ""
db.Execute strSQL

db.TableDefs.Refresh
db.QueryDefs.Refresh
DoEvents

strSQL1 = "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 & ";"
db.Execute strSQL1
db.QueryDefs.Refresh
DoEvents
Debug.Print strSQL1

'DoCmd.OpenForm "Frm_User_Dashboard", acNormal

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

Set db = Nothing

wait till form is closed before setting db -- and if the form data is not appearing, use DoEvents before setting db.  Perhaps also release it before OpenForm.  
 
instead of:
db.QueryDefs.Refresh
do
db.TableDefs.Refresh

>> Running the Update manually does work. <<
Sometimes you need more than one DoEvents