Solved Access subform in datasheet view changes my records when adding new (1 Viewer)

eugewp

New member
Local time
Today, 12:50
Joined
Dec 10, 2020
Messages
17
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.

1.png

First record (OK)
2.png

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)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:50
Joined
Oct 29, 2018
Messages
14,084
Hi. Bienvenidos a AWF!

It might be easier if you could post a sample copy of your db with test data.
 

eugewp

New member
Local time
Today, 12:50
Joined
Dec 10, 2020
Messages
17
Hi. Bienvenidos a AWF!

It might be easier if you could post a sample copy of your db with test data.
Thak you DBguy!

Here is a part of my db, working.
The form is "F4_Nueva_OC" and the subform in question is "F4_OCaux"
 

Attachments

  • extractDB.accdb
    924 KB · Views: 16

theDBguy

I’m here to help
Staff member
Local time
Today, 07:50
Joined
Oct 29, 2018
Messages
14,084
Thak you DBguy!

Here is a part of my db, working.
The form is "F4_Nueva_OC" and the subform in question is "F4_OCaux"
Okay, thanks. Now, can you please provide us with a step-by-step instruction on how to duplicate the issue?

Remember, we are not familiar with your project yet, so when I opened your form, I am not sure what to do with it at the moment.
 

eugewp

New member
Local time
Today, 12:50
Joined
Dec 10, 2020
Messages
17
Okay, thanks. Now, can you please provide us with a step-by-step instruction on how to duplicate the issue?

Remember, we are not familiar with your project yet, so when I opened your form, I am not sure what to do with it at the moment.
Yes:
For reply the issue you must go to the "F4_Nueva_OC" form. And then add some rows into the subform.

The idea of this subform is to load an auxiliary table (OCaux) so that the products added in the new OC form can be easily observed. Then, by VBA code, each one will be inserted in its corresponding table. The code is working correctly, what fails is the way of visualization, since when changing the category "Tipo" it affects the query that shows the column "Nombre" (but not the ID that I want to save)
 

eugewp

New member
Local time
Today, 12:50
Joined
Dec 10, 2020
Messages
17
Yes:
For reply the issue you must go to the "F4_Nueva_OC" form. And then add some rows into the subform.

The idea of this subform is to load an auxiliary table (OCaux) so that the products added in the new OC form can be easily observed. Then, by VBA code, each one will be inserted in its corresponding table. The code is working correctly, what fails is the way of visualization, since when changing the category "Tipo" it affects the query that shows the column "Nombre" (but not the ID that I want to save)
the way to add records is by using the combo boxes. First the "Tipo" is selected (this launches an update event to the "Nombre" field) and then the product is chosen.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:50
Joined
Oct 29, 2018
Messages
14,084
Yes:
For reply the issue you must go to the "F4_Nueva_OC" form. And then add some rows into the subform.

The idea of this subform is to load an auxiliary table (OCaux) so that the products added in the new OC form can be easily observed. Then, by VBA code, each one will be inserted in its corresponding table. The code is working correctly, what fails is the way of visualization, since when changing the category "Tipo" it affects the query that shows the column "Nombre" (but not the ID that I want to save)
When I select a Tipo from the dropdown, I am getting this prompt.

1607625811364.png
 

eugewp

New member
Local time
Today, 12:50
Joined
Dec 10, 2020
Messages
17
When I select a Tipo from the dropdown, I am getting this prompt.

View attachment 87396
I imagine that you will be directly opening the subform. The query is referred to as a subform, that is, the parent form has to be open for it to work.
The F4_OCaux subform is not intended to be used independently.
You have to enter the data by F4_Nueva_OC.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:50
Joined
Oct 29, 2018
Messages
14,084
I imagine that you will be directly opening the subform. The query is referred to as a subform, that is, the parent form has to be open for it to work.
The F4_OCaux subform is not intended to be used independently.
You have to enter the data by F4_Nueva_OC.
What am I missing? As you can see, I opened the main form and then selected an item from the dropdown in the subform and got the prompt.

1607626993361.png
 

eugewp

New member
Local time
Today, 12:50
Joined
Dec 10, 2020
Messages
17
What am I missing? As you can see, I opened the main form and then selected an item from the dropdown in the subform and got the prompt.

View attachment 87397
oh, sure, I think the problem is that your access is in English and the filters that I put in the SQL are in Spanish.
Im not in home rigth now, you can search and replace in the union query SQL Formularios!F4_Nueva_OC... by Forms!F4_Nueva_OC...
or when i come home, i will change and send.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:50
Joined
Oct 29, 2018
Messages
14,084
oh, sure, I think the problem is that your access is in English and the filters that I put in the SQL are in Spanish.
Im not in home rigth now, you can search and replace in the union query SQL Formularios!F4_Nueva_OC... by Forms!F4_Nueva_OC...
or when i come home, i will change and send.
Okay, that got rid of the prompt. But, when I select a Tipo, I don't see Nombre, Cantidad, y Precio get changed.

1607629832507.png
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:50
Joined
Oct 29, 2018
Messages
14,084
Nice!
Cantidad and Precio not change.
In Nombre is another combo box, that is related to the query.
View attachment 87406
Okay, I'll try it again later. This is why I asked for a "step-by-step" instruction earlier - to avoid unnecessary back and forth asking and answering questions regarding the process of duplicating the problem. Since we are not yet familiar with your database, we won't have any idea, right away, how to use it. Cheers!
 

eugewp

New member
Local time
Today, 12:50
Joined
Dec 10, 2020
Messages
17
Okay, I'll try it again later. This is why I asked for a "step-by-step" instruction earlier - to avoid unnecessary back and forth asking and answering questions regarding the process of duplicating the problem. Since we are not yet familiar with your database, we won't have any idea, right away, how to use it. Cheers!
Thank you very much for your help. I wait your answer
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:50
Joined
Oct 29, 2018
Messages
14,084
Thank you very much for your help. I wait your answer
Hi. I came close today, but I had to leave the office before I could finish. I'll have to start over tomorrow, since I will be working from home without a copy of the file from the office, or will have to pick it back up on Lunes.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:50
Joined
Oct 29, 2018
Messages
14,084
Bueno. I gave it another try y hice algunos cambios. Please see the attached y dejarnos si esta funciona para ti.
 

Attachments

  • extractDB.zip
    65.7 KB · Views: 17

eugewp

New member
Local time
Today, 12:50
Joined
Dec 10, 2020
Messages
17
Bueno. I gave it another try y hice algunos cambios. Please see the attached y dejarnos si esta funciona para ti.
Man, this is perfect. Thank you so much!!. How did you do that?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:50
Joined
Oct 29, 2018
Messages
14,084
Man, this is perfect. Thank you so much!!. How did you do that?
Hi. Glad to hear it worked for you. I used the usual approach of overlaying a Textbox on top of the Combobox. Unfortunately, this only works on Continuous Forms, so I had to change your Datasheet subform.

Then, since your Combobox Row Source could result in duplicate IDs, I had to create a duplicate Row Source for the Textbox and used a DLookup() to display the correct Nombre.

Hope that makes sense...
 

eugewp

New member
Local time
Today, 12:50
Joined
Dec 10, 2020
Messages
17
Hi. Glad to hear it worked for you. I used the usual approach of overlaying a Textbox on top of the Combobox. Unfortunately, this only works on Continuous Forms, so I had to change your Datasheet subform.

Then, since your Combobox Row Source could result in duplicate IDs, I had to create a duplicate Row Source for the Textbox and used a DLookup() to display the correct Nombre.

Hope that makes sense...
Again thank you very much.
 

Users who are viewing this thread

Top Bottom