query then print separate reports from one record

[brew]

Hi,



In a Nutshell, I have two tables one with Orders and one with Product Details. The Orders table has multiple Product ID's (ProductID1, ProductID2, etc..) fields which accommodates more than one item on an order.

I have a form (Orders table is the record source) where order details are filled in as well as a number of combo boxes which lookup Product ID's in the Product details table. There are print buttons next to each combo box, as i will need to print paperwork for each product

Where I run into problems is the query, it works if i put values in manually but i would like to automate it. I have a query which looks like this:

SELECT Orders.ID, Orders.Company, Products.Description 
FROM Orders, Products
WHERE Products.ProductID = Orders.ProductID1
AND Orders.ID = 1

Firstly I would like the the "Orders.ProductID1" to be update depending on which print button you press e.g. you press the print button next to the third combo and the query criteria changes to Orders.ProductID3 box.

Then I would like the "1" (Orders.ID = 1), to change to the current record of the form.

The VBA behind the Print button currently looks like this, I'm not sure how I would go about automating the changes above, or whether the print button is the best place to do it?

Private Sub cmdPrint_Click()

   Dim strWhere As String

    If Me.Dirty Then    'Save any edits.
        Me.Dirty = False
    End If

    If Me.NewRecord Then 'Check there is a record to print
        MsgBox "Select a record to print"
    Else
        strWhere = "[ID] = " & Me.[ID]
        DoCmd.OpenReport "Title Page", acViewNormal, , strWhere
        DoCmd.OpenReport "Checklist", acViewNormal, , strWhere
        DoCmd.OpenReport "Information Index", acViewNormal, , strWhere
        DoCmd.OpenReport "Sterilisation Sheet", acViewNormal, , strWhere
        DoCmd.OpenReport "Label Request", acViewNormal, , strWhere
        DoCmd.OpenReport "Label Check", acViewNormal, , strWhere
    End If

End Sub

I'm not sure if i'm going about all this in a best way, so if there are any easier/slicker ways to do this please enlighten me.

Thanks in advance for any help.

[jzwp11]

In a Nutshell, I have two tables one with Orders and one with Product Details. The Orders table has multiple Product ID's (ProductID1, ProductID2, etc..) fields which accommodates more than one item on an order.

You have a structural issue that needs to be corrected before you go any further. The problem lies in having multiple productID fields in your orders table.

If an order can have many products then you have a one-to-many relationship. Similarly a product can appear on many orders--another one-to-many relationship. When you have 2 one-to-many relationships between the same to entities, you need a junction table. A structure similar to this is what you need. Each product ordered would be a record in the junction table not a field in the orders table.


tblOrders
-pkOrderID primary key, autonumber
-dteOrder (order date)
-fkCustomerID foreign key to customer table (long integer number datatype)

tblProductDetails
-pkProdDetailID primary key, autonumber
-txtProductNumber
-txtProductDescription

tblOrderDetails (the junction table)
-pkOrderDetailID primary key, autonumber
-fkOrderID foreign key to tblOrders (long integer number datatype)
-fkProdDetailID foreign key to tblProductDetails (long integer number datatype)
-lngQty (quantity ordered; lng denotes long integer number datatype)

[brew]

That sounds sensible, I have created the junction table.

I'm struggling with integrating the new table into the form, will I need a sub form. How do i go about populating multiple records in the junction table in the sub form from one record in the main form?

[jzwp11]

will I need a sub form

Yes, you will need a subform in your order form. The subform will need to be based on the junction table. I typically show the subform in datasheet view. You would populate the productdetailID in the subform by using a combo box based on your product detail table.

If you have your relationships properly established ahead of time, Access will automatically link the subform to the main form when you add the subform to the main form. To do the linking manually, open the main form with the subform in design view. Then you would left click the frame of the subform and open the property sheet-->Data-->Link Master fields/Link child fields.

[brew]

Thanks that's really helped. Although one of my queries has stopped working.

The query is below.

SELECT Count(Junction.fkOrderID) AS CountOffkOrderID
FROM Junction, Orders
WHERE (((Orders.CompletionDate)>DateAdd("m",-1,Now())));

My intention was to count the total number of products completed in the last month (30 day), but this query seem to just count everything in the junction table.

Any ideas?

[jzwp11]

The FROM clause in your query is incorrect. Showing both tables separated by a comma will yield a Cartesian Product of the two tables. Your FROM clause must properly join the two tables via the primary key--foreign key relationship.

If you have the relationships set up in the relationship window, Access will automatically create the correct relationship when you build the query.

Can you provide the names of the field in the junction table and how they relate to the orders and products tables?

I would probably break this into 2 queries with the first query doing the filtering and the second query doing the counting.

[brew]

Thanks jzwp11

Relationships:

Orders.OrderID -->One to many --> Junction.fkOrderID

Products.ProductID -->One to many --> Junction.fkProductID

Tables:

-Junction
--ID (pk)
--fkOrderID
--fkProductID

-Orders
--OrderID
--CompletionDate

-Products
--ProductID
--ItemNo
--Description

[brew]

I think I may of found a FROM statement which seems to work:

FROM Products INNER JOIN 
(Orders INNER JOIN Junction ON Orders.OrderID = Junction.fkOrderID)
 ON Products.ProductID = Junction.fkProductID

so the whole query reads

SELECT Count(Junction.fkOrderID) AS CountOffkOrderID
FROM Products INNER JOIN 
             (Orders INNER JOIN Junction ON Orders.OrderID =Junction.fkOrderID)  
             ON Products.ProductID = Junction.fkProductID
WHERE (((Orders.CompletionDate)>DateAdd("m",-1,Now())));

[jzwp11]

By the way, I'm not sure if your criteria for the previous month would work in January in order to get the December values of the previous year, I think this should work for you:

The first query:

query name: qryMonthDetail
SELECT Junction.fkOrderID
FROM Orders INNER JOIN Junction ON Orders.OrderID = Junction.fkOrderID
WHERE Orders.CompletionDate Between dateserial(year(dateadd("m",-1,date())),month(dateadd("m",-1,date())),1) and dateadd("d",-1,dateserial(year(date()),month(date()),1))



The second query:

SELECT Count(qryMonthDetail.fkOrderID) AS CountOffkOrderID
FROM qryMonthDetail;

[brew]

Thanks for all your help, I've gone with two queries like you suggest.

[jzwp11]

You're welcome