Nested If Statements

[Joy]

Hi,

Hope I can get some help from you as I'm new to MS Access coding. On my form
I have created an email button. The email button only works if certain
conditions are met ie. mandatory fields must be completed before you can
email the form.
However there is 1 mandatory field that may or may not be completed based on
a condition ie. If ComboBox2 (mandatory field) shows value of Director or
Deputy Director then ComboBox3 (mandatory field) need not be completed. But
if ComboxBox2 does not show a value of Director or Deputy Director then
ComboBox3 must be filled-in.

At the moment for all other conditions to be met, the code is:-

If IsNull(ComboSpec2) Then
MsgBox "Incomplete Form"
DoCmd.GoToControl "ComboSpec2"

Else

If IsNull(ComboPriCentre) Then
MsgBox "Incomplete Form"
DoCmd.GoToControl "ComboPriCentre"

Else

If IsNull(ComboPriDept) Then
MsgBox "Incomplete Form"
DoCmd.GoToControl "ComboPriDept"

Else

DoCmd.SendObject acSendForm, "NewProfReqForm", acFormatXLS, "Joy Thong", , ,
"New Prof Request", "Hi Could you pls enter this prof into Orion. Thanks!!!",
False

My problem is I am not sure how to code the requirements as stated above to
ensure the condition is met. Pls help...

Thanks Joy

 

[Jeanette Cunningham]

Put an asterisk in a label next to each combo that must be filled in.
On the after update event for ComboSpec2, put code that hides the asterisk
for Combo3 if ComboSpec2 is a director or deputy.
The code below is untested air code, you may need to tweak it a bit for your
form.

If IsNull(ComboSpec2) or IsNull(ComboPriCentre) or IsNull(ComboPriDept)Then
MsgBox "You must enter a value wherever there is an asterisk"
DoCmd.CancelEvent
Else
Select Case Me.(ComboSpec2)
Case "Director", "Deputy Director"
If IsNull(ComboPriCentre) or IsNull(ComboPriDept) Then
MsgBox "You must enter a value wherever there is an asterisk"
DoCmd.CancelEvent
Case Else
If IsNull(ComboPriCentre) or IsNull(ComboPriDept) or IsNull(Me.combo3)
Then
MsgBox "You must enter a value wherever there is an asterisk"
DoCmd.CancelEvent
End Select
Else
DoCmd.SendObject acSendForm, "NewProfReqForm", acFormatXLS, "Joy Thong",
, ,
"New Prof Request", "Hi Could you pls enter this prof into Orion.
Thanks!!!",
End If

Jeanette Cunningham

 

[Joy]

Hi Jeanette
Thanks for helping me out once again...I did not use the asterisk function
as I don't like asterisks. I've used conditional formatting on mandatory
fields ie. if blank it will be in yellow.

Tried the code you suggested, tweaked to look as below. However, I get the
error msg "Case Else outside Select Case"

Code used:
If IsNull(ComboSpec2) Or IsNull(ComboSpec3_1) Or IsNull(ComboPriCentre) Or
IsNull(ComboPriDept) Or IsNull(ComboBU) Or IsNull(ComboProfession) Or
IsNull(ComboPriority) Or IsNull(ComboSalutation) Or IsNull(TxtFirstName) Or
IsNull(TxtLastName) Then
MsgBox "Incomplete Form. Pls fill in fields still in yellow"
DoCmd.CancelEvent

Else

Select Case Me.ComboSpec2
Case "Director", "Deputy Director"
If IsNull(ComboSpec2) Or IsNull(ComboPriCentre) Or IsNull(ComboPriDept)
Or IsNull(ComboBU) Or IsNull(ComboProfession) Or IsNull(ComboPriority) Or
IsNull(ComboSalutation) Or IsNull(TxtFirstName) Or IsNull(TxtLastName) Then
MsgBox "Incomplete Form. Pls fill in fields still in yellow"
DoCmd.CancelEvent

Case Else

