validation

[deb]

I have a form "f_pac" that allows user to enter a new record.
There are 4 textboxes, A,B,C,D. They are all number fields

If there is any number in A or B or C then D must remain blank.
If there is a number in D then A,B and C must be blank.

Also need a msgbox explaining this to customer if the criteria is breached.

Please help.

 

[xenou]

You could check out this example:
http://www.databasedev.co.uk/validate_textbox.html

you will have to work with the logic to meet your needs but basically running a check on FORM_BeforeUpdate would be the idea in this case. Instead of checking each field one by one you'll have some multi-valued condition based on the state of a,b,c,d (there are only two valid states as described in your post).

 

[deb]

You could check out this example:
http://www.databasedev.co.uk/validate_textbox.html

you will have to work with the logic to meet your needs but basically running a check on FORM_BeforeUpdate would be the idea in this case. Instead of checking each field one by one you'll have some multi-valued condition based on the state of a,b,c,d (there are only two valid states as described in your post).

Thank but not sure how to edit the suggested code to make it work foe me.
If a,b or c has number then d must be blank or if d has number then a,b,c must be blank.

Please help edit your suggestion to do this. I would be very appreciative.

 

[welshgasman]

Literally test for that in the BeforeUpdate event of the form as xenou explained and display an appropriate error message.?
Alternatively in the LostFocus event of each control, disable d, if a, b or c have values.

FWIW I would probably have d first for data entry? If completed, then disable the other 3 ?
You could even clear d if they then put a value in a, b or c?
It is up to you to work out how you want it all to work.?
Even put some text on the form to explain your logic? or provide adequate training.

HTH

Thank but not sure how to edit the suggested code to make it work foe me.
If a,b or c has number then d must be blank or if d has number then a,b,c must be blank.

Please help edit your suggestion to do this. I would be very appreciative.

 

[stumac]

Something like:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Nz(Me.TextD) <> 0 Then
    If Nz(Me.TextA) <> 0 Or Nz(Me.TextB) <> 0 Or Nz(Me.TextC) <> 0 Or Nz(Me.TextD) <> 0 Then
        MsgBox "Invalid entry...."
        Cancel = True
    End If
End If
End Sub

Although this would mean 0 is an allowed number.

 

[xenou]

Yes, you just need to sketch out the logic and test it.

Something like (PSEUDOCODE):

If d <> "" And a = "" And b = "" And c = "" Then
    FAIL
End If
If d = "" And (Not (a <> "" Or b <> "" Or c <> "")) Then
    FAIL
End If
If FAIL Then
   Cancel Update
   MsgBox stuff
End If

As in example above and in the link - however you must take care to test that the code works when textboxes have different types of content:

Textboxes are empty (never put any value in)
Textboxes are deleted (put a value in and then deleted it)
Textboxes contain Null
textboxes contain empty strings (hard to explain how to do this though it can happen)
Textboxes contain spaces (uggh)

So as a rule I always test len(Textbox.Value & "") to check for empty since it covers most of these cases. len(Trim(TextBox.Value) & "") would be more robust since it also covers the last case. And I'm pretty sure there are other ways that developers address this.

 

[stumac]

There are 4 textboxes, A,B,C,D. They are all number fields

If the text boxes are bound to number fields they should only contain Null or a number.

 

[xenou]

In this case you can limit testing to only handle numbers.

Edit: note that I don't generally distinguish between text and numbers because all unbound textboxes are text ... and since some access users don't really know the difference between bound and unbound textboxes, I go for the "lowest common denominator". Also we still have to test for null and the test above is still the one I would use because it works for all cases (if I remember correctly).

However, if you want to devise a test that is crafted specifically to handle only numbers and null then that is also fine.