Populating Table with data from Report

[bubba55]

I am generating a report from my database that I would like to turn into an invoice. My report generates perfectly, but I need to add an invoice number when I print the report and I need to take the data Customer ID, Invoice Total, and a maybe other data from the report and populate the Invoice Record table. I have VB code that is generating my Invoice No, creating a new record in the Invoice Record table and populating the Invoice Record Table with the Invoice number. I want to add this Invoice No to my report at time of printing, and populate the Invoice Record Table with the fields from the report as stated above. HELP! (Please!)

[Robeen]

Hi Bubba55,

If you still need help on this, could you please rephrase your question?
I found it difficult to follow how things are set up in your database and connect the dots.

It seems to me that your report is based on data from one data source [table or query?] and that the Invoice Number is in another data source [Invoice Record?].
Does your Report have a field in it for Invoice Number?

If you can clarify [or just re-phrase] your scenario, it would be easier to get help.

If I understand correctly, I would:
1. Make sure that your report has an Invoice Numbner field on it,
2. Base the report on a query that includes the Invoice Number that your VB code creates. I don't know how you are creating new Invoice Numbers in your code - but if you are incrementing your Invoice Number each time you create a new Invoice Number and your newest Invoice Number is the highest Number in there then your report query can select the 'Max([Invoice Number])' from the Invoice Record Table (along with the other fields on the report).
3. Update your Invoice Record Table with a Where Clause that references the Max([Invoice Number]) and adds data to the other fields.

I hope this helps! Let us know if you have any other questions.

[bubba55]

Yes, I thought my question was wordy, so I will try again.
I have a database with 4 tables,
Customer
LabRecords
LabActivity
InvoiceRecord
A customer provides us with a orchid seed pod. We take that seed pod and throug a lab process, plant the seed pod, replate seedlings, clone plants, etc.

The LabRecords table is where we enter the specific cross of the two plants, or the mother plant for cloning.

The LabActivity table is where we record each process that we charge for.

The InvoiceRecord table is where we want to have an invoice number, customerID, total cost, and a field for paid (Yes/No)

I have the CustomerTable working with all the reports needed and relationships.
I have the LabRecords Objects working as required.
I have the LabActivity Objects working as required.

Now for the invoice.
When I am ready to create the invoice, I select a Button - Create Invoice For Customer, from my base navigation screen. That runs a macro, Select_Individual_Invoice. This Calls The OpenForm action that opens a form with all the customers listed in a basic CustID, LastName, FirstName data sheet.
It next calls RunCode that runs a basic function called InvoiceRecord()
This VBA code opens the InvoiceRecord table, creates a new record, creates an invoice number based on current year and invoiceID.(This is working)
Next the Select_Individual_Invoice macro, calls the OpenReport action. A msg box opens asking which customer and you enter the CustomerID. The report then searches the LabActivity Table for entries for that customer that show a balance and paid=0. It then searches the LabRecords Table for the parent for each LabActivity. It formats the report showing customer, Orchid Botanical Name and then the lab activity that has been completed.
All this is working.
Now, when a report is created, does it still exist in memory? Can I add data to the report? Can I take data from that report and populate a table?

What I desire to do is once the report is generated, have a pop up window with buttons for Print Report, Make PDF of Report, Cancel or something like this. When the user selects the "Print Report' I would like to add the invoice number and date to the report before it is printed, and take the customerID and invoice total from the report and populate the current record in the InvoiceRecord table. This last paragraph of info is where I am hung!

I hope this makes what I am looking for more clear.

[Access_Blaster]

Now, when a report is created, does it still exist in memory?

A report is a object like a form, if you are designing or displaying a report it exist in RAM and /or saved to the database collection.

Can I add data to the report?

You may need to refresh your report to reflect the changes.

Can I take data from that report and populate a table?

All the information displayed on a report exist in a table already so what’s the point of duplicating it? If you want to take data from one table and populate another table you can. If you want to reflect a change in your data find a better way.

[Robeen]

Hi Bubba55,

Your Access Report is a 'container' that displays data.

The data typically comes from a Table or a Query. You haven't mentioned what your report uses as a 'Data Source' [I think it has a table as its data source?].

If your report is based on a table - then you can use data from that table to put values on your report.

You can get values from more than one table into your report if you create a query that has all the data you need - and then make that query the data source for your report.

If you don't want to create a new report, you can change the Data Source of your report in the Properties Pane - Data Tab - Record Source - so that it is getting data from the query that has all your data instead of just from one table.

To update your InvoiceRecord table, instead of thinking of it in terms of the Report updating your InvoiceRecord table, you should think of it as using data that is in the Tables in your database to Update your InvoiceRecord table.

In the code that runs when your Print Report button is clicked, you can:
1. Print a report that has been designed to get its data from a Query that has the Invoice Number and the Date - as well as all the other data you need the printed report to have.

2. Have code that executes an Update Query [action query] and updates your InvoiceRecord table with whatever data you need.

I hope this helps.

[rpeare]

If I understand this, your database is performing exactly as you want, except that you want an existing report to print with an invoice number and date when you print it but not otherwise?

