Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Columbus, Ohio, USA
    Posts
    286
    Thanks
    0
    Thanked 0 Times in 0 Posts

    List Box for Query (A2k)

    Good Morning All,

    Attached is a small database with a form and 2 tables. On the form is a list box with 27 items and a button that I want to use to build a query based on the selections from the list box. The query needs to be able to update the corresponding fields in the main table to zero. My vba skills are lacking in something of this magnitude so any and all suggestions are greatly appreciated. TIA.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: List Box for Query (A2k)

    Here is code for the command button:
    <code>
    Private Sub Command2_Click()
    Dim strList As String
    Dim strSQL As String
    Dim varItm As Variant
    ' Loop through the selected items
    For Each varItm In Me.List0.ItemsSelected
    strList = strList & ", " & Me.List0.ItemData(varItm) & "=0"
    Next varItm
    ' Check whether anything was selected.
    If strList = "" Then
    ' If not, warn user and get out
    Me.List0.SetFocus
    MsgBox "Please select at least one item from the list box.", vbExclamation
    Exit Sub
    End If
    ' Get rid of first ", "
    strList = Mid(strList, 3)
    ' Create SQL string
    strSQL = "UPDATE tbl_NewMainInfo SET " & strList
    ' Temporarily display the SQL, you can remove this later
    MsgBox strSQL, vbInformation
    ' Suppress warnings
    DoCmd.SetWarnings False
    ' Execute SQL
    DoCmd.RunSQL strSQL, True
    ' Turn on warnings again
    DoCmd.SetWarnings True
    End Sub
    </code>
    See attached version.
    Attached Files Attached Files

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Columbus, Ohio, USA
    Posts
    286
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Box for Query (A2k)

    Thank you Hans. Works great.

Posting Permissions

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