If IsNull(ComboSpec2) Or IsNull(ComboSpec3_1) Or IsNull(ComboPriCentre)
Or IsNull(ComboPriDept) Or IsNull(ComboBU) Or IsNull(ComboProfession) Or
IsNull(ComboPriority) Or IsNull(ComboSalutation) Or IsNull(TxtFirstName) Or
IsNull(TxtLastName) Then
MsgBox "Incomplete Form. Pls fill in fields still in yellow"
DoCmd.CancelEvent

End Select

Else

DoCmd.SendObject acSendForm, "NewProfReqForm", acFormatXLS, "Joy Thong", , ,
"New Prof Request", "Hi Could you pls enter this prof into Orion. Thanks!!!",
False

End If

End Sub

Where am i going wrong? Kindly pls advise. Thanks Joy

 

[akphidelt]

I think you have to end the IF statements within the select case statement.
So it should go

Select Case
If
If
End If
End If
End Select

Im not a professional so this may be wrong.

 

[akphidelt]

Let me rephrase that... I think it should go

If

Select Case
Case
If
End If
Case Else
If
End If
End Select

Else

End If

Once again, that's just a guess

 

[Albert D. Kallal]

One nice way to handel this, and is good bit less code to write out a
generic list of values to test for...

I use the following "general" code routine to give custom messages for
fields that are not
filled out.

The code below is a great way to verify fields that you want to be requited.

Another nice feature is that after the given message, the cursor (focus)
moves to the field in question.

The code is used as follows:

in the forms before update event..you go:

Cancel = MyVerify.

And, then the two following routines need be put into the forms module. You
can see how in the first example, you just put in the list of controls that
you want requited, and also the text "error" message to display. Note
carefully how the full string is enclosed in quotes.

This routine is called in teh forms on-load event:

Private Function MyVerify() As Boolean

Dim colFields As New Collection

MyVerify = False

colFields.Add "TourDate,Tour date"
colFields.Add "Description,Description"
colFields.Add "City,City"
colFields.Add "cboProvince,Province"
colFields.Add "StartDate,Start date"
colFields.Add "EndDate,end date"

MyVerify = vfields(colFields)


End Function

Private Function vfields(colFields As Collection) As Boolean

Dim strErrorText As String
Dim strControl As String
Dim i As Integer

vfields = False

For i = 1 To colFields.Count
strControl = Split(colFields(i), ",")(0)
strErrorText = Split(colFields(i), ",")(1)
If IsNull(Me(strControl)) = True Then

MsgBox strErrorText & " is required", vbExclamation, AppName
Me(strControl).SetFocus
vfields = True
Exit Function
End If
Next i


End Function

 

[Jeanette Cunningham]

I really should have tested this code before I posted it - I left quite a
few End If's. And Albert is right, it is a bit clunky. If you are happy with
Albert's code, go with that.

Jeanette Cunningham

 

[Joy]

Hi Albert

I am a little confused..thought I'd go with your code suggestion

So is this what it should look like? if I get you correctly, then:

1) Before Update Event on form- the code is like

Private Sub Form_BeforeUpdate(Cancel As Integer)
Cancel = MyVerify(Form) <--- Is this correct??
End Sub

If I leave a dot(.) after MyVerify - then the font goes red and the error
appears "Expected Identifier or bracketed expression"

2) Can you put in Private Function after Private Sub? When I place the
routine as described below for OnLoad Event on form, the following appears in
code -

Private Sub Form_Load()

At current I've got it looking like:-

Private Sub Form_Load()
Form.OnOpen
End Sub

Sorry, I am very new to this....

3) Hence the sub routines you described below are under "General" as 2
spearate sub routines.

When I test it... I am sure its not working as I am a confused...

It lets me email away even when mandatory fields are blank. Could you pls help
me further....thx Joy

 

[Joy]

Hi Jeanette

Albert's code looks good but I was confused by it... :-(

I reverted back to your code which now no longer produces error msgs, got my
End Ifs sorted thankfully.... However, there is a slight issue that I can't
seem to resolve. This EmailAway btn works, but not in the way of what I have
in mind.

In mind I have if ComboSpec2.value = Director or Deputy Director
then ComboSpec3_1 need not be completed by the user but if
ComboSpec.value <> Director or Deputy Director then user must complete
ComboSpec3_1

At the moment even if ComboSpec2.value = Director or Deputy Director which
renders a the user not required to fill in ComboSpec3_1 it still wouldn't let
me email it thru. Error msg created pops in "Incomplete form..."

How do I get it to the way I want based on the conditions desired?

My Code at present:-
Private Sub CmdEmailAway_Click()

If IsNull(ComboSpec2) Or IsNull(ComboSpec3_1) Or IsNull(ComboPriCentre) Or
IsNull(ComboPriDept) Or IsNull(ComboBU) Or IsNull(ComboProfession) Or
IsNull(ComboPriority) Or IsNull(ComboSalutation) Or IsNull(TxtFirstName) Or
IsNull(TxtLastName) Then
MsgBox "Incomplete Form. Are there any fields still yellow?"
DoCmd.CancelEvent

Else

Select Case Me.ComboSpec2
Case ComboSpec2.Value = "Director" Or ComboSpec2.Value = "Deputy
Director" Or ComboSpec2.Value = "Other"
If IsNull(ComboSpec2) Or IsNull(ComboPriCentre) Or
IsNull(ComboPriDept) Or IsNull(ComboBU) Or IsNull(ComboProfession) Or
IsNull(ComboPriority) Or IsNull(ComboSalutation) Or IsNull(TxtFirstName) Or
IsNull(TxtLastName) Then
MsgBox "Incomplete Form. Are there any fields still yellow?"
DoCmd.CancelEvent
End If
Case Else
End Select


DoCmd.SendObject acSendForm, "NewProfReqForm", acFormatHTML, "Joy Thong", ,
, "New Prof Request", "Hi Could you pls enter this prof into Orion.
Thanks!!!", False

End If

End Sub

 

[Jeanette Cunningham]

Joy,
The tricky thing is to run the check when comboSpec2 is null
and once more when comboSpec2 is not null

**Note: I am assuming that comboSpec2 has only one column, if not then the
code will need to be changed.
I am not familiar with SendObject and sending a form as HTML, I suggest you
email it to yourself first, to check that it works.
I still haven't tested this code - there may be an error.

To make the code less clunky and more compact, I have included a function
called Validate and a sub called SendTheEmail
The code for the function called Validate and the sub called SendTheEmail
goes in the code module for the form.
It is not attached to any event, it just sits by itself, separate from any
event code.
-------------------------------------
Private Function Validate() As Boolean

If IsNull(ComboPriCentre) Or
IsNull(ComboPriDept) Or
IsNull(ComboBU) Or
IsNull(ComboProfession) Or
IsNull(ComboPriority) Or
IsNull(ComboSalutation) Or
IsNull(TxtFirstName) Or
IsNull(TxtLastName) Then
Validate = False
Else
Validate = True
End Function

Private Sub SendTheEmail
DoCmd.SendObject acSendForm, "NewProfReqForm", acFormatHTML, "Joy
Thong", ,
, "New Prof Request", "Hi Could you pls enter this prof into Orion.
Thanks!!!", False
End Sub
------------------------------------


------------------------------------
Private Sub CmdEmailAway_Click()

If IsNull(ComboSpec2) Then
If IsNull(ComboSpec3_1) Or (Validate = False) Then
MsgBox "Incomplete Form. Are there any fields still yellow?"
DoCmd.CancelEvent
Else
Call SendTheEmail
End If

Else
Select Case Me.ComboSpec2
Case "Director", "Deputy Director"
If (Validate = False) Then
MsgBox "Incomplete Form. Are there any fields still yellow?"
DoCmd.CancelEvent
Else
Call SendTheEmail
End If

Case "Other"

If IsNull(ComboSpec3_1) Or (Validate = False) Then
MsgBox "Incomplete Form. Are there any fields still yellow?"
DoCmd.CancelEvent
Else
Call SendTheEmail
End If

Case Else
End Select
End If

 

[Albert D. Kallal]

Hi Albert

I am a little confused..thought I'd go with your code suggestion

So is this what it should look like? if I get you correctly, then:

1) Before Update Event on form- the code is like

Private Sub Form_BeforeUpdate(Cancel As Integer)
Cancel = MyVerify(Form) <--- Is this correct??
End Sub

Actually, looking at my code sample, you should only need call "myVerify" in
the before update

eg:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Cancel = MyVerify()

