Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Stock/Inventory (2003)

    I want to build a simple stock control database, where I can enter reciepts, and issues and have a balance. I have 3 controls on the form, QtyRec, QtyIss and balance. I thought if I used DSum in the balance control, it would work but I get Name? error, even though the field names are correct. Can anyone help me out please?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Stock/Inventory (2003)

    Without knowing how you used DSum it's impossible to tell what goes wrong.

  3. #3
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Stock/Inventory (2003)

    I put DSum("QtyRec-QtyIss","tblStock_Quantities") in the balance control.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Stock/Inventory (2003)

    If it's in the Control Source of a text box, the expression should be

    =DSum("QtyRec-QtyIss","tblStock_Quantities")

    i.e. with an = before DSum.

  5. #5
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Stock/Inventory (2003)

    Thanks Hans, that's sorted it, but I don't think that's what I want, or I'm doing it wrong. I want something like:-

    QtyRec QtyIss Balance
    10 0 10
    10 5 15
    15 5 25

    but with DSum, I'm getting one figure in Balance all the way down.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Stock/Inventory (2003)

    Try this in the Control Source for Balance:

    =[QtyRec]-[QtyIss]

  7. #7
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Stock/Inventory (2003)

    That doesn't give me the running total.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Stock/Inventory (2003)

    Running totals are easy in an Access report. In a query or form, you need DSum with a where-condition as third argument - see How to create a running totals query in Microsoft Access.

  9. #9
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Stock/Inventory (2003)

    The balance is created in a query with [QtyRec]-[QtyIss]. I then created another query using DSum("Balance","Query1"). Instead of getting a running total, I get 30 in every row of the query.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Stock/Inventory (2003)

    That won't work - please see my previous reply and the link it contains.

  11. #11
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stock/Inventory (2003)

    Check out

    <post#=585769>post 585769</post#> Update Inventory [In Stock] Amount (Access 2003/SP


    HTH, John

  12. #12
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Stock/Inventory (2003)

    Thanks John, I'll try and find the code. The attatchment is missing.

  13. #13
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stock/Inventory (2003)

    Here it is!
    Attached Files Attached Files

Posting Permissions

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