I have created an auxiliary table to later save information in different tables easily. I made a subform to load this table. To do this, use a field that modifies a query that another field uses as a row source (which stores an ID and displays a name). The problem is that when adding another record the name of the previous records is also modified if I change the first field.
First record (OK)
When i add a second record whith another label the first changes. (but no the id)
This is the query (row origin)
First record (OK)
When i add a second record whith another label the first changes. (but no the id)
SQL:
SELECT Resina.Id_Resina, Resina.Nombre
FROM Resina
WHERE ((([Formularios]![F4_Nueva_OC]![F4_OCaux]![tipo])="Resina"));
UNION SELECT Materiales.Id_Materiales, Materiales.Descripcion
FROM Materiales
WHERE ((([Formularios]![F4_Nueva_OC]![F4_OCaux]![tipo])="Material"));
UNION
SELECT Elemento.Id_Elemento, Mueble.Nombre
FROM Mueble INNER JOIN Elemento ON Mueble.Id_Mueble = Elemento.Id_Mueble
WHERE ((([Formularios]![F4_Nueva_OC]![F4_OCaux]![tipo])="Mueble"));
UNION
SELECT Elemento.Id_Elemento, Pieza.Nombre
FROM Pieza INNER JOIN Elemento ON Pieza.Id_Pieza = Elemento.Id_Pieza
WHERE ((([Formularios]![F4_Nueva_OC]![F4_OCaux]![tipo])="Pieza"));
UNION SELECT Elemento.Id_Elemento, Accesorio.Nombre
FROM Accesorio INNER JOIN Elemento ON Accesorio.Id_Accesorio = Elemento.Id_Accesorio
WHERE ((([Formularios]![F4_Nueva_OC]![F4_OCaux]![tipo])="Accesorio"));
This is the query (row origin)