Checking Multiple Criteria

[Gary Dolliver]

Hi all,
I am wanting to set up a check system based on multiple criteria (explained
below) and then depending on the result, will then do A or B. I figure it is
a pretty basic set up of:
If result = A then
A
Elseif result = B then
B
Endif
The problem I am having is creating the code for both the A and B criteria.
Ok, I will be needing to focus on 3 tables, Orders, Creations, and Items.
Orders are composed of Creations, Creations are composed of Items. The Items
table will hold the overall quantity of an Item, and there can be multiple
items for each creation, and then multiple creations to each order.
What I am wanting to do is have some code run when the user selects
"Complete order" that will check to see if a particular order type is used
(info within orders table) and that the overall quantity (looking through all
creations and items) is greater than 4. I created a query that will check
this, but am not sure how to implement it so that the result set will be
checked based on the current order number... help?
I do not have any code written yet, but can post the SQL if needed. Thank
you!
-gary

 

[John W. Vinson]

can post the SQL if needed

Please do.

John W. Vinson [MVP]

 

[Gary Dolliver]

Thank you for the reply, John.
Here is the SQL:
SELECT items.Order_ID, items.Creation_ID, Sum(items.Item_Qty) AS
SumOfItem_Qty, orders.Ship_Type_ID, orders.Order_Class_ID, items.Item_Type_ID
FROM (orders INNER JOIN creations ON orders.Order_ID = creations.Order_ID)
INNER JOIN items ON creations.Creation_ID = items.Creation_ID
GROUP BY items.Order_ID, items.Creation_ID, orders.Ship_Type_ID,
orders.Order_Class_ID, items.Item_Type_ID
HAVING (((Sum(items.Item_Qty))>=4) AND ((orders.Ship_Type_ID)<4) AND
((orders.Order_Class_ID)=1) AND ((items.Item_Type_ID) Like 5 Or
(items.Item_Type_ID) Like 7));

After looking at it, I may need to use another select query to group on
Order_ID, but as I only want it to run this on the current order, I do not
believe it should be needed?
What I am basically looking for is if the order is a certain Item_Type, and
the total item quantity is greater than 4, and the Ship_Type is less than 4,
then change it to 4, else, leave it alone.
Sorry for not posting this earlier, and thanks again.
-gary

 

[John W. Vinson]

as I only want it to run this on the current order, I do not
believe it should be needed?

There's nothing whatsoever in this query that would restrict the report to run
on the current order. I suggest that you need a WHERE clause referencing the
primary key of the table, using a reference to a form control as a criterion.

John W. Vinson [MVP]

 

[Gary Dolliver]

Hi John,
Thank you. I can definitly add a where clause to limit this to the current
record. However, how would I set this up in an "if/else" statement so that
when I close the order, it runs this select statement, and if the order falls
into either category A or B (if all the criteria are met), that it will run
the correct code? I am not sure how to set up the if/else based on the
result of a select query. Help?
Thanks
-gary

 

[Gary Dolliver]

Hi John,
Sorry, I never heard back after my last post.
I was aware of the missing WHERE clause, and I can easily put it in, but my
main question was how to set up in VBA an if/else (I believe I should use
this?) to find out if the current order (record) fits into this query
criteria, and if so, I would need to change a field on the current form.
Please let me know if you have any information which could help me or where
I should look, thanks
-gary