Message Box Problem

[sherlocked]

Here's my challenge!

I have the following code behind the On_Load event of my subform:

Private Sub Form_Load()
If Me.Status = "74" Or "33" Or "8" Or "7" Then
MsgBox "This Case Has Been Closed!", vbOKOnly, "Case Closed Alert"
End If
End Sub

The problem is, my message box is appearing every time the form loads, not only when the field contains the value I've specified.

Any thoughts as to what may be causing this problem?

 

[cyanidem]

Change it to:

If Me.Status = "74" Or Me.Status = "33" Or Me.Status = "8" Or Me.Status = "7"

Then

Or

Select Case Me.Status
    Case "74", "33", "8", "7"
        MsgBox "This Case Has Been Closed!", vbOKOnly, "Case Closed Alert" 
End Select

End If

 

[Frothingslosh]

To elaborate, the line

If Me.Status = "74" Or "33" Or "8" Or "7" Then

technically means you're asking Access to check if any of the following are true:

• Me.Status equals 74
• 33 is not 0
• 8 is not 0
• 7 is not 0

It's really easy to accidentally parse code in English rather than VBA.

Basically, Access treats numbers alone in an If/Then statement as True/False, and in VBA, False is 0, while True is anything else. It's normally STORED as -1, but anything non-zero is read as 'True'.

 

[sherlocked]

All,

Thank you for your quick replies. I tried both suggestions and I'm afraid that now the message box does not pop up at all, regardless of the value in the [Status] field. Thoughts?

 

[Frothingslosh]

Believe it or not, that's a step in the right direction. It tells us that the data in Me.Status is not any of those values.

First off, I assume you're confirming that the value saved in Status is a string with one of the values in the list. If it's numeric, you should remove the quotes (but I actually don't think it will make a difference - Access is good about comparing numeric data to numbers in strings).

What type of control is Status? If it's a combo box, those are often set up to save a value that's different than the one displayed.

Is Status also the name of the field as well as the control? Even though Access does it by default, it's a bad idea to have controls named identically to the fields they display. Normal procedure would be to prefix controls with the type of control they are, like txtStatus for a text box or cboStatus for a combo box. Helps you differentiate things during troubleshooting, and avoids potential collisions and confusion in the engine.

 

[sherlocked]

Hi,

[Status] is a bound field on a subform which displays query results. There could be many records in the query results, showing the progression of a particular item through several status steps. I want the box to pop up if one of those steps matches any of the four criteria specified in the If/Then statement.

Am I barking up the wrong tree?

 

[Frothingslosh]

No, you're on the right track, but you also didn't really answer my questions.

1. What TYPE of control is Status? Text Box, Combo Box, List Box?
2. Does Status have the same name as the field to which it is bound?

The second question is important because Access starts returning unexpected results when controls share names with fields, because it will sometimes refer to the field when you want the control or visa versa, especially when you're fuzzy on precisely how the methods you're executing may work.

Also, when stuck, it's a good idea to place a breakpoint in the code before the part that's causing a problem (just insert

Stop

as the first line in the function).

That allows you to step through the code with F8, and you can see variable values in the 'Locals' window that should be in the bottom right of the editor, or have them displayed in the immediate window via debug.print. (You will learn to love the immediate window, btw.)

Without knowing what kind of control Status is (and thus, how it's storing data), I would have to say to insert a breakpoint and check what the being reported by Me.Status really is.

 

[Frothingslosh]

Hell, if you have the form open and displaying data AND have the VBA editor open at the same time, you can pull the actual saved value in Me.Status simply by going to the immediate window and typing

Debug.Print Forms!FormName.Status.Value

(replacing FormName with the actual name of your form) and hitting enter.

 

[sherlocked]

My apologies, I appreciate your assistance

The field is a combo box, bound to the field "Status" in my table. It is called "combo14."

 

[Frothingslosh]

There's your problem.

You're referring to Me.Status. Me.Status is shorthand for 'the control named Status on the form to which this module is attached'.

So do you see the first problem?

You have no control named 'Status'. My recommendation would be to change the combo box's name to 'cboStatus', primarily because that will tell you in the future and anyone following behind you precisely which control that is and what data it contains.

So for starters, you need to change your reference from 'Me.Status' to 'Me.combo14' or whatever name you eventually go with.

I'm guessing that will fix your problem. If not, read on.

Combo boxes, as a rule, display different data than they store. They allow you to display "Case Closed" while storing the number 74. If you right-click the control in design mode and select 'Properties', a window will appear with a LOT of properties and values.

In the Format tab, one property is 'Column Count', and the very next one is 'Column Widths'. It's very likely that the count is 2, and that one of the widths is 0. On the Data tab, one of the properties is 'Bound Column', with a number. That is the number of the column (starting at 1 and counting from the left) with the actual stored value; it is almost always the 0-width column.

Finally, the 'row source' property on the data tab will either be a table/query name or a SQL statement. If you click on the '...' button on the right when the line is selected, the QBE grid will open and show you the query or table it uses to populate the list.

The main thing here is that the BOUND column is the actual value of the field, and you can use the row source to figure out precisely what field that is, and thus what the data actually is.

 

[sherlocked]

Hi,

This combo box is connected to a table so that folks can choose from a dropdown. The query behind my subform pulls all associated records from a table and displays them. When they add new records to this table, they use Combo14 to choose the status for the new record. Is this what's preventing the code from working?

I'm flummoxed.

 

[Frothingslosh]

Nope, it's not working because your code isn't checking combo14.

 

[sherlocked]

I updated the code to me.combo14. still nothing.

 

[sherlocked]

As an update, I tried putting a text box named "txtStatusMessage" bound to the "Status" field in the query results on my main form and basing the msg box on the value of this field; it still did not work. Any other ideas?

 

[Frothingslosh]

If you could, please post the code as of your last try.

Also, try putting a break in the code and looking up the precise value in the combo14 the way I described in post #10.