Prev: Display Name of Field from 1 form in an unbound box on another for
Next: Column Orders
From: aerobar2 on 20 May 2010 17:56
I have a form where the user must enter a value in at least 1 of 15 boxes.
If all of the values are null then I do not want the record added to the
table.

Once the user is finished entering their information, they click a Submit
button.

Private Sub cmdSubmit_Click()

If IsNull(countClaim) And IsNull(countContr) And IsNull(countElect) And
IsNull(countElev) And IsNull(countRev) _
And IsNull(countEs) And IsNull(countFAS) And IsNull(countInt) And
IsNull(countMBA) And IsNull(countOther) _
And IsNull(countPlmb) And IsNull(countUBI) And IsNull(countAudit)
And IsNull(countDOSH) And IsNull(countIME) Then
MsgBox "You must select a number before submit."
Exit Sub
Else
DoCmd.GoToRecord
End Sub

The problem is regardless of the code above, the record is being added to
the table.

Any suggestions?

Beth
From: John W. Vinson on 20 May 2010 18:39
On Thu, 20 May 2010 14:56:03 -0700, aerobar2
<aerobar2(a)discussions.microsoft.com> wrote:

>I have a form where the user must enter a value in at least 1 of 15 boxes.
>If all of the values are null then I do not want the record added to the
>table.
>
>Once the user is finished entering their information, they click a Submit
>button.
>
>Private Sub cmdSubmit_Click()
>
>If IsNull(countClaim) And IsNull(countContr) And IsNull(countElect) And
>IsNull(countElev) And IsNull(countRev) _
> And IsNull(countEs) And IsNull(countFAS) And IsNull(countInt) And
>IsNull(countMBA) And IsNull(countOther) _
> And IsNull(countPlmb) And IsNull(countUBI) And IsNull(countAudit)
>And IsNull(countDOSH) And IsNull(countIME) Then
> MsgBox "You must select a number before submit."
> Exit Sub
>Else
> DoCmd.GoToRecord
>End Sub
>
>The problem is regardless of the code above, the record is being added to
>the table.
>
>Any suggestions?

Put the code in the Form's BeforeUpdate event instead, and set Cancel to True
if the fields are all null.

Note that textboxes can be blank without being NULL - if the user enters data
and backspaces over it, it will contain a zero length string, which acts like
a NULL but isn't. Try:

Private Sub cmdSubmit_Click()
DoCmd.RunCommand acCmdSaveRecord ' write to disk, or try to
End Sub

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Me!countClaim & Me!countContr & Me!countElect & _
<all the other controls> & "" = "" Then
Cancel = True
MsgBox "You must select a number before submit", vbOKOnly
End If
End Sub

--

John W. Vinson [MVP]
 | 
Pages: 1
Prev: Display Name of Field from 1 form in an unbound box on another for
Next: Column Orders