469,125 Members | 1,629 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,125 developers. It's quick & easy.

Trying to get a label to display under certain criteria

14
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 label. 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
Dec 9 '15 #1
17 916
jforbes
1,107 Expert 1GB
Probably something like this:
Expand|Select|Wrap|Line Numbers
  1. me.lblWithSomeCoolText.Visible = (len(me![ID]))>0 AND instr(1,"DEG", NZ(Me![Column3], "X"))>0)
Dec 9 '15 #2
edunker
14
Okay, thank you. I will try it.
Dec 9 '15 #3
edunker
14
Im not getting that to work for me. So...let me give you a little more info, I need the label93 to display if the ID# is found in tblHISTORY column SIGN_ID and also in that row Urgency is High, Medium, or Low, but if it is complete i want Label96 to display.
Dec 9 '15 #4
jforbes
1,107 Expert 1GB
How is it not working for you? What have you tried? Are you getting an error?

Also, you asked what it would look like, so I provided a sample of what it would typically look like. I assumed that the fields you were talking about were for the Current Record and that you were looking for code to put in the OnCurrent Event of a Form. If this is not the case, the code will look a little different.
Dec 9 '15 #5
edunker
14
Yes I am getting a run time error 424. If you look at the last reply I gave you exactly where everything is located.
Dec 9 '15 #6
jforbes
1,107 Expert 1GB
What have you tried? How did you go about it?
Dec 9 '15 #7
edunker
14
Me.Label93.Visible = (Len(Me![SignID])) > 0 And InStr(1, "High", Nz([tblHISTORY]![urgency], ""))

This is what I tried and got the runtime error
Dec 9 '15 #8
mbizup
80 64KB
Try breaking it down into its components:

PART 1
Expand|Select|Wrap|Line Numbers
  1. Me.Label93.Visible = Len(Me![SignID]) > 0
  2.  
This will probably give you trouble if SignID is NULL.

Try this instead:

Expand|Select|Wrap|Line Numbers
  1. Me.Label93.Visible = Len(Trim("" & Me![SignID])) > 0
  2.  
or this:

Expand|Select|Wrap|Line Numbers
  1. Me.Label93.Visible = NZ( Me![SignID], "") <> ""

PART 2
Expand|Select|Wrap|Line Numbers
  1. Me.Label93.Visible =  InStr(1, "High", Nz([tblHISTORY]![urgency], ""))
Are you trying to determine if the Urgency field contains the word High somewhere in it?

Try this instead:

Expand|Select|Wrap|Line Numbers
  1. Me.Label93.Visible = InStr(1, "High", Nz(Me.[urgency], "")) > 0
PART 3

Once you have BOTH parts working separately, put them together with an AND:

Expand|Select|Wrap|Line Numbers
  1. Me.Label93.Visible =(NZ( Me![SignID], "") <>  "") And (InStr(1, "High", Nz(Me.[urgency], "")) > 0)

Disclaimer - this is aircode, so syntax, parentheses etc might not be spot-on.
Dec 9 '15 #9
mbizup
80 64KB
Also - you might need to swap the order of "High" and [Urgency] in your InStr function.

The syntax should be:

Expand|Select|Wrap|Line Numbers
  1. Instr(StartInteger, StringToLookFor, StringToSearchIn)
  2.  
If the result is > 0 then StringToLookFor has been found in StringToSearchIn.
Dec 10 '15 #10
jforbes
1,107 Expert 1GB
mbizup, thanks for the help on this. You've got the string parameters on the InStr() backwards. InStr() is a function I have to lookup the syntax every time I use it as it seems backwards to me.

edunker,
This seems to be a bit of a moving target...

what Event are you using this code in? And is this on a Form where you have bound the Form's RecordSource to tblHISTORY? If you are not using this on a form with a bound RecordSource, this needs to be done a bit differently. It would also explain the error you are getting.

If your Form is bound to tblHISTORY, you won't need to use [tblHISTORY] when referring to fields in the Recordset. You should then be able to follow mbizup's advice to get things working. Except, the InStr() function is going to look a bit different now since the values changed from "D,E, or G and sometimes W" to "High, Medium, Low".

Something like this may be more appropriate now that the picure is getting clearer:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.     Dim bVisible As Boolean
  3.  
  4.     bVisible = True
  5.     bVisible = bVisible And Len(Nz(Me![SIGN_ID], "")) > 0
  6.     bVisible = bVisible And InStr(1, "HighMediumLow", Nz(Me![Urgency ], "SomethingElse")) > 0
  7.     'bVisible = bVisible And ..... Something else you may decide to throw into the mix
  8.  
  9.     Me.Label93.Visible = bVisible
  10. End Sub
In the above example, the Label starts out visible and gets hidden if any of the conditions are not met.
Dec 10 '15 #11
mbizup
80 64KB
<<You've got the string parameters on the InStr() backwards. InStr() is a function I have to lookup the syntax every time I use it as it seems backwards to me.>>

Thanks - I thought it might have been backwards. :-) The syntax always throws me too.
Dec 10 '15 #12
edunker
14
No, the form is not bound to that table. its bound to a different form. but the information I am looking for is on that table and not the table that it is bound to.
Dec 10 '15 #13
jforbes
1,107 Expert 1GB
Well that changes things. So something like this might be more appropriate:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.     Dim bVisible As Boolean
  3.     Dim lSignID As Long
  4.     Dim sUrgency As String
  5.  
  6.     lSignID = Nz(Me![ID], 0) ' Get the ID of the current Record
  7.     sUrgency = Nz(DLookup("Urgency ", "tblHISTORY ", "Sign_ID=" & lSignID), "") ' Get Urgency from the History Table
  8.  
  9.     bVisible = True
  10.     bVisible = bVisible And InStr(1, "HighMediumLow", sUrgency) > 0
  11.     'bVisible = bVisible And ..... Something else you may decide to throw into the mix
  12.  
  13.     Me.Label93.Visible = bVisible
  14. End Sub
Since the record with the information in tblHistory isn't loaded on to the Form, you'll need to go out and get it. The Code above does this by first getting the ID from the Current Record. You have to have something available to find the record with, and if this isn't right, you may need to change it. Then taking the Current Records ID, it looks it up in tblHISORY and finds the Urgency. At this point it should have all the information that it needs to determine if the Label should be shown.
Dec 10 '15 #14
edunker
14
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.
Dec 10 '15 #15
jforbes
1,107 Expert 1GB
Yeah, the curve ball can be addressed, but we typically only address one problem/question per thread. So, you'll need to ask that as a new question, and when you do, it is best to try to fully explain what you want to do. To much information is typically better than not enough.

Have you had any luck with your Label or are you shelving this and working on your new problem?
Dec 10 '15 #16
edunker
14
I am shelving that problem. I will start a new thread. thanks
Dec 10 '15 #17
zmbd
5,400 Expert Mod 4TB
Along the same lines: >> https://bytes.com/topic/access/answe...inking-columns<<
Dec 12 '15 #18

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by Tim Whelan via DotNetMonster.com | last post: by
2 posts views Thread by David Cho | last post: by
2 posts views Thread by amber | last post: by
4 posts views Thread by Eric A. Johnson | last post: by
2 posts views Thread by Abhishek Srivastava | last post: by
2 posts views Thread by screechyboy | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.