Guaranteeing at least one record in a query

[irishandy]

I've got a query that is used as the data source for a form. However if the query returns nothing the form is completely blank - i.e. no controls appear at all.

How do I ensure that the query returns at least one record, even if that record just has something in a calculated field (e.g. the word "Filler")?

 

[BNPMike]

Why do you want to do this? What's the context? Have you set the form to not allow edits/updates? I can't see otherwise why no controls appear.

 

[wemeier]

If you really want to see the form with all controls on it when there is no data, you can make the form's RecordSource a UNION query that includes a "filler" row every time. For example:

SELECT a, b, c FROM Table1

UNION SELECT DISTINCT "NO DATA" AS a, "NO DATA" AS b, Count(a) AS c FROM Table1;

This will return all rows from Table1 AND a row of "NO DATA". If Table1 is empty, only the "NO DATA" row will appear. The trick is to use an aggregate function in the second SELECT statement as this will always return a row, even if the table is empty (in which case field c will contain a value of 0).

A better way to handle this would be to use the OnOpen event of the form to check to see if the form's underlying recordset has no data. If it has no data, send a MsgBox to the user saying that there is no data. Then, you can close the form or leave it open with no controls.

Finally, you can put code into the OnOpen event of your form to check to see if the form has no records to display. If it does, change the form's RecordSource to the "dummy" SELECT statement shown above:

If Me.RecordsetClone.EOF and Me.RecordsetClone.BOF Then
Me.RecordSource = "SELECT 'NO DATA' AS a, 'NO DATA' AS b, Count(a) AS c FROM Table1;"
Me.Requery
End If


Cruising the Information Superhighway
_{(your mileage may vary)}