Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Thanked 0 Times in 0 Posts

    check all controls (Access 2000)

    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")
    DoCmd.RunCommand acCmdSaveRecord
    End If

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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: check all controls (Access 2000)

    Please try to think through the structure of your code instead of just putting random statements below each other.
    <UL><LI>Logical "blocks such as " If ... End If, For ... Next etc. must be nested. You cannot intertwine such blocks.
    <LI>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.
    <LI>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.
    <LI>To check if a value is Null in code, use the IsNull function.
    <LI>Your Exit Sub statement comes before the MsgBox statement, so the message box is not displayed.
    <LI>Your MsgBox statement lacks quotes before the text to be displayed. The parentheses ( ) are superfluous.
    <LI>You don't set the object variable ctl to Nothing at the end. You should always do this.
    <LI>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
    GoTo ExitHandler
    End If
    Case Else
    ' ignore other controls
    End Select
    Next ctl

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

    Set ctl = Nothing
    Exit Sub

    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts