VBA for main form On Open event to show tabbed controls only if there is data

[isaacski]

Hi All,
I've stumped myself. To summarize as best I can, I have a report that tracks scores for our employees. From the report, you can click a button to add a new score in a form or edit an existing score(frmscoretracker). On this form there are two subforms, in a tabbed control to track additional information about the score; what areas were marked down(Trends), and was it a failing score(AutoFail).

When this form opens I have it programmed to only show the subform if there is data in it. The goal being, if I am adding a new score and there is no existing trends or Autofails for this new record, neither subforms will show - I will add an after update even to show either trends or autofail depending on the score recorded. Also, if someone chooses to edit the score, whatever subform with data, will show as well.

The second half of this works well. The first half though, doesn't. When someone clicks to add a new score, opening this main form to a new record, both of the tabs show. However, if the form opens to an existing record, the appropriate tab shows. Here is the code

Private Sub Form_Open(Cancel As Integer)
'If the subform has a record, the tab is visible, if not, the tab is not visible
If Me.frmtrends.Form.Recordset.RecordCount > 0 Or IsNull(Me.Trends) Then
Me.Trends.Visible = True
Me.TabCtl33.Visible = True
Else
Me.Trends.Visible = False
End If

'If the AutoFail subform has a record, it is visible.  If not, it is not visible
If Me.subfrmautofail.Form.Recordset.RecordCount > 0 Or IsNull(Me.Auto_Fail) Then
Me.Auto_Fail.Visible = True
Me.TabCtl33.Visible = True
Else
Me.Auto_Fail.Visible = False
End If
End Sub

Both tabs are set as not visible in the default settings. Is there something in this code that is triggering then to be visible when there is no record in the main form?

Any thoughts would be appreciated!! Sorry so lengthy

Kim

 

[Cronk]

Put a break point on your first line (F9) then progressively step through the event by pressing F8

 

[isaacski]

Something new I learned, thanks! I put a break point around the first if, then and clicked the button on the report to open the form and step through. The form didn't show up until I F8'd D) through the code, then it just popped up to the first record... am I stepping through incorrectly?

 

[Cronk]

The form open event happens as the form is about to open. That's why you don't see it until the event finishes. Note the cancel in the event parameters. This can be set to false to stop the form opening.

Using techniques like stepping through code and examining variable values is a powerful debugging 'tool'.

 

[isaacski]

Perhaps I'm not understanding. I assumed you advised to put a stop and step through so I could determine at what point the code was telling a newly added record to show both tabs instead of leaving them as not visible. How would I be able to do this if the form doesn't open until the end of the event? Perhaps you meant for another purpose?

Please forgive my ignorance on this topic...