Problem to insert all data from report by id with where to other table and VBA query (1 Viewer)

[okasanmarisa]

Hello, I need to know how I can insert all the data of a report that I have created in Access and VBA.
What I want to do is the following I have 3 tables: Carrito (In it everything will be added as if it were a shopping list)
Ingredientes (It has associated all the ingredients and the id of the recipe to which it corresponds)
And finally the Planing table (In this table you can add the days and the type of food and the recipe id, the idea is to create a report from this table. And it will show me the days and the recipe assigned lastly a button called Add to purchase, what this button will do is first compare which ingredients have the same recipe associated with the planing table if they match this table, only the id_receta will be inserted in the table carrito.


Here I explain the problem, I make the query in VBA, but when I click the button, it only inserts the first record of id_receta I want it to enter everything in the cart table, it is as if I only took the first id of the report, others not.
I show the captures

The Report "Planing" only inserts 5 registers. only the id 4, not all the ids.

The VBA query is the next:

Dim SQL As String

SQL = "INSERT into carrito select * from ingredientes WHERE id_receta = planing.id_receta;"

DoCmd.RunSQL SQL

 

[theDBguy]

Hi. Welcome to AWF!

Try copying your SQL statement to the query designer and then convert it into a SELECT query and run it.

The result should show you which records are going to be inserted.

 

[okasanmarisa]

Hi. Welcome to AWF!

Try copying your SQL statement to the query designer and then convert it into a SELECT query and run it.

The result should show you which records are going to be inserted.

Thanks but it shows me to add a parameter now i don't want to insert the id_receta parameter i need to insert all data of id_receta to table carrito.

 

[theDBguy]

Thanks but it shows me to add a parameter now i don't want to insert the id_receta parameter i need to insert all data of id_receta to table carrito.

Pick a value for id_receta and use it in the SELECT query.

 

[okasanmarisa]

Pick a value for id_receta and use it in the SELECT query.

It works once I put the recipe_id that I had assigned in the planning, when it asks me for the parameter, now how do I get it to insert them all without a parameter?

 

[theDBguy]

It works once I put the recipe_id that I had assigned in the planning, when it asks me for the parameter, now how do I get it to insert them all without a parameter?

Okay, in that case, you'll have to fix your SQL statement in your code. It should look something like this:

strSQL = "INSERT INTO carrito SELECT * FROM ingrendientes WHERE id_receta=" & Me.id_receta

Hope that helps...

 

[okasanmarisa]

Okay, in that case, you'll have to fix your SQL statement in your code. It should look something like this:

strSQL = "INSERT INTO carrito SELECT * FROM ingrendientes WHERE id_receta=" & Me.id_receta

Hope that helps...

It doesn't work either, the only thing is that it keeps inserting the records of the first recipe_id in the report list but the others don't take them I don't understand why.
I tried to use that sql sequence but the result is the same.

 

[theDBguy]

It doesn't work either, the only thing is that it keeps inserting the records of the first recipe_id in the report list but the others don't take them I don't understand why.
I tried to use that sql sequence but the result is the same.

Probably better for us to show you how to do it. Can you post a sample copy of your db with test data?

 

[okasanmarisa]

Probably better for us to show you how to do it. Can you post a sample copy of your db with test data?

Ok, thank you very much. I share the database, in the report its named it "SHOPING PLANNING, so you can help me edit that part of adding to cart"

 

[theDBguy]

Ok, thank you very much. I share the database, in the report its named it "SHOPING PLANNING, so you can help me edit that part of adding to cart"

Hi. Muchas gracias por compartir tu base de datos. Before I get too far, see if by moving the button, as I have shown in the following image, will do what you want.

 

[okasanmarisa]

Hi. Muchas gracias por compartir tu base de datos. Before I get too far, see if by moving the button, as I have shown in the following image, will do what you want.

View attachment 86329

Problem solved thank you very much for your help with this I have learned a lot about access and it worked for me !!

 

[theDBguy]

I tried it now but it always inserts the same record as above, uf I'm sorry if this problem is a nuisance, because I don't know how to solve it. Thank you very much too

Okay, just to make sure we're on the same page, let me show you what I see.

If I open the table carrito and delete all the records, this is what I start with.

If I then open the report and click on the first button, as highlighted below.

This is what I see if I look at the table carrito otra vez.

So, if that's not the result you're expecting, please tell us what it's supposed to look like instead. Thanks!

Edit: Oops, I was about to post the above, but I see you already got it sorted out.

 

[theDBguy]

Problem solved thank you very much for your help with this I have learned a lot about access and it worked for me !!

Hi. Glad to hear you got it sorted out. Buena suerte con tu proyecto!

 

[okasanmarisa]

Hi. Glad to hear you got it sorted out. Buena suerte con tu proyecto!

Thanks I will continue creating my project is about a recipe book

Without your help I could not get to the next step of my project !! thank you

 

[theDBguy]

Thanks I will continue creating my project is about a recipe book

Without your help I could not get to the next step of my project !! thank you

De nada!