Cancelling an update on a continuous form based on another value

[Julia B]

Hi, I've got a continuous form and want a before update event on a field to
cancel depending on the value in another field. The problem I've got is that
whatever I try I can't get the value of the other field during the before
update event:

Here's my code:

Private Sub PNStored_BeforeUpdate(Cancel As Integer)
'if the part number's been changed, then make sure that the record can
be changed
Dim rs As Recordset
Set rs = Me.RecordsetClone
Dim strSearch As String
'I get the problem here - I get a runtime error 2108 you must save the field
first
Me.txtStoredRecordID.SetFocus
strSearch = str(Me.txtStoredRecordID.value)
rs.FindFirst "PartNumbers.RecordID = " & strSearch
Me.Bookmark = rs.Bookmark
If IsNull(rs![PartNumbersReceived.Status]) Then
currentRecdStatus = ""
Else
currentRecdStatus = rs![PartNumbersReceived.Status]
End If
currentStoredStatus = rs![PartNumbers.Status]

If currentRecdStatus <> "New" Then
MsgBox "You cannot change the part number as this is not a new
record!!", vbExclamation, "Error"
Cancel = True
Me.PNStored.SetFocus
ElseIf currentRecdStatus = "New" And currentStoredStatus = "Live" Then
MsgBox "You cannot change the part number as this is not a new
record!!", vbExclamation, "Error"
Cancel = True
Me.PNStored.SetFocus
Else
Me.DescStored.SetFocus
End If
End Sub

Thanks in advance.
Julia

 

[Allen Browne]

Julia, can I suggest a slightly different approach?
Consider using the BeforeUpdate event of the *form*, instead of the event of
the control.

Ultimately, that's the best way to perform any validation that involves
comparing fields. You don't know what order the user will enter the data, so
using Form_BeforeUpdate() runs the check once they have entered both, and
blocks the entry if it's not right.

This example cancels the update if the Animal is a dog, but the
NumberOfKittens is more than zero:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Animal = "Dog" AND Me.NumberOfKittens > 0 Then
Cancel = True
MsgBox "A dog can't have kittens."
'Me.Undo
End If
End Sub

I didn't actually follow your specific code which seems to be trying to
change records before the record to another one before this record is saved.
(Naturally, that can't work.)

 

[Julia B]

Allen

Thanks very much for this. I've implemented your suggestion and it works
perfectly!

Julia

Julia, can I suggest a slightly different approach?
Consider using the BeforeUpdate event of the *form*, instead of the event of
the control.

Ultimately, that's the best way to perform any validation that involves
comparing fields. You don't know what order the user will enter the data, so
using Form_BeforeUpdate() runs the check once they have entered both, and
blocks the entry if it's not right.

This example cancels the update if the Animal is a dog, but the
NumberOfKittens is more than zero:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Animal = "Dog" AND Me.NumberOfKittens > 0 Then
Cancel = True
MsgBox "A dog can't have kittens."
'Me.Undo
End If
End Sub

I didn't actually follow your specific code which seems to be trying to
change records before the record to another one before this record is saved.
(Naturally, that can't work.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi, I've got a continuous form and want a before update event on a field
to
cancel depending on the value in another field. The problem I've got is
that
whatever I try I can't get the value of the other field during the before
update event:

Here's my code:

Private Sub PNStored_BeforeUpdate(Cancel As Integer)
'if the part number's been changed, then make sure that the record can
be changed
Dim rs As Recordset
Set rs = Me.RecordsetClone
Dim strSearch As String
'I get the problem here - I get a runtime error 2108 you must save the
field
first
Me.txtStoredRecordID.SetFocus
strSearch = str(Me.txtStoredRecordID.value)
rs.FindFirst "PartNumbers.RecordID = " & strSearch
Me.Bookmark = rs.Bookmark
If IsNull(rs![PartNumbersReceived.Status]) Then
currentRecdStatus = ""
Else
currentRecdStatus = rs![PartNumbersReceived.Status]
End If
currentStoredStatus = rs![PartNumbers.Status]

If currentRecdStatus <> "New" Then
MsgBox "You cannot change the part number as this is not a new
record!!", vbExclamation, "Error"
Cancel = True
Me.PNStored.SetFocus
ElseIf currentRecdStatus = "New" And currentStoredStatus = "Live" Then
MsgBox "You cannot change the part number as this is not a new
record!!", vbExclamation, "Error"
Cancel = True
Me.PNStored.SetFocus
Else
Me.DescStored.SetFocus
End If
End Sub

Thanks in advance.
Julia