Doozy of a question regarding a complex query or repainting a form after modifying a query

<everything works until the stared line below, i just wanted to fill you in on the reasoning for the set up>

I have a continuous form where items can be assigned to installation measures.  Many items can goto many measures.

ex:
total 3 plywood and 1 box of screws

2 plywood goto the door install
1 plywood and 1 box of screws goes to the window install.  (hence many to many)

I have a continuous subform [sbfrmAssignItems] next to a listbox [lstMeasures] with 2 combo boxes ([cboItemID] which is locked, and [cboTempQuantity]) in sbfrmAssignItems .  

The user selects the measure in the list box and then in the subform puts the number of items that are associated with that measure.  They then Press F5 and the items are Appended to the many to many table [tblOrderItems] .  

In order to allow the user to put values for multiple items before appending them to the many to many table.  I had to create "temp table" so the comboboxes can have unique values in them.  Without that temp table all the comboboxes assume the same values.  

Measures and items are associated on the front end then F5 is pressed and then they are appended to the many to many table and are officially associated on the backend.  

******Everything works up to this point******

What I want to do is limit the user to only the items that are available.  If an item(s) are associated to a measure and there are no more to be associated I don't want them on the continuous subform.  

Normally this would be easy, In the query for the form I would count the quantity of an item from [tblOrderItems] subtract them from the quantity in the invoice details table and if they equaled 0 then I would exclude them via the criteria.  

The problem is I can't get at the quantity in the [tblItemsAssoc] table because if I include it in my query I can no longer write values in the query (and the form).  I tried using DSum it works but goes incredibly slow and is bordering unstable.

I tried one other super complex method which changes the criteria of the query that the form is based on in VBA but I can't get the form to refresh without closing and opening the form.

How can I get that tblAssocItems included in my query to find out if there are items to yet be associated?

Or how can I repaint my subform after I change the underlying query of which it is based on?

Here is the SQL:

Thanks, JOe K.
 
 Start Free Trial

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:

I would like to somehow get this SQL which calculates to see if there is available items to yet be assigned:
 
SELECT [Quantity]-nz([MeasureQuantity]) AS TotalUnassigned
FROM tblPO_Details LEFT JOIN tblOrdersItems ON tblPO_Details.PO_DetailsID = tblOrdersItems.PO_DetailsID
WHERE ((([Quantity]-nz([MeasureQuantity]))<>0));
 
With this Query where the temp table is included to allow the user to enter data for individual items on the form:
 
SELECT tblPO_Details.PO_DetailsID, tblPO.FileNumber, sysOrdersItemAssoc.TempQuantity, sysOrdersItemAssoc.DetailsLink
FROM tblPO INNER JOIN (lstExpendClass INNER JOIN (tblPO_Details LEFT JOIN sysOrdersItemAssoc ON tblPO_Details.PO_DetailsID = sysOrdersItemAssoc.DetailsLink) ON lstExpendClass.ExpendClassID = tblPO_Details.ExpendClassID) ON tblPO.PO_ID = tblPO_Details.PO_ID
WHERE (((lstExpendClass.ExpendClass) In ("Reg Cont","Reg Mat","H&S Cont","H&S Mat")))
ORDER BY tblPO_Details.PO_DetailsID;