If statment too complex - Pls Help!!

[Pam]

I have an unbound text box (txtJobStatus) on a form with 12 other fields on
the form that are varying stages of job completion. Each one will have tech
name or n/a applied to each field as completed. I want the unbound text box
to contain the next blank field from this completed stage list. I am using
this one field on a form to show what stage each job is on without having to
list or look thru all 12 fields. If I use the following code in the control
source row of the text box, it works fine with a few of the fields, but not
all 12. I get a "too complex" msg.

=IIf (IsNull([Receiving]), "Receiving", IIf (IsNull([Disassembly]),
"Disassembly", IIf(IsNull([Balancing]), "Balancing")))

I've tried writing something similar in the "current" event for the form,
but nothing works. I've read thru the posts, but can't find anything that
will work for me. Any help is very much appreciated!! Thanks, Pam

 

[Duncan Bachen]

I have an unbound text box (txtJobStatus) on a form with 12 other fields on
the form that are varying stages of job completion. Each one will have tech
name or n/a applied to each field as completed. I want the unbound text box
to contain the next blank field from this completed stage list. I am using
this one field on a form to show what stage each job is on without having to
list or look thru all 12 fields. If I use the following code in the control
source row of the text box, it works fine with a few of the fields, but not
all 12. I get a "too complex" msg.

=IIf (IsNull([Receiving]), "Receiving", IIf (IsNull([Disassembly]),
"Disassembly", IIf(IsNull([Balancing]), "Balancing")))

I've tried writing something similar in the "current" event for the form,
but nothing works. I've read thru the posts, but can't find anything that
will work for me. Any help is very much appreciated!! Thanks, Pam

What you could do is write yourself a function rather than using the
nested IIf. Start with Entry 1 and work through Entry 12. This would go
into the underlying code of the form doing the data entry.

You also want to make sure to call the function in the AfterUpdate event
of all 12 text controls. That way your 'next step' is updated when they are.

Your control expression would be =DetermineNextStep

Private Function DetermineNextStep() as String
If IsNull([Entry1]) Then
DetermineNextStep = "Nothing Entered"
Exit Function
End If
If IsNull([Entry2]) Then
DetermineNextStep = "Receiving"
Exit Function
End If
If IsNull([Entry3]) Then
DetermineNextStep = "Disassembly"
Exit Function
End If
If IsNull([Entry4]) Then
DetermineNextStep = "Balancing"
Exit Function
End If

(etc. etc.)

End Function

 

[Pam]

Duncan,

Thanks for the prompt reply. I'm not sure if I follow everything you're
telling me.
I have a form "fWorkLog" which has the 12 job stage fields. The tech's
enter their names at each stage as it is completed. Do I enter the function
in underlying code here? If so, how and where would I do this? I'm also to
enter the call function in the After Update event (the complete code as you
have listed in your msg for each field??) The form with the unbound text
box "txtJobStatus" is on form "fTodaysWork". What I want to do is pull into
this field the next blank field that hasn't been completed. Thanks again for
your help with this. I'm not very good with code, so please bear with me.
Pam

I have an unbound text box (txtJobStatus) on a form with 12 other fields
on the form that are varying stages of job completion. Each one will
have tech name or n/a applied to each field as completed. I want the
unbound text box to contain the next blank field from this completed
stage list. I am using this one field on a form to show what stage each
job is on without having to list or look thru all 12 fields. If I use
the following code in the control source row of the text box, it works
fine with a few of the fields, but not all 12. I get a "too complex"
msg.

=IIf (IsNull([Receiving]), "Receiving", IIf (IsNull([Disassembly]),
"Disassembly", IIf(IsNull([Balancing]), "Balancing")))

I've tried writing something similar in the "current" event for the
form, but nothing works. I've read thru the posts, but can't find
anything that will work for me. Any help is very much appreciated!!
Thanks, Pam

What you could do is write yourself a function rather than using the
nested IIf. Start with Entry 1 and work through Entry 12. This would go
into the underlying code of the form doing the data entry.

You also want to make sure to call the function in the AfterUpdate event
of all 12 text controls. That way your 'next step' is updated when they
are.

Your control expression would be =DetermineNextStep

Private Function DetermineNextStep() as String
If IsNull([Entry1]) Then
DetermineNextStep = "Nothing Entered"
Exit Function
End If
If IsNull([Entry2]) Then
DetermineNextStep = "Receiving"
Exit Function
End If
If IsNull([Entry3]) Then
DetermineNextStep = "Disassembly"
Exit Function
End If
If IsNull([Entry4]) Then
DetermineNextStep = "Balancing"
Exit Function
End If

(etc. etc.)

End Function

 

[Duncan Bachen]

Duncan,

Thanks for the prompt reply. I'm not sure if I follow everything you're
telling me.
I have a form "fWorkLog" which has the 12 job stage fields. The tech's
enter their names at each stage as it is completed. Do I enter the function
in underlying code here?

Just like a form stores the code for each control's events, it can also
store generic functions that you create. You can get to the underlying
code by hitting alt-f11 button or clicking the icon for 'Code' (looks
like a square with a red, yellow, and blue color on it)

From here, you can look at all the code associated with the form. You
can also add your own code. So you'd add my function (probably at the
bottom), and you'd flesh it out to cover all 12 possibilities.

If so, how and where would I do this? I'm also to

enter the call function in the After Update event (the complete code as you
have listed in your msg for each field??)

In the Current Event of the form, instead of your huge nested IIF, you'd
put a single line:
txtJobStatus = DetermineNextStep

This would call the function, and the function would in turn determine
what to set the value of txtJobStatus too (by returning the proper string).

However, this code will only fire on the form current event. So if you
change a status field (one of the 12), the txtJobStatus won't be updated
until after you leave the record and come back to it.

If you put the same code as the Form Current event into the After_Update
event of each of the 12 status fields, then *IF* the status changes, the
function will be called and reevaluate what should be shown in txtJobStatus.

The form with the unbound text

box "txtJobStatus" is on form "fTodaysWork". What I want to do is pull into
this field the next blank field that hasn't been completed. Thanks again for
your help with this. I'm not very good with code, so please bear with me.
Pam

You'll get there eventually. Working with code will bring you into the
next phase of your Access development.