Out of Conditional Format Options / Refer to subform of subform (1 Viewer)

Insane_ai · Nov 7, 2018

This is more of a note to self / posted to assist other that may have the same problem.

Setup:
I have a form named "Support_Master".
"Support_Master" has sub-form "subOrderSub" based on the form "Support_OrderSub".
"Support_OrderSub" has a sub-form "subItemDetails" based on the form named "Support_ItemDetails"

Support_ItemDetails displays each line item in an order with a color code indicating which warehouse will supply the item for the order.

When there were only four warehouses, we could use the three conditional formatting options for 2,3,4 while warehouse 1 had the default formatting. Now that a fifth warehouse has been added, a new color code was required which exceeded the limitations of the built in Conditional Format features.

The answer to this was to change the background color of the textboxes in the detail section of the form on the fly but I had to figure out how to reference them.

I learned that referring to the control name in context of its parent is what works.

Examples:
'This FAILS:

Code:

Forms!SupportMaster!Support_OrderSub.Form!Support_ItemDetails

This Works:

Code:

Forms!Support_Master!subOrderSub.Form!subItemDetails

Full working usage example:

Code:

        For Each Ctl In Forms!Support_Master!subOrderSub.Form!subItemDetails.Form
            If Ctl.ControlType = acTextBox Or Ctl.ControlType = acComboBox Then
                Ctl.BackColor = bgColor
            End If
        Next

isladogs · Nov 7, 2018

Hi
Are you aware you can have up to 50 different CF if you use the wizard?

Or for a different approach with no limits, consider the colour categories method that I use for e.g. calendar events. See https://www.access-programmers.co.uk/forums/showthread.php?t=302041

Insane_ai · Nov 7, 2018

Thanks for the feedback. In this case, I'm still working in AC2000.

banghead: :banghead: :banghead:

:banghead: :banghead: :banghead:

)

I'll look into your example database and possibly give you "the greatest compliment" by stealing your code (again).

Begin LOL:
AH. The wizard is indeed limited to 3 cases in A2000
There is also a trick for getting more than 3 CF cases via code.
I'll need to check that and will hopefully then come back later

Feel free to steal the other idea. It will work in any version

End LOL

isladogs · Nov 7, 2018

OOPS!!!!!! Sorry about that
The perils of answering on a tablet!

Insane_ai · Nov 7, 2018

I came back to let you know that I see how I can leverage your method when I upgrade to a newer version but saw that you edited my comment rather than reply and had some fun with it.

Right now, we use global constants declared in VBA (Inherited). Your method will allow for faster and easier expansion of the color coding and I could adapt it for use in multiple forms with a single function. Thank you for that example.

isladogs · Nov 7, 2018

Found the 'trick' to overcoming the limit of 3 conditional formats using VBA
See https://access-programmers.co.uk/forums/showthread.php?t=271679

I haven't tested it & I've no idea whether it will work in A2000
Currently trying out another method based on my colour categories idea

Insane_ai · Nov 7, 2018

On a related note, while implementing this further in other subforms I discovered that it doesn't work if the subform has a form or page header / footer turned on.

Additionally, other CustomFormatting settings in the same collection (Controls on the form) will also over-ride this manual setting which at least reveals an order of operation I was not aware of.

isladogs · Nov 8, 2018

Just to explain what I meant earlier, see attached for a very simple way of getting as many colour conditions as you want. I've used 10 conditions

Two versions attached - ACCDB and a MDB in 2000 format for you

The colours are based on a lookup table and can be modified according to preference. There's only a couple of lines of code needed

In a working version I would add a colour selector to simplify setting the colour keys

isladogs · Nov 8, 2018

Here's an updated version which includes the colour selector code
Both ACCDB & MDB (2000) formats are included in the zip file

Insane_ai · Nov 13, 2018

My original solution fails spectacularly with forms in Datasheet view so I had to try the alternate solution (Source cited in code).

I've tried to get it to work without success however I was able to modify it to work in Access 2000 and provide a visual cue to the user when the limitations had been exceeded.

Per the source example, I manually set up conditions [1]=[2], [2]=[3], [3]=[4] as the placeholder expressions. Unfortunately, it appears that starting at FormatCondition(3) as suggested is not working for me to enable more than three.

This method is fully functional for me and has been adapted to two other forms to manage similar functionality.

Code:

Private Sub Form_Current()
' This method is adapted from https://access-programmers.co.uk/forums/showthread.php?t=271679
' The original post suggests using manual unchanging or un-matchable conditions.  This method failed with Error 7966
' This adaptation looks for the number of possible conditions matched by dropshipperID (Which warehouse fills the order)
' Then selects the top 3 in case there are more distribution points to consider (extremely rare)
' The three conditions are then applied to the subform.

