Listbox - moving from one to another

Dear experts -
I have a form with 2 list boxes - the left is items that are not selected (i.e., not in a given join table) and the list box on the right contains those that are. The list boxes populate correctly.
I have two buttons, one right-arrow and one left-arrow button, which are used to 'move' items from one list box to another (basically creating a SQL string that inserts or deletes items from the join table).

The code below works find - but only the FIRST time it is run. after that, it generates an error, namely "error 3420: object invalid or no longer set".

The offending line of code is the last line of the following, namely: db.Execute strSQL, dbFailOnError

Any ideas? When I check the strSQL variable, it looks fine to my eye...

Thanks!

    ' Add each Language selected in the "Available" list box.
    With Me.lstLanguagesAvailable
        For Each varItem In .ItemsSelected
            strSQL = _
            "INSERT INTO LanguageLists_Join (LanguageList_ID, LanguagePlusLocale_ID) VALUES (" & _
                     Me.txtID & ", " & .ItemData(varItem) & ")"
            db.Execute strSQL, dbFailOnError
        Next varItem
    End With

Open in new window

terpsichoreAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
variable values can be lost when there is an error.  Add an error handler to your code

   'set up Error Handler
   On Error GoTo Proc_Err

Open in new window

'   ... then your statements
 
'put this at the end of the procedure
 
Proc_Exit:
   On Error Resume Next
   'release object variables if applicable-- ie:
'   if Not rs is Nothing then
'      rs.close
'      set rs = Nothing
'   end if
'   set db = nothing
   Exit Function 'or Exit Sub
 
Proc_Err:
   MsgBox Err.Description, , _
     "ERROR " & Err.Number _
     & "   ProcedureName"   '--------- CUSTOMIZE
 
   Resume Proc_Exit
   Resume

Open in new window


   'if you want to single-step code to find error, CTRL-Break at MsgBox
   'then set Resume to be the next statement (right-click and choose Set Next Statement)
   Resume 'press F8 to execute one statement at a time
         ' -- the next one will be what caused the error)
Jeffrey CoachmanMIS LiasonCommented:
Jeffrey CoachmanMIS LiasonCommented:
here is the same file without all the extraneous objects:
Access-Basic-SampleMoveSelectedI.mdb

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
> "object invalid or no longer set"

this is probably referring to Me.lstLanguagesAvailable

perhaps try it this way:
  For Each varItem In Me.lstLanguagesAvailable.ItemsSelected
      strSQL = _
      "INSERT INTO LanguageLists_Join (LanguageList_ID, LanguagePlusLocale_ID) VALUES (" & _
               Me.txtID & ", " & Me.lstLanguagesAvailable.ItemData(varItem) & ")"
      db.Execute strSQL, dbFailOnError
  Next varItem

Open in new window

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
although it could possibly be Me.txtID  too... since there is nothing stopping the user from changing that while the code is running.

in which case, you can set that value to a variable and then use the variable, like this:
    'don't know what type of data this is -- remove comment below if this is a Long Integer
    dim nTxtID 'as long 
    with Me.txtID
        if isnull(.value) then
           .setfocus
           msgbox "You must choose a LanguageList_ID",,"Cancelling Request"
           exit sub 'or function if this is not a sub
        end if
        nTxtID =  .value
    end with

    ' Add each Language selected in the "Available" list box.
    With Me.lstLanguagesAvailable
        For Each varItem In .ItemsSelected
            strSQL = _
            "INSERT INTO LanguageLists_Join (LanguageList_ID, LanguagePlusLocale_ID) VALUES (" & _
                     nTxtID & ", " & .ItemData(varItem) & ")"
            db.Execute strSQL, dbFailOnError
        Next varItem
    End With

Open in new window

terpsichoreAuthor Commented:
Working my way through this, thanks to you two experts. I have more than enough basis for rewriting the code. thank you.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome ;) ~ happy to help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.