Access 2003 Report detail

0
votes

I have two tables that I am creating a report from. One is Orders, and the key is called OrderID. I have another table called Parts, also containing OrderID. They have a relationship as well. On my Order forms, I have a listbox and populate that based on the two OrderID's.

Now, I have a report with the data source as Orders. I also added a listbox and set the Row Source/Type to Table/Query pointing to my Parts table. However, when I set the criteria of the two OrderID's to match to display on the corresponding Parts per the Order, it only picks up the Order in the first detail, and repeats it for each successive detail. How can I get the listbox to only display where the two OrderID's match> Code used Report results

2
vbams-accessreport

2 Answers

0
votes

Consider using a subreport. Here are steps:

  1. Create a smaller Parts report separately which contains all your listed fields (PartNumber, PartDescription, PartQty, etc.) bound only to the Parts table. Be sure to not put any controls in Page Header/Footer of this subform (as they will not show on main report). Put field headers in this subreport's Report Header section.
  2. Then, on your main Orders report stretch out the detail section and place the Parts subreport just below the records in the same manner as your screenshot. Subforms are the newspaper icon in control section close to the very end which automates a walk-through Wizard -might be slightly different in AC2003 .mdb files.
  3. In the wizard, select the Parts subreport created above and confirm the matched OrderID between parent form and child subform. If relationships are set up matched keys should be auto-linked waiting for user confirmation.

Doing this your original main report will now have correctly aligned Orders and Parts for each distinct OrderID of Order table.

0
votes

Gonna need some VBA to do that, or base it off a query. The key here is that you need to INNER JOIN the tables to each other, on the OrderID. Also, adding the DISTINCT qualifier will only return unique values, thereby removing dupes. So, something like this (which will need to be changed based on your actual table names and field names):

SELECT DISTINCT Orders.OrderID
FROM tblOrders Orders
INNER JOIN tblParts Parts
ON tblOrders.OrderID = tblParts.OrderID

Then, if you're looking for a specific order, you can filter that by adding a WHERE clause.