Jam90
asked on
VISUAL BASIC CODE FOR UPDATING QUANTITY OF PRODUCT!!
OK I have a Form called frmProducts with numerous feilds including one called ItemsinStock.
I also have a command button, I want some code so that when i press the button the ItemsinStock is updated, in effect 1 is taken away. Also i want the code to pop up a message saying that the you should order some more items when the ItemsinStock = 2.
Also i have a text box next to the ItemsInStock called "NumberOfNewItems", and also have an update command button, i want some code so that when this update button is pressed the Number in the NumberOfNewItems Textbox is added to the ItemsInStock Textbox.
Hope you understand that.
Much appreciated
THANKS
I also have a command button, I want some code so that when i press the button the ItemsinStock is updated, in effect 1 is taken away. Also i want the code to pop up a message saying that the you should order some more items when the ItemsinStock = 2.
Also i have a text box next to the ItemsInStock called "NumberOfNewItems", and also have an update command button, i want some code so that when this update button is pressed the Number in the NumberOfNewItems Textbox is added to the ItemsInStock Textbox.
Hope you understand that.
Much appreciated
THANKS
<Also i want the code to pop up a message saying that the you should order some more items when the ItemsinStock = 2.>
You have to add a field on your table and set the value for MinQty.
You have to add a field on your table and set the value for MinQty.
ASKER
Table Name = tblProducts
Field Names Iwant updated are "Quantity"
FOr the pop up message cannot it just be done in VB and the minimum value be set in the code itself rather than making a textbox??
Field Names Iwant updated are "Quantity"
FOr the pop up message cannot it just be done in VB and the minimum value be set in the code itself rather than making a textbox??
<FOr the pop up message cannot it just be done in VB and the minimum value be set in the code itself rather than making a textbox??>
Do you only have one particular item on your tblProducts?
And you are not going to add a textbox, You need to add a field to your tblProducts
Let us say your tblproducts has Item, Quantity as fields
tblProducts
Item Quantity MinQty << you have to add this
AAA 5 2
BBB 15 3
getting the idea?
Do you only have one particular item on your tblProducts?
And you are not going to add a textbox, You need to add a field to your tblProducts
Let us say your tblproducts has Item, Quantity as fields
tblProducts
Item Quantity MinQty << you have to add this
AAA 5 2
BBB 15 3
getting the idea?
<
I also have a command button, I want some code so that when i press the button the ItemsinStock is updated, in effect 1 is taken away>
For this you can use
UPDATE tblProducts SET tblProducts.Quantity = [tblProducts].[Quantity]-1 ;
I also have a command button, I want some code so that when i press the button the ItemsinStock is updated, in effect 1 is taken away>
For this you can use
UPDATE tblProducts SET tblProducts.Quantity = [tblProducts].[Quantity]-1
Can you please post the field names of tblProducts.
ASKER
Hi there i dont really want to create a new feild minQty as i am assuming that all the MinQtys arethe same for all prodcuts.
THanks
THanks
ASKER
For this statement it keeps sayin Syntax error
UPDATE tblProducts SET tblProducts.Quantity = [tblProducts].[Quantity]-1 ;
UPDATE tblProducts SET tblProducts.Quantity = [tblProducts].[Quantity]-1
Jam90,
Ok how are you using it ? PLEASE paste the codes you are using.
PLEASE post the names of fields from tblProducts, we need the unique ID of the item for the update.
Ok how are you using it ? PLEASE paste the codes you are using.
PLEASE post the names of fields from tblProducts, we need the unique ID of the item for the update.
ASKER
I am using it like so..
Private Sub Command12_Click()
UPDATE tblProducts SET tblProducts.Product Quantity = [tblProducts].[Product Quantity]-1 ;
End Sub
TBLPRODUCTS FIELDS
Prodcut ID
Prodcut Name
Prodcut Price
Product Description
Prodcut Size
Images
Prodcut Quantity
Private Sub Command12_Click()
UPDATE tblProducts SET tblProducts.Product Quantity = [tblProducts].[Product Quantity]-1 ;
End Sub
TBLPRODUCTS FIELDS
Prodcut ID
Prodcut Name
Prodcut Price
Product Description
Prodcut Size
Images
Prodcut Quantity
Private Sub Command12_Click()
Dim sSQL as String
sSQL ="UPDATE tblProducts SET tblProducts.Product Quantity = [tblProducts].[Product Quantity]-1 " & _
"Where tblProducts.ProductID= Forms.frmProducts.TextboxF orProductI D; "
'------------------------- ---------- ---------- ---------- ---------- ---^^^^^^^ ^ change this to the name of textbox
CurrentDb.Execute sSQL,dbFailOnError
End Sub
Dim sSQL as String
sSQL ="UPDATE tblProducts SET tblProducts.Product Quantity = [tblProducts].[Product Quantity]-1 " & _
"Where tblProducts.ProductID= Forms.frmProducts.TextboxF
'-------------------------
CurrentDb.Execute sSQL,dbFailOnError
End Sub
Oops missed two[ ]
Private Sub Command12_Click()
Dim sSQL as String
sSQL ="UPDATE tblProducts SET tblProducts.[Product Quantity] = [tblProducts].[Product Quantity]-1 " & _
"Where tblProducts.ProductID= Forms.frmProducts.TextboxF orProductI D; "
'------------------------- ---------- ---------- ---------- ---------- ---^^^^^^^ ^ change this to the name of textbox
CurrentDb.Execute sSQL,dbFailOnError
End Sub
Private Sub Command12_Click()
Dim sSQL as String
sSQL ="UPDATE tblProducts SET tblProducts.[Product Quantity] = [tblProducts].[Product Quantity]-1 " & _
"Where tblProducts.ProductID= Forms.frmProducts.TextboxF
'-------------------------
CurrentDb.Execute sSQL,dbFailOnError
End Sub
ASKER
I get sytax Error On CurrentDb.Execute sSQL, dbFailOnError
Private Sub Command12_Click()
Dim sSQL As String
sSQL = "UPDATE tblProducts SET tblProducts.Product Quantity = [tblProducts].[Product Quantity]-1 " & _
"Where tblProducts.Product ID= Forms.frmProducts.Product ID; "
CurrentDb.Execute sSQL, dbFailOnError
End Sub
Private Sub Command12_Click()
Dim sSQL As String
sSQL = "UPDATE tblProducts SET tblProducts.Product Quantity = [tblProducts].[Product Quantity]-1 " & _
"Where tblProducts.Product ID= Forms.frmProducts.Product ID; "
CurrentDb.Execute sSQL, dbFailOnError
End Sub
Copy and paste this
Private Sub Command12_Click()
Dim sSQL As String
If Me.[Product ID] = DLookup("[Product ID]", "tblProducts", "[Product Quantity]= 3") Then
MsgBox "Order Now"
End If
sSQL = "Update tblProducts Set tblProducts.[Product Quantity]= tblProducts.[Product Quantity]-1 "
sSQL = sSQL & "Where (tblProducts.[Product Id]) = " & (Me![Product ID])
CurrentDb.Execute sSQL
End Sub
Private Sub Command12_Click()
Dim sSQL As String
If Me.[Product ID] = DLookup("[Product ID]", "tblProducts", "[Product Quantity]= 3") Then
MsgBox "Order Now"
End If
sSQL = "Update tblProducts Set tblProducts.[Product Quantity]= tblProducts.[Product Quantity]-1 "
sSQL = sSQL & "Where (tblProducts.[Product Id]) = " & (Me![Product ID])
CurrentDb.Execute sSQL
End Sub
ASKER
OK Thanks thatworks OK.
However when i press the button i can only see the Product Qauntity go down by 1 after i click in thetext box?
Is there any wayof getting it to refresh everytime i clickthe button?
Also i wanted to put a text box next to the Prodcut Quantity Box, called "AddNewItemsToStock", so that when the user typed a number intothe box and clicked the command button it would add that number to "Product Quantity" Could you writesome code to do that?
THanks
However when i press the button i can only see the Product Qauntity go down by 1 after i click in thetext box?
Is there any wayof getting it to refresh everytime i clickthe button?
Also i wanted to put a text box next to the Prodcut Quantity Box, called "AddNewItemsToStock", so that when the user typed a number intothe box and clicked the command button it would add that number to "Product Quantity" Could you writesome code to do that?
THanks
using the same command button;
Private Sub Command12_Click()
Dim sSQL As String, sqlAdd As String, j As Long
If Me.AddNewItemsToStock <> "" Then
j = Me.AddNewItemsToStock
sqlAdd = "Update tblProducts Set tblProducts.[Product Quantity]= tblProducts.[Product Quantity]+ " & j & " "
sqlAdd = sqlAdd & "Where (tblProducts.[Product Id]) = " & Me![Product ID]
CurrentDb.Execute sqlAdd
End If
If Me.[Product ID] = DLookup("[Product ID]", "tblProducts", "[Product Quantity]= 3") Then
MsgBox "Order Now"
End If
sSQL = "Update tblProducts Set tblProducts.[Product Quantity]= tblProducts.[Product Quantity]-1 "
sSQL = sSQL & "Where (tblProducts.[Product Id]) = " & Me![Product ID]
CurrentDb.Execute sSQL
End Sub
Private Sub Command12_Click()
Dim sSQL As String, sqlAdd As String, j As Long
If Me.AddNewItemsToStock <> "" Then
j = Me.AddNewItemsToStock
sqlAdd = "Update tblProducts Set tblProducts.[Product Quantity]= tblProducts.[Product Quantity]+ " & j & " "
sqlAdd = sqlAdd & "Where (tblProducts.[Product Id]) = " & Me![Product ID]
CurrentDb.Execute sqlAdd
End If
If Me.[Product ID] = DLookup("[Product ID]", "tblProducts", "[Product Quantity]= 3") Then
MsgBox "Order Now"
End If
sSQL = "Update tblProducts Set tblProducts.[Product Quantity]= tblProducts.[Product Quantity]-1 "
sSQL = sSQL & "Where (tblProducts.[Product Id]) = " & Me![Product ID]
CurrentDb.Execute sSQL
End Sub
ASKER
Okone more question for the following code can you add a statement so that after it has updated the Product Quantity box it will remove the value in the AddNewItemsToStock text box.
THanks
Dim sSQL As String, sqlAdd As String, j As Long
If Me.AddNewItemsToStock <> "" Then
j = Me.AddNewItemsToStock
sqlAdd = "Update tblProducts Set tblProducts.[Product Quantity]= tblProducts.[Product Quantity]+ " & j & " "
sqlAdd = sqlAdd & "Where (tblProducts.[Product Id]) = " & Me![Product ID]
CurrentDb.Execute sqlAdd
End If
THanks
Dim sSQL As String, sqlAdd As String, j As Long
If Me.AddNewItemsToStock <> "" Then
j = Me.AddNewItemsToStock
sqlAdd = "Update tblProducts Set tblProducts.[Product Quantity]= tblProducts.[Product Quantity]+ " & j & " "
sqlAdd = sqlAdd & "Where (tblProducts.[Product Id]) = " & Me![Product ID]
CurrentDb.Execute sqlAdd
End If
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
THANKS
that you want updated.