ms access dynamically change label on report

Question

I am printing a production ticket as a report using the following code:

Dim strCriteria As String
 strCriteria = "SELECT [PkgSize] & chr$(32) & [PkgUnit] AS Pkg, tblProducts.ProductID, tblProducts.ProductPrintName, tblProducts.Grade, " _
            & " tblCustomers.CompanyName, tblOrderDetails.ODEPriority, chr$(33) & chr$(70) & [tblProducts].[ProductID] & [tblCustomers].[ID] & chr$(33)as Expr1" _
            & " FROM tblCustomers INNER JOIN (tblOrders INNER JOIN (tblProducts INNER JOIN tblOrderDetails ON " _
            & " tblProducts.ProductID = tblOrderDetails.ODEProductFK) ON tblOrders.ORDOrderID = tblOrderDetails.ODEOrderID) ON " _
            & " tblCustomers.ID = tblOrders.ORDCustomerID " _
            & " WHERE (((tblProducts.ProductID)=[Forms]![frmInventoryTransfersManual]![cboTransferProductID]) " _
            & " AND ((tblOrderDetails.ODEPriority)= " & varPriority & ") AND (([tblOrderDetails]![ODEQtyOrdered]-[tblOrderDetails]![ODEQtyProduced])>0))"

 DoCmd.OpenReport "rptProductPaperLabelTCTRlogo", acViewPreview, , , , strCriteria

In the report I have:

Private Sub Report_Open(Cancel As Integer)
    Me.RecordSource = Me.OpenArgs
End Sub

The various text boxes on the report use the following as their Control Sources: Grade, Expr1, ProductPrintName, Pkg, and CompanyName. (Expr1 produces a barcode for scanning the ticket.)

It works perfectly. However, I also need to print a label or, could be, a text box to form a border on the report. This label/textbox will be a color assigned to CompanyName. Therefore, the ticket can be seen quickly and know who the customer is just by knowing the color of this label/textbox.

Can anyone help me to change the color of a label/text box on the report dependent on the company name. We have about 20 different customers.

nicomp nicomp · Accepted Answer · 2016-03-11T21:28:44

Add a color attribute to the table definition for the customer table. Add that color attribute to the recordsource of the form. Use that color to set the backcolor property of the control on the report in the appropriate eventhandler of the form, probably onChange?

ms access dynamically change label on report

1 Answers