Two SQL Queries (append and update) in VBA Record Lock

[Phred]

First a thanks to all who have been patiently helping me.

I am using Access 2007

I have a modest level of experience with VBA and SQL.

I have a Command button on a form - it runs an Append Query which functions fine, and it appends the new record correctly to the Tbl_SiteCategory

There may be some errors in the queries as I have been trying everything.

QUERY 1 Insert Into


__________________________________________________ ___________

Dim intSiteID As Integer, stSiteName As String, stCustID As String, stCustNm As String, stSQL As String

intSiteID = Forms!Frm_Modal_AttachCategory!SiteID

stSiteName = Forms!Frm_Modal_AttachCategory!SiteName

stCustID = Forms!Frm_Modal_AttachCategory!CustomerID

stCustNm = Forms!Frm_Modal_AttachCategory!CustomerName

stSQL = "INSERT INTO Tbl_SiteCategory" _
& "( CustomerID, CustomerName, SiteID, SiteName )" _
& " VALUES (""" & stCustID & """,""" & stCustNm & """,""" & intSiteID & """,""" & stSiteName & """);"

DoCmd.SetWarnings False

DoCmd.RunSQL stSQL

DoCmd.Save

Requery

Refresh

DoCmd.SetWarnings True

MsgBox "The new record has been added. Now select the Category, enter the Default Meal Count, and then click the Update Category button.", vbOKOnly


QUERY 2 Updates the Above record.
__________________________________________________ _________
The user makes further changes to the new record created above and then click on a command button to run the following update query.

Dim inCatID As Integer, stCatName As String, stCatID As String, inMealCt As Integer, stSQL As String

inCatID = Forms!Frm_Modal_AttachCategory!Frm_Modal_AttachCat egory2!SiteCategoryID

stCatName = Forms!Frm_Modal_AttachCategory!Frm_Modal_AttachCat egory2!PickCat.Column(0)

stCatID = Forms!Frm_Modal_AttachCategory!Frm_Modal_AttachCat egory2!PickCat.Column(1)

inMealCt = Forms!Frm_Modal_AttachCategory!Frm_Modal_AttachCat egory2!DFMealCount

DoCmd.SetWarnings False

stSQL = "UPDATE Tbl_SiteCategory" _
& " SET Category = '" & stCatName & "'," _
& " CategoryID = '" & stCatID & "'," _
& " DefaultMealCount = " & inMealCt & "" _
& " WHERE SiteCategoryID = " & inCatID & ";"

DoCmd.RunSQL stSQL

DoCmd.Save

Requery

Refresh

DoCmd.SetWarnings True

This code will not finish. I encounter several problems

I get a warning that the record is locked by another user. I am the only on on the machine.

Then the warning that the record cannot be saved right now.

If I click save anyhow, I get all kinds of corruption in the table and its trashed.

I think somehow I am not "closing out" the first query. I am not sure how to. I have put Save, Requery, Refresh to no avail.

[Phred]

Additional Info. First Query seems to work fine. When I run the second update query I get the Write Conflict dialog box. If I click the Save Record button it destroyes the table. If I click the Drop Changes button the second query updates the table perfectly.