Arranging records in Alphabetical order in MSFlexGrid

[homesick]

I have an MSFlexgrid that displays records based on Names and Dates. I was just wondering if there is anyway the user can view or scroll through each set by alphaetical order. Also, does anyone know how the records in MSFlexgrid are arranged?

here is my code...


Public Function DisplayRSGrid(rs As DAO.Recordset, Grid As MSFlexGrid, Optional Form As Form)


Dim fld As DAO.Field

On Error Resume Next

Screen.MousePointer = vbHourglass

With Grid

.Redraw = True

'Setup the grid
.Cols = rs.Fields.Count
.Rows = 1
.Row = 0
.Col = 0
MSFlexGrid1.ColWidth(0) = 2000
MSFlexGrid1.ColWidth(1) = 2000
MSFlexGrid1.ColWidth(3) = 1000
MSFlexGrid1.ColWidth(4) = 2000
MSFlexGrid1.ColWidth(5) = 600
MSFlexGrid1.ColWidth(6) = 600
MSFlexGrid1.ColWidth(7) = 600
MSFlexGrid1.ColWidth(9) = 600
MSFlexGrid1.ColWidth(12) = 600
MSFlexGrid1.ColWidth(13) = 600
MSFlexGrid1.ColWidth(14) = 800
rs.MoveFirst

'Setup the grid headings
For Each fld In rs.Fields
.Col = fld.OrdinalPosition
.ColWidth(.Col) = Form.TextWidth(String(fld.Size + 9, "a&quot) '.Width/ rs.Fields.Count
.Text = fld.Name
Next fld

'Move through each row in the recordset.
While Not rs.EOF
.Rows = Grid.Rows + 1
.Row = .Rows - 1

'loop through all fields
For Each fld In rs.Fields
.Col = fld.OrdinalPosition
.Text = fld.Value
Next fld
rs.MoveNext
Wend

.Redraw = True

End With

Screen.MousePointer = vbNormal

End Function

 

[elibb]

im not sure this is what you want, but what i would do, is this: on the query of the recorset, just add
"... order by Name" and then the recordset will be ordered, and and it will be ordered too on the grid

i hope it helps
Eli

 

[homesick]

thanks for the help but it doesn't work....

 

[Ogi]

Hi,

Do you use an SQL statement to get your data? If so, add the Order By ?????? statement at the end and where I have put ????? you put the name of your data field.

Is that of any use to you?
Regards,
Ogi.

 

[homesick]

i tired to use the ORDER BY aname but it doesn't work.....am i missing something? Here is my code.....



Public Function DisplayRSGrid(rs As DAO.Recordset, Grid As MSFlexGrid, Optional Form As Form)


Dim fld As DAO.Field

On Error Resume Next

Screen.MousePointer = vbHourglass

With Grid

.Redraw = True

'Setup the grid
.Cols = rs.Fields.Count
.Rows = 1
.Row = 0
.Col = 0
MSFlexGrid1.ColWidth(0) = 2000
MSFlexGrid1.ColWidth(1) = 2000
MSFlexGrid1.ColWidth(3) = 1000
MSFlexGrid1.ColWidth(4) = 2000
MSFlexGrid1.ColWidth(5) = 600
MSFlexGrid1.ColWidth(6) = 600
MSFlexGrid1.ColWidth(7) = 600
MSFlexGrid1.ColWidth(9) = 600
MSFlexGrid1.ColWidth(12) = 600
MSFlexGrid1.ColWidth(13) = 600
MSFlexGrid1.ColWidth(14) = 800
rs.MoveFirst

'Setup the grid headings
For Each fld In rs.Fields
.Col = fld.OrdinalPosition
.ColWidth(.Col) = Form.TextWidth(String(fld.Size + 9, "a&quot) '.Width/ rs.Fields.Count
.Text = fld.Name
Next fld

'Move through each row in the recordset.
While Not rs.EOF
.Rows = Grid.Rows + 1
.Row = .Rows - 1

'loop through all fields
For Each fld In rs.Fields
.Col = fld.OrdinalPosition
.Text = fld.Value
Next fld
rs.MoveNext
Wend

.Redraw = True

End With

Screen.MousePointer = vbNormal

End Function


Private Sub Combo5_Change()
Dim db As Database
Dim rs As Recordset

Dim strFile As String
Dim strSQL As String

strFile = "a:\tracker"
strSQL = "SELECT * FROM tracker where aname = '" & combo5.Text & "' and date = '" & Text2.Text & "'"
Set db = OpenDatabase(strFile)
Set rs = db.OpenRecordset(strSQL)

Call DisplayRSGrid(rs, MSFlexGrid1)

rs.Close
db.Close

Set rs = Nothing
Set db = Nothing


End Sub

 

[homesick]

OK...it works but it's not exactly what i want.....the records are currently appearing grouped by name and date but as each group appears(enabled by a command buton) i want them to appear in alphabetical order...eg. 3 rows of "Mary Dec 12", 4 rows of "John Dec 13" with a list of each sale they made for each day...it seems to be appearing in random order....is their anyway to group by name and date but in alphabetical order?

i tried "select * from tracker group by aname asc, date asc" but it goves me an error of "syntax in group by clause"


as you can see....i need MAJOR help...

 

[homesick]

Help....

 

[WilMead]

Group by does not sort. Well, not the groups at least. It collects so you can use functions like count and min or max.

Try "select * from tracker ORDER by aname, date "

Wil Mead
wmead@optonline.net

 

[homesick]

Thanks Will but I already have it grouped by name and date with these sql statements...


'under my combo5_change

Select * from tracker where aname = ' " &combo5.text &" ' and date = ' " &text2.text& " ' "


'under my Text2_change

Select * from tracker where date = ' " &combo5.text &" ' and date = ' " &text2.text& " ' "


what i'm trying to do is when the group of records appear...i want each group to be shown in alphabetical order.....eg. Mary for Dec 28th has 6 sales, John on Dec 28th has 3 sales, Kevin on Dec 28th has 7 sales.....they are all appearing grouped by name and date but i want John's records to appear before Kevin's followed by Mary's.....right now they are appearing in random order.....


Thanks,

Simon