Validation Help

[JKing]

Here I modified it. It should be fine with zeros now and if the user removes the zero from the wrong textbox it will replace it before inserting to remove nulls.
[code=vb]
Private Sub Form_BeforeUpda te(Cancel As Integer)

Dim strError As String 'Used to format a friendly message to user
Dim blnError As String

blnError = False
strError = "Please fix the following:" & vbCrLf

'Residential PCCs
If Me.PCC >= 100 And Me.PCC <= 299 Then
If IsNull(Me.[org-bldg-val-r]) Then
blnError = True
strError = strError & "You must enter a residential value!" & vbCrLf
End If
If Me.[org-bldg-val-c] > 0 Then
blnError = True
strError = strError & "Residentia l PCCs cannot have commercial values!" & vbCrLf
ElseIf IsNull(Me.[org-bldg-val-c]) Then
Me.[org-bldg-val-c] = 0
End If
'Commercial PCCs
ElseIf Me.PCC >= 300 And Me.PCC <= 599 Then
If IsNull(Me.[org-bldg-val-c]) Then
blnError = True
strError = strError & "You must enter a commercial value!" & vbCrLf
End If
If Me.[org-bldg-val-r] > 0 Then
blnError = True
strError = strError & "Commercial PCCs cannot have residential values!" & vbCrLf
ElseIf IsNull(Me.[org-bldg-val-r]) Then
Me.[org-bldg-val-r] = 0
End If
'Both
ElseIf Me.PCC <= 99 Then
If IsNull(Me.[org-bldg-val-r]) Then
blnError = True
strError = strError & "You must enter a residential value!" & vbCrLf
End If
If IsNull(Me.[org-bldg-val-c]) Then
blnError = True
strError = strError & "You must enter a commercial value!" & vbCrLf
End If
'PCC entered is out of range
Else
blnError = True
strError = strError & "PCC is out of range."
End If

'If something was wrong then cancel the action and display the problems
If blnError Then
Cancel = True
MsgBox strError, vbOKOnly + vbCritical, "Error!"
End If
End Sub
[/code]

[wfma]

Wow, that was fast. Thanks.

Well, It worked I think, But, when I add the record, I get a msg box saying:

You can't go to the specified record.

???

[JKing]

Are you clicking a button or something to add a record? Are any other events or code being executed?

[wfma]

Are you clicking a button or something to add a record? Are any other events or code being executed?

Yes, I used the button wisard ,to creat a add record button.

[wfma]

here is all of the codes that are running: On-Click

Private Sub enter_add_new_C lick()
On Error GoTo Err_enter_add_n ew_Click


DoCmd.GoToRecor d , , acNewRec

Exit_enter_add_ new_Click:
Exit Sub

Err_enter_add_n ew_Click:
MsgBox Err.Description
Resume Exit_enter_add_ new_Click

End Sub
Private Sub enter_dup_recor d_Click()
On Error GoTo Err_enter_dup_r ecord_Click


DoCmd.DoMenuIte m acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuIte m acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuIte m acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

Exit_enter_dup_ record_Click:
Exit Sub

Err_enter_dup_r ecord_Click:
MsgBox Err.Description
Resume Exit_enter_dup_ record_Click

End Sub
Private Sub Command124_Clic k()
On Error GoTo Err_Command124_ Click


DoCmd.Close

Exit_Command124 _Click:
Exit Sub

Err_Command124_ Click:
MsgBox Err.Description
Resume Exit_Command124 _Click

End Sub
Private Sub Command137_Clic k()
On Error GoTo Err_Command137_ Click


DoCmd.DoMenuIte m acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuIte m acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuIte m acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

Exit_Command137 _Click:
Exit Sub

Err_Command137_ Click:
MsgBox Err.Description
Resume Exit_Command137 _Click

End Sub

Private Sub Form_BeforeUpda te(Cancel As Integer)

Dim strError As String 'Used to format a friendly message to user
Dim blnError As String

blnError = False
strError = "Please fix the following:" & vbCrLf

'Residential PCCs
If Me.pcc >= 100 And Me.pcc <= 299 Then
If IsNull(Me.[org-bldg-val-r]) Then
blnError = True
strError = strError & "You must enter a residential value!" & vbCrLf
End If
If Me.[org-bldg-val-c] > 0 Then
blnError = True
strError = strError & "Residentia l PCCs cannot have commercial values!" & vbCrLf
ElseIf IsNull(Me.[org-bldg-val-c]) Then
Me.[org-bldg-val-c] = 0
End If
'Commercial PCCs
ElseIf Me.pcc >= 300 And Me.pcc <= 599 Then
If IsNull(Me.[org-bldg-val-c]) Then
blnError = True
strError = strError & "You must enter a commercial value!" & vbCrLf
End If
If Me.[org-bldg-val-r] > 0 Then
blnError = True
strError = strError & "Commercial PCCs cannot have residential values!" & vbCrLf
ElseIf IsNull(Me.[org-bldg-val-r]) Then
Me.[org-bldg-val-r] = 0
End If
'Both
ElseIf Me.pcc <= 99 Then
If IsNull(Me.[org-bldg-val-r]) Then
blnError = True
strError = strError & "You must enter a residential value!" & vbCrLf
End If
If IsNull(Me.[org-bldg-val-c]) Then
blnError = True
strError = strError & "You must enter a commercial value!" & vbCrLf
End If
'PCC entered is out of range
Else
blnError = True
strError = strError & "PCC is out of range."
End If

'If something was wrong then cancel the action and display the problems
If blnError Then
Cancel = True
MsgBox strError, vbOKOnly + vbCritical, "Error!"
End If
End Sub

[JKing]

So clicking the add record button is what causes the "You can't go to the specified record" error?

[wfma]

So clicking the add record button is what causes the "You can't go to the specified record" error?

yep...this is when I get the error

[JKing]

Open up your form properties and go to the the data tab. Is Allow Additions set to No?

[wfma]

Open up your form properties and go to the the data tab. Is Allow Additions set to No?

Good morning, It is set to yes. Everything is set to yes, but Data Entry is no.

all the settings are by default. I have made no settings

[JKing]

Does it only happen when you change something on a record and try to skip to a new one? For example your PCC code is 085 and you remove the residential value completely and then click add a new record. The code will force you to stay on that record until you fill in the residential value. This would also cause the "you can't go to the specified record" error.

[wfma]

Does it only happen when you change something on a record and try to skip to a new one? For example your PCC code is 085 and you remove the residential value completely and then click add a new record. The code will force you to stay on that record until you fill in the residential value. This would also cause the "you can't go to the specified record" error.

I entered a blank record, added my tax rate, added a res ppc code, added a res bldg, land, and yard values. then added the new values. Now the commercial values had zeros in them it clicked on the add button, and I get that error that I can't go to the specified record"

[wfma]

Hello.....anyon e there?

[nico5038]

Hmm, very long thread and the use of the BeforeUpdate event isn't the way I approach this.
I allways have a form with an Accept and Cancel button. The Cancel button holds:

IF Me.Dirty then
Me.Undo
End If
Docmd.Close

This will close the form and undo any changes.

The Accept button holds the validation like:

Private Sub btnSave_Click()
Dim txtMessage As String
On Error GoTo Err_btnSave_Cli ck
' init error message
txtMessage = ""
' Check fields in reverse order to set focus to the first
If Not Len(NZ(Me.Descr iption)) > 0 Then
txtMessage = "Descriptio n empty ?" & vbCrLf
Me.Description. SetFocus
End If
If Not Len(NZ(Me.Sever ity)) > 0 Then
txtMessage = "No Severity?" & vbCrLf & txtMessage
Me.Severity.Set Focus
End If
If Not Len(NZ(Me.Type) ) = 0 Then
txtMessage = "Recordtype empty ?" & vbCrLf & txtMessage
Me.Type.SetFocu s
End If
' Check error found
If Len(txtMessage) > 0 Then
MsgBox txtMessage
Exit Sub
End If

DoCmd.Close

Exit_btnSave_Cl ick:
Exit Sub

Err_btnSave_Cli ck:
MsgBox Err.Description
Resume Exit_btnSave_Cl ick

End Sub

The boolean isn't needed here as the txtMessage can be tested to be filled. Additional advantage is the fact that all errors are mentioned (and can be corrected) in one go.

Idea ?

Nic;o)

[nico5038]

Dear wfma,

Please post your comments/remarks here in the question instead of using a PersonalMessage , thus the other experts can help/join when I'm not in the house :-)
You PM'd me:
Hi, thanks, How would this help me. Do I make a button and apply this code to it?

Yes, the above code should be placed behind the button named in the Sub so create a button named "btnCancel" and one "btnClick" and place the code in the OnClick event of those buttons.

Nic;o)

[wfma]

OK, I should make a new button, add your code to it, should I also keep the other validation code running on the before update?

I'm not sure why I'm getting confused. I pick up programming easy, but not this time.

:(