On Error GoTo ErrHandler

Dim DB As DAO.Database
Dim QDF As DAO.QueryDef
Dim RS As DAO.Recordset
Dim strSQL As String
Dim intDSCount As Integer
Dim strSQL2 As String
Dim CTL As Control
Dim I As Integer

Dim IntOrder As Long

    IntOrder = DLookup("OrderID", "Order_Details", "Order_Details.OrderDetailID = " & Me.OrderDetailID)
    Set DB = CurrentDb
    Set QDF = DB.QueryDefs("DBTEMP_FILTER_QUERY")
    strSQL = "SELECT Titles.DropshipperID, Order_Details.OrderID FROM Order_Details INNER JOIN Titles ON Order_Details.TitleID = Titles.TitleID GROUP BY Titles.DropshipperID, Order_Details.OrderID" _
            & " HAVING (((Order_Details.OrderID) = " & IntOrder & ")) ORDER BY Count(Titles.DropshipperID) DESC;"
    
    
    QDF.sql = strSQL
    'selecting the top 3 while eliminating DS = 0 (Home Office) allows for the most formatting
    strSQL2 = "Select TOP 3 DropshipperID from DBTEMP_FILTER_QUERY Where DBTEMP_FILTER_QUERY.DropshipperID <> 0"
    intDSCount = DCount("DropshipperID", "DBTEMP_FILTER_QUERY", "DBTEMP_FILTER_QUERY.DropshipperID <>0")
    If intDSCount > 3 Then
       Me.DatasheetGridlinesColor = vbRed       'Indicate that there are others to deal watch for
    Else
       Me.DatasheetGridlinesColor = 12632256
    End If
    
    Set RS = DB.OpenRecordset(strSQL2)
    I = 0
    With RS
        Do Until .EOF
            If !DropshipperID = 2 Then
                For Each CTL In Me.Controls
                    If CTL.ControlType = acTextBox Then
                        With CTL.FormatConditions
                            .Item(I).Modify acExpression, acEqual, "[DropshipperID]=2"
                            .Item(I).BackColor = gcDS2
                            .Item(I).FontBold = False
                            .Item(I).ForeColor = vbBlack
                            .Item(I).Enabled = True
                        End With
                    End If
                Next CTL
                I = I + 1
            End If
            
            If !DropshipperID = 7 Then
                For Each CTL In Me.Controls
                    If CTL.ControlType = acTextBox Then
                        With CTL.FormatConditions
                            .Item(I).Modify acExpression, acEqual, "[DropshipperID]=7"
                            .Item(I).BackColor = gcDS7
                            .Item(I).FontBold = False
                            .Item(I).ForeColor = vbBlack
                            .Item(I).Enabled = True
                        End With
                    End If
                Next CTL
                I = I + 1
            End If
            
            If !DropshipperID = 9 Then
                For Each CTL In Me.Controls
                    If CTL.ControlType = acTextBox Then
                        With CTL.FormatConditions
                            .Item(I).Modify acExpression, acEqual, "[DropshipperID]=9"
                            .Item(I).BackColor = gcDS9
                            .Item(I).FontBold = False
                            .Item(I).ForeColor = vbBlack
                            .Item(I).Enabled = True
                        End With
                    End If
                Next CTL
                I = I + 1
            End If
            
            If !DropshipperID = 10 Then
                For Each CTL In Me.Controls
                    If CTL.ControlType = acTextBox Then
                        With CTL.FormatConditions
                            .Item(I).Modify acExpression, acEqual, "[DropshipperID]=10"
                            .Item(I).BackColor = gcDS10
                            .Item(I).FontBold = False
                            .Item(I).ForeColor = vbBlack
                            .Item(I).Enabled = True
                        End With
                    End If
                Next CTL
                I = I + 1
            End If
            .MoveNext
        Loop
    End With
    Set RS = Nothing
       
       


Set QDF = Nothing
Set DB = Nothing


ErrHandler:
    Select Case Err.Number
        Case 0
            Resume Next
        Case 20
            Resume Next
        Case 7966
            Resume Next
        Case 2465
            Resume Next
        Case Else
            MsgBox "Error: " & Err.Number & vbNewLine & Err.Description
    End Select

End Sub

Out of Conditional Format Options / Refer to subform of subform (1 Viewer)

Insane_ai

Not Really an A.I.

isladogs

MVP / VIP

Insane_ai

Not Really an A.I.

isladogs

MVP / VIP

Insane_ai

Not Really an A.I.

isladogs

MVP / VIP

Insane_ai

Not Really an A.I.

isladogs

MVP / VIP

Attachments

isladogs

MVP / VIP

Attachments

Insane_ai

Not Really an A.I.

Similar threads

Users who are viewing this thread