AfterUpdate to set the Progress field?

Dan -
1)  What  is XXX?  Date, text, yes no?  What are you storing there?
2)  How have you set up your tables / fields?  (Please don't tell me you have them set up as your screenshot).
3)  Are your stages always sequential?  1, 2, 3, 4?  or could you have 1 complete, 3 started, 2 waiting, etc?
4)  Do all clients do all stages?
5)  Do you ever have more stages?

At a bare minimum it looks to me like you have a Many-to-Many relationship going on.
See the Normalizing Data as well.

Alex - That could work - Set the control source to the field and use the AfterUpdate to set the value in the table?

Scott
1) Could be anything. Most likely a date or currency (It isn't built yet). Will most likely use an "Is Not Null" or <>0 for the criteria.

2) No, screenshot was to provide an easy to see way of showing you what i wanted to achieve. It uses one table with 4 fields (stage 1 through to stage 4) and had [Progress] as an unbound control. Now want to have [Progress] as a field in the table (So 5 fields, Stage 1 through 4, and Progress)

3) Yes, Always Sequential. Was going to write the IIF statement to say (If 1=xxx and 2=Null and 3=null etc etc). again, i think i can work out the expression, just need a way of writing the result to the table.

4) No. Many clients will cancel or not reach end stage. One of the stages in fact is NPW (Not Proceeding with) which is a Yes/No. Tick NPW will set Progress to NPW (For Example)

5) Unlikely but wouldn't rule it out.

If it helps in Excel terms, the current system that is set up uses this formula

=IFS((AND(R1792="",S1792="",T1792="",Z1792="")), "PIPELINE", (AND(R1792<>"",S1792="",T1792="",Z1792="")), "ON RISK", (AND(R1792<>"",S1792<>"",T1792="",Z1792="")), "PAID", (AND(R1792<>"",S1792<>"",T1792<>"",Z1792="")), "CLAWBACK",(AND(Z1792="NPW")), "NPW")

1st "AND" is saying "If Null in R,S,T or Z, Say PIPELINE"
2nd "AND" Says "IF R has More or Less than Null AND S/T/Z , Say ON RISK"
And so on

Again, i should be able to write this for the DB, just need a way of writing the resulting value into the table

As long as you trust your user to enter the data in order (1, 2, 3, 4) then this would be a simple Nested IIF statement.

Status: IIF(Stage4,"Stage 4",IIF(Stage3,"Stage 3") ...

Thanks Rick,

I understand the IIF statement, its where to put it i'm not sure on. I need to place it in a way that it records the result into the table (As opposed to just calculating on-the-fly)

Dan

You would use the after update (I'm assuming) to 'set' your field.

Like this"
YourFieldName = TheResultsOfYourIIFStatement

Why would you not want to calculate it on the fly? Unless you're pulling tens of thousands of records at a time, that's how I would do it.

However, if you want to store the value in the table, which again I don't recommend, then you'll need to update the value in the AfterUpdate event for each of the other fields. When Status1 is checked, for example, then you have to update it. Status2, same.

Thanks guys, i'll give it a go.

I agree with you Rick and i loath to store that info but, alas, after 3-5 years, i expect there will indeed be tens of thousands of records and believe it'll be easier for reporting/filtering purposes. All other fields use proper normalization rules but this one is special!

That's fine... you know your database... it's just not something I do unless absolutely necessary.

I thought i was ok with the IF statement! This doesn't work and was wondering if someone could cast their eye over it and help me with whats wrong.

4 Fields on the form are "monitored". They are
Text_StartDate
Text_StatementDate
Text_ClawbackDate
Text_NPWDate

This piece of code has been applied to all 4 of these. The FIELD in the table to be updated is "Status".

I realise there will be a more efficient way to do this, which i'll be happy to do once i reach the developer levels. But for now, just looking to get it working. As always, thank you.

At the start before the if add
Debug.Print Text_StartDate etc
What do you get for each field?

Wow, that's a lot of code and for only one event. What about the other 3 AfterUpdate events that will be needed?
I built a sample that uses a Nested IIF and one line of SQL. I already have the screenshot made up and ready to post.

Yeah create a sub for that and call it on each AfterUpdate

@Alex - I'm getting End IF Block error (see pics).

@Kevin & Alex - I agree its dirty and inefficent. I'm working with the knowledge i have for now understanding that once i learn more about coding, i can go back into it and clean it up. I'm only at Expert 8 for the moment

Remember this code is applied to the after update on all 4 controls

Thanks again

You can only have one Else statement in an If/Then block. The rest need to be ElseIf. Based on the code I see you writing, it looks good, but you need some fundamentals. I'd strongly recommend Access Developer 1. Might save you from pulling out some hair. :)

BTW: If, Then, ElseIf, Else is covered in Lesson 5 of ACD1