Debugging... GoToControl, SetValue, ValidationRule error?

J

Jaybird

I'm trying to debug my Invoice form. It's query based with two subforms. I
keep getting this error when I use one of my combo boxes:

"The macro or function set to the BeforeUpdate or ValidationRule property
for this field is preventing Microsoft Office Access from saving data in the
field.
* If this is a macro, open the macro in the Macro window and remove the
action that forces a save (for example, GoToControl).
* If the macro includes a SetValue action, set the macro to the AfterUpdate
property of the control instead.
* If this is a function, redefine the function in the Module window."

I'm not sure if this is the problem or not, but it's the only instance where
I use the GoToControl command and I don't use SetValue or BeforeUpdate at
all. It's a piece of code I use to assign an invoice number to records in
the subform.

Private Sub Form_Current()
Dim Response As Variant
On Error Resume Next

If IsNull(Me.INVNUM) Then
Me.INVNUM = Format(DMax("Left([INVNUM],5)", "HDRPLAT") + 1, "00000") & "A"
Response = MsgBox("Is this what you want?", vbYesNoCancel)
If Response = vbNo Then
Me.Undo
DoCmd.GoToControl ("[cboSrchbyOrder]")
Exit Sub
End If
If Response = vbCancel Then
DoCmd.GoToControl ("[cboSrchbyOrder]")
Exit Sub
End If
End If

Me.CUSTNO = Me.Parent.[Cust No]
Me.SHIPDTMD = Me.Parent.[Shipped]
Me.SHIPDTY = Me.SHIPDTMD
Me.CUSTPO = Me.Parent.[Purchase Order]
If Forms!frmInvoice!sbfInvoice.Form!txtTtlChrgs < 0 Then
Me.ckCRMEM = True
Else
Me.ckCRMEM = False
End If
End Sub

It's supposed to assign an invoice number in a specific format if there
isn't one already assigned based on the response the user gives in the
message box. This isn't supposed to even fire up the GoToContol command
unless the response is either "no" or "cancel", I don't know why I'm getting
the error. In looking at the thing, I don't see that I have included
instructions on what to do if the response is "yes". Could this be the
problem?
 
K

Ken Snell \(MVP\)

That error usually means that a validation rule or code/macro for the
BeforeUpdate event are finding that the data do not meet requirements, and
the BeforeUpdate event is being cancelled, which then cancels the
GoToControl action because ACCESS will not let you leave the control with
offending data until the data are right.

Not knowing what you have for the validation rule or BeforeUpdate
code/macro, it's hard to hazard a guess. But look for a Null value in a
control that is required to have a non-Null value, a value in a control that
doesn't match the data type of the field in its Control Source (e.g.,
letters in a date field, letters in a number field, etc.), a foreign key
that must have a value and is currently Null, etc.

--

Ken Snell
<MS ACCESS MVP>


Jaybird said:
I'm trying to debug my Invoice form. It's query based with two subforms.
I
keep getting this error when I use one of my combo boxes:

"The macro or function set to the BeforeUpdate or ValidationRule property
for this field is preventing Microsoft Office Access from saving data in
the
field.
* If this is a macro, open the macro in the Macro window and remove the
action that forces a save (for example, GoToControl).
* If the macro includes a SetValue action, set the macro to the
AfterUpdate
property of the control instead.
* If this is a function, redefine the function in the Module window."

I'm not sure if this is the problem or not, but it's the only instance
where
I use the GoToControl command and I don't use SetValue or BeforeUpdate at
all. It's a piece of code I use to assign an invoice number to records in
the subform.

Private Sub Form_Current()
Dim Response As Variant
On Error Resume Next

If IsNull(Me.INVNUM) Then
Me.INVNUM = Format(DMax("Left([INVNUM],5)", "HDRPLAT") + 1, "00000") & "A"
Response = MsgBox("Is this what you want?", vbYesNoCancel)
If Response = vbNo Then
Me.Undo
DoCmd.GoToControl ("[cboSrchbyOrder]")
Exit Sub
End If
If Response = vbCancel Then
DoCmd.GoToControl ("[cboSrchbyOrder]")
Exit Sub
End If
End If

Me.CUSTNO = Me.Parent.[Cust No]
Me.SHIPDTMD = Me.Parent.[Shipped]
Me.SHIPDTY = Me.SHIPDTMD
Me.CUSTPO = Me.Parent.[Purchase Order]
If Forms!frmInvoice!sbfInvoice.Form!txtTtlChrgs < 0 Then
Me.ckCRMEM = True
Else
Me.ckCRMEM = False
End If
End Sub

It's supposed to assign an invoice number in a specific format if there
isn't one already assigned based on the response the user gives in the
message box. This isn't supposed to even fire up the GoToContol command
unless the response is either "no" or "cancel", I don't know why I'm
getting
the error. In looking at the thing, I don't see that I have included
instructions on what to do if the response is "yes". Could this be the
problem?
Click to expand...
 
J

Jaybird

