Macro or function set to BeforeUpdate or ValidationRule property e

  • Thread starter Thread starter Rosemary
  • Start date Start date
R

Rosemary

I have a simple input form to add records to a table, and it works just fine,
except I keep getting the following error:
The macro or function set to the BeforeUpdate or ValidationRule property for
this field is preventing DOIM BILLING from saving the data in the field.

The key to the table is an autonumber field.
There's a foreign key to another table, which is set in the procedure
(Category_id), since the form is adding subcategories. I've tried different
things to see if they make a difference, but the result is the same. But
even though I see the error message, I still does the insert if I reply Yes
to the prompt. If I reply No it works just fine and does not add the record.

Please help - code is included below:
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Form_BeforeUpdate
Dim strResponse As String

Me.Category_id = Me.hld_Cat_id

If Dirty Then
strResponse = MsgBox("Do you want to save your changes?",
vbYesNoCancel, "Save SubCategory?")
Select Case strResponse
Case vbYes ' User chose Yes.
' Dirty = False (THIS IS COMMENTED OUT)
DoCmd.RunCommand acCmdSaveRecord
Case vbNo
Me.Undo
End Select
End If

Exit_Form_BeforeUpdate:
Exit Sub

Err_Form_BeforeUpdate:
MsgBox Err.Description
Resume Exit_Form_BeforeUpdate
End Sub
 
I'm not sure what the error is from (check your validation rule in both the
table and the form, make sure they're empty), but it may have something to do
with the sub ID you mentioned trying to enter. Not really sure.

In any case, there's three things to take a look at in the code you posted
(none of which I can see will make a difference as far as your error saving
goes, but hey, you never know... should probably get them cleaned up).


1) Use of the Dirty property without an object (include the Me. keyword
(for the currently active form) before Dirty)

Me.Dirty instead of Dirty

I'm not exactly sure how access reads it without the Me but its good
practice not to let the application decide for itself.

2) strResponse as a return value for MsgBox. MsgBox actually returns a
numeric value, not a string. The vbYes/vbNo are constants that have numeric
values, but makes it easier for us programmers (type ?vbyes or ?vbno in the
immediate window to see the value of each... I believe these are 6 and 7 but
not positive. The help file has all these as well). So your variable really
should be a long, or integer, or byte (any of the three should work). Access
may be converting this automatically for you, but again, we try and not let
the app do this.

3) you have the buttons for vbYesNoCancel, but you have no handling in the
code if the user hits cancel instead of yes or no (this may be intentional,
looks like you code will do nothing at all on cancel, but I thought I'd bring
it up just in case).


I'd check those things out and see where that puts you.

hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)
 
Rosemary said:
I have a simple input form to add records to a table, and it works just fine,
except I keep getting the following error:
The macro or function set to the BeforeUpdate or ValidationRule property for
this field is preventing DOIM BILLING from saving the data in the field.

The key to the table is an autonumber field.
There's a foreign key to another table, which is set in the procedure
(Category_id), since the form is adding subcategories. I've tried different
things to see if they make a difference, but the result is the same. But
even though I see the error message, I still does the insert if I reply Yes
to the prompt. If I reply No it works just fine and does not add the record.

Please help - code is included below:
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Form_BeforeUpdate
Dim strResponse As String

Me.Category_id = Me.hld_Cat_id

If Dirty Then
strResponse = MsgBox("Do you want to save your changes?",
vbYesNoCancel, "Save SubCategory?")
Select Case strResponse
Case vbYes ' User chose Yes.
' Dirty = False (THIS IS COMMENTED OUT)
DoCmd.RunCommand acCmdSaveRecord
Case vbNo
Me.Undo
End Select
End If

Exit_Form_BeforeUpdate:
Exit Sub

Err_Form_BeforeUpdate:
MsgBox Err.Description
Resume Exit_Form_BeforeUpdate
End Sub
Click to expand...

When the foem's BeforeUpdate event is triggered, the record
is guaranteed to be dirty and is already in the process of
being saved so you can not try to save it. You can cancel
the save or let it proceed:

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Form_BeforeUpdate
Dim intResponse As INTEGER

Me.Category_id = Me.hld_Cat_id

intResponse = MsgBox("Do you want to save your changes?", _
vbYesNo, "Save SubCategory?")
If intResponse = vbNoThen
Me.Undo
Cancel = True
End If
 
Linq said:
I suspect the problem is with

Me.Category_id = Me.hld_Cat_id

You cannot assign a value to a control in Form_BeforeUpdate or you'll get the
error you're seeing.
Click to expand...


Sure you can. It's done all the time for lots of reasons.

You were probably thinking of a control's BeforeUpdate event
where the value of the control can not be changed.
 
Thanks to both of you for all your help. I got it working, but moved it out
of the beforeupdate, and put it in the close button click.
 
Back
Top