Results 1 to 3 of 3
  1. #1
    Join Date
    May 2009

    Unanswered: Change form-based report to table-based report

    Hello all,
    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.

    Attached Files Attached Files

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    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:

        DoCmd.OpenReport stDocName, acPreview, , "Orders.OrderID = " & Me.OrderID.Value
    Have a nice day!

  3. #3
    Join Date
    May 2009
    Thanks Sinndho, I will give it a shot.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts