VBA code for Command Button of Form

[red6000]

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.

 

[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]

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

 

[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]

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

Thanks for all the help.