Me.OrderBy for List Box

[brsawvel]

Hello,

Is there a way to create a Me.OrderBy command for a field displayed in a listbox?

 

[pbaldy]

Use an SQL statement that includes an ORDER BY clause.

 

[brsawvel]

I know it isn't right since I got an error, but I wrote the following:

private sub command7_click()
DoCmd.RunSQL (OrderBy(tbl1.fld2))

End Sub

Am I close? This is to order a field I designate in the statement (not necessarily the pk) in a listbox.

Edit **************

I also tried using this from another thread example, but when I click the button, the fields go blank -

Private Sub CmdButtonSortByName_Click
Dim strSQL as String
strSQL = me.listbox.rowsource & "ORDER BY FieldName;"
me.listbox.rowsource = strSQL
End Sub

 

[pbaldy]

I can see the second one working, depending on what the existing rowsource is. If it already had an ORDER BY clause, then that would bomb. If it ended in a semi-colon, that would bomb. In any case, you'd have to add a space before "ORDER" to keep it from running together. Put a Debug.Print strSQL after it's built and see what it is.

 

[brsawvel]

PBaldy,

I really appreciate you helping me on this. It is driving me crazy.


I attempted the Debug.print on the code, but I'm not sure if that was supposed to send a report but nothing happened on click except what has been happening (listbox goes blank)



This is the original rowsource for the listbox (list55) -

SELECT [tblAM].[fldAssetID], [tblAM].[fldManufacturer], [tblAM].[fldCategory], [tblAM].[fldModel], [tblAM].[fldSerial], [tblAM].[fldSite], [tblAM].[fldFloor], [tblAM].[fldLocation], [tblAM].[fldPO], [tblAM].[fldPLine] FROM [tblAM];



Here is another "On_Click" Event I used for the cmd button -


Private Sub fldAssetID_Label_Click()
Me.List55.RowSource = "SELECT [tblAM].[fldAssetID], [tblAM].[fldManufacturer], [tblAM].[fldCategory], [tblAM].[fldModel], [tblAM].[fldSerial], [tblAM].[fldSite], [tblAM].[fldFloor], [tblAM].[fldLocation], [tblAM].[fldPO], [tblAM].[fldPLine] FROM [tblAM], ORDERBY [tblam].[fldLocation]"

End Sub


I pretty much copied the original rowsource from the listbox and added the me.list55.rowsource = and the Order By clause at the end, but the listbox still goes blank after I click the button.

I also tried removing the semicolon from the original rowsource thinking that might work, but it did not.

 

[brsawvel]

I'm assuming the failure on the last command I showed is in the "ORDER BY [tblam].[fldLocation]" because when I copy and paste the original rowsource into the button's "onclick" event, it looks as if it requery's rapidly (flicker) and the table comes back the same.

 

[pbaldy]

The Debug.Print will send the contents of the string to the Immediate Window in the VBA editor. It's a very valuable tool, worth figuring out how to use.

Your last attempt is pretty close, but there are a couple of errors in the SQL. First, you don't want the comma between the FROM and ORDER BY clauses. Second, it's ORDER BY, not ORDERBY (2 words).

 

[brsawvel]

HC!!!!

Thanks Alot! That was it. I really need to find a class where I can learn SQL and VB.