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.

[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
ElseIf Not IsNull(Me.[org-bldg-val-c]) Then
blnError = True
strError = strError & "Residentia l 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
[/code]

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.