Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246

    Unanswered: Building a query from code

    Hi
    I have a form that uses various selection criteria in a multiple seclection list box as selection criteria in a query.

    The query is built using the following code

    'Create query
    MyDB.QueryDefs.Delete "Export_multi_Policies_qry"
    Set qdef = MyDB.CreateQueryDef("Export_multi_Policies_qry", strSQL)

    'Open the query, built using the IN clause to set the criteria
    DoCmd.OutputTo acOutputQuery, "Export_multi_Policies_qry", acFormatXLS, , True

    The problem is that because the code deletes the original query, when I built the original query I had various linked table in it but this bit of code does not put that back in.

    Question is how do I put these tablels back in as per the original using code?
    The relationships between the tables are not permanent.

    Regards
    John

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    have a look

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    Thanks Izy

    I wonder if you can explain a few thing to me please.

    I take it that the bit of code I'm interested in is this bit

    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("ExistingQueryThatsOKexceptTheWHERE part")
    strSQL = qdef.SQL
    strSQL = stripWHERE(strSQL) & strWHE & ";"
    qdef.SQL = strSQL
    qdef.Close

    End Sub


    But I'm not certain of teh bits I need to replace or supplement my code. If I take out the bit in my code that deletes the query and the bit that creates a new query and replace it with the bits I've highlighted in bold?

    Regards
    John

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    it's a pain to rewrite the whole SQL stuff when you only want to change the WHERE or ORDER BY stuff so i got laaaaazy some while ago and use stripXxxx() code all over the place.

    you need to find out how to set the qdef object to an existing query (my example is DAO, but you probably switched to ADO so you need some research).

    qdef has a .SQL property
    strip the tail off the .SQL with a function like stripWhere() and replace it with your new stuff as in my string strWHE.

    so you need
    the stripper function plus
    some stuff to build strWHE plus
    some stuff to set qdef to an existing query plus
    strSQL = qdef.SQL
    strSQL = stripWHERE(strSQL) & strWHE & ";"
    qdef.SQL = strSQL
    qdef.Close

    if you don't want to get all artsy fartsy with strippers, rebuild your query in A's design grid, switch to SQL view (top-left button) and copy/paste the SQL into your code. ok you will have to mess around with some string concatenation, but you can just recreate the whole SQL string.....

    ....and whilst you are there, maybe there is a way you could save some disk thrashing by using a temporary query.
    Set qdef = MyDB.CreateQueryDef("", strSQL)
    creates the qrydef but doesn't save it (no save, no delete, no compact obligatory to keep you MDE in shape).

    you should be able to send the output of qdef to XL but it's not something i do so you need to check out the syntax elsewhere.

    the openrecordset syntax on a qdef goes like
    set recs = qdef.openrecordset(dbopensnapshot)
    so maybe that gives you a hint on the outputto syntax

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    Izy,
    Thanks for the help. What I've decided to do is that as my VB/SQL skills are very limited I've done a "pre query" that joins all the necessary tables and used that as the record source. It seems to work OK

    Once again thanks for your help and apologies if I wasted your time

    Regards
    John

Posting Permissions

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