Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Jul 2011
    Posts
    16

    Unanswered: Multiple Quantities in Access?

    Hello There.

    I have recently started a new job and one of my tasks was to set up a database for all the equipment they use. Which I believe I have done as this could use a primary key each.
    However another one of my tasks is to set up one for the materials/consumables that they use? Im not sure if this can be done in access but I want to find a way where i can put multiple consumables in 1 record for example

    10 plastic sheets (is one record rather then having 10 different records for each sheet)?
    Also I need a way so that this quantity can be changed when the materials are used or more are delivered?

    Or is it best to do this in excel or something?

    Any Help Would be much appreciated.

    Thanks Nirav

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Seems rather simple, or do I miss something here?
    Column 1: Item ID
    Column 2: Item Description
    Column 3: Quantity
    Have a nice day!

  3. #3
    Join Date
    Jul 2011
    Posts
    16
    Maybe i didnt make it as clear.

    I believe that would be the table I would use or similar,
    But if i was to use this in a form how would i do it ?
    For example i have
    10 pens
    someone signs out one

    On the form i would have
    Item:Pen
    Quantity taken: 1
    but this then changes the quantity in the database to 1 instead of 9?
    also is there a way to add more to it?

    Thanks again

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Use an unbound textbox for the quantity to add/substract from the stock and add/substract its value to/from the quantity stored into the table. Ex.:

    1 Textbox: Quantity (bound to the Quantity column of the table, read-only)
    2 Textbox: Amount (unbound, used add/substract).
    Then:
    Code:
    Private Sub Amount_AfterUpdate()
    
        If Not Nz(Me.Amount.Value, 0) = 0 Then
            Me.Quantity.Value = Me.Quantity.Value + Me.Amount.Value
        End If
        
    End Sub
    Have a nice day!

  5. #5
    Join Date
    Jul 2011
    Posts
    16
    Thanks i shall try that does the code go behind each text box or??

    Also i need to add job numbers for each material taken out which is ok but it gets complicated when multiple quantities are present and being used for separate jobs?

    Any Ideas thanks

  6. #6
    Join Date
    Jul 2011
    Posts
    16
    I am trying to link a text box on a form which goes to materials transactions, from the base table materials which is fine it works

    Except the quantity column, as the form is based on the transaction table it doesn't like me linking the text box up to a column in another table which is also a bast table,
    as i keep receiving the error
    #name?

    Any ideas how to link up the text box, so the quantity in the materials column does change.

  7. #7
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,422
    Provided Answers: 8
    I would write the
    1 pen take out of stock into a table

    The Write a Query showing the Total Taken which is then - from the total number been added to the System

    this will help with Auditing as you can Show How many have been taken
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  8. #8
    Join Date
    Jul 2011
    Posts
    16
    I have all my stock in one table for example 10 pens (base table)

    but in another table called transactions i want it to show the pen has been signed out for what job etc, which i have sorted but with the quantity it needs to change the field in the base table???

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could use:
    Code:
    Dim strSQL as String
    strSQL = "UPDATE Stock SET Quantity = <NewQuantity> WHERE ItemID = <ItemID>;"
    CurrentDb.Execute strSQL, dbFailOnError
    Where <NewQuantity> is the new quantity (absolute value) and <ItemId> is the unique identificator for the item you want to change the quantity.

    You can also compute the new quantity (relative value):
    Code:
    strSQL = "UPDATE Stock SET Quantity = Stock.Quantity + <TransactionQuantity> WHERE ItemID = <ItemID>;"
    where <TransactionQuantity> is the signed value of the quantity you want to add to the stock (positive value) or the quantity you want to substract from the stock (negative value).
    Have a nice day!

  10. #10
    Join Date
    Jul 2011
    Posts
    16
    i see that, thanks for the code but it doesnt like it and when i change the form to form view it still says #name? it doesnt like linking the two forms together?

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If you want to bring data from two tables in a single form you need to create a query linking the rows of both tables together.
    Have a nice day!

  12. #12
    Join Date
    Jul 2011
    Posts
    16
    Private Sub Text43_AfterUpdate()
    If Not Nz(Me.Taken.Value, 0) = 0 Then
    Materials.Quantity.Value = Materials.Quantity.Value + Me.Taken.Value
    End If
    End Sub

    I have inserted i used a sub form to bring the other table values into the form. now using that code i want to change the values in the materials table and a error keeps occuring

    Heres a screen dump

    ImageShack Album - 3 images

    I mean its all set up if i can get this last bit of code so that in the sign out materials form when the amount taken value is entered the quantity value on the subform and the materials table will change accordingly, im sure its simple but i cant seem to work it out and it was debugging
    Last edited by niravbhuva; 08-12-11 at 05:12.

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Where is the Sub Text43_AfterUpdate procedure located (i.e. in the module of which (sub)form)?

    2. Is Materials (a table I guess), and more precisely its Quantity column, part of this form RecordSource?

    3. "and a error keeps occuring": Which error? What's the message and the error code?

    4. Please post images, screenshots, etc. in this forum, not on an external site, thank you.
    Have a nice day!

  14. #14
    Join Date
    Jul 2011
    Posts
    16
    1. Its located on the materials sign out form, just the form not the sub form,
    2. Materials is a table yes, the quantity column which record source it is in a sub form the properties don't show record source?
    3. the error currently is sayin runtime error 424 object required.

    4. Apologies couldnt paste them here.

  15. #15
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297

    Smile

    Is the attached the kind of thing you're after?

    Had a spare 10 mins at work so thought I'd knock an example up for you.

    Feel free to use/edit/publish/whatever you want with it.

    Hope it helps!!


    EDIT: Oh, BTW, if you want to see the VBA code, open the form and push Alt+F11. I commented it so you should be able to get the jist of what's going on.
    Attached Files Attached Files
    Looking for the perfect beer...

Posting Permissions

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