Nested If problem

[Carrie]

Hello Again, I have been back and forth on this one and finally gave up on
the query idea and came back to trying a Nested If. There are basically 4
fields that need to be evaluated in order to update Inspection_Frequency but
I can't seem to get that to happen.

I have tried this on the GotFocus, BeforeUpdate and OnEnter events, I have
also tried it with and without the Me. Can anyone tell me what I am doing
wrong? Thanks a million!

Private Sub Inspection_Frequency_GotFocus()
If (Action = Suspend) Then
If (Me.Risk_Level = Low) And (Me.Risk_Type = 1) Then
Me.Inspection_Frequency = 5
ElseIf (Me.Risk_Level = Low) And (Me.Risk_Type = 2) Then
Me.Inspection_Frequency = 5
ElseIf (Me.Risk_Level = Low) And (Me.Risk_Type = 3) Then
Me.Inspection_Frequency = 5
ElseIf (Me.Risk_Level = Low) And (Me.Risk_Type = 4) Then
Me.Inspection_Frequency = 5
ElseIf (Me.Risk_Level = Low) And (Me.Risk_Type = 5) Then
Me.Inspection_Frequency = 1
ElseIf (Me.Risk_Level = Medium) And (Me.Downhole_Option = 1) Then
Me.Inspection_Frequency = 3
ElseIf (Me.Risk_Level = Medium) And (Me.Downhole_Option = 2) Then
Me.Inspection_Frequency = 5
ElseIf (Me.Risk_Level = Medium) And (Me.Downhole_Option = 3) Then
Me.Inspection_Frequency = 5
ElseIf (Me.Risk_Level = High) And (Me.Downhole_Option = 1) Then
Me.Inspection_Frequency = 1
ElseIf (Me.Risk_Level = High) And (Me.Downhole_Option = 2) Then
Me.Inspection_Frequency = 5
Else: Me.Inspection_Frequency = 0
End If
End If
End Sub

 

[Jeff Boyce]

Carrie

Have you tried inserting a Breakpoint at the start of the If...Then and
stepping through each command to check for values?

I'm not clear what you expect to have triggering this testing... and it
seems like you'd want to trigger it any time ANY of the fields changed.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Baz]

What is "Action"?

 

[Carrie]

Hi Jeff, I must confess VB is obviously my weak point. I have inserted the
Breakpoint and tried hitting F8 but all I get is a ding from my computer -
nothing happens. Is that not how you do it?

To answer your other question, yes, I guess I do want the Inspection
Frequency to change based on any changes to any of the other fields which I
think is where part of my problem is as I can't just put my If Statement on
an After Update Event.

At this point, I'd be happy to even create a button for On Click update the
Inspection Frequency or something like that.....

 

[Jeff Boyce]

Carrie

I didn't spot anything right off, but here are a couple ideas... (I'm
assuming "Action" is a combo box - see below).

First, create a procedure behind the form that is not connected to any of
the controls.

Next, for readability, consider using the Select Case command, something
like:

Private Sub RecalcInspectionFrequency()

If (Me!cboAction = Suspend) Then
Select Case Me!Risk_Level
Case "Low"
If Me!Risk_Type Between 1 and 4 Then
Me!Inspection_Frequency = 5
ElseIf Me!Risk_Type = 5 Then
Me!Inspection_Frequency = 1
Else
'?any other conditions to test
End If
Case "Medium"
If Me!Downhole_Option =1 Then
Me!Inspection_Frequency = 3
ElseIf Me!Downhole_Option Between 2 And 3 Then
Me!Inspection_Frequency =5
Else
'?are there any other option values?
End If
Case "High"
If Me!Downhole_Option =1 Then
Me!Inspection_Frequency = 1
ElseIf Me!Downhole_Option = 2 Then
Me!Inspection_Frequency =5
Else
'?are there any other option values?
End If
Case Else
Me!Inspection_Frequency =0
End Select
End If 'test "Suspend

After you create this, call it from the AfterUpdate event of each/every one
of the controls that contribute to the decision. That way, when something
changes in a control, the recaculation is done.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[carrietom]

