Problem of Common Form & Str() vs CStr()?

[bluy]

Hi, I'm a beginner of programming access. I have several tables which have the same structure and named by the years (eg: 1999,2004). I create a common form to show them. I use the following code to do that:

DoCmd.OpenForm "frmCommon", , , strSQL, acFormReadOnly, acHidden
' if I use Str() instead, I'll get error message, WHY?
Forms!frmCommon.RecordSource = CStr(Year(Now()))
Forms!frmCommon.Filter = strSQL
Forms!frmCommon.FilterOn = True
Forms!frmCommon!cmdPrint.Enabled = False
Forms!frmCommon!cmdPrint.Visible = False
DoCmd.SelectObject acForm, "frmCommon", False

AND what I do is a good way to finish the work? Can anyone give me some advices? Thanks!

 

[Pat Hartman]

You should not have a table for each year. All the data should be in a single table. You can use a query with selection criteria to extract a year at a time. That will dramaticly reduce the amount of code you need to write and also eliminate the annual maintenance that your current approach requires.