Validation Help

[wfma]

I have a database that calculates taxes. I have two classes of properties Residential and Commercial. Here is the information

Access:
2003

Fields:
PCC (Text)
ORG-BLDG-VAL-C (Currency)
ORG-BLDG-VAL-R (Currency)

What I want to do is.
If entered a pcc code of 100 to 199, they MUST fill the org-bldg-val-r field.
If entered a pcc code of 300 to 599, they MUST fill the org-bldg-val-c field.
If entered a pcc code of 0?? to 099, they MUST fill both fields fields.

Pcc codes 100-299 are residential properties and 300-599 are commercial class properties. You can’t have a residential code with a commercial value or a commercial code with a residential value. We use 2 different tax rates. (now if the PCC codes starts with Zero (0), then both fields will be used.

Please advise

[wfma]

Any help with this would be great!

[JKing]

Are you using a form to allow the user to enter data? If so what controls?
Or are they entering directly into the table?

[wfma]

Are you using a form to allow the user to enter data? If so what controls?
Or are they entering directly into the table?

They enter the data in to a form. As for controls, not sure what you mean?

[JKing]

By controls I mean the names of any textboxes or combo boxes that the user would be using to input the data.

[wfma]

By controls I mean the names of any textboxes or combo boxes that the user would be using to input the data.

Yes, they have some drop down boxes.

[JKing]

Okay, well I'd like to help you code some validation and it would be easier for you to understand if I knew the names of the textboxes and comboboxes so I can refer to your control names in the code.

And explain the layout of your form too. Does the user select a PCC value from a combo box? Then enter a commercial rate or residential rate into a text box? Is there a button that saves all this to the database?

[wfma]

Hmmm...

The fields that I inquiring about are not combo boxes, they are just regular database fields.

If you look at the top posting, you can see the field names and what kind of fild they are

PCC (Text)
ORG-BLDG-VAL-C (Currency)
ORG-BLDG-VAL-R (Currency)

They hand entere the pcc, both rates and values.

[JKing]

Yes, I understand those are your fields. I want to know about your form though that is being used to input new data. It's my understanding that the user enters a pcc code and based on the range you want to force them to fill in residential rate commercial rate or both. You mentioned that you were using drop downs. Which fields are being represented by the drop downs? Is there a textbox that they enter the residential/commercial values into?

[wfma]

Yes, I understand those are your fields. I want to know about your form though that is being used to input new data. It's my understanding that the user enters a pcc code and based on the range you want to force them to fill in residential rate commercial rate or both. You mentioned that you were using drop downs. Which fields are being represented by the drop downs? Is there a textbox that they enter the residential/commercial values into?

I only use one Drop Down (action-code) I use this field for sorting and grouping my reports only.

The fields org-bldg-val-r and org-bldg-val-c are both text boxes. Depending on the pcc, the user will add the building value in the right box

[JKing]

Just a few more things. Is it a bound form that is being used to edit and add records? Or is it a simple form with unbound textboxes used for the sole purpose of adding records?

[wfma]

Just a few more things. Is it a bound form that is being used to edit and add records? Or is it a simple form with unbound textboxes used for the sole purpose of adding records?

This form is used to add records only, I have other forms that are used to edit.

I have a form that I use as a menu, When you click on the button to add a record, it run a macro:
Goto Record - New
Open Form - Database mode: Add

[wfma]

Hellooooooooooo, are you still here?

[JKing]

Hi, because of the complexity of your validation I don't think this can be acheived using the validation rules for table fields.

Here's something you can try adding to the before update event of your form. From the information you've given me I'm assuming you're using a form that is bound to your table and has the data entry property set to yes. This will force the user to enter the proper values depending on the entered pcc code. Also it will catch if they entered an improper value and prompt them to remove it.

Private Sub Form_BeforeUpdate(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
        ElseIf Not IsNull(Me.[org-bldg-val-c]) Then
            blnError = True
            strError = strError & "Residential PCCs cannot have commercial values!" & vbCrLf
        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
        ElseIf Not IsNull(Me.[org-bldg-val-r]) Then
            blnError = True
            strError = strError & "Commercial PCCs cannot have residential values!" & vbCrLf
        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
 

Let me know if this is what you were looking if not I can try and work something else out or one of the other experts might.

[wfma]

Hmmmm, it seems to work, but I'm getting an error. I think I know why:

The fields org-bldg-val-c and org-bldg-val-c by default have a zero (o) in them. The 0's have to be in them for the setvalue macro to work.

I need to change to isnull statement to >0, When I did that, I got an error in the complier?

also, No matter what the pcc code is, if both fields have a ZERO, then that is ok.

[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.

Private Sub Form_BeforeUpdate(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 & "Residential 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
 

[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_Click()
On Error GoTo Err_enter_add_new_Click


DoCmd.GoToRecord , , acNewRec

Exit_enter_add_new_Click:
Exit Sub

Err_enter_add_new_Click:
MsgBox Err.Description
Resume Exit_enter_add_new_Click

End Sub
Private Sub enter_dup_record_Click()
On Error GoTo Err_enter_dup_record_Click


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

Exit_enter_dup_record_Click:
Exit Sub

Err_enter_dup_record_Click:
MsgBox Err.Description
Resume Exit_enter_dup_record_Click

End Sub
Private Sub Command124_Click()
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_Click()
On Error GoTo Err_Command137_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem 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_BeforeUpdate(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 & "Residential 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.....anyone 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_Click
' init error message
txtMessage = ""
' Check fields in reverse order to set focus to the first
If Not Len(NZ(Me.Description)) > 0 Then
txtMessage = "Description empty ?" & vbCrLf
Me.Description.SetFocus
End If
If Not Len(NZ(Me.Severity)) > 0 Then
txtMessage = "No Severity?" & vbCrLf & txtMessage
Me.Severity.SetFocus
End If
If Not Len(NZ(Me.Type)) = 0 Then
txtMessage = "Recordtype empty ?" & vbCrLf & txtMessage
Me.Type.SetFocus
End If
' Check error found
If Len(txtMessage) > 0 Then
MsgBox txtMessage
Exit Sub
End If

DoCmd.Close

Exit_btnSave_Click:
Exit Sub

Err_btnSave_Click:
MsgBox Err.Description
Resume Exit_btnSave_Click

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.

:(

[nico5038]

No, the code replaces the need for the BeforeUpdate code.
This way no "Cancel" has to be issued in the BeforeUpdate as all fields will be OK when the row is updated.
In the [Cancel] button the conditional "Me.Undo" will make sure no errors are saved.

Nic;o)

[wfma]

If I remove the before update code, them how will the validation work?

[nico5038]

You're then using the standard "Me.Close" mechanism that will update all changed values. This is "custom" behaviour for Access.

When you change data directly in a table, going to the next row triggers the same "custom behaviour".
Closing a form (or navigating to another record) triggers the same behaviour.

By not closing the form (when there's something in the txtMessage string), you're preventing an update with invalid data, but allow the user to press [Cancel] to undo the changes and leave the form.

Clearer ?

Nic;o)

[wfma]

Ok, Baby steps:

I'm going to remove the before update (Yes/No)?

Where do I copy your Close code to?

Where do i copy your Accept Code to?

[nico5038]

Ok, Baby steps:

I'm going to remove the before update (Yes/No)?
==> Yes, the entire sub

Where do I copy your Close code to?
1) Create button [OK] and
2) Name the button: btnOK
3) In the OnClick event add the code

Where do i copy your Accept Code to?
1) Create button [Cancel]
2) Name the button: btnCancel
3) In the OnClick event add the code

Nic;o)

[wfma]

I should creaat the button(s) on each form correct?

[nico5038]

Each form where you need to check the correctness needs both buttons.

Nic;o)