Thanks so much for your advice - I am going to give this a try and fingers
crossed, I'll be in business! Action, Risk_Level, Risk_Type and
Downhole_Option are all combo boxes. Action does have other possible
selections but it is only Suspend that requires an Inspection.

Risk Type and Downhole_Option are combos where the selections are narrowed
based on the Risk Level selected. I am using the After Update events to run
that - is it acceptable to have more than one function on an event?

I'll let you know how it turns out.

 

[Jeff Boyce]

Carrie

What isn't clear to me yet is whether those Comboboxes store the numbers or
the values displayed. A common approach is to "hide" the first column
(containing the IDnumber) and display the second column (the human-friendly
part).

It could make a difference in evaluating what they "contain".

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Carrie]

Hi Jeff,

The values from the combo boxes that I have shown (1, 2, 3...) are not
actually the ID's. The way the govt. sets these up is by Types and Options
(ie. Type 1, Type 2, Option 1, Option 2....) so the way I have my tables set
up is:

RiskType_ID (PK)
Risk_Type (1,2,3...)
Risk_Description (text - kind of long)

My Risk_Type field used to have entries as Type 1, Type 2 etc. but I kept
getting error messages and from what I could guess I thought that Type and
Option might be reserved words so, I removed them.

Should I be using the ID's instead?

Thanks!

 

[Jeff Boyce]

Carrie

You don't have to, but you do need to keep straight what the value is in the
combo box. The value may NOT be what is displayed ... e.g., when you hide
the first column (that's the value of the field).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Carrie]

Hi Jeff - you must be getting sick of me!

Okay - as you mentioned, the values displayed are of course, not the values
stored as I have everything tied to my ID Numbers. I went though and
determined what each of the ID numbers was so Instead of Case "Low", I put in
Case "1" as 1 is the PK ID for Risk_Level.

My problem is that on the line: If Me!Risk_Type Between 1 and 4 Then

I keep getting an error message where Between gets highlighted and it says
"Compile Error: Expected Then or GoTo"

I've been searching the help files and on line for another possible word
instead of Between but am not having any luck. I'm wondering if the issue is
the Between, or is it the fact that I'm actually asking for the ID instead of
a value of between 1 and 4?

Thanks again!

 

[Jeff Boyce]

Another way to say "between" would be:

If Me!Risk_Type >=1 and Me!Risk_Type <= 4 Then

Or you could use another Select Case statement and something like:

Case 1, 2, 3, 4

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Carrie]

Hi Again, OK I did respond to this this morning but it seems to not be
showing up? What I was asking was - I determined that all my combo boxes are
actually bound by the ID field so I changed the code below to reflect that
(ie. Case "1" instead of Case "Low".

I am getting "Compile Error: Expected Then or GoTo" and it is highlighting
the Between. I've been looking in the help files and online and I can't seem
to find any information to indicate what else I could use instead of Between?
I'm not sure if it is really Between that is causing the problem or if it is
something to do with the fact that I am using the ID numbers?

Thanks again!

 

[Carrie]

Hello Again, I'm not sure if the last 2 replies I submitted got lost, or if
they are all going to show up at once. If they do show up - I moved past
that problem and think I am really close now.

What I can't figure out is how to get RecalcInspectionFrequency to run on
the After Update Event. I put it in as Me.RecalcInspectionFrequency.Requery
but it doesn't like that. I think it isn't sure what
RecalcInspectionFrequency is because it isn't field. Hopefully this will be
the last time you hear from me!

Thanks again!

 

[Carrie]

Hey - sorry for the repeat posts - for some reason I wasn't able to see
anything past June 5th since yesterday so, I kept trying to post thinking
that I was doing something wrong. Anyways - I've got it all figured out now
and thank you Jeff so much for all your assistance (and patience). I never
would have figured this out on my own!

 

[Jeff Boyce]

de nada ... perhaps some day you'll offer support to a post you find here!

Regards

Jeff Boyce
Microsoft Office/Access MVP