I have 2 combo boxes on my main form. Market is the first one the row source of this Combo Box is table named Market. After update a vba procedure deletes and creates a table called MarketTbl. Property is the 2nd combo box the row source of this Combo Box is table named MarketTbl. After update vba procedure deletes and creates PropertyTbl. This works fine.
However when selecting a new market from my Market Combo box, a Run time error is encountered. Database Engine could not lock table MarketPropertyTbl because it is already in use by another person or process. of course my second combo box has row source = MarketTbl, and it is displaying the contents, therefore the script which deletes this table gets stopped as it's in use.
What is the common solution for this type of problem?
On update of Market, event procedure is called:
Private Sub txtMarketName_AfterUpdate()
DoCmd.SetWarnings False
Dim pmDelete As String
pmDelete = ""
pmDelete = pmDelete & "Delete * from MarketPropertyTbl;"
DoCmd.RunSQL pmDelete
Dim db As Database
Dim qdf As QueryDef
Dim tbl As TableDef
Set db = CurrentDb()
Set qdf = db.QueryDefs("qdf")
'Dim intMarket As String
Dim strMarket As String
Set tbl = db.TableDefs("MarketPropertyTbl")
strMarket = Forms![checkmax]!txtMarketName
Dim strSQL As String
strSQL = ""
strSQL = strSQL & " SELECT FacilityInfoCore.FacilityName, "
strSQL = strSQL & " FacilityInfoCore.MarketName, "
strSQL = strSQL & " FacilityInfoCore.MapName, FacilityInfoCore.DataName, "
strSQL = strSQL & " FacilityInfoCore.CoName "
strSQL = strSQL & " INTO MarketPropertyTbl "
strSQL = strSQL & " FROM FacilityInfoCore "
strSQL = strSQL & " WHERE (((FacilityInfoCore.MarketName) "
strSQL = strSQL & " =[forms]![checkmax].[txtMarketName]))"
strSQL = strSQL & " ORDER BY FacilityInfoCore.Sort;"
Debug.Print strSQL
qdf.SQL = strSQL
DoCmd.SetWarnings False
DoCmd.OpenQuery "qdf"
DoCmd.SetWarnings True
End Sub
------------------------------------------------------------------
On update of Property, event procedure is called:
Private Sub txtPropertyName_AfterUpdate()
DoCmd.SetWarnings False
Dim pmDelete As String
pmDelete = ""
pmDelete = pmDelete & "Delete * from PropertyTbl;"
DoCmd.RunSQL pmDelete
Dim db As Database
Dim qdf As QueryDef
Dim tbl As TableDef
Set db = CurrentDb()
Set qdf = db.QueryDefs("qdf")
Dim intMarket As String
strProperty = Forms![checkmax]!txtPropertyName
Dim strSQL As String
strSQL = ""
strSQL = strSQL & " SELECT FacilityInfoCore.FacilityName, "
strSQL = strSQL & " FacilityInfoCore.MarketName, "
strSQL = strSQL & " FacilityInfoCore.MapName, FacilityInfoCore.DataName, "
strSQL = strSQL & " FacilityInfoCore.CoName "
strSQL = strSQL & " INTO PropertyTbl "
strSQL = strSQL & " FROM FacilityInfoCore "
strSQL = strSQL & " WHERE (((FacilityInfoCore.FacilityName) "
strSQL = strSQL & " =[forms]![checkmax].[txtPropertyName]))"
strSQL = strSQL & " ORDER BY FacilityInfoCore.Sort;"
Debug.Print strSQL
qdf.SQL = strSQL
DoCmd.SetWarnings False
DoCmd.OpenQuery "qdf"
DoCmd.SetWarnings True
End Sub
---------------------------------------------------------
When going back to select new market;
Run Time Error: Database Engine could not lock table MarketPropertyTbl because it is already in use by another person or process
Since Market Propert Tbl is being referenced to display properties in market initially chosen the VBA cannot delete the
MarketPropertyTbl because it is in use.