Adding to recordset for use by Combobox

[g18c]

Hi, after a kick up the backside in the right direction please.

I have a combo box which i fill with a sql query, which consists of a
categoryID and description. Say i have apple, bannanas and eggs. I also
want to add None to this list (but i dont want None to appear in the
Category list as that doesnt make sense) so that when the user uses my
filter combo box they can select any of the categories, or if they
select none i will disable the form's filter.

Is this possible? When i open the recordset and addnew on the recordset
it gets added to the table, and i cant clone the recordset as it throws
an error. Code is below:

Dim rs As New ADODB.Recordset
Dim query As String
query = "SELECT Categories.CategoryID, Categories.CategoryName FROM
Categories ORDER BY Categories.CategoryName;"
rs.Open query, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic

Dim test As ADODB.Recordset
rs.AddNew
rs!CategoryID = "600"
rs!CategoryName = "None"
' end code!!

I appreciate i could end up with key problems as CategoryID is an
autonumber. Am i even going about this the right way?

Many thanks in advance,

Chris

 

[Nikos Yannacopoulos]

Chris,

To begin with, you might get into problems because you are using the
reserved keyword "query" as a variable name; use something else.
Secondly, there is no need for a recordset operation, just assign the
SQL string to the rowsource property.
Suppose you use something like:

Dim strSQL As String
strSQL = "SELECT CategoryID, CategoryName FROM Categories " & _
"UNION SELECT -1, "All" FROM Categories " &
" ORDER BY CategoryName;"
Me.cboCategory.Rowsource = strSQL

Assuming your combo (for which I have assumed the name Me.cboCategory
above) does not display the bound column, and that your autonumber is
incremental (so only positive values), you will get the "All" at the top
of the list. You can also set its default value to -1, so All is the
default selection when the form is opened. Then, in your filtering code,
skip filtering on the CategoryID field if the combo value is -1.

HTH,
Nikos