MS Access How to select record by using VBA

Question

Hey everyone,

i have some problem, i want to select some information by using vba because i want this logic when the user click on 'Ajouter au panier' the system have to select the product and add it on the list Box of 'Panier' 

this is the interface of the form_dialog to understand me 

Please can you give me the solution what is the code i can use it here ?

Answer 1

Hi Zakaria, I'm an independent adviser and will try to help.

I'm sorry, but its not quite clear what you are trying to do. As I understand it you want to add a product to a list box when you press a button. What's not clear is where you would get the Product from or why you want to do this.

Normally, a list box is populated from a table. So if the rowSource of your listbox is a table, you want to add the product to the table. But he question is why?

To add something to a table you would run an append query.

If you can provide more info, I can help with more details.

Answer 2

First of all thanks for your time & your reply,

yes logically this what I want like you sad "add a product to a list box when you press a button" 

and why I want this because if you see in the form I have already selected the product in the drop-down list (combo box) and after when i click on the button i want to add it on the list box automatically by VBA code if you know it this is like a concept of shopping cart

and this what the action i want it after clicking on the button

i hope you understand me

if you have some question tell me :)

Answer 3

Ok, but why a List box? So if its like a shopping cart and the user is selecting multiple products for an order, why a listbox? Shouldn't you be adding it to an order details table? Shouldn't you have a subform that displays the line items selected in the order? I just don't understand why a listbox.

But to tell you how to add it, I would need to know what the RowSource of the listbox is.

Answer 4

The simplest way would be to insert rows temporarily into a local Panier table, which could then be referenced in the list box's RowSource property.  As your form currently stands, the Panier table would only need a single column, ProduitID referencing the primary key of a Produits table, so the list box's RowSource would be along these lines:

SELECT Panier.ProduitID, Produits.Produit

FROM Panier INNER JOIN Produits

ON Panier.ProduitID = Produits.ProduitID;

So, the code to add the selected product to the list box would be like this:

Dim strSQL AS String

strSQL = INSERT INTO Panier(ProduitID) VALUES(" & Me.cboProduit & ")"

CurrentDb.Execute strSQL, dbFailOnError

Me.lstPanier.Requery

Once the list was confirmed as complete for the current order, it could then be inserted into an OrderDetails table with code along these lines:

Dim strSQL AS String

strSQL = INSERT INTO OrderDetails(OrderID, ProduitID) " & _

    "SELECT " & Me.OrderID & ", ProduitID FROM Panier"

CurrentDb.Execute strSQL, dbFailOnError

' empty Panier table

strSQL = DELETE * FROM Panier"

CurrentDb.Execute strSQL, dbFailOnError

Me.lstPanier.Requery

One thing your form does not seem to include is any provision for specifying a quantity of the selected product.  If a control for this were to be included in the form, then this would be referenced in the first SQL statement and inserted into a Quantité column in the Panier table, and similarly in the second SQL statement when inserting a row into OrderDetails.

Answer 5

Thanks for you reply

now i have another problem when i select the product

the information relied on product insert automatically but the picture attached not insert this the problem display

this is the code VBA in back :

and this is the code VBA in back