Results 1 to 2 of 2

Open on the fly query with VBA

  1. #1
    RogerAnnArbor is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    8

    Open on the fly query with VBA

    I have Access 2016. I would like to know how I can create a query in code in VBA and then have it open in an Access form.

    I tried:

    Dim strSQL1 As String
    Dim qdf As DAO.QueryDef

    strSQL1 = "SELECT tblSightings2001.Species, Species.LatinName, Species.BirdGroups, tblSightings2001.Numbers, tblSightings2001.Place, tblSightings2001.DateSighted " & "FROM Species INNER JOIN tblSightings2001 ON Species.Species = tblSightings2001.Species " & " WHERE (((tblSightings2001.DateSighted) Between #1/1/2012# And #12/31/2012#)) " & " ORDER BY tblSightings2001.DateSighted; "


    Set qdf = CurrentDb.QueryDefs("qryDummy")
    qdf.SQL = strSQL1
    DoCmd.OpenQuery "qryDummy"
    Set qdf = Nothing

    but it do not work.

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,307
    Open a Select query in Access does nothing at all.

    What you want to do is to use this query named "qryDummy" as the Data Source of your Form.
    Then, set the SQL code of the query like you are doing in your code above. But then instead of opening the Query, open the Form that uses the query.

    Here is an example: http://www.mrexcel.com/forum/microso...ml#post3099809
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 09-30-2016, 02:47 AM
  2. Replies: 5
    Last Post: 11-02-2015, 08:51 AM
  3. Replies: 1
    Last Post: 06-03-2014, 05:56 AM
  4. Replies: 2
    Last Post: 08-07-2013, 06:44 AM
  5. Help on report to open form to open query...
    By saseymour in forum Programming
    Replies: 13
    Last Post: 07-16-2013, 07:11 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums