Solved

VISUAL BASIC CODE FOR UPDATING QUANTITY OF PRODUCT!!

Posted on 2004-04-14
19
356 Views
Last Modified: 2013-11-25
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
0
Comment
Question by:Jam90
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 8
19 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 10826916
What is your table name? and field names?
that you want updated.

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 10826939
<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.
0
 

Author Comment

by:Jam90
ID: 10827035
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??
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 10827154
<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?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 10827234
<
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 ;
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 10827323
Can you please post the field names of tblProducts.
0
 

Author Comment

by:Jam90
ID: 10827422
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
0
 

Author Comment

by:Jam90
ID: 10827453
For this statement it keeps sayin Syntax error

UPDATE tblProducts SET tblProducts.Quantity = [tblProducts].[Quantity]-1 ;
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 10827755
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.
0
 

Author Comment

by:Jam90
ID: 10827904
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
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 10828020
Private Sub Command12_Click()
Dim sSQL as String

sSQL ="UPDATE tblProducts SET tblProducts.Product Quantity = [tblProducts].[Product Quantity]-1 " & _
           "Where tblProducts.ProductID= Forms.frmProducts.TextboxForProductID; "
'--------------------------------------------------------------------^^^^^^^^ change this to the name of textbox
CurrentDb.Execute sSQL,dbFailOnError



End Sub
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 10828033
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.TextboxForProductID; "
'--------------------------------------------------------------------^^^^^^^^ change this to the name of textbox
CurrentDb.Execute sSQL,dbFailOnError



End Sub
0
 

Author Comment

by:Jam90
ID: 10828055
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
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 10828362
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
0
 

Author Comment

by:Jam90
ID: 10831545
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
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 10833119
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
0
 

Author Comment

by:Jam90
ID: 10835034
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
       
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 485 total points
ID: 10835593
ok you can add this codes too

Private Sub Form_Load()
Me.AddNewItemsToStock = ""
End Sub


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
Me.AddNewItemsToStock = ""     '<<<< add this line

End If


0
 

Author Comment

by:Jam90
ID: 10841191
THANKS
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question