check all controls (Access 2000)

Topic

New Reply

I am looking for a code that keeeps track of all the records in my table
and in case one record is not filled in, to forbid closing.My other problem is that
some fields have a field 0, but even then the value is Null.
I imagine my code in the following way

Dim ctl As Control
For each ctl in frmNewProducts
if ctl is Null then
Exit Sub
MsgBox ( Please fill in the missing values”)
Next
Else
DoCmd.RunCommand acCmdSaveRecord
End If

I do not get anything and obvioulsy what i have written is wrong.
May i ask for helo ?

Reply | Quote

Replies

Please try to think through the structure of your code instead of just putting random statements below each other.

• Logical “blocks such as ” If … End If, For … Next etc. must be nested. You cannot intertwine such blocks.
• You can’t refer to a form frmNewProducts this way, neither do you need to. You can refer to the form that is running the code as Me.
• You loop through all controls on the form, but not all controls have a value. You don’t want to check labels, for example, but probably only text boxes, combo boxes etc.
• To check if a value is Null in code, use the IsNull function.
• Your Exit Sub statement comes before the MsgBox statement, so the message box is not displayed.
• Your MsgBox statement lacks quotes before the text to be displayed. The parentheses ( ) are superfluous.
• You don’t set the object variable ctl to Nothing at the end. You should always do this.
• You don’t need DoCmd before RunCommand.
  [/list]Here is revised code, with error handling added:

  Sub Test()
  Dim ctl As Control
  On Error GoTo ErrHandler

  For Each ctl In Me.Controls
  Select Case ctl.ControlType
  Case acTextBox, acComboBox
  If IsNull(ctl.Value) Then
  MsgBox “Please fill in the missing values”, vbInformation
  ctl.SetFocus
  GoTo ExitHandler
  End If
  Case Else
  ‘ ignore other controls
  End Select
  Next ctl

  ‘ if we get here, the record can be saved
  RunCommand acCmdSaveRecord

  ExitHandler:
  Set ctl = Nothing
  Exit Sub

  ErrHandler:
  MsgBox Err.Description, vbExclamation
  Resume ExitHandler
  End Sub

Reply | Quote

Please try to think through the structure of your code instead of just putting random statements below each other.

• Logical “blocks such as ” If … End If, For … Next etc. must be nested. You cannot intertwine such blocks.
• You can’t refer to a form frmNewProducts this way, neither do you need to. You can refer to the form that is running the code as Me.
• You loop through all controls on the form, but not all controls have a value. You don’t want to check labels, for example, but probably only text boxes, combo boxes etc.
• To check if a value is Null in code, use the IsNull function.
• Your Exit Sub statement comes before the MsgBox statement, so the message box is not displayed.
• Your MsgBox statement lacks quotes before the text to be displayed. The parentheses ( ) are superfluous.
• You don’t set the object variable ctl to Nothing at the end. You should always do this.
• You don’t need DoCmd before RunCommand.
  [/list]Here is revised code, with error handling added:

  Sub Test()
  Dim ctl As Control
  On Error GoTo ErrHandler

  For Each ctl In Me.Controls
  Select Case ctl.ControlType
  Case acTextBox, acComboBox
  If IsNull(ctl.Value) Then
  MsgBox “Please fill in the missing values”, vbInformation
  ctl.SetFocus
  GoTo ExitHandler
  End If
  Case Else
  ‘ ignore other controls
  End Select
  Next ctl

  ‘ if we get here, the record can be saved
  RunCommand acCmdSaveRecord

  ExitHandler:
  Set ctl = Nothing
  Exit Sub

  ErrHandler:
  MsgBox Err.Description, vbExclamation
  Resume ExitHandler
  End Sub

Reply | Quote