Adding a dummy record in the first row of a recordset

There are numerous reasons why you should add a dummy record as the first row of a record. You may want to add a choice for the user to select none for example, or change the first row of a dropdown that is populated by a SQL query that has a unique label, such as select.... However, the best way to add a record coming from a database is in the SQL query itself.

Here, I want to add a new row at the top of the recordset that allows the user to choose none. I don't want to have to create a new SubCategory record into the database, and even if I could, the new None value would not be sorted correctly in the first row. Instead, we will create a dummy record as the first record using SQL.

In the inner query, the first select statement will create a new dummy record using SQL, and the second query is appending the data from the database to this record using a UNION ALL. We are also going to alias the column that we are displaying, in this case, the SubCategory, as a new column that we will sort by (SubCategorySort).

The dummy record at the top will have a 0 string as the SubCategorySort value. A zero value will make the dummy record with None appear at the top of the dropdown list. We will then use the SubCategory database column value as the SubCategorySort psuedo column in the subsequent SELECT statement in order to sort the rest of the data by the SubCategory, and will use an order by statement using our dummy SubCategorySort column outside of the inner query to sort the results like so: