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 [DATEREVIEWASSIGNED] 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))))))))))))))

[MikeTheBike]

@chhines
Hi

I think I would tackle this with the Choose() function, (see VB Help).

You do not say what the Row Source of the Drop List box is, but if it is a table then I would add a field name, say 'Choose' to relate it the its use (?), as in integer type. Assign a unique number to each of the 'Choices', these should be continues numbers 1 to maxchoice. I don't know if you can make this field the bound column of the ComboBox, but if not, by using the Column property you can put the selected 'Chosen' value in a hidden form control say txtChooseIndex using the AfterUpdate event.

Then you would have a calculated like this

=Choose(Forms![Input Facility Task Reviewer]!Task_Subform.Form!txtChoosIndex,[DATEREVIEWASSIGNED]+60,[DATEREVIEWASSIGNED]+7,[DATEREVIEWASSIGNED]+30, ............. etc)
 

The order of the Choice arguments in the Choose() function would have to be in line with the 'Choose' values in the table or whatever.

This will be significantly shorter than the nested IIF() functions. Not sure if there is a limit to the number of ‘choices’ thought!

Hope that makes some sort of sense.

I am sure there are many ways to do this; perhaps someone else has other suggestions.


MTB

[ADezii]

@chhines

1. Set the Control Source of the [DATEREVIEWDUE] Field equal to a Public Function within which the logic is contained.
   

   =fCalcDateReviewDue()          'Control Source

2. Function Definition.
   

   Public Function fCalcDateReviewDue() As Date
   Dim strTaskType As String
   Dim dteDateReviewWasAssigned As Date
    
   strTaskType = Forms![Input Facility Task Reviewer]!Task_Subform.Form![TASKTYPE]
   dteDateReviewWasAssigned = Forms![Input Facility Task Reviewer]!Task_Subform.Form![DATEREVIEWASASSIGNED]
    
   Select Case strTaskType
     Case "Alternatives Analysis", "Major 1", "New 1", "Renewal", "Special Project", _
          "Stream Investigation", "Transfer"
       fCalcDateReviewDue = dteDateReviewWasAssigned + 60
     Case "Explore"
       fCalcDateReviewDue = dteDateReviewWasAssigned + 7
     Case "GP-12", "Noise Complaint", "Other"
       fCalcDateReviewDue = dteDateReviewWasAssigned + 30
     Case "Informal", "Water Loss"
       fCalcDateReviewDue = dteDateReviewWasAssigned + 45
     Case "Revision"
       fCalcDateReviewDue = dteDateReviewWasAssigned + 90
     Case Else
       fCalcDateReviewDue = #1/1/1800#
   End Select
   End Function

3. NOTE: The major assumption is made that the 2 Fields involved in the calculations are Required, and therefore cannot be NULL. If either is NULL, all bets are OFF. It is also assumed that both Fields reside on the same Sub-Form, if not make the necessary adjustment within the Function.

[NeoPa]

CHHines,

What you've managed to fit in there can be more succinctly be written as :

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

I believe the restriction is on length rather than number of items, so I suggest you have a rethink on your object names. They are far too clumsy for use in programming. Generally including spaces anywhere in an object name is unwise. If the name is not really a name but a sentence, you know you're likely to run into problems like this one.

[ADezii]

@NeoPa
Hello NeoPa. Just for curiosity, do you think that the Switch() approach is more efficient, readable, and faster than the Function/Select Case approach, especially given the tendency for Switch() to evaluate all expressions whether or not the preceding ones evaluate to True? Just curious on your thoughts on this one.

[NeoPa]

While the Switch() function evaluates all expressions before choosing when run in the VBA environment, the SQL equivalent is not so flawed (Just like the SQL versions of IIF() & Choose()). I think it would be more efficient (far less to do and no lines of code to interpret as it goes along as is the case in VBA).

