SQL with multiple similar queries

  • Thread starter Thread starter lbsstacey
  • Start date Start date
L

lbsstacey

I have designed a database where I have many queries that look the same but
they each refer to a different table that contains specific client
information. I run my queries from a process form that contains an
abbreviated name that refers to each client. I am wondering how I can change
my query to instead of referring to the table "Terms_BE" I can refer to a
variable "Terms _" & [Forms]![MainForm]![AbbrName]

INSERT INTO Terms_BE ( SubSSn, MBRSSn, Rel, [Employee_First Name],
[Employee_Middle Initial], [Employee_Last Name], EffDate, TermDate, Field21,
Expr1 )
 
Why don't you make the query dynamic, something like;

Dim db As DAO.Database
Dim qry_x As DAO.QueryDef
Dim x as String

Set db = CurrentDb
Set qry_x = db.QueryDefs("Your_Query")

x = "Terms_BE" 'Table Name

qry_x.SQL = "INSERT INTO " & x & " ( SubSSn, MBRSSn, Rel, [Employee_First Name],
[Employee_Middle Initial], [Employee_Last Name], EffDate, TermDate, Field21,
Expr1 )"

Try that
 
Back
Top