If that's the case, the very easiest thing is to create a second report (this will be your invoice report) add the two fields you want to print an invoice print this modified report instead of your original without the invoice number/date.

[bubba55]

Robeen, I am getting a little closer to what I want to happen. Just a point about the report, the data that I want to write to the Invoice table is a calculated field on the report. I think that I can get the Invoice Number to appear like you said in your response. If I run the same query in a function/sub and take that result, I think that I should be able to get the invoice total that I am looking for.

When I am finished, I want the invoice table to hold its' key, invoiceID, customerID, InvoiceTotal, InvoiceDate, and Paid (Y/N) Thanks for your help. I will let you know what I finally do to make it work! Thanks again!

[bubba55]

Well, I am still stuck.Let me divide and conquer this thing.
I select a button that generates an invoice. When I select this button I get a popup asking which customer. I enter a customerID. Next, I run a function to generate a new InvoiceNo and date and store it in the invoice table.

Here is the code:
Public Function InvoiceRecord()
'Get the current workspace
Dim CurWrk As Workspace
Set CurWrk = DBEngine.Workspaces(0)
'Create Variables InvNo(Invoice Number), InvDate (Invoice Date)
Dim InvNo As String
Dim InvDate As Date
Dim a As String
Dim b As String
Dim c As String
Dim d As String
Dim e As String
Dim MyDate
Dim dbsNSNursery_Operations As DAO.Database
Dim rstInvoiceRecord As DAO.Recordset
Set dbsNursery_Operations = CurrentDb
Set rstInvoiceRecord = dbsNursery_Operations.OpenRecordset("InvoiceRecord ")
'create invoice number
a = Date
b = Right(a, 4)
c = "00"
d = b + c
rstInvoiceRecord.AddNew
e = rstInvoiceRecord!InvoiceID
InvNo = d + e
rstInvoiceRecord!InvoiceNo = InvNo
rstInvoiceRecord!InvoiceDate = a
rstInvoiceRecord.Update
End Function

This works so now I have a new invoice number and invoice date.

I next run a macro that selecte the customerID, LastName, FirstName, from the customer table, PlantNo, PollenParent, Pod Parent, and CustID from the LabRecords table, I also select PlantNo, CustID, ActDate, ActType, ActCost from the LabActivity table. These are all related byt CustID and the Two Lab Tables by the PlantNo.
I create an invoice that groups by Customer, PlantNo, Lab Activity. Each lab activity has an associated cost, so it sums at the plantNo, and Total for the Customer.
My InvoiceNo is in a table InvoiceRecord. It has NO relationship to the CustomerID. I have added a text box on the InvoiceReport and need to get the last modified record for the InvoiceNO from the InvoiceRecord table to populate this text box. What is the best way to do this? Once we get this fixed, then I will worry about a couple of other items! THANKS!

[Robeen]

Two questions:

1. Do you want the most recent Invoice Number from your InvoiceRecord table to show up on your report?

2. Is each NEW Invoice Number the LARGEST number in your InvoiceRecord table?

If I understand your most recent post correctly, my post on 08-18-2011 at 11:27 AM may already contain the answers you are looking for.

In essence:
Base your report on a query that pulls in the most recent Invoice Number from your InvoiceRecord table.

If the new Invoice Number is the largest number in the InvoiceRecord table, you can locate it using the Max([InvoiceNumber]) syntax in the query. I have used this logic in the past and it works perfectly as long as each new invoice number is the largest number in the table.

Let me know if you have more questions.

[bubba55]

Robeen,
I have had some very good suggestions. One suggestion I had was to build a form for the user to enter the data I was looking for and then use that data to populate the table and report. OK, so I have a form that the user enters the customerID. So now I have a form with the customerID in it.

The form name is InvoiceDataSet. I have an unbound text box colled CustID on that form. In my code to generate the Invoice number:

a = Date
b = Right(a, 4)
c = "00"
d = b + c
rstInvoiceRecord.AddNew
e = rstInvoiceRecord!InvoiceID
InvNo = d + e
rstInvoiceRecord!InvoiceNo = InvNo
rstInvoiceRecord!InvoiceDate = a

***rstInvoiceRecord!InvCust = Form.InvoiceDataSet.CustID***

rstInvoiceRecord.Update

I added the line as shown above within the "***". This does not copy the data from the form. What do I need to do to make the data on that form (which is still active and on the screen) to copy the customerID to the Invoice table. If I can get both the invoice number (which is working) and the customerID both in the same record in the InvoiceRecord table, I can do what I want to do. What am I overlooking?

THANKS!!!!

[Robeen]

Hi Bubba,

Once you have all your data on your Form, you can have code that executes an 'Update' statement and puts your CustomerID into the InvoiceRecord Table for the Invoice Number currently on your Form.

This Microsoft page has a simple example that you might be able to use:
http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

In your case, you might use an update statement something like this to update your InvoiceRecord table:
dbs.Execute "UPDATE InvoiceRecord " _
& "SET customerID = CustomerID_from_Form_InvoiceDataSet " _
& "WHERE InvoiceNumber = InvoiceNumber_from_Form_InvoiceDataSet;"

Hope this helps.