Unanswered: Creating an ADD button in a shopping basket style database
I have two combo boxes. First one is category and the second one Products.
When you select a category this limits the choice of products depending on the category. That bit works fine. What I would like to do now is have an ADD button which takes the selection from the product combo and store it in a new table/field to create an order list.
It's basically an order form and I'm trying to allow the user to add products which i'll eventually add the up the price qty etc..
I'm basically copying data from 1 field in a table to another field in another table.
The subform I have set up receives the data ok but I need to increment to position so it copies into the next new record slot. Can this be done with a macro or do I need to start learning VB?
basically I'm trying to create a shopping list..sounds easy!!
P.S 10hours staring at a computer has sent my eyes funny
Inserting a new row into a table is simple. In your case you could use a little piece of VBA code.
Let's suppose that you have a Form with 2 comboboxes: Combo_Products and Combo_Categories, a textbox: Text_Quantity, a command button: Command_Add and a subform Child_Purchases that shows the contents of the Tbl_Purchases table.
In the Click event of the Command_Add button, place this code:
Private Sub Command_Add_Click()
Dim dbs As DAO.Database
Dim strSQL As String
If Not IsNull(Me.Combo_Products.Value) And Not IsNull(Me.Combo_Categories.Value) And Val(Nz(Me.Text_Quantity, 0)) > 0 Then
strSQL = "INSERT INTO Tbl_Purchases ( Product, Category, Quantity ) VALUES ( '" & _
Me.Combo_Products.Value & "', '" & _
Me.Combo_Categories.Value & "', " & _
Me.Text_Quantity.Value & " );"
Set dbs = CurrentDb
dbs.Execute strSQL, dbFailOnError
Set dbs = Nothing
The line "If Not IsNull..." checks that you have something selected in both combos and a number ( greater than zero) in the Text_Quantity textbox.
If so, the code creates a dynamic INSERT query and executes it.
Finally the Child_Purchases subform (which shows the contents of the Tbl_Purchases table) is refreshed (Me.Child_Purchases.Requery) to immediately display the new row in the list.
In a functional application there would be other values that would be added to several other columns of the table Tbl_Purchases (Customer Id, date of purchase, etc.) but you have the general idea here.
thanks Sinndho, I appreciate the effort on the VB code. I'll try to put it in place next week. I did a bit of a temporary work around taking the sample Northwind.mdb as a guide to create my orders. I finally got it to work today.
I think I need to invest a bit of time understanding VB, it seems to hold the answer to many of my problems.