Solved Changing a main form textboxes text based on a subform on a tab control having no data

[Sam Summers]

Hello once again,
Been trying all kinds of things to achieve this nut so far not successful.

I have a main form 'ViewVessel' which has a tabbed form on it 'TabCtl15' in one of the tabs i have two subforms 'MaintenanceListSubform' and 'ServiceListSubform'

What i want to do is to mainly change the text in the textbox 'NewStatus' on the 'ViewVessel form if both of these subforms have no data.
This is the code i have been attempting to achieve this with:

Private Sub Form_Load()

    Dim rst As DAO.Recordset
    
    Set rst = Forms!ViewVessel.MaintenanceListSubform.Form.RecordsetClone
    rst.MoveLast
    If Me.MaintenanceListSubform.Form.RecordsetClone.RecordCount + Me.ServiceListSubform.Form.RecordsetClone.RecordCount = 0 Then
    Forms![ViewVessel]![NewStatus].Text = "In Service"
    Else

    Set rst = Nothing
    
End If

End Sub

Is this possible?
Many thanks in advance

 

[Minty]

I would think it is, you haven't said what isn't happening.
I would add some checks into your code
Debug.print Me.MaintenanceListSubform.Form.RecordsetClone.RecordCount , Me.ServiceListSubform.Form.RecordsetClone.RecordCount
To see what values you are getting.

Be aware that subforms load before the main form as well so they may not have data until the main form is fully loaded.

You are also only going to the last record on one of the subform recordsources.

 

[Sam Summers]

I would think it is, you haven't said what isn't happening.
I would add some checks into your code
Debug.print Me.MaintenanceListSubform.Form.RecordsetClone.RecordCount , Me.ServiceListSubform.Form.RecordsetClone.RecordCount
To see what values you are getting.

Be aware that subforms load before the main form as well so they may not have data until the main form is fully loaded.

You are also only going to the last record on one of the subform recordsources.

Hi Minty,

I put the debug.print code in and also stepped through the whole lot but nothing really happened?
Not sure if i put it in the right place though?

 

[Minty]

The values should appear in the immediate window in the VBA editor. (Press ctrl + g to view it in the editor)

To make sure you are seeing something you could add
Debug.print "Values are: " & Me.MaintenanceListSubform.Form.RecordsetClone.RecordCount , Me.ServiceListSubform.Form.RecordsetClone.RecordCount

Put it in before your If statement.

 

[arnelgp]

you can also use the Form's Current event

Private Sub Form_Current()

    Dim rst1 As DAO.Recordset
    Dim rst2 As DAO.Recordset
  
    Set rst1 = Forms!ViewVessel.MaintenanceListSubform.Form.RecordsetClone
    Set rst1 = rst1.Clone
  
    Set rst2 = Me.ServiceListSubform.Form.RecordsetClone
    Set rst2 = rst2.Clone

    If rst1.RecordCount + rst2.RecordCount < 1 Then
        Forms![ViewVessel]![NewStatus] = "In Service"
    End If

    Set rst1 = Nothing
    Set rst2 = Nothing
End If

End Sub

 

[Gasman]

@arnelgp

What is the purpose of recordset.Clone please?

Also why is rst2 set to rst1 ?

 

[arnelgp]

Also why is rst2 set to rst1 ?

good catch mr.gasman.

 

[Sam Summers]

The values should appear in the immediate window in the VBA editor. (Press ctrl + g to view it in the editor)

To make sure you are seeing something you could add
Debug.print "Values are: " & Me.MaintenanceListSubform.Form.RecordsetClone.RecordCount , Me.ServiceListSubform.Form.RecordsetClone.RecordCount

Put it in before your If statement.

Right, it is saying - Values 1 1
Even though the subforms are both blank?
I suspect the query is not filtering correctly?

 

[Sam Summers]

you can also use the Form's Current event

Private Sub Form_Current()

    Dim rst1 As DAO.Recordset
    Dim rst2 As DAO.Recordset
 
    Set rst1 = Forms!ViewVessel.MaintenanceListSubform.Form.RecordsetClone
    Set rst1 = rst1.Clone
 
    Set rst2 = Me.ServiceListSubform.Form.RecordsetClone
    Set rst2 = rst2.Clone

    If rst1.RecordCount + rst2.RecordCount < 1 Then
        Forms![ViewVessel]![NewStatus] = "In Service"
    End If

    Set rst1 = Nothing
    Set rst2 = Nothing
End If

End Sub

Hi Arnel,

I have managed to get it working from your code.

Thank you once again and thank you for all of your suggestions and help as always

 

[Gasman]

good catch mr.gasman.

And setting from the .Clone property ?

 

[Minty]

@Gasman I was intriguied by the same line
Set rst1 = rst1.Clone

I'm guessing it disconnects the rs from the forms recordset clone.

 

[mike60smart]

good catch mr.gasman.

Hi Arnelgp

Could you possibly post your Code again with a Comment on each line to explain what it does?

 

[arnelgp]

it is based on my experience, that .RecordsetClone, most of the time
don't give correct Records (and record count) (maybe delayed part on msa
to commit changes, i don't know).

simply creating a clone, actually get the latest/updated recordset.