form/subform problem

[bigmac]

hello all , can you help please ?
i have a form called "sheet1" and a subform called "subform",
the form has a text box called "stat" and the subform has a text box called "status",
i have a record set to change all status values to "expired" on a set condition,
Private Sub Form_Current()
With Me.RecordsetClone
If Not (.BOF And .EOF) Then
.MoveFirst
End If
Do Until .EOF
If (![twelve month update] <= Date) And (!status & "") <> "Expired" Then
.Edit
!status = "Expired"
.Update
End If
.MoveNext
Loop
End With
End Sub

what i am trying to achieve is when i open the subform and it goes through the record set that if all the "status" values are "expired" then the "stat" text box on "sheet1" will change to "expired",
can you help please

 

[arnelgp]

you may try (on the current event of ther subform):

Private Sub Form_Current()
    Dim nTotal As Long
    Dim nCount As Long
    nTotal = Me.Recordset.RecordCount
    With Me.RecordsetClone
        If Not (.BOF And .EOF) Then
            .MoveFirst

            Do Until .EOF
                If (![twelve month update] <= Date) And (!status & "") <> "Expired" Then
                    .Edit
                    !status = "Expired"
                    .Update
                End If
                .MoveNext
            Loop
  
            'loop again to count how many has expired
            .MoveFirst
          
            Do Until .EOF
                If !status = "Expired" Then
                    nCount = nCount + 1
                End If
                .MoveNext
            Loop
       End If
    End With
      
    If nCount > 0 And nCount = nTotal Then
        If Me.Parent!stat & "" <> "Expired" Then
             Me.Parent!stat = "Expired"
            Me.Parent.Form.Dirty = False
       End If
    End If
End Sub

/code]

 

[GaP42]

Why not do a DCOUNT on the set of records that your subform is showing to test if the subform records have any status value <> "Expired". If a count > 0 is returned then the STAT in the Sheet1 form stays as is, else change it to display "Expired" on the Sheeet1 form (not stored in the underlying data).
You may need to take special account of Null values in the data.

 

[bigmac]

you may try (on the current event of ther subform):

Private Sub Form_Current()
    Dim nTotal As Long
    Dim nCount As Long
    nTotal = Me.Recordset.RecordCount
    With Me.RecordsetClone
        If Not (.BOF And .EOF) Then
            .MoveFirst

            Do Until .EOF
                If (![twelve month update] <= Date) And (!status & "") <> "Expired" Then
                    .Edit
                    !status = "Expired"
                    .Update
                End If
                .MoveNext
            Loop
 
            'loop again to count how many has expired
            .MoveFirst
         
            Do Until .EOF
                If !status = "Expired" Then
                    nCount = nCount + 1
                End If
                .MoveNext
            Loop
       End If
    End With
     
    If nCount > 0 And nCount = nTotal Then
        If Me.Parent!stat & "" <> "Expired" Then
             Me.Parent!stat = "Expired"
            Me.Parent.Form.Dirty = False
       End If
    End If
End Sub

/code]

just tried this , not working, any ideas ?

 

[bigmac]

Why not do a DCOUNT on the set of records that your subform is showing to test if the subform records have any status value <> "Expired". If a count > 0 is returned then the STAT in the Sheet1 form stays as is, else change it to display "Expired" on the Sheeet1 form (not stored in the underlying data).
You may need to take special account of Null values in the data.

can you show me how to do this please?

 

[GaP42]

The text box Stat should not be bound. Set its value using the expression builder ("expired" or "not expired") using an IIF statement that depends on the value of DCOUNT. Lookup the DCOUNT function and its parameters applying the appropriate criteria for selecting the records of interest, the name of the query for the subform, and the column name "Status" or * as what you need to count. ( Ref: DCOUNT - Microsoft)
Not back for while.

 

[arnelgp]

just tried this , not working, any ideas ?

supposed to be in subform's Current event.
see this demo. open table1 and table2 first to see that stat and status is blank.
open DemoForm, first record will not change since the date is nov-1-2024.
go to next record and see how it changes.

 

[bigmac]

supposed to be in subform's Current event.
see this demo. open table1 and table2 first to see that stat and status is blank.
open DemoForm, first record will not change since the date is nov-1-2024.
go to next record and see how it changes.

 

[bigmac]

Status not changing to expired neither is stat

 

[arnelgp]

close the db first.
right-click on it and on the shortcut menu, choose Property.
Tick "Unblock". (because you downloaded it, your OS is blocking the VBA).
open the db again and run the form.
if you see any "warnings" when you open the db, just make sure to allow it.

 

[bigmac]

close the db first.
right-click on it and on the shortcut menu, choose Property.
Tick "Unblock". (because you downloaded it, your OS is blocking the VBA).
open the db again and run the form.
if you see any "warnings" when you open the db, just make sure to allow it.

working now , will try code on my database

 

[arnelgp]

working now , will try code on my database

remember to put it on the Subform's current event not on the Main form.

 

[bigmac]

working now , will try code on my database

coped your code to my database and it seams to be working , thank you , i must have done a typo before