Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jan 2005
    Posts
    60
    Rep Power
    10

    Question Math Operations on Values Question


    I have created a table that holds the inventory information of some items i have in stock. What I want to do is this:

    1.)When a customer orders something, i enter the quantity ordered and hit a process button. When this button is clicked it automatically reduces my inventory count to reflect the reduction

    2.)When I receive shipments, i want to enter the information and hit process so that it automatically sets my inventory counts to reflect the addition of the new units

    I am having trouble figuring out how to go about doing this. I was thinking of somehow setting up some textfields and then adding buttons that set fields accordingly.

    Any help would be most appreciated.
  2. #2
  3. Database junkie..
    ASP Scholar (3000 - 3499 posts)

    Join Date
    May 2004
    Location
    Derry, N Ireland
    Posts
    3,092
    Rep Power
    52
    you can use code or, with a bit of fiddling, a simple query to do what you want.

    your process is more complicated than you state here, if you write it out in full you'll see the stages

    code like:


    'get the ID of the product ordered - assume it is on a form in a combobox
    dim myprodid
    myprodid = me.cboSelectProduct

    'then you need to get an inventory figure for that product
    'use a dlookup
    dim myprodcount
    myprodcount = nz(dlookup("[qtyremaining]","[inventorytablename]","[product id] = " & myprodid),0)

    'then you need to grab the qty ordered - again assume this comes from a text box on the form

    dim myqty
    myqty = me.txtQtyOrdered

    'then subtract qty ordered from qty in stock (ignore possibility that there may not be enough stock

    dim myprodleft
    myprodleft = myprodcount - myqty

    'finally update your inventory table with the new qty in stock

    dim mysql
    mysql = "UPDATE [your inventory table] set [your qty in stock field] = " & myprodleft & " WHERE [product id] = " & myprodid

    docmd.runsql mysql
    Regards,

    Colm



    If you found a post particularly helpful, show your appreciation by clicking the "scales" icon in the bar just above the post, at the right hand side.
  4. #3
  5. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jan 2005
    Posts
    60
    Rep Power
    10
    What language is that, Visual Basic???
  6. #4
  7. Database junkie..
    ASP Scholar (3000 - 3499 posts)

    Join Date
    May 2004
    Location
    Derry, N Ireland
    Posts
    3,092
    Rep Power
    52
    Visual Basic for Applications (VBA)
  8. #5
  9. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jan 2005
    Posts
    60
    Rep Power
    10
    I tried entering the code following your logic but I am receiving an error. The error is "Compile Error: Member or data member not found", and it is highlighting my me.cboSelectProduct in blue. Here is the code i have typed. In my inventory table i have my primary key as productid which is a lookup field referencing another table for description. I then set each of my products to a quantity of zero in the inventory table.

    Any ideas?

    Option Compare Database

    Private Sub Command4_Click()
    Dim myprodid
    myprodid = Me.cboSelectProduct

    Dim myprodcount
    myprodcount = Nz(DLookup("[Quantity in Stock]", "[Inventory Table]", "[Product ID]" = "& myprodid &"), 0)

    Dim myqty
    myqty = Me.txtQuantity

    Dim myprodnow
    myprodnow = myprodcount + myqty

    Dim mysql
    mysql = "UPDATE [Inventory Table] set [Quantity in Stock] = " & myprodnow & " WHERE [Product ID] = " & myprodid

    DoCmd.RunSQL mysql



    End Sub
  10. #6
  11. Database junkie..
    ASP Scholar (3000 - 3499 posts)

    Join Date
    May 2004
    Location
    Derry, N Ireland
    Posts
    3,092
    Rep Power
    52
    you have to edit my code and substitute all the field, table, form and control names to match your situation.

    this means anything in [..] or preceded by 'me.' needs to be altered to match your situation.
  12. #7
  13. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jan 2005
    Posts
    60
    Rep Power
    10
    what should the "me" be replaced with? Also, should i replace the bracket, or just the names within the brackets. I did go through and replace the variables accordingly I thought. For example, my combo box i renamed SelectProduct and my text box is named Quantity.
  14. #8
  15. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jan 2005
    Posts
    60
    Rep Power
    10
    I went through and made some changes and didnt get the compile error. However, when i enter my information (choose a product, then enter a quantity and click the process button) it pops up with an "enter parameter" dialogue box, and then says 0 rows will be updated, to proceed hit enter.

    Any ideas?? Also, what does it mean when it asks to enter a parameter value?
  16. #9
  17. Database junkie..
    ASP Scholar (3000 - 3499 posts)

    Join Date
    May 2004
    Location
    Derry, N Ireland
    Posts
    3,092
    Rep Power
    52
    me. should stay as it is. You need to add the correct form control names after it

    you can leave the square brackets, but field and table names must match those in your db, including any spaces. For example, you mention you have a field called "productid", but when i look at your code it refers to a field called "product id" (with a psace) - an entirely different animal.

    The variables you can call whatever you like as long as you're consistent about where you use them
  18. #10
  19. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jan 2005
    Posts
    60
    Rep Power
    10
    I did everything, and found one minor error when i was adapting your code to my field names. Thank you very much by the way for that code.

    Now it compiles but when i select a product on the form via the combo box, enter a quantity into the text field, and hit the process button i get an error saying : "Run time error 2001 You cancelled the previous operation"

    It then highlights the following coce:myprodcount = Nz(DLookup("[Quantity in Stock]", "[Inventory Table]", "[Product ID] = " & myprodid), 0)

    in yellow. Again here is my code and i really appreciate all the help you have given me.

    Option Compare Database

    Private Sub Command4_Click()
    Dim myprodid
    myprodid = Me.SelectProduct

    Dim myprodcount
    myprodcount = Nz(DLookup("[Quantity in Stock]", "[Inventory Table]", "[Product ID] = " & myprodid), 0)

    Dim myqty
    myqty = Me.Quantity

    Dim myprodnow
    myprodnow = myprodcount + myqty

    Dim mysql
    mysql = "UPDATE [Inventory Table] set [Quantity in Stock] = " & myprodnow & " WHERE [Product ID] = " & myprodid

    DoCmd.RunSQL mysql



    End Sub
  20. #11
  21. Database junkie..
    ASP Scholar (3000 - 3499 posts)

    Join Date
    May 2004
    Location
    Derry, N Ireland
    Posts
    3,092
    Rep Power
    52
    everything after the "me." is the control name, yes.
  22. #12
  23. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jan 2005
    Posts
    60
    Rep Power
    10
    Any ideas on the previous post?
  24. #13
  25. Database junkie..
    ASP Scholar (3000 - 3499 posts)

    Join Date
    May 2004
    Location
    Derry, N Ireland
    Posts
    3,092
    Rep Power
    52
    Triple check all field, table and control names, paying particular attention to spaces.

    My code above assumes that all id field are NUMERIC and not TEXT. Please confirm that this is true.

    Then compact and repair the db, remove sensitive info, zip and post here or elsewhere (eg Yahoo briefcase) with instructions as to how to access it.
  26. #14
  27. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jan 2005
    Posts
    60
    Rep Power
    10
    Yes, I did have product id as type text. is there a way to amend the code to interface with this datatype?
  28. #15
  29. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jan 2005
    Posts
    60
    Rep Power
    10
    I went ahead and just went through and changed all data types to number, and it worked GREAT!! Thank you so much for your patience and all your help.

    I have one last minor question, is there a code that will reset the combo box and textfield back to null or empty once you hit the process button?
Page 1 of 2 12 Last
  • Jump to page:

Similar Threads

  1. Question about 'default values'
    By liguor60 in forum Microsoft Access Help
    Replies: 0
    Last Post: November 27th, 2004, 06:23 PM
  2. Math question
    By GazouTG in forum ASP Development
    Replies: 1
    Last Post: November 16th, 2004, 02:08 PM
  3. Simple Math operations in a Form
    By 88fingerslukee in forum Microsoft Access Help
    Replies: 3
    Last Post: July 29th, 2004, 10:58 AM
  4. Question about returning only distinct (different) values
    By fizikas in forum Microsoft Access Help
    Replies: 1
    Last Post: March 3rd, 2004, 09:33 AM
  5. my web site is just pulling the values (question updated)
    By Steve Schofield in forum ASP Development
    Replies: 1
    Last Post: April 13th, 2001, 09:16 AM

IMN logo majestic logo threadwatch logo seochat tools logo