Need some help with query

[themastertaylor]

I've built a system to log the orders i place at work. basic structure is that i have a main orders form for generic detail i.e. date, supplier etc, then a subform in datasheet view for the actual items much like in northwind. I've got it all working now except one aspect. I used the suborm approach to assist with this one query and now i'm sorted it only half works.

Basically our suppliers all have different prices and the same supplier will charge a different amount for delivery to a different site. due to the volume of orders i place i end up going back through the system and entering the actual price after the goods are invoiced (i usually know roughly but not exactly. obviously this is rather time consuming so what i've done is placed a bolean field in the orders table to mark when an order has been checked according to the invoice.

I now want a query which will search through the checked orders and return the prices for that location and supplier for the item in question when i first input the order. i've got the query to work to a degree - it returns the prices perfectly but i have to run the query as i enter each item. e.g. if i enter item 1 and hit the button i get the price, then enter item 2 hit the button and get the price etc. however if i forget to run the query after item 1 i keep getting prices for item 2. what i'd like to be able to do is enter all of my items and then hit the button to run the query and return a price for each item in the order. i assume this is possible as the query will return the price for item 1 even if it was item 25 on the old order.

 

[wazz]

your recordset = the items in the order
with rs
.movefirst
do while not .eof
run your query
.movenext
loop
end with

hopefully something like that.