IIf statement not working, SELECT case instead?

[ADezii]

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.

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]

IIf statement not working, SELECT case instead?

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]

IIf statement not working, SELECT case instead?

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!

[ADezii]

If [DATEREVIEWASSIG NED] will always Default to the Current Date, then you can simply set its Default Value = Date(), which it currently is, and set the Locked Property of this Field to Yes.

[NeoPa]

There seem to be two issues with this question :

1. Why are you storing against a task when the item is so obviously project related?
   My advice is to take them out of your Task table and put them in the Project table instead.
2. If you are convinced that you need this facility (in spite of advice to the contrary) then you need to clear up exactly what you want. If it's simply to ensure any date already entered in a record is not changed once it's been saved, then you need a procedure, called from the Current & AfterUpdate event procedures of your form, that sets the .Locked property of the control equal to the boolean result of IsNull(Me.Contr olName).
   If, on the other hand, you want the date for each task to match any that's already been entered on any task within the project, then you're making a rod for your own back. This is one of the many reasons why the approach you seem to be using should never be employed. I don't intend to help you down that path (your path is your own choice of course) as I know you will not thank me later when you come to understand the situation more clearly.

[chhines]

IIf statement not working, SELECT case instead?

Interesting replies, NeoPA and ADezii.
ADezii, you mention just locking the current text box, but won't that lock it from entering anything at all if I do that?

NeoPA, you are indicating flawed design?
If I understand what you are getting at, the main form is the Input Facility Task Reviewer, this starts out with 4 fields, a facility name, facility #, Permit # and operator.
Then there is a task subform, which is technically the same as "project", this only has a "Completed" date.
Then, the review sub-sub-form which has a review assigned, due and completion date, which is where you helped me with the calculation.
You can view all of this in the example database I uploaded earlier.
I'm not fully understanding why this would need to be changed, but if you can make a good argument and explain more, I'm willing to listen.

By looking at it now, I'm not sure that this should be moved to the top form though. Thanks.

[ADezii]

ADezii, you mention just locking the current text box, but won't that lock it from entering anything at all if I do that?

Yes. That's why I asked in Post #21 if it will 'always' Default to the Current Date.

[chhines]

IIf statement not working, SELECT case instead?

Yes, it will Default to the current date, but the user can also put in their own date by entering a different date in within at least the last 30 days.

This date is entered when the task(project) is first assigned and the reviewers are chosen for each phase of the review.
This is what we are actually tracking, the workload of reviews and various stages a particular review is in at any point in time. For the overall task(project), the only date we are worried about is actual completion.

[NeoPa]

Yes. That's why I asked in Post #21 if it will 'always' Default to the Current Date.

@ADezii,
I think you may be confusing Default to & Set to. The default should be set one way but the operator always has the option of changing it before submission.

@Chhines
There may not be flawed design. There is definately flawed explanations of your problem. If you refer to the same items variously as tasks and projects how would you expect anyone not to be confused.

I think my last post gives you a solution that can work, even though the question wasn't clearly understood at the time. If you feel you still need further help then please respond explaining, as clearly as you can, where you're at and what you need now.

[chhines]

IIf statement not working, SELECT case instead?

NeoPA, you are probably correct, I'm not explaining it properly. My bad.
Anyway, once the DATEREVIEWASSIG NED is set by the first selection of it with a TASKTYPE and at the same time TASK #, which happens during our calculation of the DATEREVIEWDUE = DATEREVIEWASSIG NED + TASKTYPE select case statement from ADezii.

This DATEREVIEWASSIG NED should NOT change for the rest of that particular task and should be the same for all reviews done under that, i.e. each individual assigned to this task, which can be from 1-5 people.

I believe I will need some further explanation of how to set this up with the CURRENT and AFTERUPDATE events. I know what AFTERUPDATE does, but what syntax would I use to set this up with the CURRENT and AFTERUPDATE events?

[NeoPa]

I believe I will need some further explanation of how to set this up with the CURRENT and AFTERUPDATE events. I know what AFTERUPDATE does, but what syntax would I use to set this up with the CURRENT and AFTERUPDATE events?

Instead of :

Private Sub Control_AfterUpdate()
    'Your code here
End Sub

You would have :

Private Sub Control_AfterUpdate()
    Call NewRoutine()
End Sub

Private Sub Form_Current()
    Call NewRoutine()
End Sub

Private Sub NewRoutine()
    'Your code here
End Sub

Does that answer your question?

[chhines]

IIf statement not working, SELECT case instead?

NeoPA, I'm not sure I understand the logic of your suggestion. I'm sure it is painful for you to have to spell it out to me like a baby, but in my original post, it says NEWBIE. I'm not sure why we are trying to assign the IsNull value to the control. It should never be Null, so I would think this is the least choice we would want to test. Is there a coding sample somewhere so I can understand what we are attempting here?:

I was going to put these routines in the Review_subform VB section.
However, there is already an AfterUpdate set in this window, i.e, the one ADezii described before:

Private Sub DATEREVIEWASSIGNED_AfterUpdate()
Dim varRet As Variant
If Not IsNull(Forms![Input Facility Task Reviewer]!Task_Subform.Form![cboTaskType]) And _
   Not IsNull(Forms![Input Facility Task Reviewer]!Review_Subform.Form![DATEREVIEWASSIGNED]) Then
     varRet = CalcDueDate()
End If
End Sub

I started with this, but I'm missing the logic. Where to from here? SORRY, I'm lost here...

Private Sub DATEREVIEWASSIGNED_AfterUpdate()
    Call New Routine
End Sub

Private Sub Form_Current()
    Call New Routine
End Sub
  
Private Sub New Routine()
IsNull DATEREVIEWASSIGNED.Locked = True
End Sub

Thanks again.

[NeoPa]

You quote my last post (indirectly) yet you seem to be referring back to something I posted in #22.

What about the IsNull() comment is bothering you? Are you saying that the value will never be Null because it always has a default, even before anything is entered (life could be a lot simpler if you made your meaning clearer)?

If so (you may well be right), then instead of using IsNull() you'd need something that determines if you are entering a new record, like :

Private Sub New_Routine()
    Me.DATEREVIEWASSIGNED.Locked = (Not Me.NewRecord)
End Sub

This is basically saying that it should be locked in all cases except when a new record is being entered. If that is not the logic you want then please explain this again so that I can help find the code for what you need.

NB. There cannot be a space in the name of your procedure.