Recalculation Total upon Record Entry

[GregShah]

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

[ranman256]

in the form record AFTERUPDATE event, refresh the query.
if its in a listbox,then

sub Form_Afterupdate()
   lstbox.requery
end sub

[Minty]

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?

[GregShah]

I will post up later today. Tied up with normal business activities now.

Thanks for the interest.

Greg

[GregShah]

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

[GregShah]

Sorry about that first copy.

I think this is better version.

Thanks

[ssanfu]

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?)

[GregShah]

Thanks for the direction.

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

Appreciate your help so far,

Greg

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?)

[GregShah]

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

[ssanfu]

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