Page 1 of 3 123 LastLast
Results 1 to 15 of 44
  1. #1
    Join Date
    Sep 2004
    Location
    Virginia, USA
    Posts
    98

    Question Unanswered: Can A Query Be Created That Allows the User to Select the Table?

    I have a datbase with a master equipment list and a table for expenditures for each year from 1999 to present. I need a query that will allow the user to search by equipment serial number and allow the user to select which table (year) will be searched.

    Thanks.


    This forum has been a fantastic help to me from day 1.

  2. #2
    Join Date
    Sep 2004
    Posts
    161
    Yes but i think is possible only by code :

    Querystring="select * from " & NomTable
    Docmd.runsql Querystring

  3. #3
    Join Date
    Sep 2004
    Location
    Virginia, USA
    Posts
    98
    This would be in the SQL view?

    The code doesn't make much sense to me (very rusty on my SQL).

    If that is the beginning of the sequel statement, how would I write the select part of the query? Would it be:
    Select *
    From Querystring
    .........

    Thanks for the help.

  4. #4
    Join Date
    Jun 2004
    Location
    Terrapin Nation
    Posts
    205
    No, you would write that type of code and place it on a form behind a button or something. then the user would choose the table from a combo box and asjk for the results by pushing a button.

  5. #5
    Join Date
    Sep 2004
    Location
    Virginia, USA
    Posts
    98

    Thumbs up

    Thanks. I'll have to take a look and see how to do that. I appreciate the help from all.

  6. #6
    Join Date
    Sep 2004
    Location
    Virginia, USA
    Posts
    98
    I've tried putting the code (listed in previous response) behind a button and then was asked for a Macro to run. I don't have any macros set up.

    Just looking for a way for the user to select which table they want the query to run in.

    Thanks.

  7. #7
    Join Date
    Jun 2004
    Location
    Terrapin Nation
    Posts
    205
    Post the code you used.

  8. #8
    Join Date
    Sep 2004
    Location
    Virginia, USA
    Posts
    98
    Quote Originally Posted by TerpInMD
    Post the code you used.

    Here is the code. I'm new at writing code so any help would be appreciated.

    Querystring="select * from " & NomTable
    Docmd.runsql Querystring

  9. #9
    Join Date
    Jun 2004
    Location
    Terrapin Nation
    Posts
    205
    Sorry, Jepi was incorrect with sending you down the docmd.runsql path (that is for action queries like insert or append only).

    You have to create the SQL via code then create a tempquery:

    Dim dbs as dao.database
    dim qdf as dao.querydef
    dim txtSQL as string
    Dim txtQueryName as string

    txtQueryName = "SomeNameHere"

    txtSQL = "SELECT & from " & me.YourControlWithTableValue & ";"

    Set qdf = dbs.CreateQueryDef(txtQueryName, txtSQL)


    docmd.openquery qfd.Name


    Dont forget to delete the query later:


    dbs.QueryDefs.Delete qdf.Name

  10. #10
    Join Date
    Sep 2004
    Location
    Virginia, USA
    Posts
    98
    Thanks. I'll see how this will work. A couple of questions for clarification.

    1. When you say delete, do you mean as part of the SQL statement or separately? Why delete it if it will be used multiple times?
    2. When this is done, what type of "response" will I get? Will I get the window to fill in the data for the Table?
    3. Can I use one query to run this with the table as the variable (since the tables have the same structure) or do I need a separate query for each selection the customer may make?

    Thanks a lot for the help. I'll learn this stuff if it kills me!! Thanks for the great support and quick response.

    Go Terps!!

  11. #11
    Join Date
    Jun 2004
    Location
    Terrapin Nation
    Posts
    205
    If you dont want to delete the query, then why are you trying to change the source (table) at run-time? Why not juse create all the queries ahead of time and select which one you want to run based on the users action?


    This code is used for creating a dynamic query. You would want to delete the query later unless you dont care that everytime it is run there is a new query in the dB.

  12. #12
    Join Date
    Sep 2004
    Location
    Virginia, USA
    Posts
    98
    Terp,

    That is what I thought about doing. Creating a query for each year and then allow the user to select but I didn't know how to make it selectable for the user. I'm not familar with Macro writing and don't know how to do it. I can create the separate queries easy enough, I just need to know how to have the user select which year to run.

    Also, is there a way to run a query that will span multiple tables. Say if the user wanted to look at the charges from 1999 to date for a particular serial number.

    Thanks again for all of the help. I appreciate you patience.

    kc

  13. #13
    Join Date
    Sep 2004
    Posts
    161
    Merci to have corrected my syntax. I wrote too quickly.

  14. #14
    Join Date
    Sep 2004
    Location
    Virginia, USA
    Posts
    98

    Question

    Sorry for not looking sooner. Had some personal stuff going on.

    Still looking at finding a way for a query to span multiple tables. I have billing information in a separate table by year (1999 to present year) and want to run a query that can look up equipment serial numbers in one, two or all of the years and total the results.

    I'm not sure if I'm explaining this right and I hope someone can help.

    Example of what I want to do:

    Serial Number = 12345 call totals from 2001, 2002, 2003, 2004 (each year a different table) add totals for each year and total of all years.

    Thanks.

  15. #15
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    my suggestion is if you can;
    merge all years tables into one big table with a year field in it. that way you can search in one table and do that job with basic SQL queries.
    ghozy.

Posting Permissions

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