Results 1 to 6 of 6
  1. #1
    SusanCoder is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    30

    change combo box properties for one record only on continuous form

    I'm working with a continuous form and two bound combo boxes: cboCallCategory and cboYouthType. When cboCallCategory <> "Youth", I want the .Locked property for cboYouthType to equal True (Locked) and the font color for the cboYouthType label to change to gray for the current record only.



    Currently the changes are happening correctly on the current record, but also executing on the existing records, even where the existing record's cboCallCategory = "Youth".

    Should I be adding code in the On Current event proc so the changes happen only on the current record? Your advice is much appreciated.

    Here is my code in the After Update event proc for cboCallCategory:

    Code:
    Private Sub cboCallCategory_AfterUpdate()    
            If Me.cboCallCategory = "Youth" Then
                Me.cboYouthType.Locked = False
                Me.lblYouthType.ForeColor = RGB(0, 0, 0) ' black
        Else
            If Me.cboCallCategory <> "Youth" Then
                Me.cboYouthType.Locked = True
                Me.lblYouthType.ForeColor = RGB(128, 128, 128) ' gray
            End If
        End If
    End Sub

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Programmatically changing control properties will be reflected in ALL instances of control regardless of event used.

    Use Conditional Formatting for textboxes and comboboxes.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    SusanCoder is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    30
    Thank you, June7. Does conditional formatting have the capability to lock a combo box so users can't select an option?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    It can Enable/Disable and Disable causes control to 'grey out' and user cannot interact with. Locking still allows user to tab to or click into control which can be frustrating for user because it is not apparent control is locked from use and they try to type into.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    SusanCoder is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    30
    Thanks again, June7, for pointing me in the right direction.

    Here's my solution to working on a continuous subform with two combo boxes, and changing the dropdown list and value of one combo box (cboYouthType) depending on the value of another combo box (cboCallCategory) for one record only! On my subform, the user selects the value from cboCallCategory, then cboYouthType if cboCallCategory = "Youth".

    I used conditional formatting to gray out cboYouthType when cboCallCategory is not equal to "Youth". In Design View with the cboYouthType combo box selected, I chose Conditional Formatting on the Format menu, and used this code with Expression Is: [cboCallCategory] <> "Youth". I chose a light gray to highlight the combo box's field when the condition was true.

    Next, I used this code in the After Update event proc for cboCallCategory to manipulate the value in cboYouthType when cboCallCategory was not equal to "Youth" or was null:

    Code:
    Private Sub cboCallCategory_AfterUpdate()
     
        If Me.cboCallCategory <> "Youth" Then
            Me.cboYouthType.Value = Null
        Else
            If IsNull(cboCallCategory) = True Then
                Me.cboYouthType.Value = Null
            End If
        End If
           
    End Sub
    This all worked well except for one big thing...when cboCallCategory is not equal to "Youth", cboYouthType is grayed out (this is good). But the user could still select a value in cboYouthType because it is only grayed-out rather than disabled, and the text is visible under the grayed-out field (not cool and not a good look). More importantly, when the record is saved to the table, YouthType should be null when CallCategory is anything but "Youth", but in this case it was saving a value for YouthType (really not cool).

    To fix this, I added the same code to the After Update event proc for cboYouthType to manipulate the value in cboYouthType when cboCallCategory was not equal to "Youth" or was null:

    Code:
    Private Sub cboCallCategory_AfterUpdate() If Me.cboCallCategory <> "Youth" Then Me.cboYouthType.Value = Null Else If IsNull(cboCallCategory) = True Then Me.cboYouthType.Value = Null End If End If End Sub
    Now when the user tries to select a value in the grayed-out cboYouthType they still can, but the After Update code for cboYouthType nulls out the value/text in the blink of an eye and all the user sees is the grayed-out field. And the value for cboYouthType is not saved to the table.

    In the end, mission accomplished.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I tested CF with combobox and when it is Disabled I am not able to select anything.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 10-12-2017, 08:24 AM
  2. Replies: 8
    Last Post: 11-28-2015, 12:00 PM
  3. Replies: 14
    Last Post: 01-15-2015, 02:41 PM
  4. Replies: 3
    Last Post: 09-23-2013, 06:42 AM
  5. Replies: 4
    Last Post: 09-10-2013, 07:34 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums