Programming Help Please

[Mark S via AccessMonster.com]

First, due to conditions beyond my control I can not do this at the table
level. I am not a VBA programmer (yet). I need help in programming to
validate a form. If A is not null then B and C must be filled in or I can
not write the record. But If A is null and D is not null then B and C need to
be null. I'm thinking beforeupdate

If Not Isnull ([A]) Then
If Isnull () Or Isnull ([C]) Then

Msgbox "Please fill in B and C"

Cancel = True

I can't get the logic for the condition with A & D so that I can write the
record without filling B & C.

Any help is appreciate. Thank you

 

[Brendan Reynolds]

If Not IsNull (A) Then
If IsNull (B) Or IsNull (C) Then
Msgbox "Please fill in B and C"
Cancel = True
End If
Else
'A is Null
If Not IsNull(D) Then
If (Not IsNull(B)) Or (Not IsNull(C)) Then
'see note below for alternative
MsgBox "Please *don't* fill in B and C"
Cancel = True
End If
End If
End If

Note: If appropriate, rather than prompting the user to *not* fill in the
values, you could simply set them to Null ...

If Not IsNull(D) Then
B = Null
C = Null
End If

 

[Mark S via AccessMonster.com]

Hey Brendan...

It's folks like you that make it so us newbies can learn and get better. I
appreciate your help. Here is what I put in and I still get the Msgbox when I
attempt to do the D condition. D is RTF for clarity. What am I doing wrong?

Thanks again.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not IsNull([RTC]) Then
If IsNull([Competitor]) Or IsNull([Reason]) Then
MsgBox "Please enter Competitor and Reason fields"
Cancel = True
End If
Else
'A is Null

If Not IsNull(RTF) Then
If (Not IsNull(Competitor)) Or (Not IsNull(Reason)) Then
'see note below for alternative
B = Null
C = Null
Cancel = True
End If
End If
End If

End Sub

If Not IsNull (A) Then
If IsNull (B) Or IsNull (C) Then
Msgbox "Please fill in B and C"
Cancel = True
End If
Else
'A is Null
If Not IsNull(D) Then
If (Not IsNull(B)) Or (Not IsNull(C)) Then
'see note below for alternative
MsgBox "Please *don't* fill in B and C"
Cancel = True
End If
End If
End If

Note: If appropriate, rather than prompting the user to *not* fill in the
values, you could simply set them to Null ...

If Not IsNull(D) Then
B = Null
C = Null
End If

First, due to conditions beyond my control I can not do this at the table
level. I am not a VBA programmer (yet). I need help in programming to

[quoted text clipped - 15 lines]

Any help is appreciate. Thank you

 

[Brendan Reynolds]

Do you mean that you still see the "Please enter Competitor and Reason
fields" even when "RTC" appears to be empty? If so, perhaps it contains an
empty string rather than a Null value. Try changing the test for
IsNull([RTC]) to the following, which will catch both Null values and empty
strings. I'll show the original line of code commented out followed by the
new line of code, to make it clear which line of code needs to be replaced.
"vbNullString" is an intrinsic VBA constant that corresponds to an empty
string, a string with no characters, which can also be entered by typing two
double quotes with nothing between them ...

'If Not IsNull([RTC]) Then
If Not Len([RTC] & vbNullString) = 0 Then

The above is the most efficient way to check for the presence of either a
Null value or an empty string, but it may be potentially a little confusing.
The following is less efficient, but perhaps easier to follow, and the
difference in efficiency won't be noticeable unless the code is being called
many times within a loop ...

If (Not IsNull[RTC]) And ([RTC] <> vbNullString) Then

--
Brendan Reynolds

Hey Brendan...

It's folks like you that make it so us newbies can learn and get better. I
appreciate your help. Here is what I put in and I still get the Msgbox
when I
attempt to do the D condition. D is RTF for clarity. What am I doing
wrong?

Thanks again.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not IsNull([RTC]) Then
If IsNull([Competitor]) Or IsNull([Reason]) Then
MsgBox "Please enter Competitor and Reason fields"
Cancel = True
End If
Else
'A is Null

If Not IsNull(RTF) Then
If (Not IsNull(Competitor)) Or (Not IsNull(Reason)) Then
'see note below for alternative
B = Null
C = Null
Cancel = True
End If
End If
End If

End Sub

If Not IsNull (A) Then
If IsNull (B) Or IsNull (C) Then
Msgbox "Please fill in B and C"
Cancel = True
End If
Else
'A is Null
If Not IsNull(D) Then
If (Not IsNull(B)) Or (Not IsNull(C)) Then
'see note below for alternative
MsgBox "Please *don't* fill in B and C"
Cancel = True
End If
End If
End If

Note: If appropriate, rather than prompting the user to *not* fill in the
values, you could simply set them to Null ...

If Not IsNull(D) Then
B = Null
C = Null
End If

First, due to conditions beyond my control I can not do this at the
table
level. I am not a VBA programmer (yet). I need help in programming to

[quoted text clipped - 15 lines]

Any help is appreciate. Thank you

 

[Mark S via AccessMonster.com]

That worked but I had to clear the RTC field. It is formatted as Currency so
as long as I don't have the field formatted then it works fine.

Do you mean that you still see the "Please enter Competitor and Reason
fields" even when "RTC" appears to be empty? If so, perhaps it contains an
empty string rather than a Null value. Try changing the test for
IsNull([RTC]) to the following, which will catch both Null values and empty
strings. I'll show the original line of code commented out followed by the
new line of code, to make it clear which line of code needs to be replaced.
"vbNullString" is an intrinsic VBA constant that corresponds to an empty
string, a string with no characters, which can also be entered by typing two
double quotes with nothing between them ...

'If Not IsNull([RTC]) Then
If Not Len([RTC] & vbNullString) = 0 Then

The above is the most efficient way to check for the presence of either a
Null value or an empty string, but it may be potentially a little confusing.
The following is less efficient, but perhaps easier to follow, and the
difference in efficiency won't be noticeable unless the code is being called
many times within a loop ...

Hey Brendan...

[quoted text clipped - 57 lines]