Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2006
    Posts
    11

    Unanswered: Running query using input from multiple list boxes on form

    Ok ive hunted all over and found several things that look like what I want but don't work for me :s

    I want to let the user select from upto 5 list boxes on a form and then process that data in a query to find matching records.

    The only way I can think to do this now without using VB (ive never used VB willing to give it a go if needed) is to run a layered type query or several querys so first filter records based on selection from first listbox, then filter the result from that using selection from second listbox etc. Is this possible using SQL (lol I know very little SQL as well but suspect its easier than VB)

    The problems ive had are that usually results don't come up because not all the listbox's have data in them or all records come up regardless of whats in the list boxes. the bit I don't know is how to run a query on the results of another query if you get my meaning.

    ps. just off to bed now so won't be back on for several hours

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    If a listbox is multiselect, you're definitely into VBA. If not:

    Query1
    SELECT Blah FROM TableName WHERE FieldName = Listbox1

    Query2
    SELECT Blah FROM Query1 WHERE FieldName = Listbox2

    Which can be modified if selecting a listbox is optional:

    http://www.mvps.org/access/queries/qry0001.htm
    Paul

  3. #3
    Join Date
    Apr 2006
    Posts
    157
    you can always preselect a record in your listbox to ensure that all listboxes would have values.

    and then just run it in vb, woohooo

    SELECT FROM myquery/table WHERE field1 = '" & listbox1.column(x) & "' AND field2 = '" _
    & listbox2.column(x) & "' AND so on
    Only quitters quit!

  4. #4
    Join Date
    Nov 2006
    Posts
    11
    Thx for the reply's I have used the method by pbaldy it is not the most tidy way im sure, but it works how I want and is the easiest setup.

  5. #5
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    good choice, you should always handle this sort of thing in a query, unless for some reason you can't.

    As an example if the table your getting the data from changes this isn't a very comon situation but you have to use syntaxerror's method.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  6. #6
    Join Date
    Sep 2006
    Posts
    265
    If use this try of select often here a bit of code I picked up ages ago and bashed it about a bit.

    Public Function GetSelectedRecords()

    ' This function is designed to concatenate Records with a field to be used later for both IN Statements and QueryStrings
    ' The forms contain the scope of the array Records2Select and SelectNum signifies to use String or Numeric values i.e. add quotes
    ' In ALL cases the default value is zero and if the function finds a zero it knows when to stop!

    Dim intI As Integer
    Dim intN As String
    Dim intV As String
    Dim MyObject As Object
    Set MyObject = CodeContextObject

    For intI = 1 To MyObject![Records2Select]
    intV = MyObject(CStr(intI)).Value
    If MyObject![SelectNum] = "N" Then
    If intI = 1 And intV <> "0" Then
    intN = "'" & intV & "'"
    ElseIf intI <> 1 And intV <> "0" Then
    intN = intN & ",'" & intV & "'"
    End If
    Else
    If intI = 1 And intV <> 0 Then
    intN = intV
    ElseIf intI <> 1 And intV <> 0 Then
    intN = intN & "," & intV
    End If
    End If
    Next
    GetSelectedRecords = intN

    End Function

Posting Permissions

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