Results 1 to 4 of 4
  1. #1
    JimS is offline Novice
    Windows XP Access 2003
    Join Date
    Oct 2009
    Location
    Potomac, MD
    Posts
    2

    Question 2 combo boxes - Conflict

    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.
    Last edited by JimS; 10-08-2009 at 01:16 PM. Reason: additional info

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You should be using queries as the RowSource of your ComboBoxes.

  3. #3
    JimS is offline Novice
    Windows XP Access 2003
    Join Date
    Oct 2009
    Location
    Potomac, MD
    Posts
    2

    Thumbs up Issue resolved

    Thanks Rural Guy. Based on this tip and the cascadingcombosample link you posted on a similiar post I resolved this issue. Thanks for your help.

    Cascading Combo sample db



  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows XP Access 2002
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Great! Glad to hear you got it sorted.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Synchronized Combo Boxes
    By LesleaOH in forum Forms
    Replies: 34
    Last Post: 10-10-2009, 09:20 PM
  2. combo boxes
    By googenfrog in forum Forms
    Replies: 3
    Last Post: 07-03-2009, 05:41 PM
  3. Combo boxes on a form
    By Maranna in forum Forms
    Replies: 1
    Last Post: 06-22-2009, 01:48 PM
  4. combo boxes
    By labrams in forum Forms
    Replies: 0
    Last Post: 04-20-2006, 09:28 AM
  5. Combo Boxes
    By Mxcsquared in forum Forms
    Replies: 0
    Last Post: 01-19-2006, 04:59 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums