Unanswered: Change form-based report to table-based report
I have a very challenging problem I hope someone can help with.
I am going to convert our current non-standard database over to a standard one using the Order Management Database from the Microsoft site. I have attached the database with only 2 changes which I need to work out before I can proceed with the conversion.
The first change is I added a button under tasks to open the OrderDetails1 report. The second change is I included a copy of the OrderDetails1 report which is a static document to mimic our current database report and this is the sticking point for the conversion.
Basically, we have a report, similiar to the OrderDetails1 report provided here, which has been manually drawn and saved in our database with only certain information being dynamic. Those dynamic fields are tied to a control source on the 'Add an Order and Details' form (I know this is not the correct way but that is why I am trying to convert the database). This allows us to print a report of the items selected on the form.
I need to be able to use the same static report but retrieve the data from a table instead of the form. Is this possible? Is there a way to change our static report into a dynamic report created on the fly like what is normally done? Any help is greatly appreciated.
1) Create a query joining both Orders and Order Details tables:
SELECT Orders.*, [Order Details].*
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID=[Order Details].OrderID;
(you probably don't need all the columns from both tables, but as I feel lazy tonight I've done it the quick and dirty way). Record this new query (name: OrdersToOrderDetails or whatever you want).
2) Change the RecordSource property of the report to OrdersToOrderDetails.
3) Change the ControlSource of these textboxes on the report: Unit ControlSource becomes CustomerID Text31 ControlSource becomes Orders.OrderID Text32 ControlSource becomes OrderDate Field3 ControlSource becomes EmployeeID
While you're at modifying the report, you should consider renaming the controls to something more meaningful (none of my business: it's just a suggestion).
If you want to open the report on a specific record from the Add an Order and Details form, you can pass a Where condition parameter when opening it: