Issa S.
asked on
BeforeInsert Event: I don't want to insert a new record based on the value entered in a field.
Hi All,,,
My sample form is based on a tale straight
I don't want to insert a new record based on the value entered in a field for the new record.
I tried the following, as an example, but it inserts straight upon the first key hit in the "Last Name Field" and the If is useless!?
Private Sub Form_BeforeInsert(Cancel As Integer)
If Me!LastName = "AA" Then
MsgBox ("AA is not allowed"), vbExclamation, " VALIDATING LAST NAME ENTRY"
Cancel = True
End If
End Sub
Help please
My sample form is based on a tale straight
I don't want to insert a new record based on the value entered in a field for the new record.
I tried the following, as an example, but it inserts straight upon the first key hit in the "Last Name Field" and the If is useless!?
Private Sub Form_BeforeInsert(Cancel As Integer)
If Me!LastName = "AA" Then
MsgBox ("AA is not allowed"), vbExclamation, " VALIDATING LAST NAME ENTRY"
Cancel = True
End If
End Sub
Help please
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you're welcome, Issa ~ happy to help
ASKER
OOPS
When I copied the same code to my real form, it is NOT working :
- New record is inserted and the MSG does not evaluate and the UNDO does not undo... especially when the record set is empty and this is the first record.
- My original example still works well in its test place.
Can you kindly inspect my real code below. May be you spot something that I can't.
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim curMax As Currency
curMax = Me.Parent.MFAmount
If Me.SFSumAmounts - Me.AmountValB4 + Me.amount > curMax Then
MsgBox ("Yooo Yooohh ! That's too much. Max Permitted = " & curMax & " Press Esc. and correct the Amount."), vbExclamation, " VALIDATING ACCUMULATED PN AMOUTS "
Cancel = True
UNDO
End If
End Sub
Thanks again
When I copied the same code to my real form, it is NOT working :
- New record is inserted and the MSG does not evaluate and the UNDO does not undo... especially when the record set is empty and this is the first record.
- My original example still works well in its test place.
Can you kindly inspect my real code below. May be you spot something that I can't.
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim curMax As Currency
curMax = Me.Parent.MFAmount
If Me.SFSumAmounts - Me.AmountValB4 + Me.amount > curMax Then
MsgBox ("Yooo Yooohh ! That's too much. Max Permitted = " & curMax & " Press Esc. and correct the Amount."), vbExclamation, " VALIDATING ACCUMULATED PN AMOUTS "
Cancel = True
UNDO
End If
End Sub
Thanks again
try using the CONTROL BeforeUpdate event, not the form BeforeUpdate event
however, it looks like other things are happening. Please explain the logic, thank you
however, it looks like other things are happening. Please explain the logic, thank you
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OMG,,,
You are always right.
Because when it is the 1st record, the values in the expression were having NULLs. So I changed the If and the Expression to
If Nz(Me.SFSumAmounts, 0) - Nz(Me.AmountValB4, 0) + Nz(Me.amount, 0) > curMax Then
MsgBox ("Yooo Yooohh ! That's too much. Max Permitted = " & curMax & "
Then everything is fine.
Many Many Thanks again
You are always right.
Because when it is the 1st record, the values in the expression were having NULLs. So I changed the If and the Expression to
If Nz(Me.SFSumAmounts, 0) - Nz(Me.AmountValB4, 0) + Nz(Me.amount, 0) > curMax Then
MsgBox ("Yooo Yooohh ! That's too much. Max Permitted = " & curMax & "
Then everything is fine.
Many Many Thanks again
you're welcome, Issa ~ are you good now?
You're welcome if it was me you were thanking.
ASKER
I meant both of you.
One last favor on this: Why the undo in my first sample DB expression is showing "Undo" (mixed case as expected) while in the second expression (from my real DB) is showing "UNDO" capital case while I typed undo in the two cases. Is the "UNDO" a key word or what.
In both expressions, it is working well still !!!!
One last favor on this: Why the undo in my first sample DB expression is showing "Undo" (mixed case as expected) while in the second expression (from my real DB) is showing "UNDO" capital case while I typed undo in the two cases. Is the "UNDO" a key word or what.
In both expressions, it is working well still !!!!
As I already said - Undoing the entire form is punitive -. It is much more user-friendly to not undo at all or to only undo the field in error. I never use undo except in two situations.
1. the user doesn't have authority to make any changes.
2. the user answered "no" to a "save" prompt.
Me.Undo - clears ALL entries on the form.
Me.txtFieldName.Undo - clears only the specific control
1. the user doesn't have authority to make any changes.
2. the user answered "no" to a "save" prompt.
Me.Undo - clears ALL entries on the form.
Me.txtFieldName.Undo - clears only the specific control
Pat has good points -- it is better not to create a record that may be undone. Perhaps you want to use an unbound control to get this information?
ASKER
Thanks very much for your help.
Very comprehensive.
Very comprehensive.
you're welcome, Issa ~ happy to help
ASKER
Long time no see !
Thanks for your prompt reply.
I did the change and added the UNDO:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me!LastName = "AA" Then
MsgBox ("AA is not allowed"), vbExclamation, " VALIDATING LAST NAME ENTRY"
Cancel = True
Undo
End If
End Sub
Working beautifully. Many many thanks.