Subform_visible

[doub]

doub (Visitor) Aug 7, 2001
I`ve a Form including two subforms;
behind the subforms are two queries. I only want the subforms to make visible when the queries behind them have got data, else . visible = false

Thanks for suggestions!

 

[downwitchyobadself]

To put in your master form module:

Public Function TestSfrm1Recordset() as boolean

Dim rst as DAO.Recordset 'if this is Access 2k
Dim rst as Recordset 'only if this is 97

Set rst = Me![NameofYourSubform1Control].Form.RecordsetClone

TestSfrm1Recordset = (rst.RecordCount > 0)

rst.Close

End Function

Then, when you want the subform to show:

Public Function ShowHideSfrm1()

Me![NameofYourSubform1Control].Visible = TestSfrm1Recordset()

End Function


Hope this helps. Same thing for subform2.

 

[Chyld]

it will work the way you want right from the start and on the underlying queries, have the following criteria (as I presume you want the subs to show up based on the value of the main form?!) that checks against the form first. calculates the forms contents and if it matches specific stuff.

i.e.

mainform has a text box called [postcode]

form has two subforms, subform1 and subform2

in the postcode text box, you type HX3

the query for the first subform would say ' right then, I will show this record where the postcode is similar to what my mate wrote in the main form control called [postcode].

i.e.

postarea:
like [forms]![mainform]![postcode]

anyotherinfo:
rep name, product name etc..

ok, subform1 query set and functioning...

now for subform2

this query could be based on selections made in the subform1..

i.e.

repname:
like [forms]![mainform]![subform1]![repname?]

anyotherinfo:
repmobilenumber, or address etc..

now,
in the afterupdate event of the [postcode] text box, you need to type the following..

docmd.requery "subform1"
docmd.requery "subform2"

and that's it.

basically, when you enter a postcode in the main form, query 1 goes away and checks the data, if it finds owt, it
displays it.
then query 2 checks the contents of subform1 i.e. the reps name and displays the information that you need based on that info...

If you use wildcards, then yes, all your records will show. try not to use wildcards.

craig@chyld.co.uk

it wasn't falling that was embarrassing, it was realising that I hadn't tied my shoelaces in the first place.