Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2003
    Location
    paris, FRANCE
    Posts
    88

    Unanswered: transform query into sub

    Hello,
    I have created a set of queries and macros which "transform" a mother database into different tables. My problem is that I want to know how I can change a query into a module which will be commanded by a macro.
    I need to do this because my database treatment will be automatical, and when I put my query inside a macro, access asks me If I want to add the lines to the tables and so on.
    I want the user to be able to populate a table on a single click, therefore I need to point the use of a button from the form on a macro based on a module and not on queries.

    The queries I use are quite simple, they use small function based on Instr function, or the count function itself...
    Can someone tell me how to write a code line which would perform the same task as the query? After that I'll bind the action of the code and the macro.

    Thanks
    nico

  2. #2
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    why not put the following code behind a button

    docmd.setwarnings false
    docmd.openquery "queryName"
    docmd.setwarnings true

  3. #3
    Join Date
    Apr 2003
    Posts
    280
    I have never try openquery. What the difference between runSQL and openquery. Is it like openquery display the table of the criteria your looking for?

  4. #4
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    if it is a select (display data ) query - yes it will show the data
    If it is an action query (make table, update delete) you will not see the warnings that it is running due to the docmd.setwarnings false
    command

    the docmd.openquery "queryName" will run the query withour\t having to place the literal sql statement in the code or macro

  5. #5
    Join Date
    Apr 2003
    Posts
    280
    Well, sometimes you can only go so far with the Access queries. You have to write the SQL statements in vba in order to get what you want.

    for instance

    Dim strSQL as string

    strSQL = "Select FirstName From Students Where FirstName = " & Textbox.value & ""
    Docmd.SetWarning False
    Docmd.RunSQL strSQL
    Docmd.SetWarning True

  6. #6
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    what?

    a query with the same criteria would run just as well.

    As a developer I routinely will write sql statements on the fly, but for newbiews and non developers

    docmd.openquery "queryName" is more than adequate

    just my opinion

  7. #7
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    btw

    db.execute is a better solution than runsql

Posting Permissions

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