Preventing Null Value based dependent on data in other field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sandy - 31 Dec 2005 10:54 GMT

I have cancellation form which have 4 fields to be filled. In the same form i
have added corresponding 4 fields which shows what's their value in invoice
form. (thru dlookup)
Also have added validation that 4 fields in cancellation cant be different
from value in invoice fields (before update event)

Now i wanted to ensure that if Field1 based on invoice is null, then only
field1 based on cancellation should be null, else value is must. Following
are the codes:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim aSector1 As Variant
Dim aSector2 As Variant
Dim aSector3 As Variant
Dim aSector4 As Variant
aSector1 = DLookup("[Ticket Level]![FltTicketSector1]", "Ticket Level",
"[Ticket Level]![SubTransNo]=[Cncl Ticket Level]![SubTransNo]")
aSector2 = DLookup("[Ticket Level]![FltTicketSector2]", "Ticket Level",
"[Ticket Level]![SubTransNo]=[Cncl Ticket Level]![SubTransNo]")
aSector3 = DLookup("[Ticket Level]![FltTicketSector3]", "Ticket Level",
"[Ticket Level]![SubTransNo]=[Cncl Ticket Level]![SubTransNo]")
aSector4 = DLookup("[Ticket Level]![FltTicketSector4]", "Ticket Level",
"[Ticket Level]![SubTransNo]=[Cncl Ticket Level]![SubTransNo]")

If Me!FltCnclTicketSector1 <> Me![Text17] Then
MsgBox " Sector 1 Mismatch", vbOKOnly
Cancel = True
ElseIf Me!FltCnclTicketSector2 <> Me![Text21] Then
MsgBox " Sector 2 Mismatch", vbOKOnly
Cancel = True
ElseIf Me!FltCnclTicketSector3 <> Me![Text23] Then
MsgBox " Sector 3 Mismatch", vbOKOnly
Cancel = True
ElseIf Me!FltCnclTicketSector4 <> Me![Text25] Then
MsgBox " Sector 4 Mismatch", vbOKOnly
Cancel = True
End If
End Sub

How should i ensure that Field1 of cancellation cant be null if there's
value in corresponding Field1 based on invoice value or Field1 of
cancellation can be null when value in corresponding Field1 based on invoice
value is null
Thanks

Reply to this Message

Wayne Morgan - 31 Dec 2005 14:20 GMT

I don't fully follow what you're asking with the way it is worded. I get
that you want to verify if a field is Null only if another field is or isn't
Null. To do this, you could use something similar to the following:

If IsNull(FieldA) And Not IsNull(FieldB) Then
'Do what you need to here
End If

or

If IsNull(FieldA) And IsNull(FieldB) Then
'Do what you need to here
End If

Replace FieldA and FieldB with the appropriate fields or expressions that
get or contain the values of the fields in question.

Signature

Wayne Morgan
MS Access MVP

>I have cancellation form which have 4 fields to be filled. In the same form
>i
[quoted text clipped - 43 lines]
> value is null
> Thanks

Reply to this Message