End Sub

Ignore my comments about placing a call, or any code in the forms on-load
event...you do NOT need to do this (my sorry..just was not thinking here).

So, just add the above, and also in the form add the two routines as shown:

eg:

Private Function MyVerify() As Boolean

Dim colFields As New Collection

MyVerify = False

colFields.Add "TourDate,Tour date"
colFields.Add "Description,Description"
colFields.Add "City,City"
colFields.Add "cboProvince,Province"
colFields.Add "StartDate,Start date"
colFields.Add "EndDate,end date"

MyVerify = vfields(colFields)


End Function

Private Function vfields(colFields As Collection) As Boolean

Dim strErrorText As String
Dim strControl As String
Dim i As Integer

vfields = False

For i = 1 To colFields.Count
strControl = Split(colFields(i), ",")(0)
strErrorText = Split(colFields(i), ",")(1)
If IsNull(Me(strControl)) = True Then

MsgBox strErrorText & " is required", vbExclamation, AppName
Me(strControl).SetFocus
vfields = True
Exit Function
End If
Next i

end function

Both the functions above can be private, and should be planced in the forms
code module....

Note that if you allow "zero length" strings, then the "null" check will not
work. Thus, in function vfields() change:

If IsNull(Me(strControl)) = True Then

to

if len(nz(me(strControl),"")) = 0 then

 

[Joy]

Hi Jeanette
You're not wrong here! Its a trick to ensure that ComboSpec3_1 is not null
or is null!

ComboSpec3_1 has only 1 column - mandatory field so long as ComboSpec2 does
not show value of Director or Deputy Director

ComboSpec2 has only 1 column & is a mandatory field

Took in your suggestion to put in a Validate function and a sub named
SendtheEmail.

AcFormatHTML works really well when sending off the form.

The validate code looks like:

Private Function Validate() As Boolean

If IsNull(ComboSpec3_1) Or IsNull(ComboSpec2) Or IsNull(ComboPriCentre) Or
IsNull(ComboPriDept) Or IsNull(ComboBU) Or IsNull(ComboProfession) Or
IsNull(ComboPriority) Or IsNull(ComboSalutation) Or IsNull(TxtFirstName) Or
IsNull(TxtLastName) Then
Validate = True
Else
Validate = False
End If

End Function

My code for CmdEmailAway button is:-


Private Sub CmdEmailAway_Click()

If IsNull(ComboSpec3_1) Or (Validate = True) Then
Call SendTheEmail
Else


Select Case Me.ComboSpec2
Case ComboSpec2.Value = "Director" Or ComboSpec2.Value = "Deputy
Director" Or ComboSpec2.Value = "Researcher or Lab Professional"
If (Validate = False) Then
MsgBox "Incomplete Form. Are there any fields still yellow?"
DoCmd.CancelEvent

Else
Call SendTheEmail
End If

Select Case Me.ComboSpec2
Case ComboSpec2.Value <> "Director" Or ComboSpec2.Value <>
"Deputy Director" Or ComboSpec2.Value <> "Researcher or Lab Professional"
If (Validate = False) Then
Call SendTheEmail

Else
'do nothing
End If

End Select

End Select

Call SendTheEmail

End If

End Sub
--------
I must be doing something wrong... on testing when I leave out mandatory
fields like TxtFirstName, it still allows the user to email it. I thought the
the Email button and validate function would have stopped it...

Also... if I leave null ComboSpec3_1 as ComboSpec is showing Director, the
form still gets emailed.

What am I doing wrong?

Thanks Joy

 

[Jeanette Cunningham]

Joy,
just read through the validate code.
If all the required fields are null, the code sets validate to true - when
it should be false.
Sorry about that.

so that code for validate should be like this:
----------------------------
If IsNull(ComboSpec3_1) Or IsNull(ComboSpec2) Or IsNull(ComboPriCentre) Or
IsNull(ComboPriDept) Or IsNull(ComboBU) Or IsNull(ComboProfession) Or
IsNull(ComboPriority) Or IsNull(ComboSalutation) Or IsNull(TxtFirstName) Or
IsNull(TxtLastName) Then
Validate = False
Else
Validate = True
End If

End Function