Create a form that pulls records from two identical tables

[templeowls]

Confusing title. Let me explain. I have two tables that hold customer complaints, tbl2019 and tbl2020. They have an identical structure. Don't get me started on why they they are seperated; the owner likes it that way. Before the new year, I had a form that really dug into each entry of tbl2019. It lets me add notes and actions taken to each entry in the table. I'd like to utilize the same form but have both 2019 and 2020's entries flow into it.



Any idea how? Is that even possible?

[JoeM]

This is what owners should do their thing and let the IT staff do their job!

I would recommend combining them into one single table, and then creating multiple queries for each year, if the owner wants to see two different objects for each year.
(Seeing as how the users should NEVER be accessing the underying tables anyway, they should be none the wiser).

Barring that, I would say that you could do a UNION query to sew them back together in a query, but that is not updateable.
So if you are not able to do that, I would think that you may have to do something with Subforms.

[Dave14867]

I agree with JoeM, no need to separate the tables, keep it all in 1 table and use a query to extract out what is needed. Another option that would only show 2020 data is to copy you existing Form/report, rename it for 2020 and have it access the 2020 table.

[June7]

A new table each year? For how many years does owner plan to use this db? If owner wants to dictate design then owner should build and suffer the ensuing insanity.

VBA could change form RecordSource based on selection from a combobox.

[Bulzie]

Maybe create 2 Search Combo boxes for 2019 and 2020. In the AfterUpdate event of each combo box, change the forms RecordSource to the correct one you selected. That should populate the form with the right fields and also save it to the right table. As others said this is not the way to do it as now you are manually having to keep adding fields and controls to your database, but if you must.

So in the AfterUpdate event of each combo box(will name them Search2019 and Search 2020), you might have something like:

For Search2019 combo box:
Forms![frmMain].RecordSource = "SELECT tbl2019.* FROM tbl2019 WHERE ID = " & me.Search2019
Forms![frmMain].Requery

For Search2020 combo box:
Forms![frmMain].RecordSource = "SELECT tbl2020.* FROM tbl2020 WHERE ID = " & me.Search2020
Forms![frmMain].Requery

Assuming all the fields in each table are exactly the same. If ID is Text would be WHERE ID = '" & me.Search2020 & "'" so that is = single double & me.Search2020 & double single double