I would like to import the contents of some text boxes on a form into a report that is called from a command button on the form
On the form i calculate things like the total materials cost, total labor cost, Sales tax, discount percent given on this order etc
I then call/open an invoice report which via a query has the customers, name, address etc
I'm having a mental blank on how to import some of the calculated fields on my form into the invoice.
Thanks in advance.
On my form I specify if the client gets trade pricing as distinct from Retail pricing on materials & labour, also if Sales tax is applicable or not for this job.
I found that running two subreports on the invoice report did not always give the right answer. I tried using TempVars but again was not getting consistent results. By using the solution proposed by DHookum the problem has been solved.
Thanks to every one who has made suggestions. As previously mentioned the problem is now solved thank you
If you are happy with that solution, who am I to argue. The limitation is that you MUST have the form open to the correct record when you run the report. This seems like it could easily be a point of failure.
On my form I specify if the client gets trade pricing as distinct from Retail pricing on materials & labour, also if Sales tax is applicable or not for this job.
Pat Hartman makes a valid point with regard to the need for the form to be open at the correct record when the report is opened. This is fine if, in the application, the only way to generate the invoice is from the form, and only one invoice need be generated at any one time. That's rather limiting, however, and would prevent the sort of functionality, like that which I have in an invoicing application, to email multiple invoices to customers.
The following is the RecordSource for the parent report, which also includes subreports to return the subtotals and totals.
SQL:
SELECT CanShrinkLines([Invoices].[Customer],[Invoices].[AddressLine1],
[Invoices].[AddressLine2],[City],[CountyOrRegion],[Invoices].
[PostCode],[Country]) AS CustomerAddress,
Invoices.InvoiceNumber, Invoices.InvoiceDate, Products.Product,
InvoiceDetails.Quantity, InvoiceDetails.UnitPrice,
InvoiceDetails.TaxRate, [InvoiceDetails].[UnitPrice]*[Quantity] AS Amount
FROM Products
INNER JOIN ((Customers
INNER JOIN (Countries
INNER JOIN (CountiesOrRegions
INNER JOIN (Cities
INNER JOIN Invoices
ON Cities.CityID = Invoices.CityID)
ON CountiesOrRegions.CountyOrRegionID = Cities.CountyOrRegionID)
ON Countries.CountryID = CountiesOrRegions.CountryID)
ON Customers.CustomerID = Invoices.CustomerID)
INNER JOIN InvoiceDetails
ON Invoices.InvoiceNumber = InvoiceDetails.InvoiceNumber)
ON Products.ProductID = InvoiceDetails.ProductID
WHERE InParam([Invoices].[InvoiceNumber],[Forms]![frmEmailDlg]![txtInvoiceList]) = TRUE;
As you can see the only reference to a control in the calling form as a parameter is in the WHERE clause as the argument for the InParam function. This function, which was published by Microsoft many years ago, references a hidden control in the form containing a value list of invoice numbers to simulate the use of the IN operator with a parameter. The IN operator itself does not accept a parameter as its argument. The form in question is a dialogue form containing a multi-select list box in which one or more invoices can be selected.
All of the computations necessary are in the query, and the queries used by the subreports as their RecordSource properties, so these are completely independent of the calling form, which merely determines which invoices are generated. Consequently as many invoices as necessary can be emailed simultaneously to the customers.
PS: You might be wondering why the customer's name and address data are stored in the Invoices table, rather than in the Customers table. That is because, in this application, a decision was made that the customer's name and address data current at the time the invoice was raised would be retained with each invoice record, regardless of any subsequent changes which might be made to those data. Consequently each customer's current name and address data are stored in columns in the Customers table, and are assigned to the columns in the Invoices table when an invoice is raised.
