Trying to get a label to display under certeria

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

Answer 1

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.

Answer 2

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...

Answer 3

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. 

Answer 4

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

Answer 5

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.

Answer 6

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