Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Mar 2006
    Location
    Baltimore, Maryland.. It's a big place you won't be able to find me!
    Posts
    15

    Unanswered: Passing Multiple Criteria from form to query

    Hello all,

    I am having trouble passing a criteria from a form to a query. I have a multi-select list box and use the following code to string the SQL together.

    Private Sub house_Click()

    Dim frm As Form

    Dim ctl As Control

    Dim varitem As Variant

    Dim strsql As String


    Set ctl = house

    strsql = ""

    For Each varitem In ctl.ItemsSelected

    strsql = strsql & ctl.ItemData(varitem) & " Or "

    Next varitem

    strsql = Left$(strsql, Len(strsql) - 4)

    End Sub




    My question is how do I pass it to my query?

    I have on the QBF [forms]![tst]![house]

    However I get no results.

    Any help would be appreciated.

    thanks

    Joe

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Jokeascool
    My question is how do I pass it to my query?
    I'm afraid the answer is - you can't

    You will probably have to write the entire SQL statement in your code and create\ modify the query programatically. So - an option would be to extend your strsql to include "SELECT a, b, c FROM MyTable WHERE " first. Create a QueryDef object, instantiate it as your current query and alter the .SQL property with your new SQL string.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2006
    Location
    Baltimore, Maryland.. It's a big place you won't be able to find me!
    Posts
    15

    Thank You

    Thank you for your help. I actually thought that I might have to do that, so I started to experiment and can not seem to get it to work. Here is what I have so far. You pick a bunch of items from the multi select list box. These are then passed to a sub that will run when a button is clicked. I have tried to run the query by hard coding in some parameters. Here is what I have so far.




    Sub ThisBetterWork()





    Dim qdftemp As QueryDef

    Dim lineone As String

    Dim linetwo As String

    Dim linethree As String

    Dim linefour As String

    lineone = "SELECT Community_tbl.Community_Code, Community_tbl.Community_Name, Trade_Partner_Products_Tbl.Vendor_Id, Trade_Partners_Tbl.Vendor_Name, Trade_Partner_Products_Tbl.Phase, Options_Tbl.Option_Description, Trade_Partner_Products_Tbl.Price, [price]*[quanity] AS Total, House_Type_tbl.House_Name, Take_Offs_Tbl.House_Code, Take_Offs_Tbl.Cost_Code, Take_Offs_Tbl.Option_Number, Take_Offs_Tbl.Product, Take_Offs_Tbl.Quanity"

    linetwo = "FROM Community_tbl, Options_Tbl INNER JOIN ((House_Type_tbl INNER JOIN Take_Offs_Tbl ON House_Type_tbl.House_Code = Take_Offs_Tbl.House_Code) INNER JOIN (Trade_Partners_Tbl INNER JOIN (Phases_Tbl INNER JOIN Trade_Partner_Products_Tbl ON Phases_Tbl.Phase = Trade_Partner_Products_Tbl.Phase) ON Trade_Partners_Tbl.Vendor_ID = Trade_Partner_Products_Tbl.Vendor_Id) ON Take_Offs_Tbl.Product = Trade_Partner_Products_Tbl.Product) ON Options_Tbl.Option_Number = Take_Offs_Tbl.Option_Number"

    linethree = "WHERE (((Community_tbl.Community_Code) =" & Chr(34) & "rk" & Chr(34) & ")" & "And ((Trade_Partner_Products_Tbl.Phase) = 363))"

    linefour = "ORDER BY Take_Offs_Tbl.House_Code, Take_Offs_Tbl.Option_Number;"


    qdftemp = curentdb.querydefs(Contract_step_one_qry)


    qdftemp.sql = lineone & linetwo & linethree & linefour



    End Sub




    Now I am just starting with VBA so do not be afraid to laugh and shout cruel things because this is not right. I would rather enjoy that to be honest.

    Anyway. If you could let me know where I went wrong!!

    Thanks

    Joe

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    DUPLICATE - deleted
    currently using SS 2008R2

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    some obvious things:

    your query will contain
    QuanityFROM
    and
    Option_NumberWHERE

    ...add some spaces and worry about the spelling of "Quanity"

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I've seen much worse stabs at this sort of thing by "experienced" programmers

    How does the below do (note - added spaces otherwise your clauses are not separated. Also you need to use the SET statement when instantiating an object - a bit silly considering the LET is implicit but there you go - fixed in .NET I also removed the brackets that Access unnecessarily puts in where clauses - they can cause syntax errors if you are editing around them).

    If it still fails please let us know the error message. Also - you don't have an error trap so it will come up with a Debug\ END message box if there is an error. Click debug and the offending line would be highlgihted. Post that too.

    Code:
    Sub ThisBetterWork()
     
    Dim qdftemp As QueryDef
     
    Dim lineone As String
     
    Dim linetwo As String
     
    Dim linethree As String
     
    Dim linefour As String
     
    lineone = "SELECT Community_tbl.Community_Code, Community_tbl.Community_Name, Trade_Partner_Products_Tbl.Vendor_Id, Trade_Partners_Tbl.Vendor_Name, Trade_Partner_Products_Tbl.Phase, Options_Tbl.Option_Description, Trade_Partner_Products_Tbl.Price, [price]*[quanity] AS Total, House_Type_tbl.House_Name, Take_Offs_Tbl.House_Code, Take_Offs_Tbl.Cost_Code, Take_Offs_Tbl.Option_Number, Take_Offs_Tbl.Product, Take_Offs_Tbl.Quanity "
     
    linetwo = "FROM Community_tbl, Options_Tbl INNER JOIN ((House_Type_tbl INNER JOIN Take_Offs_Tbl ON House_Type_tbl.House_Code = Take_Offs_Tbl.House_Code) INNER JOIN (Trade_Partners_Tbl INNER JOIN (Phases_Tbl INNER JOIN Trade_Partner_Products_Tbl ON Phases_Tbl.Phase = Trade_Partner_Products_Tbl.Phase) ON Trade_Partners_Tbl.Vendor_ID = Trade_Partner_Products_Tbl.Vendor_Id) ON Take_Offs_Tbl.Product = Trade_Partner_Products_Tbl.Product) ON Options_Tbl.Option_Number = Take_Offs_Tbl.Option_Number "
     
    linethree = "WHERE Community_tbl.Community_Code =" & Chr(34) & "rk" & Chr(34) & "And Trade_Partner_Products_Tbl.Phase = 363 "
     
    linefour = "ORDER BY Take_Offs_Tbl.House_Code, Take_Offs_Tbl.Option_Number;"
     
    Set qdftemp = curentdb.querydefs(Contract_step_one_qry)
     
    qdftemp.sql = lineone & linetwo & linethree & linefour
     
    End Sub
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ...and you make your life slightly more complicated with
    linethree = "WHERE (((Community_tbl.Community_Code) =" & Chr(34) & "rk" & Chr(34) & ")" & "And ((Trade_Partner_Products_Tbl.Phase) = 363))"

    which could read (adding one of the missing spaces mentioned above en passent and discovering/correcting a third missing space):

    linethree = " WHERE (((Community_tbl.Community_Code) =" & Chr(34) & "rk" & Chr(34) & ") And ((Trade_Partner_Products_Tbl.Phase) = 363))"

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Mar 2006
    Location
    Baltimore, Maryland.. It's a big place you won't be able to find me!
    Posts
    15
    Thank you for the checks. I also cannot seem to instantiate the querydef.


    thanks

    Joe

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Looks like we are singing from the same hymm sheet Izy
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    hi Pootle,

    seems like we keeping time too!

    your e.g. dropped a couple of quotes in the Set
    - should read:
    Set qdftemp = curentdb.querydefs("Contract_step_one_qry")

    izy
    currently using SS 2008R2

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    This is garbage -- see later in the thread

    ...and since we are talking about qdftemp, why not go the whole hog and use

    Set qdftemp = curentdb.querydefs("") ' <<<< horribly wrong

    which creates a truly temporary qdef that only lives as long as whatever you DIMmed it in (typically the sub/func).

    EDITED A FEW SECONDS LATER BUT NOT FLAGGED AS EDITED BY THE SITE:
    LATER: decided it's best to caution about this bit since i don't use "" qdefs as .rowsource or .recordsource: makes a perfectly fine .recordsource or .rowsource as long as you use it before you lose it
    LATER STILL ---- i can't make it work as .rowsource or .recordsource: please post if you find a way to do this

    izy
    Last edited by izyrider; 03-10-06 at 12:05.
    currently using SS 2008R2

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by izyrider
    your e.g. dropped a couple of quotes in the Set
    Oops - well spotted.
    Quote Originally Posted by izy
    Set qdftemp = curentdb.querydefs("")

    which creates a truly temporary qdef that only lives as long as whatever you DIMmed it in (typically the sub/func).

    decided it's best to caution about this bit since i don't use "" qdefs as .rowsource or .recordsource
    Coo - so how would you use it as one of those? Can you assign a qdef object to those props?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Mar 2006
    Location
    Baltimore, Maryland.. It's a big place you won't be able to find me!
    Posts
    15

    thanks

    I really aprreciate very ones help!

    when i ran the sub I get the error:

    Compile Error

    User defined type not defined

    this occurs at the:

    Dim qdftemp As QueryDef



    Thanks

    Joe

  14. #14
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    try
    DAO.querydef

    Pootle: that's why i added the caution - never tried it!
    ...and it probably wont work!!

    i use "" qdefs as follows:

    dim dabs as dao.database
    dim qdef as dao.querydef
    dim recs as dao.recordset

    Set dabs = CurrentDb
    Set qdef = dabs.CreateQueryDef("")
    qdef.Connect = myConnectionString
    qdef.ReturnsRecords = True
    qdef.SQL = "USE myDatabase EXEC mysp_thisOne " & [Forms]![frmMain]![myBox]
    Set recs = qdef.OpenRecordset(dbOpenSnapshot)


    izy
    currently using SS 2008R2

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yup - click Tools -> References -> Scroll to Microsoft DAO 3.6 Object Library and tick. I you aren't using ADO (if you don't know you aren't) untick Microstoft ActiveX Data Objects 2.x too.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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