SubForm Strategy - No Records

[gray]

Hi

One thing that drives me to distraction is the opening of subforms and what strategy to employ when there are 0 records.

I guess most of you have seen this... Subforms get opened before mainform, but the criteria on which they are loading at any given time might retrieve 0 records. If they are not populated they don't open as such and then all references to them in the main Form_Open event fail left right and centre.

I have a 'my_form_is_loaded module' which can tell if they are open... helps to a degree... but catch 22... if they are not open, one can't call the subform form_open event because they aren't open... derrr

I have a dummy record '"No Records Found" in all my tables which I manipulate into the RS when there are no other records... but it's reams of code across en entire project.

Just wondered what strategies other people employed?

 

[vbaInet]

I don't follow gray. What is your question?

 

[gray]

Hi

My main-form module has lots of references to its subforms. The subforms are populated by SQL Select Statements. These statements which include WHERE clauses are applied to the subforms via their ADODB RS.source params.

When a SELECT WHERE statement retrieves 0 records the subforms become unloaded. Unfortunately this means that all subsequent references to them in the main-form e.g. fred=my_subform_control.form!ID... fail.

In fact any references to any of the subform properties fail as subform object appears to have been cleared (the subform not subform-control). And it's not possible to give the subform another SELECT (one that might retrieve records) because

my_subform_control.form.recordsoure = "SELECT ......"

fails as there is no object any longer.

So I think the best way I can phrase the question is, how do you prevent a subform from unloading when 0 records are retrieved by it?

thnx

 

[vbaInet]

You can use the IsError() command to subvert the problems with the references.

So in your reference to the subform check if IsError() is True, if it isn't you can set whatever properties you want, if it is do nothing.

 

[gray]

Ok thanks... not used that before... and that's a good tip for other things too...

I guess though it's only one half of the equation of this particular issue... let's say for example... I change the SELECT statement for the subform... it retrieves 0 records and unloads itself, I trap that using IsError but based on retrieving 0 records I need to change the SELECT statement.

The attempt to set my_subform_control.form.recordset.source = "SELECT ...." will still fail because the subform object is Nothing...

I've never quite understood why Access unloads a form that has 0 records.... causes havoc....

 

[vbaInet]

What are the Allow Additions and Data Entry properties of your subform set to?

 

[gray]

Apologies, my error.

I have just converted to ADODB recordsets and this architecture works in a slightly different way. In fact the reason my subform object was disappearing was my own fault ... I hadn't set the recordset up correctly.

I think the situation I describe is still true for DAO based subforms though... in fact that's what led me astray here.

I'm sorted now anyway thanks.....

 

[vbaInet]

Glad to hear!