Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2012
    Posts
    56

    Unanswered: update a table from listbox data

    Hi all

    I want to update a table field based on a value in a multiselect listbox. The code I am using

    strSQL = "Update [tblTransMain] Set [Payment_ID] = 2 "

    For Each varSelect In Me.ListInvoice.ItemsSelected
    strIDs = strIDs & Me.ListInvoice.ItemData(varSelect) & ","
    Next

    strIDs = "WHERE Trans_ID = ListInvoice.Column(0, varSelect)"

    strSQL = strSQL & strIDs

    CurrentDb.Execute strSQL, dbFailOnError

    The error I get is
    Undefined function ListInvoice.column in expression


    I don't understand this so any help would be great

    Thanks Bob

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    As you wrote the criteria, ListInvoice.Column(0, varSelect) is a litteral that the Jet engine tries to interpret as a function (ListInvoice) with two arguments (0 and varSelect), while ListInvoice actually is a reference to a control on the current form. Try:
    Code:
    strIDs = "WHERE Trans_ID = " & ListInvoice.Column(0, varSelect)
    Note: If Trans_ID is not numeric, use:
    Code:
    strIDs = "WHERE Trans_ID = '" & ListInvoice.Column(0, varSelect) & "'"
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    as with all these sort of errors
    if you use the integrated debugger you will more easily spot this type of error


    put a breakpoint on the code
    run it, and then step through the code examining what is happening.

    or if the debugger is too powerfull or to easy to use (especially for those who think coding is a macho business ) use msg box to display variables. but debugging takes a small amount of time to learn but is far more flexible and powerfull than using msgbox to debug
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Oct 2012
    Posts
    56

    Still problems

    I have enclosed a copy of the code screen showing the error. I have stepped through the code and everything seems ok but the Sql will not execute. so any help will be good. the code is in the screen print.

    Thanks Bob

    Ps thanks to Sinndho for getting this far
    Attached Files Attached Files

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    OOps, did not notice first. Try:
    Code:
    For Each varSelect In Me.ListInvoice.ItemsSelected
        If Len(strIDs) > 0 Then strIDs= strIDs & ","
        strIDs = strIDs & Me.ListInvoice.ItemData(varSelect)
    Next
    strIDs = "WHERE Trans_ID IN (" & strIDs & ");"
    Have a nice day!

  6. #6
    Join Date
    Oct 2012
    Posts
    56

    Solved

    Thanks for all the help Sinndho the problem was I put the = and it should have been in. Still learning but managed to work it out myself with a little direction

    Bob


  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •