Total of page Q 1

[stillwillyboy]

I looked thru the past posts and saw a few q's that related to this, but still I couldn't get it to work. I must be missing something very simple.

In a table I have Quantity and Price. In a query for info in this table I have: ExtendedAmount = Quantity * Price.
It calc's properly.

I have a report tied to the query. I have in my detail section of the report the above three fields. There may be more than one line of information. The info shows properly in the detail section.

I want the grand total to appear in the page footer. In the control source of a text box I have: =sum([ExtendedAmount])

I keep getting Error. What am I missing?

TIA.

 

[THWatson]

Have you tried putting the =sum([ExtendedAmount]) in the Report Footer rather than the Page Footer?

Tom

 

[dhookom]

There is a MS knowledge base article that describes how to create Page Totals. Sorry, I don't recall the url but you should be able to search the FAQs here or google.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[stillwillyboy]

Duane, thanks for the direction. I found what I was looking on for on the MS site. KB # 216311. I had to modify it to handle two fields. It works. Would you please provide some input for the following?

I have my tables and query. I use these to provide info to my reports (invoices).

Can the information when a report page (an invoice) is generated be saved in a table? The invoice number, invoice date and calculations are being created at the report level, not the table or query level.

I want to save the Customer #, the invoice date, invoice number and amount (and maybe something else I haven’t thought of yet) to a table AFTER the date and invoice number have been assigned. I’m thinking that I can use this for my Accounts Receivable (AR) table.

Also, if I can do the above, then I was wondering if I could use the Page Number function to assign the invoice numbers. I would need to read the AR table to find the highest number used and then increment it for each new invoice. I do not want to manually assign invoice numbers.

I feel that as each weeks invoices are produced, I would need to use an Append query to update my AR table. I still have to figure out how to apply the payments, debits and credits.

Is there a better way?

TIA

Bill

 

[dhookom]

I think you are going about this backwards. I would generate all the records and invoice numbers etc in tables and then simply run a report based on the information.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[stillwillyboy]

Use my query to sort and total all the info into another table? Each invoice may have more than one line of detail that would need to be summed and then have an invoice number assigned to it. Sound close?

Bill

 

[dhookom]

When I have created an app like this in the past, I have a field in the billing detail for "InvoiceID". I run code that assigns new invoice numbers to the proper billing detail records and then print a report based on the InvoiceIDs.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[stillwillyboy]

I have an OrderID number that links the Orders table with the OrdersDetail table, but this is not the same as an invoice number. It is more for internal purposes than anything else. I could have the following:

OrderID Part Cust# Tkt# PO# Quantity
17 abc 36 1234 7777 100
18 abc 36 4567 7777 200
19 abc 36 1472 8888 300
20 abc 36 7568 7777 400

The orders for lines 17, 18 & 20 need to be on one invoice and line 19 needs to be on its own invoice. The PO determines when a new invoice is needed.

Each invoice could have any number of lines of detail. There is no way that I will know thoughout the week when and what parts/orders that I will have to enter. I want the system to assign the invoice numbers.

 

[dhookom]

Any way you do this, you need to make a selection of records to invoice. Use this selection to:
-grab the next highest invoice number (increment it)
-assign than invoice number to the selected OrderID records
-Print the Invoice for the new invoice numbers.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[stillwillyboy]

Duane,

I am currently at a loss as to how to handle the invoice numbering. Given that I have an Orders and OrdersDetail table, should I create a subdata sheet and create a report from that? How do I get the invoice number? Each entry into the Orders table is assigned an OrderID number as is the OrderDetail that goes with it. How can I assign one invoice number to multiple lines of detail?

TIA,

Bill

 

[dhookom]

If all order details for on OrderID are billed on the same invoice, then you don't need to add invoice numbers to the order details. Just add invoice numbers to the orders table. You should be able to select the Orders that you want to invoice. I would create a recordset of these orders. Then find the current maximum InvoiceID value and add one each time you move to the next record in the recordset. Update each order record with the incremented InvoiceID.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[stillwillyboy]

Each time an Order and OrderDetail is entered, each is given the same number by autonumber, even though they are in two different tables. As an entry is made into each table, the autonumber increases. This is what links the two tables and the Order and OrderDetail. The is not the same as an invoice number.

It sounds like your saying to manually enter all the orders for a particular PO to a particular invoice number. But given that orders come in throughout the week, there is no practical way for me to look up previously entered orders, see the invoice number assigned and then enter the newer orders to that invoice.

As backwards as my idea is, it works except I do not understand how to save the info that appears on the report (invoice) to a table for history and AR. Plus, I need to figure out how to increment the page number.

Am I understanding your suggestion?

 

[dhookom]

