Access 2007 Emtpy Query Quirk

Question

I have an Access 2007 DB in which I want to display a split-view form (half form, half datasheet) with a data source of a query. However, there is a chance that the query could be empty. In the event that the query has zero rows, none of the form controls are displayed. Is there any way to make sure all the form controls are displayed on an empty query?

Answer 1

Sorry at work and cannot give a detailed answer.

Is the query editable? In other words can you insert or update rows in the query? Try to bind to an editable query and see if that helps.

Seth

Answer 2

I haven't worked with Access 2007's split forms, but usually in Access the empty recordset and blank controls means you have Allow Additions turned off. If it's ON, then a blank empty record will be displayed, and as soon as you edit one of the controls, it will add the record.

If that's not suitable, you'll have to some fancy stuff with altering the recordsource. I do this all the time to display forms before the user has supplied criteria to retrieve a restricted recordsource. Usually, I'll give the form a recordsource like this, where MyTable is the smallest table in the database:

  SELECT TOP 1 Null As Field1, Null As Field2...
  FROM MyTable

This is very fast, and provides values for the fields so you don't have #Name errors. It is also non-editable, so the only way to make it work is to have a New Record button that inserts a new record (with appropriate foreign-key value) and changes the recordsource to the proper one for viewing/editing data.

Like I said, this is all messy. If you can make it work by just turning on Allow Additions, it's much simpler.

Answer 3

Check out this article. It relates to David Fenton's answer but is more comprehensive, which may be helpful in trouble shooting.