create summary "table" at beginning of report

[EnginerdUNH]

I first need to apologize that I will need to break this post up into two consecutive posts because there's a lot of text. I am trying to create makeshift "table" of sorts at the beginning on one of my reports which I made using several textboxes that I want to populate with data from the following query:

Code: Select all

SELECT qryResultCombos.SupplierID, tblSuppliers.SupplierName, qryResultCombos.TestTypeID, tblTestType.TestType, qryResultCombos.ResultID, tblResult.Result, qryCountCombosR1.CountOfResult1ID, qryCountCombosR2.CountOfResult2ID, qryCountCombosR3.CountOfResult3ID
FROM (((((tblSuppliers INNER JOIN qryResultCombos ON tblSuppliers.SupplierID = qryResultCombos.SupplierID) INNER JOIN tblTestType ON qryResultCombos.TestTypeID = tblTestType.TestTypeID) INNER JOIN tblResult ON qryResultCombos.ResultID = tblResult.ResultID) INNER JOIN qryCountCombosR1 ON (tblSuppliers.SupplierID = qryCountCombosR1.SupplierID) AND (tblTestType.TestTypeID = qryCountCombosR1.TestTypeID) AND (tblResult.ResultID = qryCountCombosR1.ResultID)) INNER JOIN qryCountCombosR2 ON (tblSuppliers.SupplierID = qryCountCombosR2.SupplierID) AND (tblTestType.TestTypeID = qryCountCombosR2.TestTypeID) AND (tblResult.ResultID = qryCountCombosR2.ResultID)) INNER JOIN qryCountCombosR3 ON (tblResult.ResultID = qryCountCombosR3.ResultID) AND (tblTestType.TestTypeID = qryCountCombosR3.TestTypeID) AND (tblSuppliers.SupplierID = qryCountCombosR3.SupplierID)
ORDER BY qryResultCombos.SupplierID, qryResultCombos.TestTypeID, qryResultCombos.ResultID;

The table that I have on the report has labels for "Result 1", "Result 2", and "Result 3" on the top row with secondary labels for each tblResult.Result underneath and labels tblTestType.TestType for the rows in the first column. The textboxes are the inner cells of the table which I have tried to populate the values two different ways, both using the DLOOKUP function. My apologies for not being able to show the table on this post, I'm not very familiar with how to create tables in forum posts.

[EnginerdUNH]

The first method I tried to populate the textboxes was by setting the control source under the properties tab using the following function:

Code: Select all

=DLookUp( [qryCombineCounts]![CountOfResult1ID] ,"qryCombineCounts", [qryCombineCounts]![SupplierID] = [cmbSupplierName] , [qryCombineCounts]![TestTypeID] = 1, [qryCombineCounts]![ResultID] = 1)
or
=DLookUp( [qryCombineCounts]![CountOfResult1ID] ,[qryCombineCounts], [qryCombineCounts]![SupplierID] = [cmbSupplierName] , [qryCombineCounts]![TestTypeID] = 1, [qryCombineCounts]![ResultID] = 1)

of which I got a #Type error. The second method I tried was by setting the value via VBA on the On Load event using the following code:

Code: Select all

