Add To Cart Style Table

[Lewis825]

Where to start?

I am a VBA beginner so please play nice.

I have a form which displays all our products in a list. Click a product and this displays the Products Details page, pretty standard set-up. This pulls all the information from the products table.

However,


I would like a button and text box which allows me to enter a quantity then click 'Order'.
I would then like the product information (i.e. ProductID, Name, Description etc & the user inputted quantity etc) to be put into seperate table much like a shopping cart on a website.

I can then add a number of items to this table (cart)

I have seen many different ways of doing this but none seem to work or suit.

Please help!!!

[JamesDeckert]

1. clear temp table (or delete/create it)
2. create an append query which appends values from form to the temp table.

You could use vba to create a recordset and append data to the recordset, but the above method will be less coding and less VBA.
FYI: usually you don't need to save name, description, etc. Only save the ProductID, qty (maybe price if it's possible for it to change), date and you can always reference the other info in the product table. You only need to save the data that is unique to this order.

[Lewis825]

How do I make an Append Query Between a form and a table?

I only get the option to add a tabel or another qurey?

[JamesDeckert]

Create the append query and save it. Make a button on the form that runs the query
docmd.runsql

[June7]

Why don't you bind a form to the cart table and have combobox for selecting products?

Code can delete records when the form is opened or give user option to delete records.

Is this a multi-user database?

[Lewis825]

JamesDeckert - You say make the Qurey? please advise how to put in it? What am I linking?

June - Yes is a multi user DB. A combo box would not be suitable. I have a form listing the range of products. When the user double clicks a line, it open another form which the product details in full. I simpley want an order button on this screen which will add that item to a table which i can then go about sending to the supplier.

Thank you for all your help. I do appreicete your input.

[June7]

Still not clear to me why can't use a form with combobox. Conventional approach would be a form/subform arrangement. Main form bound to Orders table and subform bound to OrderDetails table. Combobox in subform to select products.

Is this multi-user db split? If you want to use a 'temp' table it must be located in the frontend.

Even if you use a 'temp' table as a 'cart', still need the records saved to OrderDetails table.

[Lewis825]

I have got a step futher,

I used recordsets to input data into the cart table

very happy chappy


I am now having dificulty referencing a different table in the recordset.

.AddNew
.Fields("Product Id") = Text0
.Fields("Product Name") = Text2
.Fields("Product Price") = Text6
.Fields("Quantity") = Text10
.Fields("Order date") = Date
.Fields("Order ID") = !NextNo.NextNo
.Update

all the fields update perfectly bar the Order ID which must equal the value in Table NextNo, field NextNo

how should i reference it?

[June7]

Not making sense. Post the recordset SQL statement.

[JamesDeckert]

Is Table NextNo, field NextNo on your form? If not, you'll have to go through hoops to get the value. You should put it on your form, then you can reference it just like your other text boxes.
Or is it an autonumber field? If so, it'll fill in automatically when you update the record.

[Lewis825]

James

YOU'RE A HERO

Thank you, it worked.

i had to add the table to the form

[Lewis825]

How would I add a prefix to it.

So i want the word 'Direct' then the 'NextNo' Value.

so when viewed it would display Direct2 etc

[June7]

No need to save with the prefix. It can be calculated when needed in query or textbox. If you do save with prefix and want to be able to correctly sort/filter records, will need placeholder zeros, like: Direct000002

Otherwise, Direct100 will sort before Direct2.

Regardless, expression to concatenate prefix: "Direct" & Format([Order ID], "0000")

Advise not to use spaces in naming convention. Better would be OrderID or Order_ID.