Row Source of ComboBox Question

[panteraboy]

Hi fellow byters, I was wondering is it possible to combine a value list definition and an SQL query in the same line of the row Source of a combo box. The Value List does the trick for now but if a new operating system type is entered into the database i wish for the combo box to pick it up instead of adding it each time into a value list. The SQL works fine as well but i Wish to add the All Clause

Value List version

"Windows Business Proffesonal";"Windows XP Proffesonal";"All"

SQL Query

SELECT DISTINCT pc_small_specs.operating_sysytem FROM pc_small_specs";"

The following is giving errors but it shows what i wish the ComboBox to display

SELECT DISTINCT pc_small_specs.operating_sysytem FROM pc_small_specs";"All";

Is this possible or should I be taking a different approach
Regards
Panteraboy

[NeoPa]

A different approach.

Create the data you need in a table and maintain that when and where necessary. You can even create a dummy type entry for "All".

[panteraboy]

Dead on NeoPa was worth asking.
regards panteraboy

[NeoPa]

Pleased to help Paul :)

[panteraboy]

I took your advice NeoPa on using the dummy format. It took me a while to get my head around what you were saying but it works just fine. entered all as the model name and used the following

Dim model As String
Set db = CurrentDb
Set qdf = db.QueryDefs("Admin_query")
 model = "='" & Me.CboFind.Value & "' "
 If (Me.CboFind.Value = "All") Then
 model = " Like '*' "
 End If
     strSQL = "SELECT pc_small_specs.* " & _
         " FROM pc_small_specs " & _
         " WHERE pc_small_specs.model" & model & _
         " ORDER BY pc_small_specs.product_id;"

Thanks Again NeoPa

[NeoPa]

No worries Paul. Glad it helped :)

BTW there's another technique you can use when you have filtering which is flexible (Sometimes you want to use wildcards but sometimes not). This works based on the fact that "X = 'Y'" is equivalent to "X Like 'Y'" whenever 'Y' has no wildcard characters embedded.

Your code could then be :

Dim model As String

Set db = CurrentDb
Set qdf = db.QueryDefs("Admin_query")

model = Replace("Like '%V' ", "%V", IIf(Me.CboFind = "All", "*", Me.CboFind)
strSQL = "SELECT * " & _
         "FROM pc_small_specs " & _
         "WHERE [model] " & model & _
         "ORDER BY product_id;"

Does that make sense?