Dont save Record

  • Thread starter Thread starter Bob Vance
  • Start date Start date
On my Close button on my form can I have
Dont save record if [tbpayment] = "0"
Click to expand...

Don't use the Close button for this; instead use the form's BeforeUpdate
event:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Me![tbpayment] = "0" then
Cancel = True
MsgBox "Record not saved, tbpayment is zero"
End If
End Sub

This assumes that you have a field (not a table, as the name might suggest)
named tbpayment, and that it is a Text field which could contain a text string
consisting of a single 0. If it's a Number or Currency field, remove the "
marks.
 
Brilliant John , I suppose I can change the message so I can have an option
to make payment. Thanks Bob

John W. Vinson said:
On my Close button on my form can I have
Dont save record if [tbpayment] = "0"
Click to expand...

Don't use the Close button for this; instead use the form's BeforeUpdate
event:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Me![tbpayment] = "0" then
Cancel = True
MsgBox "Record not saved, tbpayment is zero"
End If
End Sub

This assumes that you have a field (not a table, as the name might
suggest)
named tbpayment, and that it is a Text field which could contain a text
string
consisting of a single 0. If it's a Number or Currency field, remove the "
marks.
Click to expand...
 
Brilliant John , I suppose I can change the message so I can have an option
to make payment. Thanks Bob
Click to expand...

The message can be anything you like; if you want you can give the user the
choice of entering data:

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim iAns As Integer
If Me![tbpayment] = "0" then
Cancel = True
iAns = MsgBox("Record not saved, tbpayment is zero." _
& "Click OK to fix, Cancel to quit", vbOKCancel)
If iAns = vbOK Then
Me.tbpayment.SetFocus
Else
Me.Undo
End If
End If
End Sub
 
John W. Vinson said:
Brilliant John , I suppose I can change the message so I can have an
option
to make payment. Thanks Bob
Click to expand...

The message can be anything you like; if you want you can give the user
the
choice of entering data:

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim iAns As Integer
If Me![tbpayment] = "0" then
Cancel = True
iAns = MsgBox("Record not saved, tbpayment is zero." _
& "Click OK to fix, Cancel to quit", vbOKCancel)
If iAns = vbOK Then
Me.tbpayment.SetFocus
Else
Me.Undo
End If
End If
End Sub
Click to expand...

Thanks John, But OK is closing my form down when selected!
Regards Bob
 
John W. Vinson said:
Brilliant John , I suppose I can change the message so I can have an
option
to make payment. Thanks Bob
Click to expand...

The message can be anything you like; if you want you can give the user
the
choice of entering data:

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim iAns As Integer
If Me![tbpayment] = "0" then
Cancel = True
iAns = MsgBox("Record not saved, tbpayment is zero." _
& "Click OK to fix, Cancel to quit", vbOKCancel)
If iAns = vbOK Then
Me.tbpayment.SetFocus
Else
Me.Undo
End If
End If
End Sub
Click to expand...

Thanks John, But OK is closing my form down when selected!
Regards Bob
Click to expand...

Shouldn't, if you a) have this in the form's BeforeUpdate event (rather than
its Close event) and b) set Cancel to true.

If those are both the case, please post your entire actual code.
 
John W. Vinson said:
John W. Vinson said:
Brilliant John , I suppose I can change the message so I can have an
option
to make payment. Thanks Bob

The message can be anything you like; if you want you can give the user
the
choice of entering data:

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim iAns As Integer
If Me![tbpayment] = "0" then
Cancel = True
iAns = MsgBox("Record not saved, tbpayment is zero." _
& "Click OK to fix, Cancel to quit", vbOKCancel)
If iAns = vbOK Then
Me.tbpayment.SetFocus
Else
Me.Undo
End If
End If
End Sub
Click to expand...

Thanks John, But OK is closing my form down when selected!
Regards Bob
Click to expand...

Shouldn't, if you a) have this in the form's BeforeUpdate event (rather
than
its Close event) and b) set Cancel to true.

If those are both the case, please post your entire actual code.
Click to expand...
Thanks John this is my code

Private Sub Form_BeforeUpdate(Cancel As Integer)
'If Me![tbPaidAmount] = "0" Then
'Cancel = True
'MsgBox "Not Saved !!! Amount Paid is $0.00"
'End If
Dim iAns As Integer
If Me![tbPaidAmount] = "0" Then
Cancel = True
iAns = MsgBox("No Payment has been entered! " _
& "Click OK to Enter Payment or Cancel to quit", vbOKCancel)
If iAns = vbOK Then
Me.tbPaidAmount.SetFocus
Else
Me.Undo
End If
End If

End Sub
 
Thanks John this is my code

Private Sub Form_BeforeUpdate(Cancel As Integer)
'If Me![tbPaidAmount] = "0" Then
'Cancel = True
'MsgBox "Not Saved !!! Amount Paid is $0.00"
'End If
Dim iAns As Integer
If Me![tbPaidAmount] = "0" Then
Click to expand...

If the table field tbPaidAmount is a Number or Currency field (rather than
Text) get rid of the quotemarks. The text string "0" is NOT the same as a
number 0.
 
