Page 2 of 2 FirstFirst 12
Results 16 to 28 of 28
  1. #16
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737

    They are not different as far as the final result is concerned. I find my method of concatenation easier to read, plus I can step through code and check each segment if need be, as it is constructed, whereas the other method does not support this. That might be the most compelling reason for me to do it that way. In addition, I have copied/pasted those pieces right from MS websites and dumped them into code to play with and they often fail. For me, they are more of a pain than benefit.

    As for highlighting blank combo lists, ensure that the color hasn't ended up as a design property after some sort of form save you've done. You also could post what you get from your message box as a clue. I gotta run, so maybe you need to zip your db and attach it to this thread. Read the instructions on posting attachments if you need to.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  2. #17
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    As for highlighting blank combo lists, ensure that the color hasn't ended up as a design property after some sort of form save you've done
    Everything checks out here. An interesting thing is when I select or type a value into the highlighted combobox and change focus to another field, the background color changes back to normal (white), which is what it is supposed to do. But if there was a design property change, then the background color would not change back to normal, correct?

    You also could post what you get from your message box as a clue
    When I ran the following:
    Code:
    If rs.RecordCount > 0 Then Me.txtAlternateA.BackColor = vbYellow
    mess = "A: " & rs.RecordCount
    
    
    MsgBox (mess)
    The message box comes up and is blank. I've never seen this happen before. I tweaked to the following just to make sure there was nothing wrong with the message box itself:

    Code:
    If rs.RecordCount > 0 Then Me.txtAlternateA.BackColor = vbYellow
    mess = "A: "
    
    
    MsgBox (mess)
    (I took out the recordcount object) And it worked just fine. So, it's having a problem with the "& rs.RecordCount". Not sure what the problem is.

  3. #18
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    when I select or type a value into the highlighted combobox and change focus to another field, the background color changes back to normal (white), which is what it is supposed to do. But if there was a design property change, then the background color would not change back to normal, correct?
    This action is considered an update as well as a couple of other events which I can only guess that you do or don't have running. It's the only way I can think of that would change the color back to normal.

    If the count is greater than 0, don't expect a message. If you want to know the count, the original code I posted had that (you would not use an If block that tests for > 0). You only need to MoveLast if you want to know the actual count. If you want a message if there is anything greater than zero, you'd just use Msgbox rs.recordcount.

    This is dragging out a bit. Can you not post a zipped db with the necessary parts if you don't get it solved after this?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #19
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    You only need to MoveLast if you want to know the actual count. If you want a message if there is anything greater than zero, you'd just use Msgbox rs.recordcount.
    Adjusted my code accordingly and same results. A message box pops up but it has nothing in it.
    Code:
    Set rs = db.OpenRecordset(strSql)
    rs.MoveLast
    mess = rs.RecordCount
    
    
    MsgBox (mess)
    '***
    'If you only care if there is at least one record, then this will do
    If rs.RecordCount > 0 Then Me.txtAlternateA.BackColor = vbYellow
    I cannot post a zipped file. It would take me days to take out the data and non-essential parts for this problem.

  5. #20
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Apparently, I did not clue in that the table/query data underlying these controls is such that for any given record, it is possible to have a value for one field/control but not for its child control, thus there is still a record being returned, but the child field is Null (my bad ). So you have to test for Null in the recordset field. Unless you are storing empty strings ("") it should not be necessary to test for those as well. Based on the sql I see for your combos, you are only retruning 1 field, so the recordset field to test is 0 (rs.Fields is zero based). This has been added to the first code block I posted, as well as a block to ensure the color is something other than yellow if a subsequent update on a parent control results in null values for the child. A requery of the child control was also added to clear it out in that case. I added a line marked missing. That one raises a red flag for me in that you should have gotten a run time error.

    Once the parent selection is made, the child should become yellow if it has no list values. If you want its color to be something else after a value has been chosen, you'll need an after update event on it to set that color. Otherwise it will stay yellow throughout the rest of the form edit (assuming user does not go back and alter the parent). Something like
    If Me.cmbChild <> "" Then Me.cmbChild.BackColor = vbWhite
    My solution was tested and works, but obviously I cannot include your code and test as a whole procedure.

    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 db=CurrentDb 'THIS WAS MISSING - (NOT THE ISSUE)
    Set rs = db.OpenRecordset(strSql)
    If Not IsNull(rs.Fields(0)) Then
        Me.cmbSystem.BackColor = vbYellow
    Else
        Me.cmbSystem.BackColor = vbWhite
    End If
    
    With Me
        .cmbSystem.Requery
        .cmbSystem = ""
    End With
    
    'close recordset and reclaim memory before exiting procedure
    rs.Close
    Set rs = Nothing
    Set db = Nothing 
    
    End Sub
    Last edited by Micron; 07-21-2016 at 08:26 AM. Reason: set db = nothing

  6. #21
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    Just tried it and the field turns yellow when there are no records returned.

    I looked over my code to make sure there's no other highlighting going on, and the only other function I have is:

    Code:
    Private Sub txtAlternateA_AfterUpdate()
    
    
    If Not IsNull(Me.txtAlternateA) Then
    Me.txtAlternateA.BackColor = vbWhite
    End If
    
    
    
    
    End Sub
    ^Where the backcolor changes back to normal if a choice is made either by selecting from the drop down or typed in manually.

  7. #22
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Just tried it and the field turns yellow when there are no records returned.
    What does this mean? No records for what?

    From what I see of your combo row source sql statements, in your case Null is a record. That is not the same as no values for your control row source. When you click on the control, there is nothing visible in the list, right? But the sql returns one record because there are fields with values in that record, just no value for the field related to the second control. That field is Null, hence no visible value showing in the combo list.

  8. #23
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    Sorry, basically the same result as everything else I've tried, which is I update a combobox, a second combobox performs a rowsource change based on what is in my first combobox, and if no values exist to populate the second combobox's list, then the second combobox turns yellow.

    I would like the second combobox to turn yellow ONLY if there are values populated in the list. I would like it to stay white if no values exist to populate the list OR a value is chosen/typed in the second combobox.

    And by "no values", I mean Nulls.

    You are correct in that the field is Null, and thus there is no visible value showing in the combo list. The idea is that since I cannot utilize list count or anything like that to achieve my goal, the only other way to do this without changing the control type to something other than a combobox is to evaluate the returned record in the combo list. For my purpose, the combo list will always be 1 record. If that record is Null, keep the background color white. If that record is Not Null, turn it yellow. Once a value is chosen or typed into the combo, turn the combo white if not already.

    Am I understanding you correctly on this?

  9. #24
    Bulzie is online now VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    So rereading it all looks like you guys are close, you need to perform this action in the AfterUpdate on Combobox1.
    Besides checking for null, maybe check that it is not blank "" or empty. I have had it where i thought it should be nulls but it would not work so had to check for all 3.

    If Not IsNull(rs.Fields(0)) AND rs.Fields(0) <> "" AND Not IsEmpty(rs.Fiels(0)) Then Me.cmbSystem.BackColor = vbYellow
    Else
    Me.cmbSystem.BackColor = vbWhite
    End If

  10. #25
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Yes, you're understanding the null part, but the desired behavior you're describing is what I achieved in my test database, using the code parts I supplied. For these pictures, there are no values for id 22, so the control goes back to white. It is yellow when the list is NOT null. You can try Bulzie's suggestion, but I have it working without that. I don't know what you're missing. If you cannot at least post a db with a form and supporting tables/queries for this, then I don't think I can help further.
    Click image for larger version. 

Name:	Casc1.jpg 
Views:	11 
Size:	5.8 KB 
ID:	25241
    Click image for larger version. 

Name:	Casc2.jpg 
Views:	11 
Size:	3.9 KB 
ID:	25242
    The only other comment I can make is that I did not type in the control to test it. However, the behavior might depend on property settings such as Limit To List.

  11. #26
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    I know this is a little hypocritical of me, but could you attach your test database so that I can look at it beside my own database and figure out the deltas?

    Thanks

  12. #27
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I don't consider it hypocritical at all; nor do I consider the request unreasonable. My take is that what you desire is so simple to do that I must not be understanding the requirement, and if that is the case, I apologize. I've imported the form used to make the previous image and its supporting table into a new db. If you are still stuck after this, and if I have the correct solution, you should be able to do the same rather than upload your entire db. I would attempt to provide what you need and upload the changes. If I have the solution and you cannot replicate it using the attached db, then I will require you to upload in order for me to continue helping. I think that's a reasonable limitation to impose after this many posts for a problem that is, by all indications, solved. Good luck!
    CascCombo.zip

  13. #28
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    Sorry for the delayed reply, but after messing with my database in comparison to yours in post # 27, I figured out why mine was not working. I set my SQL statement in the beginning of the code, and I had a condition in setting the rowsource that really was not necessary. When I took out that condition, it suddenly started to work.

    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
    I removed the red code and it worked for my test data.

    However, I tried it out with some real data and did not work. The fields turned yellow even though there were no records to choose in the rowsource. I tried messing with the different character combinations to see if it was a text vs numerical issue and same problem.

    Then I deleted a real record, and re-added it to my table and tried the form again and it worked. So it seems to have a problem with the data already in my table versus newly added data. Easily fixed by temporarily deleting data and pasting back into the table.


    Thank you all for your help! Works like a champ!

Page 2 of 2 FirstFirst 12
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