BeforUpdate Code

[JamesJ]

I'm using the following code in the BeforeUpdate of the form
to confirm data changes:

Public Function ConfirmDataChange(Cancel As Integer)

Set frm = Screen.ActiveForm

Select Case MsgBox("Record(s) have been added or changed." _
& vbCrLf & "Save the Changes?", vbYesNo, "Record
Change")

Case vbYes 'go ahead and save.

frm.Requery

Case vbNo 'undo the changes.
Cancel = True
frm.Undo

End Select

End Function

I use the same code in several forms and have just added
the frm.Requery. Prior to adding this I wasn't getting an error.
Now I get the following error.

Run-time error '2115'
The Macro or Function set to the BeforeUpdate or ValidationRule
property for this field is preventing mydbname from saving data in the
field.

I have no idea which field it is refering to, and am assuming it is the
BeforeUpdate that is triggering this seeing I have nothing in the
ValidationRule form or table.

Any help will be appreciated,
James

 

[Dirk Goldgar]

I'm using the following code in the BeforeUpdate of the form
to confirm data changes:

Public Function ConfirmDataChange(Cancel As Integer)

Set frm = Screen.ActiveForm

Select Case MsgBox("Record(s) have been added or changed." _
& vbCrLf & "Save the Changes?", vbYesNo, "Record
Change")

Case vbYes 'go ahead and save.

frm.Requery

Case vbNo 'undo the changes.
Cancel = True
frm.Undo

End Select

End Function

I use the same code in several forms and have just added
the frm.Requery. Prior to adding this I wasn't getting an error.
Now I get the following error.

Run-time error '2115'
The Macro or Function set to the BeforeUpdate or ValidationRule
property for this field is preventing mydbname from saving data in the
field.

I have no idea which field it is refering to, and am assuming it is the
BeforeUpdate that is triggering this seeing I have nothing in the
ValidationRule form or table.

What is your intent in adding the Requery? The record is about to be saved,
and you're telling the form to requery itself before it has saved the
record. I'm guessing that is the problem. So why the Requery? If you need
it, I expect you could put it in the AfterUpdate event, but do you need it?

 

[Graham R Seach]

James,

At the time you get to the BeforeUpdate event, you already have an open
transaction pending. When you tell Access to requery the form, it has to
save the open transaction first, but it can't because the BeforeUpdate event
hasn't finished yet. If all you want to do is save the changes, then just
let it complete the BeforeUpdate event. I would also leave the Cancel
assignment in the BeforeUpdate event. Your code should probably be as
follows:

Private Sub myForm_BeforeUpdate(Cancel As Integer)
Cancel = Not ConfirmDataChange
End Sub

Public Function ConfirmDataChange() As Boolean
If vbNo = MsgBox("Record(s) have been added or changed." & _
vbCrLf & "Save the Changes?", _
vbYesNo, "Record Change") Then

Screen.ActiveForm.Undo
Else
ConfirmDataChange = True
End If
End Function

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[JamesJ]

I see now. I'm rewuerying to soon.
But, for instance the new record remains in the new record row (last row)
and is not sorted along with the other records.I want a requery to order the
records to put the new record in the proper place.
I'll try it in the afterupdate.

James

 

[JamesJ]

I try it.

Thanks,
James

James,

At the time you get to the BeforeUpdate event, you already have an open
transaction pending. When you tell Access to requery the form, it has to
save the open transaction first, but it can't because the BeforeUpdate
event hasn't finished yet. If all you want to do is save the changes, then
just let it complete the BeforeUpdate event. I would also leave the Cancel
assignment in the BeforeUpdate event. Your code should probably be as
follows:

Private Sub myForm_BeforeUpdate(Cancel As Integer)
Cancel = Not ConfirmDataChange
End Sub

Public Function ConfirmDataChange() As Boolean
If vbNo = MsgBox("Record(s) have been added or changed." & _
vbCrLf & "Save the Changes?", _
vbYesNo, "Record Change") Then

Screen.ActiveForm.Undo
Else
ConfirmDataChange = True
End If
End Function

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia