IIf statement not working, SELECT case instead?

[chhines]

I have a very long IIf statement. I think maybe I've reached the limit of how many choices you can have in the control source "Build" statement of a text box on a form. Really, the IIF statement is very confusing looking at it, so I suppose there is a better way, but I don't know how. When I try to add additional choices, nothing happens, I save, get no errors, but the new choices don't work! I am trying to calculate a due date [DATEREVIEWDUE]text field on a subform based off of the value chosen in another subform on the same main form using a field called [TASKTYPE]. Whatever value is chosen in TASKTYPE (dropdown box) it is compared with the value in the [DATEREVIEWASSIG NED] text box which calculates the DATEREVIEWDUE. It works for many instances, but when I tried to add additional options beyond 14 choices, access doesn't save the changes. It will only allow me to change the existing entries. I've attached the sample code of my current IIf statement that works. Thanks in advance.

** Edit ** Added text and removed attachment (as the attachment requires downloading which makes it a little hard to see normally).

=IIf(Forms![Input Facility Task Reviewer]!Task_Subform.Form!TASKTYPE="Alternatives Analysis",[DATEREVIEWASSIGNED]+60,
 IIf(Forms![Input Facility Task Reviewer]!Task_Subform.Form!TASKTYPE="Explore",[DATEREVIEWASSIGNED]+7,
 IIf(Forms![Input Facility Task Reviewer]!Task_Subform.Form!TASKTYPE="GP-12",[DATEREVIEWASSIGNED]+30,
 IIf(Forms![Input Facility Task Reviewer]!Task_Subform.Form!TASKTYPE="Informal",[DATEREVIEWASSIGNED]+45,
 IIf(Forms![Input Facility Task Reviewer]!Task_Subform.Form!TASKTYPE="Major 1",[DATEREVIEWASSIGNED]+60,
 IIf(Forms![Input Facility Task Reviewer]!Task_Subform.Form!TASKTYPE="New 1",[DATEREVIEWASSIGNED]+60,
 IIf(Forms![Input Facility Task Reviewer]!Task_Subform.Form!TASKTYPE="Noise Complaint",[DATEREVIEWASSIGNED]+30,
 IIf(Forms![Input Facility Task Reviewer]!Task_Subform.Form!TASKTYPE="Other",[DATEREVIEWASSIGNED]+30,
 IIf(Forms![Input Facility Task Reviewer]!Task_Subform.Form!TASKTYPE="Renewal",[DATEREVIEWASSIGNED]+60,
 IIf(Forms![Input Facility Task Reviewer]!Task_Subform.Form!TASKTYPE="Revision",[DATEREVIEWASSIGNED]+90,
 IIf(Forms![Input Facility Task Reviewer]!Task_Subform.Form!TASKTYPE="Special Project",[DATEREVIEWASSIGNED]+60,
 IIf(Forms![Input Facility Task Reviewer]!Task_Subform.Form!TASKTYPE="Stream Investigation",[DATEREVIEWASSIGNED]+60,
 IIf(Forms![Input Facility Task Reviewer]!Task_Subform.Form!TASKTYPE="Transfer",[DATEREVIEWASSIGNED]+60,
 IIf(Forms![Input Facility Task Reviewer]!Task_Subform.Form!TASKTYPE="Water Loss",[DATEREVIEWASSIGNED]+45))))))))))))))

[chhines]

ADezii. When I try to add the debug line, I get, "compile error: Invalid Outside procedure." I've tried adding it in the "Input Facility Task Reviewer" portion of VB and also in the "Reviewer_F orm" portion, same error. No I didn't forget to set the function as the control source for [DATEREVIEWDUE].

NeoPA, no I didn't try your idea, it wasn't that I didn't want to, just I had gotten pretty close with ADezii's idea. I feel we're right there. A lot of the data in the database is confidential, so I'm not sure that I would want to upload the database.

[ADezii]

@chhines
To be perfectly honest, I'm pretty much out of options at this point. Unless you can replace the confidential data with Dummy Data (only a couple of Records should do), there isn't much else that I can offer. Another option would be to Upload a simulated Database to me at my Personal E-Mail Address outside of this Forum. Whatever you decide to do, it's your choice.

[chhines]

ADezii, I'll try breaking it down some tonight and will repost back here something you can work with...Thanks.

[chhines]

ADezii, I've attached a copy of the database---modified.
I didn't put a lot of data in it, it appears to be still making the same error with what I have in there.
Hopefully you can figure something out from what I have in there.
If you need to, add additional data, or let me know and I'll add more records. Thanks again.

[NeoPa]

If you'd prefer even that be kept private, I'm happy to remove it for you after ADezii has downloaded his copy. Just let me know.

[ADezii]

@chhines
I've made many changes to your Database, some of which I'll point out here, and I'll also make certain notations. The rest will be up to you. Study the Attachment for details.

1. CalcDueDate() is now a Public Function in the SendMail Module.
2. [cboTASKTYPE] is now Bound to [TASKTYPE]
3. [DATEREVIEWASSIG NED] is now Bound to [DATEREVIEWASSIG NED]. It's Control Source equal to the Function has been removed.
4. Look at the same code in the AfterUpdate() Event of [cboTASKTYPE] and [DATEREVIEWASSIG NED] to see how [DATEREVIEWDUE] is populated.
5. You must Reset the Reference to your Outlook Object Library.
6. As previously indicated, code in the AfterUpdate() Events of [cboTASKTYPE] and [DATEREVIEWASSIG NED] calculates the correct Value to the [DATEREVIEWDUE] Field. Short of restructuring, you must be very careful which Records in the Sub-Forms are selected before you populate either Field, or the wrong Values will be set in the incorrect Sub-Form Record. This must be fixed by you.
7. Study the Attachment carefully.

[chhines]

ADezii, I have some ?'s with what you've done.
You mentioned you put the Public function in the SendMail Module.
Why there?
I actually wasn't even using the SendMail module yet, and I'm not sure if it will work due to a limitation in Outlook, so will it work if the SendMail module doesn't?

But anyway, why did making it a public function in SendMail Module make it work when it didn't elsewhere as a pub. funct.?
What is the purpose of the line below the End Select that looks like it is referencing the calculation?

When you say cboTASKTYPE and txtDATEREVIEWAS SIGNED are now BOUND, how does this change their relationship in the form?
Where and how can I tell that they are bound and what does it mean?

The event procedures for the After Update prop., why the If Not IsNull?
They should both never be null, one is a combo box which will only allow choices from a specific list and the other is a text box (DATEREVIEWASSI GNED) that has a default value of today's date, which means it will never be null either.

As for the comments in #'s 5 & 6, I’m not sure what you mean about resetting the Outlook reference & how often I'd have to do that.
Also, I was unable to duplicate the error of auto population messing up the calculated DUEDATE based on selecting the wrong field out of sequence.
I'm sure the problem exists as you say, but couldn’t' that be fixed by setting the Tab Order of the form? Thanks again.

[ADezii]

You mentioned you put the Public function in the SendMail Module.
Why there?

You can put it in the Form's Code Module, but it should be declared as Private and not Public.

I actually wasn't even using the SendMail module yet, and I'm not sure if it will work due to a limitation in Outlook, so will it work if the SendMail module doesn't?

Yes, as long as the Module exists.

But anyway, why did making it a public function in SendMail Module make it work when it didn't elsewhere as a pub. funct.?

Using it as the Control Source was in the wrong context.

What is the purpose of the line below the End Select that looks like it is referencing the calculation?

It's returning the Value of the CalcDueDate() Function to the Field in the Sub-Form.

When you say cboTASKTYPE and txtDATEREVIEWAS SIGNED are now BOUND, how does this change their relationship in the form?

No, their Values are now stored Fields in the Record Source of the Form.

Where and how can I tell that they are bound and what does it mean?

Look at the Control Source Properties.

The event procedures for the After Update prop., why the If Not IsNull?
They should both never be null, one is a combo box which will only allow choices from a specific list and the other is a text box (DATEREVIEWASSI GNED) that has a default value of today's date, which means it will never be null either.

You could enter Value(s) in these Fields,inadvert ently delete them, then move off the Field in which case the Function will crash. A precaution.

As for the comments in #'s 5 & 6, I’m not sure what you mean about resetting the Outlook reference & how often I'd have to do that.

In any Code Window, Tools ==> References. Only done once unless the Outlook Version changes.

Also, I was unable to duplicate the error of auto population messing up the calculated DUEDATE based on selecting the wrong field out of sequence.
I'm sure the problem exists as you say, but couldn’t' that be fixed by setting the Tab Order of the form?

The way it is currently structured, you must be careful to see what Record is selected in each Sub-Form before you enter Values in either one of these Fields. It should probably be a Form/Sub-Form/Sub-Sub Form structure.

[NeoPa]

Let me see what I can do with the selected questions (You'll need to wait for ADezii for the others as they are specific to his code).

PS. I started this a long while ago, but got interrupted by a visitor. Unfortunate, but still there are points in here worth reading.

1. Q. You mentioned you put the Public function in the SendMail Module.
   Why there?
   A. I would guess this was just an available standard module. Public functions are only callable from outside of their own module (EG. from within a SQL string) if they are in a standard code module.
2. Q. I actually wasn't even using the SendMail module yet, and I'm not sure if it will work due to a limitation in Outlook, so will it work if the SendMail module doesn't?
   A. Yes
3. Q. But anyway, why did making it a public function in SendMail Module make it work when it didn't elsewhere as a pub. funct.?
   A. See 1 above.
4. Q. When you say cboTASKTYPE and txtDATEREVIEWAS SIGNED are now BOUND, how does this change their relationship in the form?
   A. Bound controls on a form automatically link their values to the underlying record source. An unbound control shows on the form, but its value is only held there. It is shown but not stored anywhere. Bound controls, on the other hand, reflect the values stored in the record. When moving between records the value changes automatically.
5. Q. Where and how can I tell that they are bound and what does it mean?
   A. The ControlSource property lets you know if a control is Bound or not. If it is unset, or set to a calculation of some kind, then it is unbound. It is only bound if it contains the name of one of the fields from the recordset.
6. Q. They should both never be null, one is a combo box which will only allow choices from a specific list and the other is a text box (DATEREVIEWASSI GNED) that has a default value of today's date, which means it will never be null either.
   A. I suspect this is simply defensive programming. Code that covers scenarios that you don't even expect to occur. This is generally considered good.
   NB. Because a control has a default vale does not mean it cannot be Null. That would be false logic. Careful of such assumptions.
7. Q. As for the comments in #'s 5 & 6, I’m not sure what you mean about resetting the Outlook reference & how often I'd have to do that.
   A. I suspect that ADezii had to reset the Outlook reference in his code as it didn't match yours exactly (for whatever reason). He found a similar reference to use that worked for him. That does mean though, that is different from the one you were using and you probably need to set it back before use. This he cannot do for you as he is missing the relevant reference.

[chhines]

ADezii and NeoPA. Thanks so much for all your help with fixing this problem, it seems to be working great now!!

More importantly, you've explained the logic to me so I know what is going on and can use the logic in the future if I want to make changes this or add additional items as such.

I have an additional question.

If I want to "grey out" the value of the [DATEREVIEWASSIG NED] & probably even [DATEREVIEWDUE] fields after it is entered for the first time for a particular task, what is the best way to do this? Essentially, after the first person enters a record for a particular task, the review date assigned is and the due date calculated, it should be the same for due date and assigned date for everyone associated with the project, no matter when they start on their portion of it.

Thanks again!