John W. Vinson said:
Thanks John this is my code

Private Sub Form_BeforeUpdate(Cancel As Integer)
'If Me![tbPaidAmount] = "0" Then
'Cancel = True
'MsgBox "Not Saved !!! Amount Paid is $0.00"
'End If
Dim iAns As Integer
If Me![tbPaidAmount] = "0" Then
Click to expand...

If the table field tbPaidAmount is a Number or Currency field (rather than
Text) get rid of the quotemarks. The text string "0" is NOT the same as a
number 0.
Cancel = True
iAns = MsgBox("No Payment has been entered! " _
& "Click OK to Enter Payment or Cancel to quit", vbOKCancel)
If iAns = vbOK Then
Me.tbPaidAmount.SetFocus
Else
Me.Undo
End If
End If

End Sub
Click to expand...
Click to expand...
Thanks John it was a currency field, but still did the same thing, I will
use my other code instead....Thanks Bob
 
John I have one problem with this code if I click into my sub form and not
entered an amount into tbPaidAmount I am betting my message
warning....Thanks Bob

John W. Vinson said:
Thanks John this is my code

Private Sub Form_BeforeUpdate(Cancel As Integer)
'If Me![tbPaidAmount] = "0" Then
'Cancel = True
'MsgBox "Not Saved !!! Amount Paid is $0.00"
'End If
Dim iAns As Integer
If Me![tbPaidAmount] = "0" Then
Click to expand...

If the table field tbPaidAmount is a Number or Currency field (rather than
Text) get rid of the quotemarks. The text string "0" is NOT the same as a
number 0.
Cancel = True
iAns = MsgBox("No Payment has been entered! " _
& "Click OK to Enter Payment or Cancel to quit", vbOKCancel)
If iAns = vbOK Then
Me.tbPaidAmount.SetFocus
Else
Me.Undo
End If
End If

End Sub
Click to expand...
Click to expand...
 
John I have one problem with this code if I click into my sub form and not
entered an amount into tbPaidAmount I am betting my message
warning....Thanks Bob
Click to expand...

Please post your actual code and the names and datatypes of the relevant
fields (*datatypes*, not Formats).
 
Thanks John,Actually this code is good as I can click into my subform and
still enter a Amount in tbPaidAmount
But if I dont enter an amount the ok selection will just close my form dowm
and not let me enter amount
Regards Bob
Private Sub Form_BeforeUpdate(Cancel As Integer)
'If Me![tbPaidAmount] = "0" Then
'Cancel = True
'MsgBox "Not Saved !!! Amount Paid is $0.00"
'End If
Dim iAns As Integer
If Me![tbPaidAmount] = 0 Then
Cancel = True
iAns = MsgBox("No Payment has been entered! " _
& "Click OK to Enter Payment or Cancel to quit", vbOKCancel)
If iAns = vbOK Then
Me.tbPaidAmount.SetFocus
Else
Me.Undo
End If
End If

End Sub
 
Thanks John,Actually this code is good as I can click into my subform and
still enter a Amount in tbPaidAmount
But if I dont enter an amount the ok selection will just close my form dowm
and not let me enter amount
Regards Bob
Private Sub Form_BeforeUpdate(Cancel As Integer)
'If Me![tbPaidAmount] = "0" Then
'Cancel = True
'MsgBox "Not Saved !!! Amount Paid is $0.00"
'End If
Dim iAns As Integer
If Me![tbPaidAmount] = 0 Then
Cancel = True
iAns = MsgBox("No Payment has been entered! " _
& "Click OK to Enter Payment or Cancel to quit", vbOKCancel)
If iAns = vbOK Then
Me.tbPaidAmount.SetFocus
Else
Me.Undo
End If
End If
Click to expand...

If nothing is entered, then tbPaidAmount is Null (not zero) so the IF
statement will not be satisfied, and the form will update and close normally.

You can trap zero *and* Null values by using

If NZ(Me!tbPaidAmount) = 0 Then
 
Thanks John , But tbPaidAmount has a default value of 0 , just so you can
see where to enter the dollar payment.........Regards Bob

John W. Vinson said:
Thanks John,Actually this code is good as I can click into my subform and
still enter a Amount in tbPaidAmount
But if I dont enter an amount the ok selection will just close my form
dowm
and not let me enter amount
Regards Bob
Private Sub Form_BeforeUpdate(Cancel As Integer)
'If Me![tbPaidAmount] = "0" Then
'Cancel = True
'MsgBox "Not Saved !!! Amount Paid is $0.00"
'End If
Dim iAns As Integer
If Me![tbPaidAmount] = 0 Then
Cancel = True
iAns = MsgBox("No Payment has been entered! " _
& "Click OK to Enter Payment or Cancel to quit", vbOKCancel)
If iAns = vbOK Then
Me.tbPaidAmount.SetFocus
Else
Me.Undo
End If
End If
Click to expand...

If nothing is entered, then tbPaidAmount is Null (not zero) so the IF
statement will not be satisfied, and the form will update and close
normally.

You can trap zero *and* Null values by using

If NZ(Me!tbPaidAmount) = 0 Then
Click to expand...
 
Back
Top