Results 1 to 4 of 4

Thread: VBA Error

  1. #1
    Join Date
    Nov 2003
    Posts
    18

    Unanswered: -

    -
    Last edited by TheGame7; 12-10-03 at 10:56.

  2. #2
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: VBA Error

    You can resolve this by declaring your variables only once... at the top of the procedure (directly underneath the "On Error GoTo ErrHandler" statement...

    You don't need to declare them in each Case... Once they are declared you assign their values in the separate cases...

    HTH

  3. #3
    Join Date
    Nov 2003
    Posts
    1,487
    The error you are receiving is simply because you have declared one or more variables more than ONCE within your procedure. Unless you use ReDim, which you dont need in this case, You can are permitted to declare any variable only once, you have declared the same variables many times in your subroutine. Simple fix......remove the following variable dimensions from benieth your Case statements. In particular:

    Dim Msg, Response
    Dim StrInput, StrMsg

    Place these dimensions instead, just above your 'On Error GoTo ErrHandler' statement. It should look like this:

    Code:
    Private Sub cmdOK_Click()
    '------------------------------------
    'You can dimension these only once
    'in a procedure.
    Dim Msg, Response
    Dim StrInput, StrMsg
    '------------------------------------
    On Error GoTo ErrHandler
    Select Case frameCustomerOperations.Value
       Case 1
           DoCmd.OpenQuery "frmNewCustomer"
    
       Case 2
           StrMsg = "Please enter password"
           StrInput = InputBox(Title:="View Selection Menu", Prompt:=StrMsg)
           'Enter password to open selection menu
    
           If StrInput = "cookie" Then
              Me.txtFirstName.Enabled = True
              Me.txtFirstName.Locked = False
              Me.txtFirstName.SpecialEffect = 2
              Me.txtSurname.Enabled = True
              Me.txtSurname.Locked = False
              Me.txtSurname.SpecialEffect = 2
              Me.txtDateOfBirth.Enabled = True
              Me.txtDateOfBirth.Locked = False
              Me.txtDateOfBirth.SpecialEffect = 2
              Me.txtAddressLine1.Enabled = True
              Me.txtAddressLine1.Locked = False
              Me.txtAddressLine1.SpecialEffect = 2
              Me.txtAddressLine2.Enabled = True
              Me.txtAddressLine2.Locked = False
              Me.txtAddressLine2.SpecialEffect = 2
              Me.txtTown.Enabled = True
              Me.txtTown.Locked = False
              Me.txtTown.SpecialEffect = 2
              Me.txtCounty.Enabled = True
              Me.txtCounty.Locked = False
              Me.txtCounty.SpecialEffect = 2
              Me.txtPostalCode.Enabled = True
              Me.txtPostalCode.Locked = False
              Me.txtPostalCode.SpecialEffect = 2
              Me.txtTelephoneNumber.Enabled = True
              Me.txtTelephoneNumber.Locked = False
              Me.txtTelephoneNumber.SpecialEffect = 2
              Me.txtE_MailAddress.Enabled = True
              Me.txtE_MailAddress.Locked = False
              Me.txtE_MailAddress.SpecialEffect = 2
    
              'If password is correct the relevant fields are unlocked and become sunken.
    
          Else
             GoTo PasswordErrHandler
             'Incorrect password so display error message
             PasswordErrHandler:
             Msg = "Incorrect Password"
             Response = MsgBox(Msg)
         End If
    
       Case 3
           StrMsg = "Please enter password"
           StrInput = InputBox(Title:="View Selection Menu", Prompt:=StrMsg)
           'Enter password to open selection menu
           If StrInput = "cookie" Then
               Me.txtTab.Enabled = False
               Me.txtTab.Locked = False
               Me.txtTab.SpecialEffect = 2
    
           'If password is correct the relevant fields are unlocked and become sunken.
           Else
               GoTo PasswordErrHandler
               'Incorrect password so display error message
    
    PasswordErrHandler:
              Msg = "Incorrect Password"
              Response = MsgBox(Msg)
           End If
    
       Case 4
            StrMsg = "Please enter password"
            StrInput = InputBox(Title:="View Selection Menu", Prompt:=StrMsg)
            'Enter password to open selection menu
            If StrInput = "cookie" Then
               Me.txtTab.Enabled = False
               Me.txtTab.Locked = False
               Me.txtTab.SpecialEffect = 2
    
              'If password is correct the relevant fields are unlocked and become sunken.
           Else
              GoTo PasswordErrHandler
              'Incorrect password so display error message
    
    PasswordErrHandler:
              Msg = "Incorrect Password"
              Response = MsgBox(Msg)
           End If
    
       Case 5
           DoCmd.OpenReport "rptFixturesByReferee-Today", acViewPreview
    End Select
    
    ErrHandler:
       'Code to handle errors.
       On Error Resume Next
    End Sub
    I see you are coverting all your text boxes. You can do this with far less code by doing it this way:

    Code:
    Dim Ctrl As Control
    For Each Ctrl In Me.Controls
        If Ctrl.ControlType = acTextBox Then
            Ctrl.Locked = False
            Ctrl.SpecialEffect = 2
            Ctrl.Enabled = True
        End If
    Next Ctrl
    Just a thought

  4. #4
    Join Date
    Nov 2003
    Posts
    1,487
    Whooops.....someone beat me to it...I'm to long winded I quess.

Posting Permissions

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