INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Parameter Query question

Parameter Query question

(OP)
hello
In a saved query, is there any way to use a parameter for a table's name in the FROM statement.
I get an error message (Parameter 'Enter_Table_Name' specified where a table name is required) when I try this...

CODE

PARAMETERS [Enter_Table_Name] Text(255);
SELECT t.X, t.Y, COUNT(*) AS Z
FROM  [Enter_Table_Name] t
GROUP BY t.X, t.Y; 

I use this as a quick but frequent diagnostic test, so I don't really want to build the query in VBA.
Can it be done in a saved query?

thanks in advance
Teach

RE: Parameter Query question

I doubt it can be done as hoped.

Consider a simple generic function that you can reuse again and again.

Duane
Hook'D on Access
MS Access MVP

RE: Parameter Query question

No it cannot be done nor can you do it with field names.

RE: Parameter Query question

Here is code to do this. I would make a form with a button on it. Have a subform with a datasheet. Then you can change it on the fly

CODE -->

Public Sub ChangeTableQuery()
  Const queryName = "myQuery"
  Dim tableName As String
  Dim tdf As TableDef
  Dim qdf As QueryDef
  Dim validTable As Boolean
  Dim existingQuery As Boolean
  Dim continueCancel As Long
  Dim strSql As String
  Dim CDB As DAO.Database

  'Get and Verify Table
  Do
     tableName = InputBox("Enter table Name", "Enter Table Name")
     For Each tdf In CurrentDb.TableDefs
       If tableName = tdf.Name Then
         validTable = True
        Exit For
      End If
     Next tdf
     If Not validTable Then
       continueCancel = MsgBox("Table " & tableName & " is not valid. Enter new table name or cancel to exit.", vbOKCancel, "Invalid Name")
       If continueCancel = vbCancel Then Exit Sub
     End If
  Loop Until validTable = True
  'See if the existing qdf exists
  For Each qdf In CurrentDb.QueryDefs
    If qdf.Name = queryName Then
      existingQuery = True
      Exit For
    End If
  Next qdf
  'some sql string
  strSql = "Select * from " & tableName & " order by 2"
  Set CDB = CurrentDb
  If Not existingQuery Then
    Set qdf = CDB.CreateQueryDef(queryName, strSql)
  Else
    Set qdf = CDB.QueryDefs(queryName)
    qdf.SQL = strSql
    CurrentDb.QueryDefs.Refresh
  End If
  DoCmd.Close acQuery, queryName
  DoCmd.OpenQuery qdf.Name
End Sub 

RE: Parameter Query question

(OP)
Nicely done, MajP! Very instructive.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close