Private Sub Report_Load()
    'Result 1 summary
    Me.txtCountX11_1.Value = DLookup("[CountOfResult1ID]", "qryCombineCounts", "[SupplierID]= " & Me.cmbSupplierName.Value & " AND [TestTypeID] = 1 AND [ResultID] = 1")
    Me.txtCountX12_1.Value = DLookup("[CountOfResult1ID]", "qryCombineCounts", "[SupplierID]= " & Me.cmbSupplierName.Value & " AND [TestTypeID] = 1 AND [ResultID] = 2")
    Me.txtCountX13_1.Value = DLookup("[CountOfResult1ID]", "qryCombineCounts", "[SupplierID]= " & Me.cmbSupplierName.Value & " AND [TestTypeID] = 1 AND [ResultID] = 3")
    Me.txtCountX21_1.Value = DLookup("[CountOfResult1ID]", "qryCombineCounts", "[SupplierID]= " & Me.cmbSupplierName.Value & " AND [TestTypeID] = 2 AND [ResultID] = 1")
    Me.txtCountX22_1.Value = DLookup("[CountOfResult1ID]", "qryCombineCounts", "[SupplierID]= " & Me.cmbSupplierName.Value & " AND [TestTypeID] = 2 AND [ResultID] = 2")
    Me.txtCountX23_1.Value = DLookup("[CountOfResult1ID]", "qryCombineCounts", "[SupplierID]= " & Me.cmbSupplierName.Value & " AND [TestTypeID] = 2 AND [ResultID] = 3")
    Me.txtCountX31_1.Value = DLookup("[CountOfResult1ID]", "qryCombineCounts", "[SupplierID]= " & Me.cmbSupplierName.Value & " AND [TestTypeID] = 3 AND [ResultID] = 1")
    Me.txtCountX32_1.Value = DLookup("[CountOfResult1ID]", "qryCombineCounts", "[SupplierID]= " & Me.cmbSupplierName.Value & " AND [TestTypeID] = 3 AND [ResultID] = 2")
    Me.txtCountX33_1.Value = DLookup("[CountOfResult1ID]", "qryCombineCounts", "[SupplierID]= " & Me.cmbSupplierName.Value & " AND [TestTypeID] = 3 AND [ResultID] = 3")

    'Result 2 summary
    Me.txtCountX11_2.Value = DLookup("[CountOfResult2ID]", "qryCombineCounts", "[SupplierID]= " & Me.cmbSupplierName.Value & " AND [TestTypeID] = 1 AND [ResultID] = 1")
    Me.txtCountx12_2.Value = DLookup("[CountOfResult2ID]", "qryCombineCounts", "[SupplierID]= " & Me.cmbSupplierName.Value & " AND [TestTypeID] = 1 AND [ResultID] = 2")
    Me.txtCountx13_2.Value = DLookup("[CountOfResult2ID]", "qryCombineCounts", "[SupplierID]= " & Me.cmbSupplierName.Value & " AND [TestTypeID] = 1 AND [ResultID] = 3")
    Me.txtCountx21_2.Value = DLookup("[CountOfResult2ID]", "qryCombineCounts", "[SupplierID]= " & Me.cmbSupplierName.Value & " AND [TestTypeID] = 2 AND [ResultID] = 1")
    Me.txtCountx22_2.Value = DLookup("[CountOfResult2ID]", "qryCombineCounts", "[SupplierID]= " & Me.cmbSupplierName.Value & " AND [TestTypeID] = 2 AND [ResultID] = 2")
    Me.txtCountx23_2.Value = DLookup("[CountOfResult2ID]", "qryCombineCounts", "[SupplierID]= " & Me.cmbSupplierName.Value & " AND [TestTypeID] = 2 AND [ResultID] = 3")
    Me.txtCountx31_2.Value = DLookup("[CountOfResult2ID]", "qryCombineCounts", "[SupplierID]= " & Me.cmbSupplierName.Value & " AND [TestTypeID] = 3 AND [ResultID] = 1")
    Me.txtCountx32_2.Value = DLookup("[CountOfResult2ID]", "qryCombineCounts", "[SupplierID]= " & Me.cmbSupplierName.Value & " AND [TestTypeID] = 3 AND [ResultID] = 2")
    Me.txtCountx33_2.Value = DLookup("[CountOfResult2ID]", "qryCombineCounts", "[SupplierID]= " & Me.cmbSupplierName.Value & " AND [TestTypeID] = 3 AND [ResultID] = 3")

    'Result 3 summary
    Me.txtCountx11_3.Value = DLookup("[CountOfResult3ID]", "qryCombineCounts", "[SupplierID]= " & Me.cmbSupplierName.Value & " AND [TestTypeID] = 1 AND [ResultID] = 1")
    Me.txtCountx12_3.Value = DLookup("[CountOfResult3ID]", "qryCombineCounts", "[SupplierID]= " & Me.cmbSupplierName.Value & " AND [TestTypeID] = 1 AND [ResultID] = 2")
    Me.txtCountx13_3.Value = DLookup("[CountOfResult3ID]", "qryCombineCounts", "[SupplierID]= " & Me.cmbSupplierName.Value & " AND [TestTypeID] = 1 AND [ResultID] = 3")
    Me.txtCountx21_3.Value = DLookup("[CountOfResult3ID]", "qryCombineCounts", "[SupplierID]= " & Me.cmbSupplierName.Value & " AND [TestTypeID] = 2 AND [ResultID] = 1")
    Me.txtCountx22_3.Value = DLookup("[CountOfResult3ID]", "qryCombineCounts", "[SupplierID]= " & Me.cmbSupplierName.Value & " AND [TestTypeID] = 2 AND [ResultID] = 2")
    Me.txtCountx23_3.Value = DLookup("[CountOfResult3ID]", "qryCombineCounts", "[SupplierID]= " & Me.cmbSupplierName.Value & " AND [TestTypeID] = 2 AND [ResultID] = 3")
    Me.txtCountx31_3.Value = DLookup("[CountOfResult3ID]", "qryCombineCounts", "[SupplierID]= " & Me.cmbSupplierName.Value & " AND [TestTypeID] = 3 AND [ResultID] = 1")
    Me.txtCountx32_3.Value = DLookup("[CountOfResult3ID]", "qryCombineCounts", "[SupplierID]= " & Me.cmbSupplierName.Value & " AND [TestTypeID] = 3 AND [ResultID] = 2")
    Me.txtCountx33_3.Value = DLookup("[CountOfResult3ID]", "qryCombineCounts", "[SupplierID]= " & Me.cmbSupplierName.Value & " AND [TestTypeID] = 3 AND [ResultID] = 3")
End Sub

The above code works for me on the first instance of Me.cmbSupplierName.Value (the SupplierName field on the report is a combobox with the bound column being SupplierID). Once the report goes to the next section (I have a section breaks on the supplier field), the table displays with the same information as in the last section which tells me that either the above code is not in the right event for the report or that it does not change the value of Me.cmbSupplierName.Value in the code after each new section. Am I am going about this all wrong?

[HansV]

The code that you use in Report_Load shows how you should have used DLookup in the first method: each of the three arguments of DLookup must be a string.

But using Report_Load is not the right event to use - it is executed just once when the form is loaded. Instead, use the On Format event of the report section that contains combo box.

[EnginerdUNH]

thank you Hans for your reply, I also did a little more digging myself and was able to correctly format the control source as follows so that I no longer need to use the code:
=DLookUp(" [qryCombineCounts]![CountOfResult1ID] ","qryCombineCounts"," [qryCombineCounts]![SupplierID] =" & [cmbSupplierName] & " AND [qryCombineCounts]![TestTypeID] = 1 AND [qryCombineCounts]![ResultID] = 1")