Subtract Quantities Across Forms

[SuperABen]

This is probably a basic question, but I have been aggravating myself silly over it, and so I was hoping someone somewhere has the answer!



I have been working on designing an Access database for my equipment business.

I have two invoicing reports right now, #1 is the workorder invoice, #2 is the parts invoice. These two are based on a parts inventory form (#3).

#1. One creates an invoice for a service order "workorder." The form that feeds the table the report steals information from allows me to input the name of the parts and the quantities of each used.

The "parts used" field contains a drop down box based on the parts inventory form, #3.

#2. The second invoice report sells parts by themselves without labor added in. Its base form and table is a version of my workorder form.

The "parts sold" field contains a drop down box based on the my parts inventory form, #3.

#3. The parts inventory form allows me to store part information and part quantities in stock. Both forms that collect the information for my invoicing reports can grab the part number from the parts inventory form's base table and use that relevant information on the invoice. This form also shows my quantities.

Here's what I would like to do: I want the quantity on my parts inventory form (#3) to drop by the quantity I specify in the invoicing forms automatically.

How would I go about doing that? I know how to subtract quantities WITHIN forms/tables, but not across a two forms/tables.

Does anyone have a formula of some sort so that I can fill in the appropriate information? I'm mechanically minded, not digital, so any help would be appreciated!

If I didn't explain the scenario well, let me know and I'll try again.

Many thanks in advance!

Ben W.

[alcapps]

You do it by calling the after update event of the qty field invoicing the question is is that a sub form of invoicing or a level above?
if is a level down then me!subformname!form!control(fieldname) = me!subformname!form!control(fieldnamee) - me!qtyfieldname

if you can send me shell of the database I can make changes for you.

[SuperABen]

Alcapps, thanks for the reply.

It actually isn't a sub form, but rather a completely separate and independent form/table.

The basic principle is that every part is identified by an auto assigned number in both my original #1 (workorder form) and #3, the inventory form. I need the #1 workorder form to change the appropriate quantities in stock on my #3 inventory, by referencing the identifying number.

I believe I forgot to explain that originally.

I hope my uneducated language makes sense. If that doesn't help you out, let me see what I can do about sharing the basic design with you.

Thank you!
Ben W.

[alcapps]

In the form that needs the updated number is it calculated?
if so you could simply put forms!formname.requery

if not
if both forms are open
call the field that needs to cchange if you are in that form you can use me. Otherwise use forms!formname!controlname =
forms!formname!controlname - me.controlnametosubtactfrom

[SuperABen]

alcapps, I used your basic principle and came up with this:

Forms!Parts2.[Products Subform].Controls!UnitsSold = Forms!Parts2.[Products Subform].Controls!UnitsSold + Me.Quantity

Parts2 is the main inventory form, that uses a "Products Subform" to show the transactions. I also realized that by the way I designed everything else, it was better to add the quantities.

This code works extremely well, but (like you said it would) only when both forms are open. Can we make it work "in the background?"

Second, this code changes the quantity of the first record only in the Products Subform. I would like it to change the quantities in the appropriate record by the identifying number. The "identifying number" is already a field in the workorder (labeled in the code as "Me.")

Thanks for your help. I don't know why this section of the database has messed with my brain so well... it seems too simple to cause this much trouble!

Ben W.

[alcapps]

Usually when a calculated number is used on a secondary form you check to see if the form is open. If it is you update the value and or requery. If it is not open you run the calc on open of that form so that the value shows correctly.