sorting the combo box after requery.

[Indra]

Hi There,

I am using the two combo box in my form to contorl the record. the 1st
combo box dispaly all the groupitem. and the 2nd one display the items
with in the grop selected in 1st combo box. its working nicely except
i have small problem or sorting.

Do any one any idea how can i sort the 2nd combo box contain after
requery.

the code i am using (in 1st drop down box) is as below:

Private Sub DropdownLimit_AfterUpdate()
On Error GoTo Err_DropdownLimit_AfterUpdate

Dim strSQL As String, strwhere As String, strQ As String
Dim strID As String
Dim lngID As Long

strQ = Chr$(34)

strSQL = "SELECT TOP 100 PERCENT dbo.Items.ItemNumber,
dbo.Items.ItemCategory, dbo.Items.ItemProduct, dbo.Items.ItemService,
dbo.Items.ItemSize, dbo.Items.ItemUnit, dbo.Items.ItemBookPrice,
dbo.ItemCategories.ItemGroup FROM dbo.Items INNER JOIN
dbo.ItemCategories ON dbo.Items.ItemCategory =
dbo.ItemCategories.ItemCategoryID"

If Nz(Me.DropdownLimit) > 0 Then
lngID = Me.DropdownLimit
strwhere = " WHERE dbo.ItemCategories.ItemGroup = " & lngID
Me.ItemNumber.RowSource = strSQL & strwhere
Me.ItemNumber.Requery

End If

Exit_DropdownLimit_AfterUpdate:
Exit Sub

Err_DropdownLimit_AfterUpdate:
MsgBox Err.Description
Resume Exit_DropdownLimit_AfterUpdate

End Sub

I try to put order by in strSQL select statment, but it display the
error message.

Please let me know if you have any information.

Thanks.
Indra.

 

[Ken Snell [MVP]]

You didn't post how you tried it, but the following should work if you
replace FieldName with the actual name of the desired sorting field:

Me.ItemNumber.RowSource = strSQL & strwhere & _
" ORDER BY FieldName;"