Perform an action on each row from a SELECT query.

[HairyArse]

Hey guys,

I'm a relative beginner to VB having spent the last few years learning PHP and MySQL.

I'm currently building a system to manage our quotations, purchase orders and sales orders. For each of the three systems there's a main form and a sub form, for example, tbl_quotations links to tbl_quotation_details - you have one quotation with many items. The exact same structure applies to purchase orders and sales orders.

I've built a form which contains many search parameters and is used to filter quotations in the associated sub form. I'm now trying to build a function to convert a quotation and its many items into a purchase order or a sales order, with the exact same number of items. This would be straight forward if they only ever contained one item.

In PHP I would run a select query to grab all of my records, save them to an array and then loop through the array running an INSERT query on each row.

I figured the same way would make sense in VB but read somewhere that creating an array to do this isn't necessary. So first of all can anyone confirm that? What is the corect way to do this? Using an array? Or can I simply run a SELECT query and then an additional INSERT within it?

If I do need to use an array, what's the best way of setting up a 2 dimensional one that contains all of the fields from my query?

 

[JamesMcS]

I think it would just be an append query in access. For example in the on click of a "Convert to PO" button you'd have the SQL statement that appends the quote data from main and sub tables into your PO table. No need to create an array as far as I know....

 

[HairyArse]

What's complicating it for me is that I will never know how many rows will need to be INSERTED. There's always be one row for the sales order itself which is the easy bit. The unknown is however many order_details row I'll need to insert.

Will an INSERT THIS INTO THAT (SELECT THIS FROM THAT WHERE QUOTE = THIS) work for multiple results?

 

[JamesMcS]

Well, I think if you design the query in the right way, you should get your sales order number repeated down the whole table, next to each quote item, and robert's your father's brother

 

[DCrake]

Can you not have the details in on table and a marker to indicate what type of status the order line has. (Q)uotation, (P)urchase or (S)ales. Then simply change the marker.

 

[JamesMcS]

And yeah basically that's the right way to go SQL wise. If you're doing it from a form you might need to refer to the record that's being displayed on the form at the moment, so your criteria would be something like "WHERE Sales_Order=forms![Form_Name].form![Sales_Order]"

Or something.....

 

[HairyArse]

Can you not have the details in on table and a marker to indicate what type of status the order line has. (Q)uotation, (P)urchase or (S)ales. Then simply change the marker.

I could but I might - and generally will - need one instance of each simultaneously. Also, quotes might have the same item numerous times but with different quantities, i.e. price for 10, 100, 1000. And so obviously the PO and SO wouldn't reflect that.

Nice idea though and I like your thinking.

 

[DCrake]

So in your line details table. When you have sent them a quote for various quantities, as prices can be different for different quantities, you simply have a flag in the detail lines tabel to indicate which ones appear in the actual order. Then once the purchase order has been raised you can purge the quotes table of redundant lines.

 

[HairyArse]

That's not gonna work in this instance because POs, SOs and Qs also have their own unique fields that don't need to carry across. They also need to get marked off as a complete or live depending on their progress so they do need to remain as three entirely separate entities.

It sounds like an INSERT INTO (SELECT.... might work but I'm tearing my hair out with it because i've got to:

select my quotation fields from quotations, then also select each associated record in quotation details, then insert into sales_orders and each associated line in sales_order_details - all of which makes me think that perhaps my original thoughts of using an array might well be the correct way to do things.

My brain hurts.

 

[DCrake]

You will need a 4 step process.

Step 1: Append details from quotations header table into the sales order header table which should generate a new SO number.

Step2: Append relevant quotation lines from quotations details table into sales order lines table where the quotation number primary key = quotes lines foreign key.

Step 3: Delete the quotations lines records for the quotation number foreign key

Step 4: Delete the quotation header details from the quotation header file for the primary key = quotation number.

Example:

Quotations Header Table

Quote No: 123
Customer: abc
Other fields....

Quotation details tables
QuoteRef: 123
QuoteLine:1
Product: xzp
Qty: 3
Price: £10
Wanted:True

QuoteRef: 123
QuoteLine:2
Product: xzp
Qty: 10
Price: £8
Wanted: False

QuoteRef: 123
QuoteLine:3
Product: xzp
Qty: 1
Price: £15
Wanted:False

Step 1
Insert into Sales Order Header Select * From Quotations Header Where QuoteNo = 123

Step 2
Insert into Sales order lines Select * from Quotations Lines Where QuoteRef = 123 and Wanted = True

Step 3
Delete * from Quotations Lines Where QuoteRef = 123

Step 4
Delete * form Quotations Header where quote no = 123

No code is untested and is for brevity only.

 

[HairyArse]

Yeah that makes sense but I'll never need to delete the original quote because I'll always want to be able to follow 'the trail' backwards from an order to its origin.

There's a chance I'll be building an enquiries system that comes before Quotations too, so that definitely confirms the need to retain historical data.

It's funny, it's one of those tasks that I can work out quite easily what I need to do, it's just coding it that's puzzling me.

I'll report back with my progress. Thanks for the suggestions.