refer to a table in VBA

[SUZYQ]

I have a database that has tables with current data, and tables that
have history data. The structure of these tables is basically exactly
the same (except for some versioning dates).

Depending on where the record I need to retrieve lies, I may want to
use Table1 or Table1_History.

I'll then write a large SQL statement where I want to be able to use a
variable to hold the correct table's name to return the record to
populate a form.

So, how do I set the table name on the fly via VBA code so that I can
refer to it in the SQL statement?

Thanks!

 

[tina]

not sure i understand what you're getting at - it sounds like you already
know how to do what you're asking. just set the String variable to the name
of the correct table, and then concatenate the variable into your SQL
statement wherever you need to use the table name.

if that's not what you're getting at, then explain in more detail where
you're "stumped".

hth

 

[Albert D.Kallal]

You sql can be as follows

strWhatTable = inputbox("what table to use")

strWhatID = inputbox ("what reocrd to edit")

strSql = "select * from " & strWhatTable & " where ID = " & strWhatID

forms!MyFormName.RecordSource = strSql


So, the above would prompt the user as for the table name..and then the
"id". Of course, the above assumes that both tables have a field called
"id".