Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2009
    Posts
    10

    Unanswered: Newbie - update quantity help!!

    Hey guys,
    Im really quite new to access 2007, and im trying to build a simple inventory system where a user can bring up the item he/she wants and it automatically shows the quantity of the product, the unit the product is in and the section its in (I also want to show the costs etc in a later date) and can basically changed the quantity of the product selected and it can then save the new quantity value. AS I SAID IN THE TITLE I HAVE READ THE ALLEN BROWNE ARTICAL ON THIS BUT DUE TO ME BEING SO BASIC I DONT UNDERSTAND WHAT HE IS SAYING. So heres my problem...


    Basically i have a table (Product_tbl) with the following fields:
    Product_ID
    Product_Name
    Product_Qua
    Unit
    Section
    And a second table (Cost_tbl) with the following fields:

    Cost_ID
    Product_ID
    Job_price
    Rent_price
    RRP
    Actual_Selling_Price
    My only relationship is a one-to-many from Product_ID in the Product_tbl to the Product_ID in the Cost_tbl.

    So far a have a combo box for the Product_Name and 3 text boxes linked to the combo box to show quantity, unit and section.

    Now i need simple instuctions of how to enter a new quantity of the product selected in the combo box into a text box somehwere else on the form or into the same textbox the quantity on hand is shown in, and have the new quantity saved to the original record ( so not making any new records)

    Thanks in advance

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    To show a total in a field, make an unbound field (if you don't want to store the value, otherwise add the sourceboject of the field in your table.)

    If it's an unbound field, simply put this in for the sourceobject: =[Job_price]*[Actual_Selling_Price] (or whatever fields you're calculating.) This will automatically update it any time one of the price fields is changed.

    If you want to store in a totals field in the table, make the totals field the sourceobject (like your other data fields but perhaps lock it) and then in both the Job_Price and Actual_Selling_Price field's AFTERUPDATE Event, add code like this:

    me!MyTotalPriceField = me!Job_Price * me!Actual_Selling_Price.

    There are pro's and con's to doing it either way. Most people will argue that you should never store a calculated field but I've come across several reason for doing so (ie. to re-utilize that value in other formulas geared around this value to save from having to write work-around code (ie. it's much simplier to total a field versus a calculation.) Financial applications also utilize storing the value in the table as well (especially for IT auditing). And if you're doing many calculations on a large recordset, this may beneficial for retrieving reports faster. I'm not a big fan of those who say you must always 'calculate the values and not store them' as I often see people then try to write all this extra code just to use that calculated value in other calculations (which becomes messy and difficult to troubleshoot). Just a fore-warning that you'll see some posts where someone will say this is a hard-fast rule which you must never break (I call it lack of experience). In fairness though, most of the time you don't need to store the calculated field but again, don't think this is a rule you MUST follow. Do what's best for your application and how you re-use that calculated value.

    Another way you could do it (which may make the query non-updatable) is to put the formula as an expression in the query itself: ex: SomeNameExpresion: [Job_Price] * [Actual_Selling_Price]. Even though the query may become non-updatable, the advantage to using this method is that you can then easily total this column on the form.
    Last edited by pkstormy; 11-22-09 at 03:03.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I kept the last post even though I'm not quite sure if it answered your question fully.

    Another method is to:
    a. Write a function which returns the value.
    b. Use the Dlookup command to get a value from another table which may be needed in the calculation.
    c. Have all the fields needed in the rowsource query of the combobox so you can utilize all the values (based on what's selected) using code like: me!PriceField = me.MyCombobox.column(1) * me.MyCombobox.column(2)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    In regards to this:
    "Now i need simple instuctions of how to enter a new quantity of the product selected in the combo box into a text box somehwere else on the form or into the same textbox the quantity on hand is shown in, and have the new quantity saved to the original record ( so not making any new records)"

    You could also create a Quantity unbound (or bound) field where they enter the quantity and then do the calculation off of that value using the example in the 1st post. I might do this in the bound field unless the calculation is updating the bound field (then I'd use an unbound field). It depends on how I want the form to work.

    Don't forget that you may need to add a Refresh command in the code right before doing the calculation code!!
    Last edited by pkstormy; 11-22-09 at 03:01.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    Just to expand a little bit on pkstormy's excellent advice. When you create queries in Access, you can actually refer to VBA queries in the wizard. The format is
    SELECT Field1, Field2, ComputeAComplexQuantity(Field3, Field4) As SomeNameOrOther, Field 5, Field6 FROM SomeTableOrOther.
    The nice thing about this method is that when abstracting data, you can use the query instead of the table name, without needing to remember the name of the function which created the computed item.

  6. #6
    Join Date
    Nov 2009
    Posts
    10
    Hey guys

    Thanks very much for your replies,

    But my original thread was actually asking about how to update my quantity of a product and so when the next user opens the record the new quantity will be showing.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the answer is you don't
    you run a query to work out what the current quantity is as and when required
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Nov 2009
    Posts
    10
    ok so every time a user brings up the form, and selects a product the latest quantity will show??

    If thats so can you explain in simpler steps of how to make the query and join it to the appropriated fields on my form?

    Im not very good with queries at all

    Thanks

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    it requires you to write a query which sums transactions
    ie adds goods delivered to you, subtracts goods sold, well that and other transactions likes goods returned, goods exchanged and so on.

    so you need to know what way a stock movement affects stock (sale = reduction in stock)
    you need to use the SQL sum function in a query
    you then need to paramerterise the query or write it on the fly. so that it returns the stock position for a specific product

    you need to run the query within the form and then populate the control.
    so that means running the query in the forms on current event

    you could decide to to use a domain function such as dlookup or dsum.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •