Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Location
    Willamette Valley, Oregon
    Posts
    7

    Unanswered: Running a query from a multi-select list box

    Right now I'm using a form with a combo box to run a make table query. This works fine, but I'd like to change the combo box so I can select multiple parameters when I run the query (rather than run the query for each parameter and hope that I catch the duplicates).

    I've found information on how to do this on this website: (mvps.org/access/forms/frm0007.htm), but I'm feeling rather dense because I'm not sure what this code needs to be attached to (the combo box, the "ok" confrol button, the query?). Any suggestions?

    thanks
    Annie

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i don't see what mvps is getting at, and since i'm mostly using passthru queries, i haven't got the energy to experiment.

    tell me: can you get to the stage where you can pull FieldName and SelectedValue from the multi-select into a string so it looks like

    ((fieldname = this) OR (fieldname = that) OR (fieldname = other))

    if yes: i have a workaround (in DAO) which i use dozens of times a day.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    telly & red wine for me now. here's my workaround (all error handling removed cos it's specific to my stuff).
    if you can get to the string mentioned above, it will work.

    set for compact on exit or your .MDE will grow and grow....

    best of luck! izy





    either put something equivalent inline or make yourself a global module containing:

    Code:
    Public Function stripWHERE(strWHERE As String) As String
        'PURPOSE
        '   strip the WHERE clause from an SQL string
        'REQUIRES
        '   any string containing the word-space "WHERE "
        'ACTION
        '   locate the first occurrance of the word-space string "WHERE " and remove all text starting with the "W
        'RETURNS
        '   orignal string without the WHERE clause (or "" in the event of an error
        
        Dim intICI As Integer
        strWHERE = Nz(strWHERE, " ")
        intICI = InStr(1, strWHERE, "WHERE ")
        If intICI = 0 Then
            stripWHERE = ""
        Else
            stripWHERE = Left$(strWHERE, intICI - 1)
        End If
    End Function



    then... (it's DAO code, but ADO-equivalent probably exists)

    Code:
    Private Sub WhateverYouLike()
        Dim strSQL As String
        Dim strWHE As String
        Dim dabs As DAO.Database
        Dim qdef As DAO.QueryDef  
        strWHE = "WHERE ((fieldname = this) OR (fieldname = that) OR (fieldname = other))"
        Set dabs = CurrentDb
        Set qdef = dabs.QueryDefs("ExistingQueryThatsOKexceptTheWHEREpart")
        strSQL = qdef.SQL
        strSQL = stripWHERE(strSQL) & strWHE & ";"
        qdef.SQL = strSQL
        qdef.Close
    End Sub
    currently using SS 2008R2

  4. #4
    Join Date
    Aug 2003
    Location
    Willamette Valley, Oregon
    Posts
    7
    Thanks much! I'll see if I can get it to cooperate.

    Annie
    -----
    I thought that it was a learning curve, not a learning cliff!

Posting Permissions

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