I discovered a long while ago (to my chagrin as I was very inexperienced then on the SQL side - I wasn't even aware it was behind all my QueryDefs then) that any VBA procedure in a QueryDef slowed it down inexorably. The factor was quite extreme. I had to search for alternatives then to avoid such unacceptable delays. I'm pretty sure it's related to the fact that VBA is only a semi-compiled language. It doesn't compile to machine code but to some sort of interpretable half-way house. I'm convinced it would execute more quickly (IE also more efficient than) than the code, notwithstanding the efficient nature of the code.

Clearly it's more readable as it's all in one place. Understanding what a query is doing is never helped by the requirement to switch to another window to see what is happening within a procedure.

All in all, though I recognise the logic of your thoughts on this one, I would never recommend such an approach unless absolutely necessary. I know you're a big fan of all that's VBA, so sorry to be so unequivocal, but there really is no getting away from the answer as far as I see it.

PS. I just reread my previous post and realised I didn't make it clear I was addressing myself to the OP. If it came across as a critique of your post ADezii, it was certainly not intended to. I've updated it to read more clearly.

[ADezii]

@NeoPa
I never interpreted it as a critique of my code, but simply wanted your opinion on the matter. You know how much I respect it.

[chhines]

Thanks for all the replies.
Unfortunately, I still can't get it to work.
Everything I try, the [DATEREVIEWDUE] field just shows #NAME? in it.
I changed what appeared to be a couple of errors in the function call, as the [DATEREVIEWASSIGNED] text box was named [DATEREVIEWASASSIGNED] in ADezii's reply.
Also, I changed the Task subform in the string variable, because this field is actually on the Review_subform, not the Task_subform.
I think we're close, I've attached my function again to see if maybe someone sees what is wrong. I'm thinking maybe in the two string variables, maybe we aren't referring EXACTLY to the path or way it needs to call data from those two subforms in the function.
How can I be sure that this path below in bold is correct, is there somewhere I can check this?
dteDateReviewWasAssigned = Forms![Input Facility Task Reviewer]!Review_Subform.Form![DATEREVIEWASSIGNED]
When I look at the form in Visual Basic Project window under the class objects, it has each referenced as "Form_FORMNAMEHERE" which isn't the name we used when referring to it. Thanks again.

[ADezii]

@chhines
With your Form/Sub-Form Opened:

• Press CTRL+G to Open the Debug Window
• Type in the following line, then press the Enter Key:
  

  Debug.Print Forms![Input Facility Task Reviewer]!Review_Subform.Form![DATEREVIEWASSIGNED]

• The proper Value for ![DATEREVIEWASSIGNED] should be displayed
• You're right, you are very close to a resolution to this problem.
• Did you fdorget to set the Control Source of the [DATEREVIEWDUE] Field equal to the Function, as in:
  

  =CalcDueDate()

• If the above does not solve the problem, can you Upload a copy of the Database. I'm sure that the answer is very simple.

[NeoPa]

@chhines
Have you tried what I suggested in post #4? I see no reference to it.

If you're not interested that's fine, but if I'm to help, I need to know what you've tried, so that I can direct you further. If you prefer to work exclusively with ADezii's solution then that's no problem, but it would be nice to know if so.

[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_Form" 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. [DATEREVIEWASSIGNED] is now Bound to [DATEREVIEWASSIGNED]. It's Control Source equal to the Function has been removed.
4. Look at the same code in the AfterUpdate() Event of [cboTASKTYPE] and [DATEREVIEWASSIGNED] 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 [DATEREVIEWASSIGNED] 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 txtDATEREVIEWASSIGNED 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 (DATEREVIEWASSIGNED) 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 txtDATEREVIEWASSIGNED 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 (DATEREVIEWASSIGNED) 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,inadvertently 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 txtDATEREVIEWASSIGNED 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 (DATEREVIEWASSIGNED) 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 [DATEREVIEWASSIGNED] & 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 [DATEREVIEWASSIGNED] 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.ControlName).
   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]

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]

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]

@ADezii
@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]

NeoPA, you are probably correct, I'm not explaining it properly. My bad.
Anyway, once the DATEREVIEWASSIGNED 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 = DATEREVIEWASSIGNED + TASKTYPE select case statement from ADezii.

This DATEREVIEWASSIGNED 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]

@chhines
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]

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.

[chhines]

NeoPA,
I think I have made a grave error in the explanation of my problem here.
I'm attaching a copy of the database that ADezii asked for when he fixed my original problem.
The form you would want to look at is the Input Facility Task Reviewer.

The only time a "record" is brand new is when it is entered for the first time via the Task_subform, the "task #" is autogenerated when you click in the Task_subform to add a new task.
Once the task is created, and all information is filled in on both the Task_subform and the Review_subform for it, from that point on, I want the DATEREVIEWASSIGNED after it is entered and DATEREVIEWDUE is calculated to both remain the same for everyone else who will work on that task.

It may be confusing because there is a TASK# field on the Task_subform and a TASK# field on the Review_subform.
We're only concerned about the TASK# field on the Task_subform.
Basically, each Task created on the Task_subform can have several "subtasks",if you will, on the Review_subform. For each one of those "subtasks" I want the DATEREVIEWASSIGNED and DATEREVIEWDUE to be the same as was originally set and not editable.
Hopefully, I'm explaining this better this time.

