How to use a form to write to a union query? (more details inside)

[Vita]

Hi so allow me to explain before you say that isn't possible because maybe a union query isn't what I need. Also thank you for your time.

I have 2 tables with similar data. Table1 is the one we use the most and table2 is all the etc/one off data entries that we still need access too.

Both tables have a similar structure to this image:

And on this new form I have a combobox that I want to drop down and have a list of all the heats from both tables.
So I thought to use a Union Query to merge them and it worked until I tried to click one. When I click one it is supposed to display all of the fields (eg C, Mn, P, S) in text boxes that have the control source set to that field so that the user can edit the values if needed.

I am sure you can see the problem now. Since I used a union query I can't set the control source anymore because it does not know what table to change the values on.
One thought that occurred to me would be to add a tag to each Heat that said which table it was from but I am not sure how I would write the control or if it would even work.

So I am wondering how I should accomplish this? I greatly appreciate any help. I can't actually merge the tables because one of them gets updated by an append query frequently and I am concerned it would accidentally overwrite some records.


Pseudocode on how I thought the tags could set the control:

if(tag = table1)
then Control = Table1.C
else if(tag = Table2)


then Control = Table2.C
else msgbox "error"

[Gicu]

Use an unbound main form with the search\filter combo unbound as well. On that form add two datasheet subforms bound to the two tables and use the master\child linking to link the combo to each of the two subforms. See attached sample.

Cheers,

[Vita]

I apologize for being a noob but I have some questions. Why are there 2 subforms? I am assuming 1 for each table?
I was hoping to have it a bit more simplistic visually. I was aiming for a combobox that shows all the heats from the two tables and just displays the values on the side like in the below picture.

I'm attempting to make a form so that they can add or edit heats whenever they need to.
(added heats would only go to table2 and I plan to add a button for that specifically)
P.S. Ignore that it says "Import" I haven't changed the label name yet

[xps35]

In your example data there are multiple rows with the same Heat. So you will need continuous forms. And two (sub)forms because you cannot edit union queries.

[Gicu]

You cannot edit a union query. So you either have two subforms bound to each of the tables like I showed you or you dynamically change the record source of the form based on your selection. You have to use a union query for the row source of the combo to show the heats from both tables.
You could use something like this as the row source to show the source: "Select Heat, "Table1" As Source From Table1 UNION Select Heat, "Table2" As Source From Table2;"
In the AfterUpdate event of the combo you would then set the record source to either Table1 or Table2:

Me.RecordSource=Me.cboHeat.Column(1)
'Add code to either filter on the selected heat or navigate to it

Cheers,

[Vita]

I will have to do some research on continuous forms it seems. I have not learned about them yet.

[Gicu]

I think datasheet (sub)forms are better suited for your case...

[Vita]

You cannot edit a union query. So you either have two subforms bound to each of the tables like I showed you or you dynamically change the record source of the form based on your selection. You have to use a union query for the row source of the combo to show the heats from both tables.
You could use something like this as the row source to show the source: "Select Heat, "Table1" As Source From Table1 UNION Select Heat, "Table2" As Source From Table2;"
In the AfterUpdate event of the combo you would then set the record source to either Table1 or Table2:

Me.RecordSource=Me.cboHeat.Column(1)
'Add code to either filter on the selected heat or navigate to it

Cheers,

Oh this solution seems nice. so after event on the combobox the "Me.RecordSource" would tell it which table it came from and I could have the subform retrieve the heat from the table that the combobox says?

[Gicu]

Yes, please see update sample. The master\child linking does the "retrieving" for you.

Cheers,

[Gicu]

So, did it work?

Cheers,

[Vita]

So, did it work?

Cheers,

I could not get it too. It wouldn't let me set the master or child. I tried a few different methods.
I was trying some VBA to set the heats but I'm used to variables in other programming languages.

Dim HeatNo As String 'Should equal the Heat in the combobox. Something like A1121
HeatNo = Me.HeatChangeCbo.Column(0)
Dim TableName As String 'Should equal the tablename field of the selected heat in the combobox. Like OtherHeats
TableName = Me.HeatChangeCbo.Column(1)




Me!SfrmHeat!Heat = TableName!Heat 'Should set the subform heat control to be tablename.form!Heatno or OtherHeats.form!A1121
Me!SfrmHeat.Form!C = TableName & "." & HeatNo & ".Column(1)"
Me!SfrmHeat.Form!Mn = Me.HeatChangeCbo.Column(2)

I tried two different methods on C and Mn to see if I could get either to work.

[Gicu]

Look again closer at my sample as it is fairly simple to do; the main form is unbound (does not have a record source), the combo has two columns and its row source is the union query; then in the AfterUpdate event of the combo you set the record source of the subform to the source table from combo.Column(1) - which is the second column.

You cannot do what you are trying in your above code as need to set the record source of the subform first, not the control source of individual controls (table!field is not a valid syntax for the control source property)....

Cheers,

[Vita]

I will attempt this tomorrow and get back to you. Also apologies I am still learning so I don't really understand why its incorrect syntax. Most of my prior experience with coding was in python.

[Vita]

Look again closer at my sample as it is fairly simple to do; the main form is unbound (does not have a record source), the combo has two columns and its row source is the union query; then in the AfterUpdate event of the combo you set the record source of the subform to the source table from combo.Column(1) - which is the second column.

You cannot do what you are trying in your above code as need to set the record source of the subform first, not the control source of individual controls (table!field is not a valid syntax for the control source property)....

Cheers,

I actually came up with a better way to solve the issue that would also futureproof it a bit. I am combining the tables and adding 3 unique identifiers for each row.
1 for local autonum, another for the autonum from the table its being imported from (so that the record can be updated instead of appended which uses some vba code), and one that has a name for it.
That way its all in one master table

[Gicu]

Sounds like a good plan!

Cheers,