subform disappear when query null

  • Thread starter Thread starter Jen
  • Start date Start date
J

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
 
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.
 
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.
 
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.


Jen said:
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 said:
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.
Click to expand...
Click to expand...
 
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 "
 
Back
Top