Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

VBA code for Command Button of Form

Discussion in 'Information Technology' started by red6000, Jul 28, 2006.

  1. red6000

    red6000
    Expand Collapse
    Guest

    Hi,

    I have a button which open my InputForm using the 'OpenForm' action and
    'Add' datamode so that I can only add a new records to my table.

    The Input Form has various Text Box's (for arguments sake named TextBox1 to
    5).

    I have disabled the 'close' button on the form and have a command button
    which run's the standard close form macro.

    However what I would like to do is instead of using the default close form
    macro is to have VBA code that:

    ****Strart Code
    If TextBox1.Value = "yes" and TextBox2.value="" and TextBox3.value="" and
    TextBox4.value="" and TextBox5.value="" then
    MsgBox = ("You must enter at least one value in TB 2 to 5")
    Exit Sub
    End if
    InputForm.Close
    ****End Code

    Is this possible. What concerns me (and why I'm not sure what to do) is
    what if the user has input more than one new record, how can I write the
    code to validate new each record? Perhaps the code needs to also be on the
    event of clicking the NewRecord button?

    Hope that makes sense.

    Thanks for any help or advice.
     
  2. Loading...

    Similar Threads Forum Date
    Introducing The Spn Code Of Forum Etiquette Announcements Feb 20, 2016
    UK Sikh 'code of silence' on sexual grooming Breaking News Sep 2, 2013
    Can a Dress Code Bar Ethnic and Religious Attire Based on Customer Preferences in the US? Questions and Answers May 3, 2011
    Pacific Code Exempts Religious Drivers (Fiji) Breaking News Feb 16, 2011
    Controversial Gurdwaras 'Breaking the Sikh Code' on Meat and Alcohol Hard Talk Dec 2, 2010

  3. Luiz Cláudio C. V. Rocha

    Luiz Cláudio C. V. Rocha
    Expand Collapse
    Guest

    Hi red2006,

    your form is bound to a table, correct?

    So you can use Form_BeforeUpdate event to run your routine. BeforeUpdate
    event can be cancelled if the conditions are not met:

    If "your conditions" = False Then
    Cancel=True
    MsgBox "Your message"
    'Me.Undo (optional)
    End If

    Remember also that empty strings ("") are different from Null values. I
    believe the correct syntax for you is "If IsNull(Me.textbox2)=true".

    --
    Luiz Cláudio C. V. Rocha
    Coordenador de Projetos FórumAccess
    São Paulo - Brasil
    MVP Office
    http://www.msmvps.com/officedev


    "red6000" wrote:

    > Hi,
    >
    > I have a button which open my InputForm using the 'OpenForm' action and
    > 'Add' datamode so that I can only add a new records to my table.
    >
    > The Input Form has various Text Box's (for arguments sake named TextBox1 to
    > 5).
    >
    > I have disabled the 'close' button on the form and have a command button
    > which run's the standard close form macro.
    >
    > However what I would like to do is instead of using the default close form
    > macro is to have VBA code that:
    >
    > ****Strart Code
    > If TextBox1.Value = "yes" and TextBox2.value="" and TextBox3.value="" and
    > TextBox4.value="" and TextBox5.value="" then
    > MsgBox = ("You must enter at least one value in TB 2 to 5")
    > Exit Sub
    > End if
    > InputForm.Close
    > ****End Code
    >
    > Is this possible. What concerns me (and why I'm not sure what to do) is
    > what if the user has input more than one new record, how can I write the
    > code to validate new each record? Perhaps the code needs to also be on the
    > event of clicking the NewRecord button?
    >
    > Hope that makes sense.
    >
    > Thanks for any help or advice.
    >
    >
    >
     
  4. red6000

    red6000
    Expand Collapse
    Guest

    Thanks, I'm almost there with it, but I'm getting a error messagebox that
    I'd prefer not to be displayed. The 2 messagesare:

    WARNING MESSAGE
    You can't save this record at this time
    Database may have encountered an error while trying to save the record etc
    etc

    If I choose Yes then the form closes(which I don't want)
    If I choose No then I get the 'ACTION FAILED' error message with HALT
    buttons (if that makes sense).

    My 2 bits of code are:

    Private Sub Form_BeforeUpdate(Cancel As Integer)

    If Correct.Value = "No" And IsNull(Me.pc1) = True And IsNull(Me.pc2) = True
    And IsNull(Me.pc3) = True And IsNull(Me.pc4) = True And IsNull(Me.pc5) =
    True And IsNull(Me.ac1) = True And IsNull(Me.ac2) = True And IsNull(Me.ac3)
    = True And IsNull(Me.ac4) = True And IsNull(Me.ac5) = True Then
    Cancel = True
    MsgBox ("You have marked the case as wrong, but not entered any error
    codes!!!")
    End If

    End Sub

    Private Sub SaveAndClose_Click()

    On Error GoTo Err_SaveAndClose_Click
    If Correct.Value = "No" And IsNull(Me.pc1) = True And IsNull(Me.pc2) = True
    And IsNull(Me.pc3) = True And IsNull(Me.pc4) = True And IsNull(Me.pc5) =
    True And IsNull(Me.ac1) = True And IsNull(Me.ac2) = True And IsNull(Me.ac3)
    = True And IsNull(Me.ac4) = True And IsNull(Me.ac5) = True Then
    Cancel = True
    Exit Sub
    End If

    Dim stDocName As String
    stDocName = "OpenInputForm.CloseDataForm"
    DoCmd.RunMacro stDocName

    Exit_SaveAndClose_Click:
    Exit Sub

    Err_SaveAndClose_Click:
    MsgBox Err.Description
    Resume Exit_SaveAndClose_Click

    End Sub



    "Luiz Cláudio C. V. Rocha" <LuizCludioCVRocha@discussions.microsoft.com>
    wrote in message news:B5EE4C3E-AEA2-4852-97E3-96D46F6A748E@microsoft.com...
    > Hi red2006,
    >
    > your form is bound to a table, correct?
    >
    > So you can use Form_BeforeUpdate event to run your routine. BeforeUpdate
    > event can be cancelled if the conditions are not met:
    >
    > If "your conditions" = False Then
    > Cancel=True
    > MsgBox "Your message"
    > 'Me.Undo (optional)
    > End If
    >
    > Remember also that empty strings ("") are different from Null values. I
    > believe the correct syntax for you is "If IsNull(Me.textbox2)=true".
    >
    > --
    > Luiz Cláudio C. V. Rocha
    > Coordenador de Projetos FórumAccess
    > São Paulo - Brasil
    > MVP Office
    > http://www.msmvps.com/officedev
    >
    >
    > "red6000" wrote:
    >
    >> Hi,
    >>
    >> I have a button which open my InputForm using the 'OpenForm' action and
    >> 'Add' datamode so that I can only add a new records to my table.
    >>
    >> The Input Form has various Text Box's (for arguments sake named TextBox1
    >> to
    >> 5).
    >>
    >> I have disabled the 'close' button on the form and have a command button
    >> which run's the standard close form macro.
    >>
    >> However what I would like to do is instead of using the default close
    >> form
    >> macro is to have VBA code that:
    >>
    >> ****Strart Code
    >> If TextBox1.Value = "yes" and TextBox2.value="" and TextBox3.value=""
    >> and
    >> TextBox4.value="" and TextBox5.value="" then
    >> MsgBox = ("You must enter at least one value in TB 2 to 5")
    >> Exit Sub
    >> End if
    >> InputForm.Close
    >> ****End Code
    >>
    >> Is this possible. What concerns me (and why I'm not sure what to do) is
    >> what if the user has input more than one new record, how can I write the
    >> code to validate new each record? Perhaps the code needs to also be on
    >> the
    >> event of clicking the NewRecord button?
    >>
    >> Hope that makes sense.
    >>
    >> Thanks for any help or advice.
    >>
    >>
    >>
     
  5. Luiz Cláudio C. V. Rocha

    Luiz Cláudio C. V. Rocha
    Expand Collapse
    Guest

    If your record is in edit mode and you close the form, Access will try to
    save the record, so you don't need to ask him to save and close, just to
    close (BeforeUpdate event will be fired anyway).

    You can create a private function to check whether your conditions are true:

    Private Function CanSaveRec() As Boolean
    If Correct.Value = "No" And IsNull(Me.pc1) = True And IsNull(Me.pc2) = True _
    And IsNull(Me.pc3) = True And IsNull(Me.pc4) = True And _
    IsNull(Me.pc5) = True And IsNull(Me.ac1) = True And IsNull(Me.ac2) = True _
    And IsNull(Me.ac3) = True And IsNull(Me.ac4) = True And _
    IsNull(Me.ac5) = True Then
    CanSaveRec=False
    MsgBox "You have marked the case as wrong, but not entered any error
    codes!!!"
    Else
    CanSaveRecord=True
    End Function

    BeforeUpdate will be like this:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    If CanSaveRecord() = False Then
    Cancel=true
    End If
    End Sub

    And button click will be like this:

    Private Sub SaveAndClose_Click()
    If CanSaveRec()=True Then
    DoCmd.Close
    End If
    End Sub


    --
    Luiz Cláudio C. V. Rocha
    Coordenador de Projetos FórumAccess
    São Paulo - Brasil
    MVP Office
    http://www.msmvps.com/officedev


    "red6000" wrote:

    > Thanks, I'm almost there with it, but I'm getting a error messagebox that
    > I'd prefer not to be displayed. The 2 messagesare:
    >
    > WARNING MESSAGE
    > You can't save this record at this time
    > Database may have encountered an error while trying to save the record etc
    > etc
    >
    > If I choose Yes then the form closes(which I don't want)
    > If I choose No then I get the 'ACTION FAILED' error message with HALT
    > buttons (if that makes sense).
    >
    > My 2 bits of code are:
    >
    > Private Sub Form_BeforeUpdate(Cancel As Integer)
    >
    > If Correct.Value = "No" And IsNull(Me.pc1) = True And IsNull(Me.pc2) = True
    > And IsNull(Me.pc3) = True And IsNull(Me.pc4) = True And IsNull(Me.pc5) =
    > True And IsNull(Me.ac1) = True And IsNull(Me.ac2) = True And IsNull(Me.ac3)
    > = True And IsNull(Me.ac4) = True And IsNull(Me.ac5) = True Then
    > Cancel = True
    > MsgBox ("You have marked the case as wrong, but not entered any error
    > codes!!!")
    > End If
    >
    > End Sub
    >
    > Private Sub SaveAndClose_Click()
    >
    > On Error GoTo Err_SaveAndClose_Click
    > If Correct.Value = "No" And IsNull(Me.pc1) = True And IsNull(Me.pc2) = True
    > And IsNull(Me.pc3) = True And IsNull(Me.pc4) = True And IsNull(Me.pc5) =
    > True And IsNull(Me.ac1) = True And IsNull(Me.ac2) = True And IsNull(Me.ac3)
    > = True And IsNull(Me.ac4) = True And IsNull(Me.ac5) = True Then
    > Cancel = True
    > Exit Sub
    > End If
    >
    > Dim stDocName As String
    > stDocName = "OpenInputForm.CloseDataForm"
    > DoCmd.RunMacro stDocName
    >
    > Exit_SaveAndClose_Click:
    > Exit Sub
    >
    > Err_SaveAndClose_Click:
    > MsgBox Err.Description
    > Resume Exit_SaveAndClose_Click
    >
    > End Sub
    >
    >
    >
    > "Luiz Cláudio C. V. Rocha" <LuizCludioCVRocha@discussions.microsoft.com>
    > wrote in message news:B5EE4C3E-AEA2-4852-97E3-96D46F6A748E@microsoft.com...
    > > Hi red2006,
    > >
    > > your form is bound to a table, correct?
    > >
    > > So you can use Form_BeforeUpdate event to run your routine. BeforeUpdate
    > > event can be cancelled if the conditions are not met:
    > >
    > > If "your conditions" = False Then
    > > Cancel=True
    > > MsgBox "Your message"
    > > 'Me.Undo (optional)
    > > End If
    > >
    > > Remember also that empty strings ("") are different from Null values. I
    > > believe the correct syntax for you is "If IsNull(Me.textbox2)=true".
    > >
    > > --
    > > Luiz Cláudio C. V. Rocha
    > > Coordenador de Projetos FórumAccess
    > > São Paulo - Brasil
    > > MVP Office
    > > http://www.msmvps.com/officedev
    > >
    > >
    > > "red6000" wrote:
    > >
    > >> Hi,
    > >>
    > >> I have a button which open my InputForm using the 'OpenForm' action and
    > >> 'Add' datamode so that I can only add a new records to my table.
    > >>
    > >> The Input Form has various Text Box's (for arguments sake named TextBox1
    > >> to
    > >> 5).
    > >>
    > >> I have disabled the 'close' button on the form and have a command button
    > >> which run's the standard close form macro.
    > >>
    > >> However what I would like to do is instead of using the default close
    > >> form
    > >> macro is to have VBA code that:
    > >>
    > >> ****Strart Code
    > >> If TextBox1.Value = "yes" and TextBox2.value="" and TextBox3.value=""
    > >> and
    > >> TextBox4.value="" and TextBox5.value="" then
    > >> MsgBox = ("You must enter at least one value in TB 2 to 5")
    > >> Exit Sub
    > >> End if
    > >> InputForm.Close
    > >> ****End Code
    > >>
    > >> Is this possible. What concerns me (and why I'm not sure what to do) is
    > >> what if the user has input more than one new record, how can I write the
    > >> code to validate new each record? Perhaps the code needs to also be on
    > >> the
    > >> event of clicking the NewRecord button?
    > >>
    > >> Hope that makes sense.
    > >>
    > >> Thanks for any help or advice.
    > >>
    > >>
    > >>

    >
    >
    >
     
  6. red6000

    red6000
    Expand Collapse
    Guest

    All sorted, just me being stupid and not referring my OnClick event to the
    VBA code.

    Thanks for all the help.

    "red6000" <red1000002001@yahoo.com> wrote in message
    news:44ba93d5$0$3514$ed2619ec@ptn-nntp-reader01.plus.net...
    > Thanks, I'm almost there with it, but I'm getting a error messagebox that
    > I'd prefer not to be displayed. The 2 messagesare:
    >
    > WARNING MESSAGE
    > You can't save this record at this time
    > Database may have encountered an error while trying to save the record etc
    > etc
    >
    > If I choose Yes then the form closes(which I don't want)
    > If I choose No then I get the 'ACTION FAILED' error message with HALT
    > buttons (if that makes sense).
    >
    > My 2 bits of code are:
    >
    > Private Sub Form_BeforeUpdate(Cancel As Integer)
    >
    > If Correct.Value = "No" And IsNull(Me.pc1) = True And IsNull(Me.pc2) =
    > True And IsNull(Me.pc3) = True And IsNull(Me.pc4) = True And
    > IsNull(Me.pc5) = True And IsNull(Me.ac1) = True And IsNull(Me.ac2) = True
    > And IsNull(Me.ac3) = True And IsNull(Me.ac4) = True And IsNull(Me.ac5) =
    > True Then
    > Cancel = True
    > MsgBox ("You have marked the case as wrong, but not entered any error
    > codes!!!")
    > End If
    >
    > End Sub
    >
    > Private Sub SaveAndClose_Click()
    >
    > On Error GoTo Err_SaveAndClose_Click
    > If Correct.Value = "No" And IsNull(Me.pc1) = True And IsNull(Me.pc2) =
    > True And IsNull(Me.pc3) = True And IsNull(Me.pc4) = True And
    > IsNull(Me.pc5) = True And IsNull(Me.ac1) = True And IsNull(Me.ac2) = True
    > And IsNull(Me.ac3) = True And IsNull(Me.ac4) = True And IsNull(Me.ac5) =
    > True Then
    > Cancel = True
    > Exit Sub
    > End If
    >
    > Dim stDocName As String
    > stDocName = "OpenInputForm.CloseDataForm"
    > DoCmd.RunMacro stDocName
    >
    > Exit_SaveAndClose_Click:
    > Exit Sub
    >
    > Err_SaveAndClose_Click:
    > MsgBox Err.Description
    > Resume Exit_SaveAndClose_Click
    >
    > End Sub
    >
    >
    >
    > "Luiz Cláudio C. V. Rocha" <LuizCludioCVRocha@discussions.microsoft.com>
    > wrote in message
    > news:B5EE4C3E-AEA2-4852-97E3-96D46F6A748E@microsoft.com...
    >> Hi red2006,
    >>
    >> your form is bound to a table, correct?
    >>
    >> So you can use Form_BeforeUpdate event to run your routine. BeforeUpdate
    >> event can be cancelled if the conditions are not met:
    >>
    >> If "your conditions" = False Then
    >> Cancel=True
    >> MsgBox "Your message"
    >> 'Me.Undo (optional)
    >> End If
    >>
    >> Remember also that empty strings ("") are different from Null values. I
    >> believe the correct syntax for you is "If IsNull(Me.textbox2)=true".
    >>
    >> --
    >> Luiz Cláudio C. V. Rocha
    >> Coordenador de Projetos FórumAccess
    >> São Paulo - Brasil
    >> MVP Office
    >> http://www.msmvps.com/officedev
    >>
    >>
    >> "red6000" wrote:
    >>
    >>> Hi,
    >>>
    >>> I have a button which open my InputForm using the 'OpenForm' action and
    >>> 'Add' datamode so that I can only add a new records to my table.
    >>>
    >>> The Input Form has various Text Box's (for arguments sake named TextBox1
    >>> to
    >>> 5).
    >>>
    >>> I have disabled the 'close' button on the form and have a command button
    >>> which run's the standard close form macro.
    >>>
    >>> However what I would like to do is instead of using the default close
    >>> form
    >>> macro is to have VBA code that:
    >>>
    >>> ****Strart Code
    >>> If TextBox1.Value = "yes" and TextBox2.value="" and TextBox3.value=""
    >>> and
    >>> TextBox4.value="" and TextBox5.value="" then
    >>> MsgBox = ("You must enter at least one value in TB 2 to 5")
    >>> Exit Sub
    >>> End if
    >>> InputForm.Close
    >>> ****End Code
    >>>
    >>> Is this possible. What concerns me (and why I'm not sure what to do) is
    >>> what if the user has input more than one new record, how can I write the
    >>> code to validate new each record? Perhaps the code needs to also be on
    >>> the
    >>> event of clicking the NewRecord button?
    >>>
    >>> Hope that makes sense.
    >>>
    >>> Thanks for any help or advice.
    >>>
    >>>
    >>>

    >
    >
     

Share This Page