VBA code for Command Button of Form

ExpandCollapse

Guest

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?

"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.
>
>
>

"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.
>>
>>
>>

ExpandCollapse

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

Well friends, I have finally done it, after many many years of trying, I have finally overcome fear. As I sit here on this sunday morning, devoid of any fear, looking out my shop window at the park opposite, at the children, at the families, I feel no fear.

About Us

Our community has been around for many years and pride ourselves on offering unbiased, critical discussion among people of all different backgrounds. We are working every day to make sure our community is one of the best.

Like us on Facebook

Support SPN

The management works very hard to make sure the community is running the best software, best designs, and all the other bells and whistles. We'd really appreciate your support!