I also have additional questions: I have a question about when I use the VB code editor, when I start typing in control references and stuff, I don't get the autofill option as I type(i.e.Me.NewRecord)in some instances, which I guess makes me fill like my code can't be right. Maybe this doesn't matter and I should keep coding without worrying about it.
Also, when creating the Call New_Routine, etc. I'm always confused about how I need to refer to my controls from form to form. You refer to DATEREVIEWASSIGNED as Me.DATEREVIEWASSIGNED, but I wonder if it matters which form you are on which controls how you should refer to it.

For Example, in ADezii's call for them he put in the DATEREVIEWASSIGNED_AfterUpdate() procedure below.
I've put in Bold the way he referred to it.

If Not IsNull(Forms![Input Facility Task Reviewer]!Task_Subform.Form![cboTaskType]) And _
Not IsNull(Forms![Input Facility Task Reviewer]!Review_Subform.Form![DATEREVIEWASSIGNED])

Also, your syntax for the actual call of the New_procedure. Is it exactly as it should be? I mean do you use the words "Call New_procedure"? Does the syntax work the same way as a function call does?

Thanks again, especially for your patience!

[NeoPa]

@chhines
I will have to look at this when I get some time. Reviewing databases is very much more time intensive than responding to questions posted (as long as they're clear and make sense) in the thread. I never do such work during work hours.

It does seem (sight unseen) as if this may well be a clearer and workable explanation though. i'll let you know when i've tried to follow your instructions. That will be the acid-test of course.
@chhines
There are times when IntelliSense won't work for you. They are various, but some of the reasons (there are probably more) are that you don't have a reference set to the correct library; Code is in the process of execution and is just stopped; The item you're typing in is simply not included in the published list. The first would indicate a problem (generally), whereas the other two wouldn't. Nothing's ever too straightforward, but generally be suspicious if it's not working & you don't understand why.
@chhines
See Referring to Items on a Sub-Form. This gives you the full SP.

Essentially though, it does matter whether you are referring to items on the same form (that the running code is associated with) or on another subform, or even open & unconnected form. See the article for the full details.
@chhines
Interesting question. Procedures can be called in different ways. I'm a bit of a purist & I don't like the way VB or VBA has the same syntax for referring to arrays as it also supports for calling procedures. Hence I prefer to use the Call syntax to indicate explicitly I'm not referring to an array. Call expects the parameters enclosed within parentheses (), whereas, without Call. they would be without. Either should work. Call also indicates that, regardless of whether the procedure is a subroutine or a function (the latter returns a result), the result is not used.

[chhines]

NeoPA,
I've tried several ways to get my date assigned to grey-out and disable after initial update, but I can't get it to work.
I've added to the DATEREVIEWASSIGNED After_update procedure and I also have it in the property for the default value.
I realize at this point I probably shoudn't have it in both places, but I was just basically testing to see what happens in all instances.
I want the DATEREVIEWASSIGNED to be populated with today's date to start, but the initial entry user should be able to change it.
Don't pay any attention to the background formatting, I was testing what different controls do there.
I'm getting the date of 12/31/1899 in the date field now, which from what i've researched is equivalent of 0 in Access.
I can get the two date fields to greyout(disable) once you move to the next record, but it still allows you to change the date in the next record, which you shouldn't be able to do.
I'm attaching an up to date copy of the database so you can see exactly what happens when you open the Input Facility task reviewer form. Thanks.

[NeoPa]

It seems I must apologise.

I thought I had cleared up all my outstanding work but i seem to have forgotten about this one. Let me see if I can get something done on it for you over the weekend.

[NeoPa]

No wonder this is complicated. It seems that there is a lot of confusion over the actual design (in my mind at least, though I suspect in yours too). This should always be properly understood before anything (at all) is implemented. At least I now know enough about your setup to allow me to ask questions so you can't so easily misunderstand me.

1. Please explain in plain English why the DATEREVIEWASSIGNED & DATEREVIEWDUE are held in the Review_Table rather than in the Task_Table?
2. Am I right in thinking you want to store the original date that the record was entered into this field (DATEREVIEWASSIGNED) until somebody chooses to set it explicitly, then, once that has happened once, to disallow any further changes to that field? This is not what you were saying (hence the real difficulty coming up with an answer that satisfied you).

[chhines]

NeoPA,
Yes, essentially you are correct. As you say, "store the original date the record was entered into this field (DATEREVIEWASSIGNED) until somebody chooses to set it explicitly, then, once that has happened once, to disallow any further changes to that field?"
That is what I want to do.

While it does appear confusing, actually the completion date field under the TASK table applies to the task. The reason I guess now it was decided to put the DATEREVIEWASSIGNED/DUE in the review table is the actual part we are tracking in the whole database is the actual review information. It may be a design flaw as you say, but at this point, I don't think i want to make changes to the actual design and layout.

This is the last change I want to make to this database that I have been working on for a few weeks now. If you can help me get this done, I'd be most appreciative before I turn it over to them. Thanks again for your patience and persistence.

[NeoPa]

@chhines
The trouble with this is that there is no real way to determine when a date is entered, whether it is the actual required date, or just the default added to avoid being without a date at all. How important is it to you that your date is defaulted? If it is absolutely important, we could consider two approaches :

1. No default value set, but add an event procedure handling entry into the control, and if the current value is still Null, then change it to the value of Date().
2. Keep the default value, but create another field in your record indicating whether or not the value has ever been changed (NB. If the default value was actually the one required you will never know whether or not it is the actual value required). This is very clumsy because it is doing it completely the wrong way. It doesn't really make sense, but is a result of the misdesign that, for the moment, you want to live with.

@chhines
This is a situation you should go to great lengths to avoid in future. An article (Normalisation and Table structures) by MSquared from this site, is the best I've seen to help explain how best to do this and why it makes such a difference. Well worth reading up on before your next database project.
@chhines
I will help as I can. Let's see if this gets you where you need to be.

[chhines]

NeoPA,
This actually was my first database project, so there was a lot of learning in ALL aspects. So I'm sure it is much flawed. Ultimately, just to get this done, I'd rather use the approach where we call a procedure to check if it is NULL and then put in the default date, so let's go that route. How do we do this?

After doing much thinking about this, I came to the same realization you had in your post, which does make it sort of impossible. ("If the default value was actually the one required you will never know whether or not it is the actual value required").

I'd like to hear the second way you speak of about having another field added to check if the date has ever been changed. I'm very curious to see how you'd handle this. BTW, I don't know what the NB stands for...

Thanks again for all your help and patience.

[NeoPa]

@chhines
This only works if you leave the default out of it. That way the operator only ever enters a date when they know it is correct.

The code for this would be much the same as the earlier suggestion (posts #28 & #30) but with the New_Routine() as follows :

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

@chhines
This is a little complicated. In truth, I would not handle it like this. I would go the way of using Null to indicate the date had not been entered and use the code posted. That's always assuming of course, that I would get into a similar situation, which I would hope not to. You can get away with it as a relative newbie. If I made a design mistake like that I'd be looking for who'd spiked my drinks :D
@chhines
NB is older and even more common than BTW for instance. It is actually latin and stands for Nota Bene - or note well. It is generally used when someone wants to draw attention to a concept or otherwise warn of a situation.

[chhines]

Well, NeoPa, I tried that. I don't think with the flawed design that we'll ever be able to get this to work though.
I tested it and copied it exactly as you said.
I think maybe I missed something here though.

In your earlier post, you said, "add an event procedure handling entry into the control, and if the current value is still Null, then change it to the value of Date(). "
With the code posted in your last post however, this won't get that done I don't believe. If I missed something, my bad.

Anyway, I just found out today that I'm most likely going to lose my job this Friday, so this will all be for not anyway. Don't worry, it has nothing to do with this, just a lack of funds due to non-settled budget. I just wanted to finish this up as my last hurrah, since I won't be able to work on this project after that...Thanks.

I tried something like this:

Private Sub New_Routine()
 
    If (Not IsNull(Me.DATEREVIEWASSIGNED)) Then
    Me.DATEREVIEWASSIGNED.Locked = True
    Else
    Me.DATEREVIEWASSIGNED = Date
    Me.DATEREVIEWASSIGNED.Locked = True
    End If
 
End Sub

However, this (due to the flawed design of the whole database) kind of contradicts itself and basically puts in the default date if it ISNULL, which defeats the purpose, I guess. I was just trying to add a test in there for the procedure you said I needed above.

Thanks again hopefully we can get this done real soon. With everything else on my mind it is hard to completely focus on this now. Thanks.

[NeoPa]

@NeoPa
Please check this (quoted) bit again.

Your code seems to be working to a completely different agenda from this. NB. The explanation around it is also important.

Line #6 of your code sets the value of [DATEREVIEWASSIGNED]. This will stop the whole concept working at all.

Line #7 sets .Locked to True where it should be setting it to False.

If you have a reason for wanting to change the code then let's hear it. I suggested it from the understanding I had of what you needed. That may be wrong, but if so it may help to update me on that. I'll look over anything you suggest of course, but do be careful of making the code less appropriate, rather than more.