Hide subform if no records

[geno]

Hi,
Is there a way to hide a subform on a main form if there is no records in the subform?
Thanks

 

[RuralGuy]

Not a particularly good user interface but SubFormControls have a .Visible property.

 

[geno]

The code I'm using is:
Private Sub Form_Current()
If Forms![frmAddNewParts]![frmStockMaintenance].Form.RecordsetClone.RecordCount = 0 Then
Forms![frmAddNewParts]![frmStockMaintenance].Form.Visible = False
Else
Forms![frmAddNewParts]![frmStockMaintenance].Form.Visible = True
End If

When the form opens I get this error:
runtime error 2455
You entered a expression that has an invalid reference to the property form/report. The first line of code is highlighted when in debug mode.

Any ideas.
Thanks

 

[RuralGuy]

You want to work with the SubFormControl.

Private Sub Form_Current()
If Forms![frmAddNewParts]![frmStockMaintenance].Form.RecordsetClone.RecordCount = 0 Then
'Forms![frmAddNewParts]![frmStockMaintenance].Form.Visible = False
[B]If this is on frmAddNewParts then you don't have to go through the Forms collection.
Change to:
Me!frmStockMaintenance.Visible = False[/B]
Else
'Forms![frmAddNewParts]![frmStockMaintenance].Form.Visible = True
[B]Me!frmStockMaintenance.Visible = True[/B]
End If

 

[geno]

Hi,
I've changed the code to:
Private Sub Form_Current()
If Forms![frmAddNewParts]![frmStockMaintenance].Form.RecordsetClone.RecordCount = 0 Then
Me![frmStockMaintenance].Form.Visible = False
Else
Me![frmStockMaintenance].Form.Visible = True
End If
End Sub

But I still get the same error.
Anymore ideas?
Thanks

 

[RuralGuy]

Don't use the "Form" reference!
Me![frmStockMaintenance].Form.Visible = True
s/b
Me![frmStockMaintenance].Visible = True

 

[geno]

Sorry missed some of your suggestion, here's the code now:

Private Sub Form_Current()
If Forms![frmAddNewParts]![frmStockMaintenance].Form.RecordsetClone.RecordCount = 0 Then
Me![frmStockMaintenance].Visible = False
Else
Me![frmStockMaintenance].Visible = True
End If
End Sub

Still get the same error.

 

[RuralGuy]

Is the SubForm bound to a table/query? Use the RecordSet.RecordCount rather than RecorsSetClone.RecordCount

 

[geno]

The subform is run from a query. I've tried your suggestion:
Private Sub Form_Current()

If Forms![frmAddNewParts]![frmStockMaintenance].Form.Recordset.RecordCount = 0 Then
Me![frmStockMaintenance].Visible = False
Else
Me![frmStockMaintenance].Visible = True
End If

End Sub

Still get the same error.

 

[RuralGuy]

Time for some diagnostic code! Put two new textboxes on your main form. We'll call one txtSubForm and the other one txtVisible.

Set the Control Source of txtSubForm to:
=frmStockMaintenance.Form.Recordset.RecordCount

Set the Control Source on txtVisible to:
=frmStockMaintenance.Visible = False

Which one throws the error?

 

[geno]

Neither one errors out....

 

[RuralGuy]

Ok, change the OnCurrent code to:

If Me.frmStockMaintenance.Form.Recordset.RecordCount = 0 Then
  Me.frmStockMaintenance.Visible = False
Else
  Me.frmStockMaintenance.Visible = True
End If

We're not using ! or the Forms collection or the [].

By the way, do the diagnostic TextBoxes show the RecordCount and the Visible condition?

 

[geno]

Still errors out..

 

[RuralGuy]

By the way, do the diagnostic TextBoxes show the RecordCount and the Visible condition?

I don't see the answer.

 

[geno]

Hi,
Figured out what was going on, there was a goto new record when the form opens. This was causing the error.
Thanks for all your help, the lesson was great.

 

[RuralGuy]

You're welcome. It is interesting here to try and see with your fingers. Glad you got it working.