Re: Query base on a changing table

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

.