Blank Form Issue

[Gray]

Hi



I have spent literally weeks trying to circumvent the 'blank' form issue when forms/subforms have zero records. How on earth is this supposed to work??

When I add a filter to a form and the form returns 0 records, I set allowadditions=true on my mainform; this prevents subsequent vba references to the subform (and/or its controls) from failing.

I store a given user' s filter/orderby settings in a table and when next opening the form, my form-open event applies the latest filters and orderby params. However, now when there 0 records returned and, again, I set allowadditions=true on my mainform, subsequent references to the subform do fail? (I think this is because the subform has not been opened?). This only happens when there is also a particular type of OrderBy specified i.e. if the user has elected to sort on a combobox control which is populated by a rowsource = "SELECT blah blah" statement. When these are added to the OrderBy an entry similar to that shown below appears in the form's OrderBy property:-

[Lookup_Parent__Table__Unique__No__Combobox].[Category] DESC

These orderBys work quite happily when records are returned.

This is how the combo is populated:-

SELECT TBL1.Friendly_Name As Category,
TBL1.Date_Record_Added,
TBL1.Added_By_Unique_No,
TBL1.Date_Record_Last_Updated, 
TBL1.Last_Updated_By_Unique_No 
FROM Table_Names AS TBL1 WHERE (TBL1.Deleted=False) 
ORDER BY TBL1.Added_By_Unique_No

It seems to me that the only way to get around this is to force the subform open again whenever a 0 records condition is found... perhaps with some 'dummy' data? but how would I do that?? I certainly don't want any 'dummy' records displayed in the subform.

How would you the experts handle this please?

[Bob Fitz]

Hi Gray

I'm not sure that I understand the problem corectly, but if you are saying that applying filters and orderby causes problems you could test for the existance of records in the form's OnOpen event and if there are no records then set the FiltersOn to False and OrderBy to "".
Remember that a subform opens before the main form opens.

Hope this helps.

[NTC]

As you know, fundamentally, if there is no record in the record source & no ability to add a new record - - then the form opens blank.

Nothing changes that - so - one either adds a label saying "No Data for this Record" or whatever that toggles on/off visibility... or a single dummy record...or permit new data entry... that's really your universe of choice.

Your order by issue is somewhat tangental to your fundamental issue. The correct answer depends on your situation. I myself have used the label visibility technique - but also sometimes it is only a matter of education of the users.....

Hope this helps.

[Gray]

Hi All

Thanks for the replies.... As you say, I can check for the existence of a '[Lookup' in the orderby before applying the filter and switch OrderByOn=False before the filter to nip the problem in the bud... but I am still curious as to why it's these particular sort criteria i.e. '[Lookup_ xxxx ]' that cause issues... I imagine thousands of people use thse in orderbys so I am surprised to find it occurs?

rgds