How do I change a table name from a query using VBA? 1

[Aximboy]

I need to run a report based on the selected year on the form. I have 5 tables with different years worth of data; (I know this is a bad idea but this is what my manager wants)

tblYear2011
tblYear2012
tblYear2013
tblYear2014
tblYear2015

I also have a form with combobox where a user can select which year he or she can print. Selecting a year on the combobox runs a report for that particular year/table.

The report’s record source is from this query, qrySUMMARYREPORT, and this query is based on the tables above (depending on which year is selected)

How do I change a table name from a query using VBA?
Or is there a better approach on this one?

Thanks in advance!!!

 

[dhookom]

Do you want to change the table name in a query or with a query? You might be able to implement faq701-7433.

If the structures are all the same each year, you can create a union query of all the years and add a column for the year number. Then filter the report for the year.

Duane
Hook'D on Access
MS Access MVP

 

[Aximboy]

Mr. dhookom

Thank you, thank you & thank you!