Select which table for a form to edit

[b3nb123]

At the moment I've got three tables for each of three year's worth of clients, the form for each is the same, so I've duplicated the form three times and edited the Record Source of each. So that works, with three forms listed for me to double click on to edit.
But would it be tidier to have one form, then a radio button to select which table to load into the form? Can anyone talk me through how I'd do this?

Thanks

[ranman256]

You should have 1 form, and 1 table.
all years in 1 table ,a year field lets you differentiate.

then filter on 1 client, or 1 year.

[b3nb123]

I like the years to have their own table, that way I don't have clients info from 5 years ago, once the years have passed I file the old year away and don't swim in all the old data!

[davegri]

You could do it on one form. The radio button after_update event would change the recordsource for the form to the appropriate table/query.

[b3nb123]

That sounds like what I need. Unfortunately, I've never worked with queries / radio buttons etc, I'm guessing it's a little too complicated to just walk me through it?!

[davegri]

I'm guessing it's a little too complicated to just walk me through it?!

Can you post your db here? I'll take a look and see what is required.

[accesstos]

Hi b3nb123!

Take a look in the attached sample database to take some ideas in one-table way.

Regards,
John

[b3nb123]

Thanks, I'll look into that when I have a chance!

[dashiellx]

I like the years to have their own table, that way I don't have clients info from 5 years ago, once the years have passed I file the old year away and don't swim in all the old data!

I understand that intuitively this seems like a good idea. However, your current database design will just make your Access application buggy, slow and inefficient. If you can change the structure, I'd highly recommend doing so now, rather than later when you realize you have to rebuild several days/weeks/months of work properly.

[orange]

Thanks, I'll look into that when I have a chance!

I recommend you do that ASAP and get your design more along relational principles. No need for a table per year and duplication/replication. accesstos has given a sample design/solution with 1 table in answer to your post.

Good luck with your project.

[Missinglinq]

You're attempting to commit what is commonly known as 'Spreadsheet by Access' or 'Excel by Access!' If you insist on this approach, you'd be better off using a Spreadsheet program...because you're going to lose most of the speed/advantages of using a relational database!

Simply put...

• All Records for all Years go into a single Table
• That Table has a Field that identifies the Year for any particular Record
• To see Records for a particular Year...you simply filter the Form by the desired Year

Doing this is relatively easy...we'll be happy to help with that!

And, as Orange suggested...you need to do this ASAP! Postponing this will only increase the time you spend, later fixing it, exponentially!

Welcome to Access Forums!

Linq ;0)>