subform disappear when query null

[Jen]

Hello. My subform disappears when it's query is null (which make sence I
guess). However, on this subform I have only one textbox that counts the
values retrieved by the query (recordsource for textbox would be like
"=Count(some_value_in_the_query)". If the query is null then this textbox
shouln't disappear, instead it should show the value "0".
I have tried various Nz(), IIf.., on the textbox but now I realize that
couln't help me. They should somehow be in the query; if the query retrieves
null then it instead should retrieve _something_ so that the query would not
be null, thus making the textbox disappear. I hope I maky any sence at all
here, would be glad to try to clarify what I mean if someone would ask.

The query:

SELECT fakturor.fakturanr, qry_kritisk.kritisk
FROM qry_kritisk INNER JOIN fakturor ON qry_kritisk.fakturanr =
fakturor.fakturanr
WHERE (((qry_kritisk.kritisk)<Date()));

Jen

 

[Allen Browne]

You could place a text box on the main form.
Set it's Control Source to:
=If [Sub1].[Form].[RecordsetClone].[RecordCount] > 0,
Nz([Sub1].[Form].[MyTextBox], 0), 0)

If the subform does nothing but get the count, DCount() may be simpler.

 

[Jen]

Hello Allen and thank you for responding. I didn't know that one could have
an If statement in the Control Source. Can't get it to work either.
I tried this approach:

Private Sub Form_Current()
If RecordsetClone.RecordCount > 0 Then
txt_antal_sena.Value = "1"
Else
txt_antal_sena.Value = "0"
End Sub

txt_antal_sena is the only textbox on that form, but still the form is blank
when there's no recird in the underlying query. Jen.

 

[Allen Browne]

Sorry, Jen, it's supposed to be an Immediate If function call, i.e.:
=IIf [Sub1].[Form].[RecordsetClone].[RecordCount] > 0,
Nz([Sub1].[Form].[MyTextBox], 0), 0)


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Hello Allen and thank you for responding. I didn't know that one could have
an If statement in the Control Source. Can't get it to work either.
I tried this approach:

Private Sub Form_Current()
If RecordsetClone.RecordCount > 0 Then
txt_antal_sena.Value = "1"
Else
txt_antal_sena.Value = "0"
End Sub

txt_antal_sena is the only textbox on that form, but still the form is blank
when there's no recird in the underlying query. Jen.

You could place a text box on the main form.
Set it's Control Source to:
=If [Sub1].[Form].[RecordsetClone].[RecordCount] > 0,
Nz([Sub1].[Form].[MyTextBox], 0), 0)

If the subform does nothing but get the count, DCount() may be simpler.

 

[Jen]

Got it working. Thanks Allen (once again). I thought it would be an iif
statement, but tried some variations without getting it to work so I asked.

This is how the recordsource looks like in case someone other than me may
benefit:

=IIf(antal_sena_subform.Form.RecordsetClone.RecordCount>0;Nz(antal_sena_subf
orm.Form!txt_antal_sena;0);0) & " st "