Link duplicate form to new table

[OldFart]

I have a form linked to a table for year 2008/09. I have duplicated the table
(without data) for 2009/10 and want to do the same with the form but don't
know how to make the form link to the new table - it insists on linking to
the old one.

 

[Arvin Meyer [MVP]]

You have a design problem. You do not build or add tables or fields for each
new period. Instead use a filter (query) to cull the records you want to
"discard" and display the rest. If you base your current form on that query,
you will not need to rebuild anything. All you'll ever need to do is once
per year, change the criteria in the query. 15 to 20 seconds to do and test,
as opposed to many hours rebuilding data structure.

 

[OldFart]

Unfortunately, design problem, or not, I have to have a separate table for
each year.

 

[Arvin Meyer [MVP]]

As one who has been designing databases since 1983, I disagree. Still you
can solve your problem by changing the recordsource of the form. You can
even do it on the fly with a list box or option group:

Private Sub fraRecordSource_AfterUpdate()
Select Case Me.fraRecordSource
Case 1
Me.RecordSource = "qryfrmJobsPending_Open"
Case 2
Me.RecordSource = "qryfrmJobsPending_Complete"
Case 3
Me.RecordSource = "qryfrmJobsPending_All"
End Select
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Larry Linson]

Please explain to me _why_ it would be necessary to accomplishing your
purpose that there be a separate table for each year. It's possible that you
have some totally unique requirement, and I would like to know what it is
because I can't stretch my imagination that far. But, I'm certainly willing
to listen to see if I can understand.

I've been in the computer business since 1958, so I don't think you can
"outoldfart" me.

Larry Linson
Microsoft Office Access MVP

 

[OldFart]

I'm afraid disagreeing doesn't help. As someone who only very rarely
'messes' with Access, apart from entering data on a form I created, since
which I have now slept many times.

Thanks for trying to help.

 

[OldFart]

The best reason is because that's how I want to do it.

 

[Arvin Meyer [MVP]]

The code I provided below will do exactly what you want (even though it's
wrong)

Put an Option Group Frame on your form and change the recordsource in the
AfterUpdate event. Alternatively, you can build a table of table names and
select the table from a list or combo box.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com