Where is RecordSet result displayed? 1

[stillwillyboy]

This code was obtained from the help file. I have modified it to match my db and query. The question is: When this code runs, where does the resulting recordset appear. There is no new table, no change to the original table and no error message. How/where do I see the results of this code?
Is there code missing?

Please help. I am getting frustrated. TIA. Bill

Sub SortX2()

Dim dbsOrderEntry1_BE As Database
Dim rstInvoices As Recordset

Set dbsOrderEntry1_BE = OpenDatabase("Order Entry1_BE.mdb")
' Open a Recordset from an SQL statement that specifies a
' sort order.
Set rstInvoices = _
dbsOrderEntry1_BE.OpenRecordset("SELECT * " & _
"FROM [qry-orders query] ORDER BY orderid ", _
dbOpenDynaset)

dbsOrderEntry1_BE.Close

End Sub

 

[RoyVidar]

You have (probably succesfully) opened the query which is now available in the recordsetvariable rstInvoices.

To "see the results", you could for instance do the following after the opening and before the closing of the recordset:

[tt]dim idx as integer
with rstInvoices
if not .bof and not .eof then
do while not .eof
for idx=0 to .fields.count-1
debug.print .fields(idx),
next idx
debug.print
.movenext
loop
end with[/tt]

But the question is, what do you want?

Roy-Vidar

 

[stillwillyboy]

Roy,

How do I know that the query has been opened?

I am wanting a table, form, report, recordset, anything that shows me that the SQL in the code has worked. Otherwise, how do I know that it has worked? Also, if I don't know if what I have now is working, how can I continue to manipulate the data from the query?

Simply put: I am looking for proof that the my code is working.

TIA,

Bill

 

[RoyVidar]

So, you didn't try the above "proof"? (hit CTRL+G after running to see the results)

Or try just
[tt]rstInvoices.movelast
msgbox rstInvoices.recordcount[/tt]

Roy-Vidar

 

[stillwillyboy]

Roy,

I looked in the Immediate window. The info is there. Now, how do I get the info to a table? I am wanting to use the info for producing invoices. Each invoice could have multiple lines of detail.

The current query table has some of the following:
OrderID Cust# ShopID PO# Qty Price XAmt Ship
12 36 1 11 1000 .01 10.00 0.00
13 52 3 52 100 1.00 100.00 20.00
14 36 1 11 2000 .01 20.00 0.00
15 199 3 99 1000 .50 500.00 0.00
16 36 2 24 500 .10 50.00 0.00

From the very abbrevisted table, I would need four invoices.
Invoice one would have two lines of detail:
1) OrderID 12 Cust# 36 PO# 11 $10.00 + 0.00 = $10.00
2) OrderID 14 Cust# 36 PO# 11 $20.00 + 0.00 = $20.00
Total of invoice $30.00

Invoice two would have one line of detail:
1) OrderID 16 Cust# 36 PO# 24 $50.00 + 0.00 = $50.00
Total of invoice $50.00

Invoice three would have one line of detail:
1) OrderID 13 Cust# 52 PO# 52 $100.00 + 20.00 = $120.00
Total of invoice $120.00

Invoice four would have one line of detail:
1) OrderID 15 Cust# 199 PO# 99 $500.00 + 0.00 = $500.00
Total of invoice $500.00

When I print the invoices, I need both the detail lines and the total to show. How do I sum the extended amounts (qty * price) and the shipping charges (manually entered during order entry) from each line of detail for different customers to get my invoice total?

If I can sort and sum the info to a table, wouldn't this give me the ability to do the above? But I can't see how a table could hold both the detail and totals at the same time for each invoice. Unless the invoice total is like a running sum figure?

TIA. Bill

 

[RoyVidar]

So - now we've come to what you really want, creating invoice reports.

I think the usual approach, is to create a query which calculates everything per each line:

LineTot: ([qty] * [price]) + [XAmt]

Base the report on this query (the stored query, not a recordset). Then use the sorting and grouping facilities within a report to do the totals (use the Summary Options in the wizard, and tweak, if necessary afterwards)

Roy-Vidar

 

[stillwillyboy]

Roy,

That was the way I did my first attempt. I went to the recordset b/c of another's suggestion.

Anyway, I did exactly what you have suggested and it works perfectly, except for a few things:

1)What is the "proper" way to assign an invoice number to the invoices? I used the Page function and it works, but how do I store the last invoice number (page #) so next weeks invoices begin with the next number up?

2)The query calc's the individual lines and the totals are calc'd when I run the reports (invoices). How do I save the invoice totals? They are not in a table, they are on the report (invoice). Can you save information calc'd in a report to it's own table?

I believe that the solution will be the same for both 1 & 2 above.

If I can get the two issues above, I think that I can then move on to my accounts receivable table and a few other things.

Thanks Roy. You've given me some hope that I was on the right track at the start.

 

[RoyVidar]

Glad to her you've got it working.

The "proper" way of assigning a unique number might differ dependent on the situation.

In it's simplest form (like my invoices;-)), there's only one invoice per order (but the order might contain several items - giving a one to many relationship between them). In my order table, I have an orderfield, which is assigned when the order is created, and a field for invoice number, which is calculated at the time the "print invoice button" is pressed.

How to do this:
- in the simplest form, the standalone system, you could probably use the DMax function on a control bound to the invoice number field in the table:

[tt]if isnull(Me!txtInvoiceNo.Value) then
Me!txtInvoiceNo.Value=DMax("field",Table")+1
end if[/tt]

If assigning the unique number is supposed to be in a multiusersystem, I'd say take a look at MichaelRed's faq faq700-184 (because of what might else happen if two users try to assign an invoice number at the same time)

Storing calculated values. No you should not! As long as the values the total is derived from is stored in the tables, you should not store them. It's bound to give errors at some time, you should calculate them (the same way you do in the report) each time they are needed.

What happens if you change the qty of one item in the qty field, forget to update -> all future reporting is wrong!

Roy-Vidar

 

[stillwillyboy]

Roy,

It will be a single user system. I want to put the tables and queries on the server but have the forms and reports on the PC. (I think I said that right).

You said "there's only one invoice per order (but the order might contain several items - giving a one to many relationship between them)". Is this the same way as I gave in the example:

Invoice one would have two lines of detail:
1) OrderID 12 Cust# 36 PO# 11 $10.00 + 0.00 = $10.00
2) OrderID 14 Cust# 36 PO# 11 $20.00 + 0.00 = $20.00
Total of invoice $30.00

In both the Orders and OrdersDetail table, the OrderID number is generated (autonumber) whenever a new order is entered. Neither table has an Invoice# field at this time. This is where I am a bit confused. I need to total the individual lines of detail and then assign the invoice number. Each invoice may have more than one line of detail.

I've read about not storing values, but what if I change a price and then someone asks me to reprint a past invoice? It will come out with the new price, not the original price.

Thanks, Bill