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?

 

[Pat Hartman]

If you are actually using Date as a field name, I would suggest changing it immediately. You will find that your code is NOT working as you expect. Take a look at this example to see the problem.

Useful code AND the effects of using bad column names

This database has several useful code procedures that you can copy into your BE to fix up defaults you don't like: 1. Change the allow ZLS string property to No If you want to make a text field required, it makes no sense at all to have the Allow ZLS property set to Yes. 2. Turn off sub...

www.access-programmers.co.uk

I recently modified the example to include a field named Now which means you have no way to get the current date. prior to that modification to the example, you could get the date by using Now() Look at the code in the click event of the print button next to Date. Open your form and then try printing from a record into the immediate window to see the problem in your data.

Here is a picture showing the "print" of the Date example.

 

[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"

 

[Pat Hartman]

@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.

 

[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