Am I correct in assuming that:
-you can specify a group of orders that require invoicing
-each order and all of its details will be on a single invoice
-invoice numbers will be numbered sequentially
If all the above is true then you should be able to:
-generate a recordset of orders to be invoiced
-grab the highest invoice number already applied and add one
-edit an order record to update an invoicenumber field
-move to the next order and add one to the invoice number
-loop like this through all orders requiring invoicing
Then print out new invoices.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[stillwillyboy]

Duane, In response to...

Am I correct in assuming that:
-you can specify a group of orders that require invoicing.
Yes. I have the Orders and OrdersDetail tables that hold the info to be invoiced. I currently do not have a Delete and/or Append query to clear the tables after invoicing.

-each order and all of its details will be on a single invoice.
Yes, but some invoices may have more than one order and/or part and its details. Some customers may receive more than one invoice for the same part in the same week. It depends on the P.O. and the shop where the work was done.

-invoice numbers will be numbered sequentially.
Yes.

If all the above is true then you should be able to:
-generate a recordset of orders to be invoiced
Is this recordset a single table with both Orders and OrdersDetail?

-grab the highest invoice number already applied and add one.
OK. VBA?

-edit an order record to update an invoicenumber field.
Does this mean that the recordset has to be created with a blank invoice number field?

-move to the next order and add one to the invoice number
-loop like this through all orders requiring invoicing
Then print out new invoices.
OK.

Thanks.

 

[dhookom]

There should be no reason to ever delete Order or OrderDetails records. I expect that you would add an InvoiceNumber field to the Order tables. Regarding which Order records are ready to be invoiced, that is your concern.

Do you understand how to write code to create a recordset and then update that recordset?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[stillwillyboy]

Do you understand how to write code to create a recordset and then update that recordset?"

Not yet. I have some VBA experience with Excel. I am reading the VBA aspects of Access now in the A2K2 Bible.

 

[dhookom]

Please check back with any questions on recordset details after you have done some reading. I don't want this thread to become a tutorial.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[stillwillyboy]

Duane, I’ve done some reading. In my situation, a recordset is simply a set of records from a table(s) created by a query. This is the same as a dynaset. From my readings, I have tried to create the code to open the recordset for the query named: “qry-Orders Query”. But I have a few problems and/or questions.

When I execute the following code, I get the message “User-defined type not defined”. This happens at the Dim CurrDB as database line. My system is not recognizing the keyword “database”

When I REM the above line, I then get the message “Variable Not Defined” for the line “Set CurrDB = CurrentDb”, which makes sense b/c the variable has not been defined.

My query calc’s the amount for each line of detail, but not for the total invoice because until the info is processed, I don’t know the total of each invoice. Each invoice may have more than one line of detail. Each line will have the “ExtendedAmount” (qty * price) and “ShippingCharges” (manually enterd). The invoice should show this for each line plus the grand total which is = ExtendedAmount + ShippingCharges.

With that said, when the corrected code executes, is there a new table that is generated for the info? I don’t see in the code where the info appears. TIA. Bill

Option Compare Database
Option Explicit

Private Sub InvoiceRecordSetQuery()
'On Error GoTo form_afterupdate_err
Dim CurrConn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim CurrDB As database 'keyword not being recognized
‘database name is: “Order Entry1_BE”

Set CurrDB = CurrentDb
Set CurrConn = New ADODB.Connection
With CurrConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = CurrConn
.Open
End With

Set rst = New ADODB.Recordset
rst.CursorType = adOpenStatic
rst.LockType = adLockReadOnly
rst.Open "qry-Orders Query"
With rst
'is this where a MakeTable query goes?
End With
rst.Close

'form_afterupdate_exit:
' Exit Sub
'form_afterupdate_err:
' MsgBox "error"
' Resume form_afterupdate_exit


End Sub

 

[dhookom]

I don't use ADO. I would use DAO which might require you to set a reference to the DAO library if you don't have one. Then you code to create a recordset would be:

Dim CurrDB as DAO.Database
Dim rs as DAO.Recordset
Set CurrDB = CurrentDb
Set rs = CurrDb.OpenRecordset("[qry-Orders Query]")

I am not sure where you got the idea that you need a make table query. You could have a table of invoice information with one record per invoice that stored the invoice number, date created, who created, ...


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[stillwillyboy]

I set the DAO reference, but when I run the code I get a message telling me that it cannot find the query named "qry-Orders Query". The query definitely exists. I verified the spelling. I tried the path to the db also, but it also did not work.

"You could have a table of invoice information with one record per invoice that stored the invoice number, date created, who created, ..." This is confusing me. How do I have a table of invoice information with one record per invoice if I have multiple lines of detail? How do I get the sum of the detail lines?