Can't trigger If statement

[davesmith202]

I have a continuous Form and a label field to the right of the last field. When I click this label, I want it to run some code to trigger a msgbox. My code is below.

Dim txtAdvice As String
    
    txtAdvice = ""

    If (Me.field1= "abc") And (Me.field2= "No") Then
    
        txtAdvice = "xyz."
        Else
        txtAdvice = "123"
    End If

MsgBox txtAdvice

But when I click the label field on any row, it will always popup the msgbox with "abc". Why is that?

Thanks,

Jon

 

[boblarson]

Is Me.field2 a text field? If not then you need a different test. If it is a boolean you would need

If (Me.field1= "abc") And (Me.field2= False) Then

 

[boblarson]

So, to add to it - your test values must not be exactly what you are thinking. I would use Debug.Print to find out what those fields are actually returning.

 

[davesmith202]

Bob, good suggestion to use Debug.Print. This helped me discover that the issue lays with where the cursor is. When I clicked the Label on another record, the cursor was still in the first record. Consequently, it picked up the values from the first record.

Is that because I used a label or does a command button leave the cursor in there too? I suppose I would have to use a record focus type of command too?

 

[boblarson]

A label can't get focus, so yes. You could use a TEXT BOX formatted to LOOK like a label.

 

[davesmith202]

Clever idea Bob, thanks!

 

[davesmith202]

Actually Bob, is there a line of code that lets me select the current record when I click on the label?

 

[boblarson]

The problem is that, as you say, the current record is where the cursor lies. And since a label cannot get the focus, it cannot change which record you are on. The other method than the text box to look like a label is to use a button because it will change which record is the currently selected record as well. So if the focus is on the first record and you click on the 4th button, the 4th record will be the one that has focus and the code will work. The same goes with the text box formatted as a label.

However, another method is to combine the button with the label. You can have your label and put a command button over the label and set the button's TRANSPARENT property to YES. Then you can't see the button but it will act in every way just like a regular button.

 

[davesmith202]

I did the invisible button thing. Believe it or not I used to know this stuff but if you don't use it you lose it! I put Visible to No but that disables it. Thanks for the recommendation Bob.

 

[boblarson]

Glad to be able to help.