By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,064 Members | 1,542 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,064 IT Pros & Developers. It's quick & easy.

Row Source of ComboBox Question

P: 48
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
Expand|Select|Wrap|Line Numbers
  1. "Windows Business Proffesonal";"Windows XP Proffesonal";"All"
SQL Query
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT pc_small_specs.operating_sysytem FROM pc_small_specs";"All";
Is this possible or should I be taking a different approach
May 15 '08 #1
Share this Question
Share on Google+
5 Replies

Expert Mod 15k+
P: 31,494
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".
May 15 '08 #2

P: 48
Dead on NeoPa was worth asking.
regards panteraboy
May 16 '08 #3

Expert Mod 15k+
P: 31,494
Pleased to help Paul :)
May 16 '08 #4

P: 48
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

Expand|Select|Wrap|Line Numbers
  1. Dim model As String
  2. Set db = CurrentDb
  3. Set qdf = db.QueryDefs("Admin_query")
  4.  model = "='" & Me.CboFind.Value & "' "
  5.  If (Me.CboFind.Value = "All") Then
  6.  model = " Like '*' "
  7.  End If
  8.      strSQL = "SELECT pc_small_specs.* " & _
  9.          " FROM pc_small_specs " & _
  10.          " WHERE pc_small_specs.model" & model & _
  11.          " ORDER BY pc_small_specs.product_id;"
Thanks Again NeoPa
May 23 '08 #5

Expert Mod 15k+
P: 31,494
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 :
Expand|Select|Wrap|Line Numbers
  1. Dim model As String
  3. Set db = CurrentDb
  4. Set qdf = db.QueryDefs("Admin_query")
  6. model = Replace("Like '%V' ", "%V", IIf(Me.CboFind = "All", "*", Me.CboFind)
  7. strSQL = "SELECT * " & _
  8.          "FROM pc_small_specs " & _
  9.          "WHERE [model] " & model & _
  10.          "ORDER BY product_id;"
Does that make sense?
May 23 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.