I'm thinking this a form problem

[FXST2001]

Hello All,

I'm fine tuning a personnel db but having problems figuring this out:

I have a subform in my db that lists four components:

crit1, crit2, crit3 and crit4 and an overall Status

The part I'm having a drama with is trying to find a way that when one of the crit becomes "Not Ready" that the overall
Status box also reflects "Not Ready"

e.g.

crit1 01/07/10 Ready (valid 6 months)
crit2 07/05/10 Ready (Valid 12 months)
crit3 15/02/10 Ready (valid 12 months)
crit4 26/05/10 Ready (valid 6 months)
Status Ready

crit1 01/07/10 Ready (valid 6 months)
crit2 07/01/10 Not Ready (Valid 12 months)
crit3 15/02/10 Ready (valid 12 months)
crit4 26/05/10 Ready (valid 6 months)
Status Not Ready

Clear as mud?

 

[dbDamo]

Something along the lines of:-

If Me.crit1 = "Ready" Then
   If Me.crit2 = "Ready" Then
      If Me.crit3 = "Ready" Then
         If Me.crit4 = "Ready" Then
         Me.status = "Ready"
         Else
         Me.status = "Not Ready"
         End If
      Else: Me.status = "Not Ready"
      End If
   Else: Me.status = "Not Ready"
   End If
Else: Me.status = "Not Ready"
End If

You'll need to place this code in the form's OnCurrent event and also in each of the crit controls AfterUpdate events, so the best way to do this would be to create a function with my code and call that function from the required events.

Edit - sample attached

 

[FXST2001]

Thanks dbDamo,

I'll give that a go tomorrow when my mind has had a break, but now it's off to bed.


Cheers Damo


Eddie

 

[dbDamo]

No probs, let me know if you have any problems

 

[SOS]

If those are fields (Crit1, Crit2, Crit3, Crit4) then I suggest you NORMALIZE your database as repeating fields are not the way to go.

If those are ROWS in a table, then a simple DCount should find any that have the status you are looking for:

If DCount("*", "tableNameHere", "[SomeIDHere]=" & Me!IDField & " AND [CritField]='Not Ready'") > 0 Then