Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Maker (v2000)

    I have a SQL statement that is generated in VBA as a user completes fields on a form. A report then opens and the resulting SQL code is loaded as the report's Record Source. It runs quite nicely. I have no trouble updating a form or report using a SQL statement from VBA. However, I recently came across another need...how can I update a query with the resulting SQL statement. Once the SQL statement is created and the form is closed, I will still need to recall the SQL until the next time the form is used. As a result, I thought updating a saved query could do this for me but I don't know how to update a query in code. If there's a different approach, I'm all ears.
    Thanks!
    Dashiell

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query Maker (v2000)

    A querydef object has a SQL property, which you can set in code to your new SQL string, if that's what you are asking.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Maker (v2000)

    If I want to update qrySample with the following VBA:
    MySQL = "SELECT tblClients.ClientName FROM tblClients WHERE (((tblClients.ClientName) Like "John*"));"
    How would I do it?
    Thanks Charlotte!

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Query Maker (v2000)

    Try this:
    Dim Qdf as DAO.QueryDef
    MySQL = "SELECT tblClients.ClientName FROM tblClients WHERE (((tblClients.ClientName) Like "John*"));"
    Set Qdf = CurrentDB.QueryDefs("qrySample")
    Qdf.SQL = MySQL
    Set Qdf = Nothing

  5. #5
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Maker (v2000)

    That worked great. Thanks Pat!!

Posting Permissions

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