Solved conditional formatting problem

[bigmac]

can you help me please , i have a subform with two columns , one called [date] and another called [status], what i am trying to achieve is that if the [date] value in a row is <= todays date then the value in [status] will change to " expired", can you show me how i can do this please?

 

[arnelgp]

you need an Update query or Update the Recordset of the subform.
on the Current Event of the subform, add this code:

Private Sub Form_Current()
With Me.RecordSetClone
    If Not (.BOF And .EOF) Then
        .Movefirst
    End If
    Do Until .EOF
        If ![Date] <= Date Then
            .Edit
            !Status = "Expired"
            .Update
        End If
        .MoveNext
    Loop
End With

 

[cheekybuddha]

@arnelgp, you suggest to run this update on the whole rs/table every time the user navigates to a different record?

 

[CJ_London]

Status: iif([date]<=date,"Expired")

Note 'date' is not a good name for a field - it can get confused with the date function which returns todays date

if your date field contains time element (may be hidden due to formatting) then you need

Status: iif([date]<date+1,"Expired")

 

[bigmac]

thank you arnelgp it works

 

[arnelgp]

run this update on the whole rs/table every time the user navigates to a different record?

it won't hurt for a hundreds of records.

also he can add code to check if it the value is already "Expired", so need not to update it.

 

[bigmac]

it won't hurt for a hundreds of records.

also he can add code to check if it the value is already "Expired", so need not to update it.

how do i do this?

 

[arnelgp]

Private Sub Form_Current()
With Me.RecordSetClone
    If Not (.BOF And .EOF) Then
        .Movefirst
    End If
    Do Until .EOF
        If (![Date] <= Date) And (!Status & "" <> "Expired") Then
            .Edit
            !Status = "Expired"
            .Update
        End If
        .MoveNext
    Loop
End With

 

[bigmac]

Private Sub Form_Current()
With Me.RecordSetClone
    If Not (.BOF And .EOF) Then
        .Movefirst
    End If
    Do Until .EOF
        If (![Date] <= Date) And (!Status & "" <> "Expired") Then
            .Edit
            !Status = "Expired"
            .Update
        End If
        .MoveNext
    Loop
End With

stopped working now?

 

[cheekybuddha]

Adjust to:

' ...
        If (![Date] <= Date) And (!Status & "") <> "Expired" Then
' ...

However, do you really need this field in your table at all?

If [Date] <= Date then it's expired, so just use the expression suggested by CJ_London in post #4, in a query to use as your form's RecordSource.

Then no need for any code, and it will update by itself.

 

[bigmac]

Adjust to:

' ...
        If (![Date] <= Date) And (!Status & "") <> "Expired" Then
' ...

However, do you really need this field in your table at all?

If [Date] <= Date then it's expired, so just use the expression suggested by CJ_London in post #4, in a query to use as your form's RecordSource.

Then no need for any code, and it will update by itself.

hello cheekybuddha , can you show me how to do this please?

 

[cheekybuddha]

What is the RecordSource of your form?

What are the fields in the table if it is just a table name?

 

[bigmac]

the fields in the table are [date](this is a date field) and [status] this field shows "active" or "expired"

 

[cheekybuddha]

Just 2 fields in the table?

What is the table name?

Are there any other fields/controls on your form?

 

[bigmac]

Just 2 fields in the table?

What is the table name?

Are there any other fields/controls on your form?

there are a lot more fields on the subform, not just those two i have mentioned, but they are the only two i want to work with, the table is called "subform"

 

[bigmac]

@bigmac You get far better results if you respond to ALL questions. Are you actually using the word "Date" as the name of a field in your table? If so, you have a problem that you need to fix. Please review the example I linked to.

hi pat , i have changed the name of the field to " twelve month update"

 

[bigmac]

would an update query be better for this?

 

[bigmac]

would an update query be better for this?

just thinking aloud

 

[Cronk]

I didn't see any mention of the non-normalized table where field Status is dependent on the date field. Rather than store Status in the table, on the form or report have the source for the Status field = iif(DateField< Now, "Expired", "Not Expired") or whatever

 

[bigmac]

hi all, and thank you for your input, i have tried a query update and this works fine as well ,