IIf statement not working, SELECT case instead?

[chhines]

IIf statement not working, SELECT case instead?

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 DATEREVIEWASSIG NED 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 DATEREVIEWASSIG NED 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.New Record)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 DATEREVIEWASSIG NED as Me.DATEREVIEWAS SIGNED, 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 DATEREVIEWASSIG NED_AfterUpdate () procedure below.
I've put in Bold the way he referred to it.

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

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]

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 DATEREVIEWASSIG NED 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 DATEREVIEWASSIG NED and DATEREVIEWDUE to be the same as was originally set and not editable.
Hopefully, I'm explaining this better this time.

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.

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.New Record)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.

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.

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 DATEREVIEWASSIG NED as Me.DATEREVIEWAS SIGNED, 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 DATEREVIEWASSIG NED_AfterUpdate () procedure below.
I've put in Bold the way he referred to it.

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

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.

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?

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 DATEREVIEWASSIG NED 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 DATEREVIEWASSIG NED 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 DATEREVIEWASSIG NED & 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 (DATEREVIEWASSI GNED) 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]

IIf statement not working, SELECT case instead?

NeoPA,
Yes, essentially you are correct. As you say, "store the original date the record was entered into this field (DATEREVIEWASSI GNED) 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 DATEREVIEWASSIG NED/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]

NeoPA,
Yes, essentially you are correct. As you say, "store the original date the record was entered into this field (DATEREVIEWASSI GNED) 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.

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.

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 DATEREVIEWASSIG NED/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 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.

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.

I will help as I can. Let's see if this gets you where you need to be.

[chhines]

IIf statement not working, SELECT case instead?

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]

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?

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

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.

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

BTW, I don't know what the NB stands for...

Thanks again for all your help and patience.

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]

IIf statement not working, SELECT case instead

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]

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

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 [DATEREVIEWASSIG NED]. 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.