Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2013
    Posts
    22

    Unanswered: Need help with efficient way to achieve a fairly complex task!

    Hi All,

    I have this issue. I have a list of 37 check-boxes on a form. I would like the user to be able to check as many of them in and run a query to give them results. However, the only way I am able to think is from a brute force technique but writing queries for all possible combination and running thru a series of if conditions to execute those specific queries!

    Now that’s not the most efficient way to do this, I was hoping if there any ideas. Oh just one more thing, these check-boxes serves as the condition of what queries get executed.

    Thanks!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    nope
    write the query on the fly
    if required save that query to the query defs collection

    eg
    dim strWhereClause as string
    strWhereClause = " WHERE 1=1" 'the 1=1 is a trick that means you always have a valid where clause if no check boxes are selected
    'then go through aLL 37 CHECK BOXES AND SET THE WHERE CLAUSE AS REQUIRED
    if mycheckbox1.value = true then 'add this term to the where clause
    strWhereClause = strWhereClause & " mynumericcolumn = anumericvalue"
    endif
    if mycheckbox2.value = true then 'add this term to the where clause
    strWhereClause = strWhereClause & " mystringcolumn = 'astringvalue'"
    endif

    'then build your SQL
    dim strSQL as string
    strSQL = "SELECT my, column, list FROM mytable" & strWhereClause
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2013
    Posts
    22
    Hey, thank you for the suggestion, i have one more question, so can this method also incorporate any combinations of the 37 check-boxes inputs? Mean 1,2 and 1,4,5,6,8,34 etc? Secondly once i have the SQL built, how would i execute this SQL?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    as you are writing the SQL then its entirely up to you what groupings / logic you apply.


    best to save it to the querydefs collection and run it form there. as its a query it can be the source for forms or reports. note if you do save to the querydefs collection make certain that all columns needed inthe consumer of that data are in the SELECT bit. if your DB is a multi user DB then no doubt you will already have split into a separate front and back end but you'd really need to have separate front ends for each user as any user will overwrite any pre existing query of the same name

    if you 'just' need to use the current form then assign the SQL to the forms rowsource
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jan 2013
    Posts
    22
    ok, but i have never used QueryDef and also i have never tried to build the query. i guess i am still kinda new this....

  6. #6
    Join Date
    Jan 2013
    Posts
    22
    Hey Healdem,

    So basis your suggestion (which BTW is best way to move forward), i have written this code but it worked once but next time around its giving me an error saying "1test1" already exists. here is the code below, i would really appreciate guidance.

    Private Sub Command127_Click()
    Dim fsqlst, sqlst, wherecl, condtion As String
    Dim rs As DAO.Recordset
    Dim db As DAO.Database
    Dim aa As DAO.QueryDef

    sqlst = "SELECT MasterPipe.Client, MasterPipe.Region, MasterPipe.[Deal Name], MasterPipe.[Client Type], MasterPipe.Market, MasterPipe.[Sales/RM/Meeting Owner], MasterPipe.[Proj Type], MasterPipe.DocumentLocation, MasterPipe.[Assets (MM) Millions], MasterPipe.[Est Rev (M) Thousands], MasterPipe.Priority, MasterPipe.CKC, MasterPipe.Consultant FROM MasterPipe"

    'db.QueryDefs.Delete "1test1"


    If USCustody.Value = True Then
    wherecl = " WHERE (((MasterPipe.[US Custody])=" & "'X'" & ")" & " Or (MasterPipe.[US Custody])=" & "'x'" & ");"
    fsqlst = sqlst & wherecl
    'MsgBox fsqlst
    Else
    If GlobalCustody.Value = True Then
    wherecl = " WHERE (((MasterPipe.[Global Custody])=" & "'X'" & ")" & " Or (MasterPipe.[Global Custody])=" & "'x'" & ");"
    fsqlst = sqlst & wherecl
    End If
    End If




    Set db = CurrentDb
    Set aa = db.CreateQueryDef("1test1", fsqlst)
    With aa
    .ReturnsRecords = True
    .sql = fsqlst
    End With
    DoCmd.OpenQuery "1test1", acViewNormal, acEdit
    DoCmd.DeleteObject aa, "1test1"

    'db.QueryDefs.Delete ("aa")

    rs.Close
    db.Close
    End Sub

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    At present I can only see you deleting the query '1test1' here:-
    Code:
    DoCmd.DeleteObject aa, "1test1"
    but I don't think you are using the correct syntax
    I'd expect
    Code:
    DoCmd.DeleteObject(acQuery, "1test1")
    if you want to use deleteobject

    you seem to have commented out what I'd expect to work

    Code:
    db.QueryDefs.Delete "1test1"
    aslthough I'd probably use:-
    Code:
    currentdb.QueryDefs.Delete "1test1"
    as currentdb is effectivley a macrothat refers to the current db object

    personally I'd leave the query defined until the next time you want to run it witrh different queries. it can be usefull to leave such queries around (especially if yiu run a report of the back of the same query
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Jan 2013
    Posts
    22
    Thank you so very much i liked your last statement and have used that! thank you again for all your help!

  9. #9
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Also notice that you can dynamically change the contents (i.e. the SQL statement) of a query object (a QueryDef object ,actually):
    Code:
    Dim qdf As DAO.QueryDef
    Set qdf = CurrentDB.QueryDefs("1test1") ' the Query 1test1 must exist.
    qdf.SQL = strSQL  ' strSQL  is a string variable that contains the new SQL Statement for the query
    qdf.Close
    Set qdf = Nothing
    Have a nice day!

Posting Permissions

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