Sub-Form Display

[katie george]

Hiya -

I wondered if anyone knows if its possible to display a subform only when there is data to display. I have a tab control with 3 subforms in datasheet view, each sub-form is linked to the main form via a JOB_ID field - I only want the sub-form to display if it has a related record, at the moment it displays a blank record with the JOB_ID from the main form.

Thanx

Katie

 

[Rusty]

I was wondering if the subform is only visible if there is data to display, how will the user enter data into the subform?

Yours confusedly,

Rusty

 

[CBragg]

How about

'If [NameOfField] (from the subform) IsNull then
Subform.Visible = False

Hope this is of help.

 

[Rusty]

How about

'If [NameOfField] (from the subform) IsNull then
Subform.Visible = False

Hope this is of help.

I tried that and it doesn't work...

I reckon it should be something like this (placed in the OnCurrent evnt of the main form):

Private Sub Form_Current()
If IsNull(Fieldname) Then
subformName.Visible = False
Else
subformName.Visible = True
End If

End Sub

However, the code needs to pick out the field from the subform or else it'll only be picking out the JOB_ID field on the main form, which is not the 'target' in this case.

Any ideas? I think it needs to be in the line "If IsNull(Fieldname) Then"

Rusty

 

[katie george]

Thanx for the ideas, i'll have a go and see how i get on!

 

[CBragg]

Yeah your code looks correct, i was just rushing something out.

In the query there should be a bound field which relates the sub forms. I suggest that the code is put on an after update on one of the fields on the main form and then the (FieldName) should reference one of the bound fields.

Should probably look like this:

If Isnull ([Forms]![sub-form]![FieldName]) Then
[Forms]![Subform].visible = false.

Something along those lines i reckon ??

 

[Rusty]

Katie,

I've cracked it!!

In the On Current event of the main form you need to place the following code:

Private Sub Form_Current()
If IsNull([subformName]![JOB_ID]) Then
subformName.Visible = False
Else
subformName.Visible = True
End If

End Sub

Thanks to C Bragg for the help on the final (most important) piece of the puzzle.

Rusty

 

[Mike375]

You should probably add a GoToControl (on the mainform) before the Visible/Invisible in case someone has clicked on the subform when it is visible.

In a SetValue macro the condition is

[Forms]![MainForm]![SubF]![Field] Is Null for SetValue Visible No
[Forms]![MainForm]![SubF]![Field] Is Not Null for SetValue Visible Yes

If the cursor is on the subform then you can't make it invisible so therefore best to do GoToControl on the MainForm

Mike

 

[Rusty]

You should probably add a GoToControl (on the mainform) before the Visible/Invisible in case someone has clicked on the subform when it is visible.

I tested that out and the code I posted before works fine on its own even when someone has clicked on the subform when it is visible and then goes to a record on the main form where it is not - the subform still disappears/appears correctly.

I guess that's because it's in the On Current event and so the code runs when the focus moves from one record to another.

Thanks for the heads up though.

Rusty

 

[Mike375]

I tested that out and the code I posted before works fine on its own even when someone has clicked on the subform when it is visible and then goes to a record on the main form where it is not - the subform still disappears/appears correctly.

I guess that's because it's in the On Current event and so the code runs when the focus moves from one record to another.

Thanks for the heads up though.

Rusty

I never thought of that. I just made the macro above for one of my form/subforms but just brought the data base window forward and ran the macro direct to test.

You can play about forever with this stuff

Mike