Open form and show or hide controls depending on data 1

[hansretallick]

I have some controls on a form open that are shown or hidden depending on whether or not there is data accessed by queries. I have decided to default to the controls being visible on form open, and if the query results in no data, the controls are then hidden. For some reason the controls are visible whether there is data or not. Can anyone show me what I am doing wrong? Here is an example of the code I am using in the OnOpen property of the form.

'Run Pay Landlord controls

DoCmd.OpenQuery "qryStatementDue1", acViewNormal, acEdit

If Query!qryStatementDue1.RecordsetClone.RecordCount = 0 Then

Me.ImgPayLandlords.Visible = False
Me.lblPayLandlords.Visible = False

End If

I run this three times for three controls based on three different queries. All of the controls are visible whether there is data or not.

I have tried

If Me.RecordsetClone.RecordCount = 0 Then

but that doesn't work either.

 

[TheAceMan1]

How are ya hansretallick . . .

For starters, bear in mind [blue]you can't hide a control if it has the focus![/blue] If this is true for any of the controls you wish to hide you'll have to move the focus to a control that stays visible 1st. Next try the following code:

[blue]   Dim db As DAO.Database, rst As DAO.Recordset
   
   Set db = CurrentDb
   Set rst = db.OpenRecordset("qryStatementDue1", dbOpenDynaset)
   
   If rst.BOF Then [green]'no records if true[/green]
      Me.[purple][B][I]ControlNameThatStaysVisible[/I][/B][/purple].SetFocus
      Me.ImgPayLandlords.Visible = False
      Me.lblPayLandlords.Visible = False
   End If
   
   Set rst = Nothing
   Set db = Nothing[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[hansretallick]

Many thanks, Ace Man. That works perfectly.

Kind regards
Hans