Wow. The only field that was required out of three tables was causing the
error, it seems. I was trying to make sure that all data got entered before
the user moved to the next record, but it had unforeseen consequences :-(
Based on the error message, it was on my list to check this out, but your
response sort of eliminated other possibilities I thought were more likely.
Thank you so much! MVP's are a godsend! (As well as everyone else trying to
be helpful on this site ;-) Have a great day!
--
Why are you asking me? I dont know what Im doing!

Jaybird


Ken Snell (MVP) said:
That error usually means that a validation rule or code/macro for the
BeforeUpdate event are finding that the data do not meet requirements, and
the BeforeUpdate event is being cancelled, which then cancels the
GoToControl action because ACCESS will not let you leave the control with
offending data until the data are right.

Not knowing what you have for the validation rule or BeforeUpdate
code/macro, it's hard to hazard a guess. But look for a Null value in a
control that is required to have a non-Null value, a value in a control that
doesn't match the data type of the field in its Control Source (e.g.,
letters in a date field, letters in a number field, etc.), a foreign key
that must have a value and is currently Null, etc.

--

Ken Snell
<MS ACCESS MVP>


Jaybird said:
I'm trying to debug my Invoice form. It's query based with two subforms.
I
keep getting this error when I use one of my combo boxes:

"The macro or function set to the BeforeUpdate or ValidationRule property
for this field is preventing Microsoft Office Access from saving data in
the
field.
* If this is a macro, open the macro in the Macro window and remove the
action that forces a save (for example, GoToControl).
* If the macro includes a SetValue action, set the macro to the
AfterUpdate
property of the control instead.
* If this is a function, redefine the function in the Module window."

I'm not sure if this is the problem or not, but it's the only instance
where
I use the GoToControl command and I don't use SetValue or BeforeUpdate at
all. It's a piece of code I use to assign an invoice number to records in
the subform.

Private Sub Form_Current()
Dim Response As Variant
On Error Resume Next

If IsNull(Me.INVNUM) Then
Me.INVNUM = Format(DMax("Left([INVNUM],5)", "HDRPLAT") + 1, "00000") & "A"
Response = MsgBox("Is this what you want?", vbYesNoCancel)
If Response = vbNo Then
Me.Undo
DoCmd.GoToControl ("[cboSrchbyOrder]")
Exit Sub
End If
If Response = vbCancel Then
DoCmd.GoToControl ("[cboSrchbyOrder]")
Exit Sub
End If
End If

Me.CUSTNO = Me.Parent.[Cust No]
Me.SHIPDTMD = Me.Parent.[Shipped]
Me.SHIPDTY = Me.SHIPDTMD
Me.CUSTPO = Me.Parent.[Purchase Order]
If Forms!frmInvoice!sbfInvoice.Form!txtTtlChrgs < 0 Then
Me.ckCRMEM = True
Else
Me.ckCRMEM = False
End If
End Sub

It's supposed to assign an invoice number in a specific format if there
isn't one already assigned based on the response the user gives in the
message box. This isn't supposed to even fire up the GoToContol command
unless the response is either "no" or "cancel", I don't know why I'm
getting
the error. In looking at the thing, I don't see that I have included
instructions on what to do if the response is "yes". Could this be the
problem?
Click to expand...
Click to expand...
 
J

Jaybird

Ken, thank you so much for your earlier response. Another problem has popped
up... Now when I open the form I get an error message stating that the
object I'm referring to does not support this method... I clicked on the
debug button and this came up:

Private Sub Form_Current()
Dim Response As Variant

If Me.txtTtlChrgs < 0 Then
Forms.frmInvoice.sbfHDRPLAT.Form.CRMEM = 0
Me.CRMEMONotes.Visible = True
Else
Forms.frmInvoice.sbfHDRPLAT.Form.CRMEM = 1
Me.CRMEMONotes.Visible = False
End If

End Sub

This line was highlighted:

Forms.frmInvoice.sbfHDRPLAT.Form.CRMEM = 1

I thought maybe that since the underlying field was text that I needed to
put my values in quotes. I did that and the error stopped occurring.
However, I was right in the middle of this e-mail when I thought of it and
couldn't quite remember what the error said. To reproduce it, I removed the
quotes and opened the form again, but the error still did not occur. What
the hey? I'm just about done with this form, but everytime I think that,
something else pops up. Now I'm just waiting for the other shoe to drop...
Any clue as to what may be going on here?
 
K

Ken Snell \(MVP\)

Your syntax is wrong. Change this line:
Forms.frmInvoice.sbfHDRPLAT.Form.CRMEM = 1

to this:
Forms("frmInvoice).Controls("sbfHDRPLAT").Form.CRMEM = 1

or to this (assuming that the code is running in the frmInvoice main form's
module:
Me.sbfHDRPLAT.Form.CRMEM = 1
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

GoToControl in BeforeUpdate 4
Stopping an error message from apearing 3
Setting Focus after error 5
setvalue problem 1
Macro or function set to BeforeUpdate or ValidationRule property e 5
Form_beforeUpdate question 5
Audit Trail 8
Allen Browne's Audit Trail Not Working 1

Top