Results 1 to 4 of 4
  1. #1
    fishen88 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    3

    Passing Multi Select List Box selected records to a table using an append/update quer

    Hi all,

    I currently am using a set of cascading comboboxes that do wonderful at filtering down the data in my unbound listbox. I am at the point now though that I want to take the selected records and when they are highlighted, by pressing an update button, update an existing table with the records.

    My listbox is named lsttaxonomyno, update button is called cmdupdate, and the table is called record.

    Any suggestions on what to do differently or how to fix my code would be greatly appreciated.

    My current code at the moment is as follows:

    Private Sub cmdUpdate_click()






    Dim intlistcount
    Dim intx As Integer
    Dim strsql As String


    intlistcount = Me!lstTaxonomyno.ListCount


    For intx = 0 To intlistcount
    If Me!lstTaxonomyno.Selected(intx) Then
    If strsql & "" = "" Then
    strsql = "(qrytaxonomyno.taxonomyno)='" & Me!lstTaxonomyno.ItemData(intx) & "' "
    Else
    strsql = strsql & " or " & "(qrytaxonomyno.taxonomyno)='" & Me!lstTaxonomyno.ItemData(intx) & "' " '
    End If
    End If
    Next intx
    If strsql & "" = "" Then
    MsgBox "No criteria selected, all records will be displayed", vbInformation, "System Message..."
    strsql = "update Me.records set taxonomyno = " _
    & " Where taxonomyno = " & Me.lstTaxonomyno.Column(0, intx)
    Else
    strsql = "update Me.records qrytaxonomyno.taxonomyno from qrytaxonomyno Where ((" & strsql & "));"
    End If




    Me!cnttaxonomyno.Form.RecordSource = strsql


    End Sub

    Thanks.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Well, let's start with the fact that you can't set the record source of a form to an action query. If you're trying to execute that SQL:

    CurrentDb.Execute strsql, dbFailOnError

    Also, see here for a more efficient loop:

    http://www.baldyweb.com/MultiselectAppend.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    fishen88 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    3
    Quote Originally Posted by pbaldy View Post
    Well, let's start with the fact that you can't set the record source of a form to an action query. If you're trying to execute that SQL:

    CurrentDb.Execute strsql, dbFailOnError

    Also, see here for a more efficient loop:

    http://www.baldyweb.com/MultiselectAppend.htm

    This helped out tremendously!

    I got the functioning working now. My original attempt deserves a face to palm effort.

    Thanks!

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 14
    Last Post: 12-06-2012, 11:25 AM
  2. Replies: 5
    Last Post: 08-23-2012, 09:33 AM
  3. Replies: 4
    Last Post: 05-08-2012, 10:04 AM
  4. Replies: 2
    Last Post: 03-27-2012, 01:02 PM
  5. Multi-select listbox and update flag in table
    By Suresh in forum Programming
    Replies: 0
    Last Post: 12-19-2007, 01:04 AM

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