Access 2000 - error "the expression you entered refers to an object that is closed"

[weston]

In Access 2000 I have a form with a tab control with 3 tabs. I have a button that is supposed to check if each question on the form has been completed. The code that is under this button is below. It works for the questions on the first 2 tabs but when it gets to the questions on the 3rd tab I get the following error message "The expression you entered refers to an object that is closed or doesn't exist".

This code does work properly on another form that only has 1 tab but also fails on a third form that has 5 tabs (it fails on the 5th tab)/

I am pretty new to adding VB to Access forms so any help would be appreciated.

Here is the code
Private Sub Command199_Click()
Dim ctl As Control
On Error GoTo Err_command199
For Each ctl In Me.Controls
If IsNull(ctl) Then
MsgBox "You must enter a value into " & ctl.Controls(0).Caption & "."
ctl.SetFocus
Cancel = True
Exit Sub
End If
Next

Exit_command199:
Exit Sub

Err_command199:
MsgBox Err.Description
'MsgBox "All the data have been entered"
Resume Exit_command199
End Sub

[weston]

I have been working more on solving this problem and have some additional information.

On the bottom of each page I have a calculate command button that calculates a score. I have zeroed in that the problem occurs at the bottom of page 2. The code works through all the fields properly until the bottom of page 2 before the calculate button is clicked. I then get the error " The expression you entered refers to an object that is closed or doesn't exist". When I click the calculate button the error message changes to "Object doesn't support this property or method". Then if I click the first field on page 3 with a valid answer the code works properly with the rest of the fields on page 3.

I wonder if there is some control there that I can't see but I am not sure how to look for it.
Thanks again for any help.

[ADezii]

In Access 2000 I have a form with a tab control with 3 tabs. I have a button that is supposed to check if each question on the form has been completed. The code that is under this button is below. It works for the questions on the first 2 tabs but when it gets to the questions on the 3rd tab I get the following error message "The expression you entered refers to an object that is closed or doesn't exist".

This code does work properly on another form that only has 1 tab but also fails on a third form that has 5 tabs (it fails on the 5th tab)/

I am pretty new to adding VB to Access forms so any help would be appreciated.

Here is the code
Private Sub Command199_Click()
Dim ctl As Control
On Error GoTo Err_command199
For Each ctl In Me.Controls
If IsNull(ctl) Then
MsgBox "You must enter a value into " & ctl.Controls(0).Caption & "."
ctl.SetFocus
Cancel = True
Exit Sub
End If
Next

Exit_command199:
Exit Sub

Err_command199:
MsgBox Err.Description
'MsgBox "All the data have been entered"
Resume Exit_command199
End Sub

Cancel = True

If you are trying to Cancel the Update of a Record from within the Click() Event of a Command Button, you cannot set Cancel = True.

[weston]

I have kept working on the problem and have found the solutiion I think.

The text box that held the result of the calculation from the calculate button was empty. Once I put a default missing value in that field everything works now.

[missinglinq]

As ADezii said, you can only include Cancel = True in subs such as BeforeUpdate events. You can tell whether or not this code can be used by the Cancel As Integer as in here:
Private Sub YourField_BeforeUpdate(Cancel As Integer).

"Object doesn't support this property or method" is probably being caused because you're not just checking controls such as textboxes or comboboxes for nulls and setting focus to them, you're checking all controls, including labels, and trying to set focus to them if they're null. Since labels (among other controls) cannot receive focus, the don't support this property or method, and hence the error. You need to correct these first. Also, you have your code set up so that, in the middle of your loop, if a control is null, you exit the sub! That's not right! You need to correct the problem, then continue the loop.

Let's start by replacing

For Each ctl In Me.Controls
 If IsNull(ctl) Then
 MsgBox "You must enter a value into " & ctl.Controls(0).Caption & "."      ctl.SetFocus
Cancel = True
Exit Sub
 End If
Next

With this

For Each ctl In Me.Controls
 If (TypeOf ctl Is TextBox) Or (TypeOf ctl Is ComboBox) Then
  If IsNull(ctl) Then
  MsgBox "You must enter a value into " & ctl.Controls(0).Caption & "."
  ctl.SetFocus
  End If
End If
Next

[ADezii]

I have kept working on the problem and have found the solutiion I think.

The text box that held the result of the calculation from the calculate button was empty. Once I put a default missing value in that field everything works now.

ling had some excellant pointers and you should follow them. There is something else that will help in similar type situations. Anytime you may be referencing Properties/Methods of Controls as a whole, without regards to what Properties or Methods may be applicable to them, you should use

On Error Resume Next

at the beginning of the code segment.

[weston]

Thanks for the help. I will try what you have suggested.