Query base on a changing table

[NomoreSpam4Me]

Hi there, this is my situation:

I have a query base on table.
But every week i get a new table and i dont want to have to go in my
query and maje the change. Is there a way i can have a pop up window
asking me on what table i want my query base on?

Like a criteria for a field. But in this case i'm not looking for a
field but more for a table.

thx a lot.

bye the way i'm using Win XP with Acess 03.

[SilvrT]

I created 2 table with identical fields.

The following code will prompt for which table, create a specific query
based on the table entered, and open the query displaying specific
records.

You may need to set a Reference to the Microsoft DAO 3.6 Object Library
Sub ShowVariableTables()
Dim strTableName As String
Dim strsql As String
Dim dbsCurrent As database
Dim qdfMyQuery As querydef

Set dbsCurrent = CurrentDb
strTableName = InputBox("Which Table?")

strsql = "SELECT DivisionID, Division, DivisionName, Description " & _
"FROM " & strTableName & " " & _
"WHERE Division = 'SFP'"

DoCmd.DeleteObject acQuery, "qryMyQuery"

Set qdfMyQuery = dbsCurrent.CreateQueryDef("qryMyQuery", strsql)

DoCmd.OpenQuery "qryMyQuery"

End Sub