Bound Object Frame Combo sorted Results

[kevsim]

I have a Combo Box attached to a Object frame where I can select a category from the Frame for the Combo Box to display, this works OK.
If I select All Categories I can have a sorted listing display in the combo box.
When I select a Particular Category, I wish the results to be a sorted listing.

The code for the Particular Category selection is as follows -
SQLText = "SELECT Q_FormFix.T_Fix.FixID,Q_FormFix.Problem,Q_FormFix. Category FROM Q_FormFix WHERE Category = "


Select Case Me![Frame142]

For the data to be sorted, where would I insert the ORDER BY clause.
I have tried several ways but only wind up with errors.
I would appreciate some advise.
kevsim

[jzwp11]

By what field to you want to sort?
I was a little confused by the code snippet you included. How do you pass the category value from the combo box to the SQLText?
What is the datatype of the Category field in Q_FormFix?
I'm not quite sure why you are using the object frame rather than the combo box itself.

I would usually see code like this (the code below assumes that the datatype of the category field is numeric):

SQLText = "SELECT Q_FormFix.T_Fix.FixID,Q_FormFix.Problem,Q_FormFix. Category FROM Q_FormFix WHERE Category = " & me.comboboxname & " ORDER BY  somefield"

[kevsim]

jzwp11,
Thanks for the information.
I tried the code but the combobox list was blank.
I also tried changing the code a little but made no change.
When I remove the additional code the combobox list appears again.

I have attached the full code and would appreciate further assistance.
kevsim

[jzwp11]

If I understand what you are doing, the ORDER BY clause must come after the WClause variable

 Private Sub Frame142_AfterUpdate()
  Dim SQLText, WClause
   
  Select Case Me![Frame142]
  Case 1
  WClause = Chr(34) & "S" & Chr(34)
  Case 2
  WClause = Chr(34) & "H" & Chr(34)
  Case 3
  WClause = Chr(34) & "G" & Chr(34)
  Case 4
  WClause = Chr(34) & "M" & Chr(34)
  Case 5
  WClause = Chr(34) & "O" & Chr(34)
  Case 6
  SQLText = "SELECT Q_FormFix.T_Fix.FixID, Q_FormFix.Problem ,Q_FormFix.Category FROM Q_FormFix "
   
  WClause = ""
  End Select
   
  With Me![Combo27] 
  .RowSource = SQLText & WClause & “ ORDER BY Q_FormFix.Problem”
  .Requery
  End With
   
  End Sub

[kevsim]

jzwp11,
Thanks again for the info.
I tried what you suggested and I could then have the list sorted by the selected category.
However if all categories were selected the list remained blank.
I have made it work but the code looks sloppy, I have attached the code.
kevsim

[jzwp11]

In your option group, can the user select only one option or several options at a time? How does the user select ALL? If they can select multiple items, your present code will not work.

Will the list of options change over time? i.e. will new categories be added? If so, that implies that you will have to update your form & code with each change (I would not want to do that!). Wouldn't it be better to have the categories stored in their own table and then reference them in your table using a foreign key. With that approach, you can use a multi-select list box in conjuction with the combo box. You can feed the list box with a query based on the table holding the categories.

[kevsim]

jzwp11,
The frame is now working how I want it to even though the code is a little sloppy.
With the option frame if you require ALL categories be listed, press the A button.
Each category has it's own button and only one category at a time can be listed.
The category listing is fixed and would not change over time.

I thank you for the assistance.
kevsim

[jzwp11]

Glad to hear that it is working; good luck with your project.