Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262

    Conditional Formatting on a combobox before a value is chosen

    I have a combobox that populates the rowsource from a table based on user input into another combobox.
    The combobox pretty much always evaluates to 1 value for the purpose below, but has many uses so I cannot use a list box or regular text box.

    I would like the combobox to highlight if there are values (not Nulls or blanks) in the combobox's rowsource result post-user input, before a value is chosen from the list.


    The idea behind this is to let the user know that a value is available to be chosen in this combobox, but the user has the freedom to not chose it from the list.


    The problem is that when the combobox's rowsource is loaded, EVEN if there is no value in the list (in other words the rowsource evaluation based on the user input returned no data), the list count evaluates to 1. So, I cannot use the listcount property for this problem.

    So, is there a way to evaluate the values in the combobox list rather than the value in the combobox itself?


    Is there a way to implement the built-in "NotInList" function to evaluate the combobox list for anything that is not blank?

    Thank you

  2. #2
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    I don't really follow your post; but will say that the formatting properties of the combo box control can be managed/altered at any suitable event - not necessarily just an event of the combo box control itself. So that if you can identify an event at any point of the form's user experience and can establish a unique logical value via a query or the value of any field in a table - then you should be able to accomplish what you seek.

  3. #3
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Not sure if I got what it all but you could skip using the Conditional Formatting feature and do it manually. Maybe in the AfterUpdate event for the combo box, run the same criteria you are using to populate the combo box and test for values. You can do a recordcount and if > 1 it has values. Even if the count is 1 you could test if for nulls. Then if it has values add code to color backcolor of combobox, .combobox1.backcolor = "blue" or whatever. Now this will not work if these fields are on a continuous form as they would all turn blue.

  4. #4
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    As far as I can tell, a recordcount for a combobox is actually a listcount, which I have already tried. Also, a blank record in the combobox list still evaluates listcount to 1.

    How do I test the list for nulls? As far as my testing went, checking for nulls only evaluates when there is a value in the combobox itself, not the combobox list.

    I do not believe this is on a continuous form.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Is there a spot (form load event) that you could test the row source sql for a record count? If > 0 then change the back color of the combo. The record count will be zero if the sql produces no records.

  6. #6
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    It would be based on an afterupdate event on a combobox.

    How would i test the rowsource sql for record count? What does the code look like?

  7. #7
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    It would be based on an afterupdate event on a combobox.
    This cannot be based on any combo box event if you want to know if there are any items in the list. As I mentioned, this has to be figured out when the form opens or just after. The form open event occurs before the load event, but since this test does not rely on testing any form data (which isn't loaded in the Open event) it can go into the Open event. The following would go in between your form open event Sub / End Sub statements. Replace txtMyControl with the name of your textbox to be colored. You need a reference to the DAO object model. If you don't know how to do that, please Google "ms access set references" or something like that. As long as you use the query or sql statement here that is being used for the combobox, it will tell you if there is anything in the list, or how many are in the list, depending on which version you use. I have provided both. NOTE: this is 'air code' and is not guaranteed to work. I do overlook small details from time to time. Post back with comments/issues.

    Code:
    Dim db as DAO.Database
    Dim rs as DAO.Recordset
    Dim strSql as String 'use this especially if the recordset is to be base on a sql statement
    
    '***
    'do one or the other of these, not both
    Set rs = db.OpenRecordset ("name of your query") 
    Set rs = db.OpenRecordset (strSql)
    '***
    'If you only care if there is at least one record, then this will do
    If rs.RecordCount >0 Then Me.txtMyControl.backcolor = vbBlue
    'If you want to know how many records were returned, then this before the counting line above
    'rs.MoveLast
    'rs.MoveFirst 'if you want to go back to the beginning and perform a loop on the records (which you don't)
    
    'close recordset and reclaim memory before exiting procedure
    rs.Close
    Set rs = Nothing
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    This cannot be based on any combo box event if you want to know if there are any items in the list
    Do you mean that this cannot be based on any combo box event that which I am trying to see if any items are returned in the same combo box? Because that makes perfect sense. Seeing if there are items in the combo box list before I have done anything to change this list would be useless to me.

    Just to clarify, I put some data into a FIRST combobox, and a SECOND combobox (which will search for records based on what I put in my FIRST combo box) is the combo box I want to highlight if non-blank/null records are returned.

    I guess a better way to put it is that the SECOND combo box behaves like a cascading combobox, except its list is always 1 record or a blank record, never more than that for this scenario. So, if I do this test on the form opening or just after, it will always highlight the SECOND combobox, because it will default to another list of records found in another table unless I change the input in my FIRST combobox. Am I interpreting the "form open" event correctly?

    Does this allow me to do a record count on the SECOND combobox after the FIRST combobox is updated? Or still no?

  9. #9
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I re-read your post just now, and can still see how one could conclude it was the first combo you wanted to know the count on. That's how I took it - sorry.
    What I wrote should perform the task regardless of which of the events mentioned are used - the premise is that whatever the row source for the combo is, it can be counted (as long as it's not a Value List). Where it goes does matter in order to suit your purpose. For a combo serving as a sub member of a cascading group, the event would be the after update of the parent control. So the event would be (as you correctly stated) the after update event of the parent. Hope it works for you.

  10. #10
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    I apologize for the confusion.

    I tried the code and it highlighted the combobox fields when there is a blank record in the list. I guess that means a blank record is still considered a record?

    Can I tweak this to highlight as long as the list is not null/blank?

  11. #11
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    post your code

  12. #12
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    Code:
    Private Sub cboRev_AfterUpdate()
    
    
    Me.txtComponent = Nz(DLookup("Primary", "DRAWINGS", "Drawing='" & Me.cboComponent & "' AND Revision ='" & Me.cboRev & "'"), "")
    Me.txtDescription = DLookup("Description", "PDatabase", "[PartNumber] = '" & Me.txtComponent & "'")
    Me.txtUOM = DLookup("PurchaseUOM", "PDatabase", "[PartNumber] = '" & Me.txtComponent & "'")
    Me.txtCageCode = DLookup("CageCodeA", "Drawings", "[Drawing] = '" & Me.cboComponent & "'")
    Me.txtCageCodeA = DLookup("CageCodeB", "Drawings", "[Drawing] = '" & Me.cboComponent & "'")
    Me.txtCageCodeB = DLookup("CageCodeC", "Drawings", "[Drawing] = '" & Me.cboComponent & "'")
    Me.txtCageCodeC = DLookup("CageCodeD", "Drawings", "[Drawing] = '" & Me.cboComponent & "'")
    
    
    
    
    
    
    'Load AlternateA into the drop down
    On Error Resume Next
       txtAlternateA.RowSource = "SELECT DRAWINGS.AlternateA " & _
                "FROM DRAWINGS " & _
                "WHERE DRAWINGS.Primary = '" & Me.txtComponent & "'" & _
                "ORDER BY DRAWINGS.AlternateA; "
           
         
     
     
                    
    'Load AlternateB into the drop down
    On Error Resume Next
       txtAlternateB.RowSource = "Select DRAWINGS.AlternateB " & _
                "FROM DRAWINGS " & _
                "WHERE DRAWINGS.Primary = '" & Me.txtComponent & "' AND DRAWINGS.AlternateB Is Not Null " & _
                "ORDER BY DRAWINGS.AlternateB;"
                
           
              
                
    'Load AlternateC into the drop down
    On Error Resume Next
       txtAlternateC.RowSource = "Select DRAWINGS.AlternateC " & _
                "FROM DRAWINGS " & _
                "WHERE DRAWINGS.Primary = '" & Me.txtComponent & "'" & _
                "ORDER BY DRAWINGS.AlternateC;"
                
                
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSql As String 'use this especially if the recordset is to be base on a sql statement
    
    
    '***
    'do one or the other of these, not both
    'Set rs = db.OpenRecordset("name of your query")
    Set rs = db.OpenRecordset(strSql)
    '***
    'If you only care if there is at least one record, then this will do
    If rs.RecordCount > 0 Then Me.txtAlternateA.BackColor = vbYellow
    'If you want to know how many records were returned, then this before the counting line above
    'rs.MoveLast
    'rs.MoveFirst 'if you want to go back to the beginning and perform a loop on the records (which you don't)
    
    
    MsgBox ("A: " & rs.RecordCount)
    
    
    'close recordset and reclaim memory before exiting procedure
    rs.Close
    Set rs = Nothing
    Just as a disclaimer, the "txtAlternateA" is actually a combobox (not a text box) and it is too much hassle to rename it and adjust all my code.

  13. #13
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    In regards to post #12, I have to make some adjustments to the Loading statements, but it should not affect the end goal.

  14. #14
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Code:
    Dim strSql as String 'use this especially if the recordset is to be base on a sql statement
    You didn't assign a valid sql statement to the variable, but you chose to use it (over the other method, a valid query name). You cannot simply pass a variable name to any function or object and expect it to work without giving it a value at some point. I've re-written your code to show some other approaches which would condense it (as well as show that declarations should come first) and have guessed that AlternateA is the one whose count you want to check. Afterwards, I thought it was probably B and/or C. If incorrect, reassign the variable to one of the others in the same way and ignore whatever changes I made to the rest of the code if they don't suit you. I don't use line continuation characters and have shown another method in the assignment.

    Code:
    Private Sub cboRev_AfterUpdate()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSql As String
    
    With Me
      .txtComponent = Nz(DLookup("Primary", "DRAWINGS", "Drawing='" & Me.cboComponent & "' AND Revision ='" & Me.cboRev & "'"), "")
      .txtDescription = DLookup("Description", "PDatabase", "[PartNumber] = '" & Me.txtComponent & "'")
      .txtUOM = DLookup("PurchaseUOM", "PDatabase", "[PartNumber] = '" & Me.txtComponent & "'")
      .txtCageCode = DLookup("CageCodeA", "Drawings", "[Drawing] = '" & Me.cboComponent & "'")
      .txtCageCodeA = DLookup("CageCodeB", "Drawings", "[Drawing] = '" & Me.cboComponent & "'")
      .txtCageCodeB = DLookup("CageCodeC", "Drawings", "[Drawing] = '" & Me.cboComponent & "'")
      .txtCageCodeC = DLookup("CageCodeD", "Drawings", "[Drawing] = '" & Me.cboComponent & "'")
    End With
    
    'Load AlternateA into the drop down
    On Error Resume Next
      strSql = "SELECT DRAWINGS.AlternateA FROM DRAWINGS  WHERE DRAWINGS.Primary = '" 
      strSql = strSql & Me.txtComponent & "' ORDER BY DRAWINGS.AlternateA;"
       txtAlternateA.RowSource = strSql
                    
    'Load AlternateB into the drop down
       txtAlternateB.RowSource = "Select DRAWINGS.AlternateB FROM DRAWINGS WHERE " &_ 
                "DRAWINGS.Primary = '" & Me.txtComponent & "' AND DRAWINGS.AlternateB Is Not Null " & _
                "ORDER BY DRAWINGS.AlternateB;"
              
    'Load AlternateC into the drop down
       txtAlternateC.RowSource = "Select DRAWINGS.AlternateC FROM DRAWINGS WHERE " & _
                "DRAWINGS.Primary = '" & Me.txtComponent & "' ORDER BY DRAWINGS.AlternateC;"
        
    Set rs = db.OpenRecordset(strSql)
    If rs.RecordCount > 0 Then Me.txtAlternateA.BackColor = vbYellow
    MsgBox ("A: " & rs.RecordCount)
    
    'close recordset and reclaim memory before exiting procedure
    rs.Close
    Set rs = Nothing
    
    End Sub
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  15. #15
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    My apologies. I had a feeling I was forgetting to assign the sql statement, I just didn't know how.

    It still highlights the combobox when there are blank records in the combobox list.

    Also, is there a reason you assign the sql twice? It appears to be just a kind of concatenation of two statements that I originally had as one, but are they really any different?

    This:
    Code:
     strSql = "SELECT DRAWINGS.AlternateA " & _
      "FROM DRAWINGS " & _
      "WHERE DRAWINGS.Drawing = '" & Me.cboComponent & "' AND DRAWINGS.Revision ='" & Me.cboRev & "' AND DRAWINGS.AlternateA Is Not Null " & _
      "ORDER BY DRAWINGS.AlternateA;"
       txtAlternateA.RowSource = strSql
    Or this:
    Code:
    strSql = "SELECT DRAWINGS.AlternateA FROM DRAWINGS  WHERE DRAWINGS.Drawing = '"
      strSql = strSql & Me.cboComponent & "' AND DRAWINGS.Revision ='" & Me.cboRev & "' AND DRAWINGS.AlternateA Is Not Null ORDER BY DRAWINGS.AlternateA;"
       txtAlternateA.RowSource = strSql
    I just want to make sure I'm not mucking up your code.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Conditional formatting for combobox
    By boboivan in forum Access
    Replies: 4
    Last Post: 04-24-2016, 05:07 AM
  2. Replies: 2
    Last Post: 11-12-2015, 10:46 AM
  3. Replies: 6
    Last Post: 08-04-2015, 08:42 AM
  4. Replies: 6
    Last Post: 05-26-2013, 09:10 PM
  5. Conditional formatting
    By avarusbrightfyre in forum Forms
    Replies: 3
    Last Post: 07-01-2011, 11:18 AM

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