Results 1 to 10 of 10
  1. #1
    GregShah is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Location
    Canton, Ohio
    Posts
    60

    Recalculation Total upon Record Entry

    I am using a simple database to track my production of items in my plant. I created a table where I add all the new quantities and names. I have another table that records the quantity, date and name of each part as it is completed.

    The items in the production table are subtracted from the Total table until the quantity reaches zero and the order is completed.



    I use a query to track the names of the pieces that have a need quantity greater than zero. I use this list in a form list box to cut down on errors.

    My question is as I enter my production into the form, the quantity decreases and finally will reach zero. Is there a way that I can update the Greater Than Zero query as I make each entry into the production table? This will prevent over entering production by closing out the name of a product once the required value hits zero.

    Thanks for your help,

    Greg

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    in the form record AFTERUPDATE event, refresh the query.
    if its in a listbox,then
    Code:
    sub Form_Afterupdate()
       lstbox.requery
    end sub

  3. #3
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Generally you wouldn't store these values, simply calculate them on the fly, and then they are guaranteed to be "live" and accurate.
    If the query results are displayed on a form a simple refresh or requery should update things.

    Can you post up your table designs , to get a better picture?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  4. #4
    GregShah is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Location
    Canton, Ohio
    Posts
    60
    I will post up later today. Tied up with normal business activities now.

    Thanks for the interest.

    Greg

  5. #5
    GregShah is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Location
    Canton, Ohio
    Posts
    60
    Here are the tables to see what I am trying to do.

    I track the balance required greater than zero and use that list to make sure the proper name is chosen for the production report. Once balance needed hits zero, name no longer appears on the list box.

    Thanks,

    Greg
    Attached Files Attached Files

  6. #6
    GregShah is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Location
    Canton, Ohio
    Posts
    60

    Sample DB

    Sorry about that first copy.

    I think this is better version.

    Thanks
    Attached Files Attached Files

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I would suggest you fix a few things:
    1) you have a form and several fields named "Description". "Description" is a reserved word and shouldn't be used as an object name.
    2) You have spaces in object names. Should never have spaces in object names.
    3) You have special characters/punctuation in object names. One example is the field "Complete?". (question mark)
    ---------------------------------------------
    Object names should be letters and numbers.
    Do not begin an object name with a number.
    NO spaces, punctuation or special characters (exception is the underscore) in object names


    I re-read your first post (many times).
    Nary a form name, nor a table/query name. Since I don't know your business and you didn't give directions on how to see/replicate the problem, I am lost.


    Maybe you could give a narrative on what to do to see the problem?? (using object names?)

  8. #8
    GregShah is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Location
    Canton, Ohio
    Posts
    60
    Thanks for the direction.

    Let me clean things up and I will describe the issue better.

    Appreciate your help so far,

    Greg



    Quote Originally Posted by ssanfu View Post
    I would suggest you fix a few things:
    1) you have a form and several fields named "Description". "Description" is a reserved word and shouldn't be used as an object name.
    2) You have spaces in object names. Should never have spaces in object names.
    3) You have special characters/punctuation in object names. One example is the field "Complete?". (question mark)
    ---------------------------------------------
    Object names should be letters and numbers.
    Do not begin an object name with a number.
    NO spaces, punctuation or special characters (exception is the underscore) in object names


    I re-read your first post (many times).
    Nary a form name, nor a table/query name. Since I don't know your business and you didn't give directions on how to see/replicate the problem, I am lost.


    Maybe you could give a narrative on what to do to see the problem?? (using object names?)

  9. #9
    GregShah is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Location
    Canton, Ohio
    Posts
    60

    Revised Database and Explanation

    Here is a brief description of this database.

    The table mastershaftlist is a list of the shafts required by job number. The dailyshaftproduction is the quantity and shafts produced by date.

    I identify the shafts by joining the job number and mark number since the same mark could be used on multiple jobs.

    The queries bsumshaftbyid and bsumshaftproduction are used to simply sum up the total quantity and quantity produced. If shafts appear on either list more than once, these queries add the entries together as one.

    The query shaftrequiredandproduced compared the number of shafts required to the number of shafts produced and shows the balance of shafts still needing producted.

    The shaftgtz is a list of the shafts that are not completed.

    The form and subform are used to enter the number of shafts produced each day. I use a combo box to restrict the shaft id to shafts that still need to be produced. It uses the GTZ query to see if the shaftid is required.

    In the case that there is one pole required, once I select that id I would like the combo box list to be updated and the shaft id not to show up in the combo list. I know if I back out of the form and go back in the id will not longer show up. However, I am hoping there is a way to have the shaftrequiredandproduced and GTZ query to updated and the completed shaftid drop off the combo box list.

    Hope this clarifies my issue better.

    Thanks,

    Greg
    Attached Files Attached Files

  10. #10
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I am not real fond of your table designs. You might read this: Microsoft Access Tables: Primary Key Tips and Techniques

    I removed the question mark in field "Completed?"
    I removed the space in the names for "Unit Weight" and for "Job List".

    I added a query and created a function.
    Added code for buttons.


    See if this is close
    Attached Files Attached Files

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 03-06-2016, 04:23 AM
  2. Total record sum
    By gmaster in forum Access
    Replies: 2
    Last Post: 10-13-2014, 01:59 AM
  3. Replies: 3
    Last Post: 03-09-2013, 10:39 AM
  4. Replies: 4
    Last Post: 08-14-2012, 07:14 AM
  5. Replies: 6
    Last Post: 04-17-2012, 10:32 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums