none
Trying to get a label to display under certeria RRS feed

  • Question

  • I am try to develop a program on Microsoft Access where I need a label to be visible if it meets 2 certeria at the same time in a table. So lets call it label A is only visible when ID# in column 1 is present with column 3 saying D,E, or G. But not visible if column 3 says w. what would the code look like for something like this. 

    Thanks


    Wednesday, December 9, 2015 3:57 PM

Answers

  • Sure... run update queries with the criteria in the WHERE clauses.  You can finesse the queries in the query builder before putting them in code.  It would look something like this (generic).  You'll need to adjust for precise syntax, field names etc:

    Dim strCritID as string
    Dim strCritUrgency as string
    
    strCritID = "YourIDField = " & me.IDField
    
    ' For records you need to update to 'yes'
    strCritUrgency = " AND YourOtherField IN ('text1', 'text2', 'etc')"
    
    strSQL = "UPDATE YourTable SET YourField = 'Yes' WHERE " _
         & strCritID & strCritUrgency
    
    currentdb.execute strsql, dbfailonerror
    
    
    ' For records you need to update to 'no'
    
    strCritUrgency = " AND YourOtherField = 'text4'"
    
    strSQL = "UPDATE YourTable SET YourField = 'No' WHERE " _
         & strCritID & strCritUrgency
    currentdb.execute strsql, dbfailonerror
    


    Miriam Bizup Access MVP

    Friday, December 11, 2015 3:51 PM

All replies

  • Hi UMD,

    >> So lets call it label A is only visible when ID# in column 1 is present with column 3 saying D,E, or G. But not visible if column 3 says w.

    Where the label? Was it stored in Form? Did you want to check all of the values in column1 and column 3 or check the each record? If you want to set the visible property of Label in Form, you could refer the simple code below in Form current event:

    Private Sub Form_Current()
     If Me.Id > 5 Then
        Me.Label44.Visible = True
     Else 
        Me.Label44.Visible = False
     End If
    End Sub

    You need to modify the logic for your own requirement like change the if statement.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, December 10, 2015 3:27 AM
  • Did you post this on another forum recently?  the question looks one I've posted to already recently -  maybe just a frequently asked question.

    If you're having trouble getting both criteria to work together, then try handling them separately.  Try starting with the label hidden.  You can keep the code simpler (possibly easier for you to understand) by handling each condition separately, and then combining them for the final result (read the inline comments, please):

    dim blCrit1 as boolean
    Dim blCrit2 as Boolean
    Me.lblMyLabel.Visible = false
    blCrit1 = NZ(Me.lstBox.Column(0), "") <> ""   ' Check for the ID in the first column.  Note that column count starts at 0.
    Select Case Me.lstBox.Column(2)   ' Using column 2... since column count is zero based, the third column is actually column 2.
            Case "D","E", "G":  blCrit2 = true
            Case Else:  blCrit2 = False
    End Select
    Me.lblMyLabel.Visible = blCrit1 And blCrit2

    


    Miriam Bizup Access MVP



    EDIT -- I may be misreading this.  I'm assuming the column's you mention are in a listbox...
    • Edited by mbizup MVP Thursday, December 10, 2015 2:39 PM
    Thursday, December 10, 2015 2:32 PM
  • I need the label93 to display if the SignID from tblSigns is found in tblHISTORY column SIGN_ID and also in that row column Urgency is High, Medium, or Low, but if it if complete is in column Urgency i want Label96 to display. I hope that better explains my situation. 
    Thursday, December 10, 2015 2:47 PM
  • If signID is available in your form's data (is your form bound to tblSigns), you can use DLookup to get the value of Urgency from tblHistory  you'll have to adjust this for correct table/field names, if the ID field is text or numeric, and a variety of other things):

    Dim strUrgency as string

    strUrgency = "" & DLookup("YourFieldName", "YourTableName", "YourIDField = " & Me.SignID)

    Select case strUrgency

       ' etc

    end select


    Miriam Bizup Access MVP


    • Edited by mbizup MVP Thursday, December 10, 2015 3:03 PM
    Thursday, December 10, 2015 3:03 PM
  • Okay, so I just got thrown a curve ball. I will try explaining this the best I can. 

    Is it possible to update the text in tblSign column Open_Work_Order to say yes if tblHISTORY Column Urgency is High, Medium, or Low for the same Sign id and then say no when tblHISTORY Column Urgency is Completed.
    Thursday, December 10, 2015 3:24 PM
  • Sure... run update queries with the criteria in the WHERE clauses.  You can finesse the queries in the query builder before putting them in code.  It would look something like this (generic).  You'll need to adjust for precise syntax, field names etc:

    Dim strCritID as string
    Dim strCritUrgency as string
    
    strCritID = "YourIDField = " & me.IDField
    
    ' For records you need to update to 'yes'
    strCritUrgency = " AND YourOtherField IN ('text1', 'text2', 'etc')"
    
    strSQL = "UPDATE YourTable SET YourField = 'Yes' WHERE " _
         & strCritID & strCritUrgency
    
    currentdb.execute strsql, dbfailonerror
    
    
    ' For records you need to update to 'no'
    
    strCritUrgency = " AND YourOtherField = 'text4'"
    
    strSQL = "UPDATE YourTable SET YourField = 'No' WHERE " _
         & strCritID & strCritUrgency
    currentdb.execute strsql, dbfailonerror
    


    Miriam Bizup Access MVP

    Friday, December 11, 2015 3:51 PM