'Add to List' Button

[flying linus]

I have two tables, tblProducts and tblShopping_List.

Each table is configured as described below:

tblProducts:
ProductID - Primary Key (Autonumber)
ProductName (Text)
CategoryID (Number) - Linked to tblCategory

tblShopping_List:
ShoppingListID - Primary Key (Autonumber)
ProductID (Number) - Linked to tblProducts
Quantity (Number)

On a form, I am showing a list box of tblProducts items and a continuous subform of the tblShopping_List items. I want to create a command button that will take the ProductID of the selected item in the tblProducts and add that ProductID as new record in the tblShopping_List. But, I want the code to cycle through the items in the Shopping_List and if the item exists, then only update the quantity by one. If the item does not exist, then add a new record with the new ProductID.

Using code found here in one of the sample databases, I am able to add items to shopping list, but I can't seem to get it to update the quantity for existing items, it just adds a new record. Anyone have any sample code that can help me out?

 

[ShaneMan]

Hey Linus,

Post your code that your using to add a record and the code that your using in attempting to add 1 to the quantity. That should help you in getting help.


Shane

 

[flying linus]

Here is an example database of what I am trying to do. Take a look at the form Add2Cart. I am a newbie to anything beyond what the built in wizards give you, so if you guys can offer any advice to how to code the command button, I would appreciate it. I want to be able to click the button and look through all records of tblcart and if the selected product exists, then increment the quantity, otherwise add the product to the cart.

 

[flying linus]

Finally got it to work.

Here's the code:

Private Sub AddProduct2List_Click()
On Error GoTo Err_AddProduct2List_Click

Dim SQLTxt As String
DoCmd.SetWarnings False
SQLTxt = "INSERT INTO ShoppingList ( Quantity, ProductID ) SELECT " & 1 & " ," & [List1].Value & ";"
If DLookup("[ProductID]", "ShoppingList", "[ProductID]=" & [List1].Value & "") Then
SQLTxt = "UPDATE ShoppingList SET ShoppingList.Quantity = [Quantity]+1 WHERE ((([ShoppingList]![ProductID]) = " & [List1].[Value] & " ));"
End If
DoCmd.RunSQL SQLTxt

DoCmd.Requery ("subfrmShoppingList")
DoCmd.SetWarnings True
'********************

Exit_AddProduct2List_Click:
Exit Sub

Err_AddProduct2List_Click:
MsgBox Err.Description
Resume Exit_AddProduct2List_Click

End Sub