Form-CmdAdd & If IsNull msg bx (add button still adds a record with an empty fields to my table)

[GinaFlan]

Help!!!! I have searched and searched. I am building a form for work for our different departments. When testing it. I hit the add button. A message box pops up Please add Fiscal Year! But the record is still added to the table with the Fiscal year missing. The only way the form recognizes the Fiscal year is missing is if I put the code under CmdAdd_Click(). I have read about putting the code in before update and such. Maybe I need to remove all of the if thens out of CmdAdd and then test placing the if then codes somewhere else. I really need help, no one else around here is very familiar to Access I am so close to finishing this form if I could just get this part to work.

Private Sub CmdAdd_Click()
'Add data to form
CurrentDb.Execute "INSERT INTO Scorecards (ForTheMonth,FiscalYear,Groups,Contact,Goals,Bench marks,Results,Comments)" & _
"VALUES('" & Me.ComboMonth & "','" & Me.ComboFiscal & "','" & Me.ComboDepartment & "','" & Me.txtContact & "','" & Me.ComboGoals & "','" & Me.txtBenchmarks & "','" & Me.TxtActualBenchmark & "','" & Me.txtComments & "');"
'message box


If IsNull(Me.ComboFiscal) Then ' No business unit
MsgBox ("Please add Fiscal Year!"), vbOKCancel ' Tell user
Me.ComboFiscal.SetFocus ' Focus the control
Exit Sub ' Exit the method
End If ' End the IsNull test
If IsNull(Me.txtContact) Then ' No business unit
MsgBox ("Please add Contact!"), vbOKCancel ' Tell user
Me.txtContact.SetFocus ' Focus the control
Exit Sub ' Exit the method
End If ' End the IsNull test

If IsNull(Me.TxtActualBenchmark) Then ' No business unit
MsgBox ("Please add Results!"), vbOKCancel
Me.TxtActualBenchmark.Undo ' Undo
Me.TxtActualBenchmark.SetFocus ' Focus the control
Exit Sub ' Exit the method
End If ' End the IsNull test

If IsNull(Me.ComboGoals) Then ' No business unit
MsgBox ("Please add Goals!"), vbOKCancel ' Tell user
Me.ComboGoals.SetFocus ' Focus the control
Exit Sub ' Exit the method
End If ' End the IsNull test









If Me.ComboMonth = "January" And TxtActualBenchmark.Value = "N/A" Then
MsgBox "Sorry but value cannot be N/A for the selected month"
Cancel = True
End If
If Me.ComboMonth = "February" And TxtActualBenchmark.Value = "N/A" Then
MsgBox "Sorry but value cannot be N/A for the selected month"
Cancel = True
End If
If Me.ComboMonth = "April" And TxtActualBenchmark.Value = "N/A" Then
MsgBox "Sorry but value cannot be N/A for the selected month"
Cancel = True
End If
If Me.ComboMonth = "May" And TxtActualBenchmark.Value = "N/A" Then
MsgBox "Sorry but value cannot be N/A for the selected month"
Cancel = True
End If
If Me.ComboMonth = "July" And TxtActualBenchmark.Value = "N/A" Then
MsgBox "Sorry but value cannot be N/A for the selected month"
Cancel = True
End If
If Me.ComboMonth = "August" And TxtActualBenchmark.Value = "N/A" Then
MsgBox "Sorry but value cannot be N/A for the selected month"
Cancel = True
End If
If Me.ComboMonth = "October" And TxtActualBenchmark.Value = "N/A" Then
MsgBox "Sorry but value cannot be N/A for the selected month"
Cancel = True
End If
If Me.ComboMonth = "November" And TxtActualBenchmark.Value = "N/A" Then
MsgBox "Sorry but value cannot be N/A for the selected month"
Cancel = True
End If


If IsNumeric(Me.TxtActualBenchmark) = "False" _
And txtBenchmarks.Value = "No negatives" Then
Cancel = True
Me.TxtActualBenchmark.Undo
MsgBox "Please enter a numeric value ", _
vbInformation, "Missing Information"
End If



'Requery Form
frmscorecardssub.Form.Requery
'Clear Form


CmdClear_Click
End Sub

Private Sub CmdClear_Click()

Me.ComboGoals = ""
Me.txtBenchmarks = ""
Me.txtContact = ""
Me.ComboDepartment = ""

Me.TxtActualBenchmark = ""
Me.txtComments = ""
Me.ComboGoals.SetFocus

End Sub
Private Sub CmdClose_Click()
DoCmd.Close
End Sub
Private Sub ComboDepartment_AfterUpdate()
Me.ComboGoals.Requery
Me.frmscorecardssub.Form.Requery
End Sub
Private Sub ComboDepartment_Change()
Me.txtContact.Value = Me.ComboDepartment.Column(1)
End Sub
Private Sub ComboFiscal_BeforeUpdate(Cancel As Integer)
End Sub
Private Sub ComboGoals_Change()
Me.txtBenchmarks.Value = Me.ComboGoals.Column(1)
End Sub


Private Sub Form_BeforeUpdate(Cancel As Integer)
End Sub
Private Sub Form_Dirty(Cancel As Integer)
If IsNull(Me.ComboMonth) And IsNull(Me.ComboFiscal) And IsNull(Me.txtContact) And IsNull(Me.ComboGoals) And IsNull(Me.txtBenchmarks) And IsNull(Me.TxtActualBenchmark) Then
Me.CmdAdd.Enabled = False
Else
Me.CmdAdd.Enabled = True
End If
End Sub
Private Sub Form_Load()
'Clear Form
CmdClear_Click
End Sub

Private Sub TxtActualBenchmark_BeforeUpdate(Cancel As Integer)
If IsNull(Me.TxtActualBenchmark) Then ' No business unit
MsgBox "Please add Results!"
Me.TxtActualBenchmark.Undo ' Undo
Me.TxtActualBenchmark.SetFocus ' Focus the control
Exit Sub ' Exit the method
End If ' End the IsNull test
If IsNumeric(Me.TxtActualBenchmark) = "False" _
And txtBenchmarks.Value = "No negatives" Then
Cancel = True
Me.TxtActualBenchmark.Undo
MsgBox "Please enter a numeric value ", _
vbInformation, "Missing Information"
End If
End Sub

[GinaFlan]

My form is not bound to the table they are adding records to, the table they are adding records to is an empty table.

[John_G]

Hi -


Your Add_Click code is adding (inserting) the new record before any of the validity checks are done. Move the INSERT statement to somewhere AFTER all the checks are done, so that the new record is created only if all the data is valid.

Note too that Cancel = True is meaningless in the Add_Click code - an OnClick event cannot be Canceled. Use Exit Sub as you did with the other validation checks.

[GinaFlan]

Yay it works so far Thank youThank you Thank you

[John_G]

Another happy user! Glad I could help.

[GinaFlan]

I just need one more piece of code. The underlined part is wrong. I need help correcting the code. If there is a value in Goals then results can't be blank. I need to say

If IsNull(Me.TxtActualBenchmark) And (Me.ComboGoals) Is not Null Then
MsgBox ("Please add Results!"), vbOKCancel
Me.TxtActualBenchmark.SetFocus
Exit Sub
End If

[GinaFlan]

Results (TxtAcutalBenchmark) is not bound so I figure I need to base on the value in another field for access to recognize it

[GinaFlan]

I got it to work with ""

If (Me.TxtActualBenchmark.Value = "") Then ' No business unit
MsgBox ("Please add Results!"), vbOKCancel ' Tell user
Me.TxtActualBenchmark.SetFocus ' Focus the control
Exit Sub ' Exit the method
End If ' End the IsNull test

[Missinglinq]

A couple of things:

In VBA code

(Me.ComboGoals) Is not Null

should be

Not IsNull(Me.ComboGoals)

You also need to be aware that

If (Me.TxtActualBenchmark.Value = "")

is not checking to see if TxtActualBenchmark is Null, it's checking to see if it is a Zero-Length String, and Null and a ZLS are not the same! In Access, the majority of the time when a Control is 'empty,' it is Null. If you simply leave a Control 'empty' it is Null. If you enter data in a Control, then BackSpace over it, it is Null. If you Delete data from a Control, the Control is Null. As best I've been able to figure out, the only way to get a Control to contain a Zero-Length String is either to populate the Control through code with something like

Me.ControlName = ""

or to Import data from a program where Fields can contain ZLSs rather than Nulls.

Despite the unlikeliness of ZLSs, most experienced Access developers use code that addresses both possibilities. There are several hacks for doing this, with most developers preferring one or the other. I use

If Nz(Me.ControlName, "") = "" Then

others like

If (Me.ControlName & "") = "" Then

and there are probably others.

Linq ;0)>

[GinaFlan]

Ok I will try the If Nz(Me.ControlName, "") = "" Then
Thank you so much