Sorting

[Bob Laubla]

I have a possibly silly question. I taught access to myself by using it so I might have spotty knowledge in certain areas. I have access 2003.

This is my code:

runSQL "SELECT DISTINCT TopicLabel, ContentLabel, First(Section) AS firstSection INTO TMP_tblLabels FROM TMP_tblMain GROUP BY TopicLabel, ContentLabel HAVING ContentLabel<>'__Heading__'"
Dim rstLabels As New ADODB.Recordset
rstLabels.Open "TMP_tblLabels", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
rstLabels.MoveFirst

strHeaders = "SortNum|" & IIf(labels, "Info|", "") & IIf(qnumcol, "Num|", "") & UCase(Join(strSurvey, "|"))
strFieldTypes = "Text" & IIf(labels, "|Text", "") & IIf(qnumcol, "|Text", "") & rep("|Memo", UBound(strSurvey) + 1)
createTable2 "TMP_tblOut", strHeaders, strFieldTypes

Dim rstOut As New ADODB.Recordset
rstOut.CursorLocation = adUseClient
rstOut.Open "TMP_tblOut", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
While Not rstLabels.EOF
    rstOut.AddNew
    rstOut!SortNum = sortQ(Join(strSurvey, "|"), rstLabels!FirstSection, rstLabels!TopicLabel, rstLabels!ContentLabel)
    If labels Then rstOut!Info = formatInfo(rstLabels!TopicLabel, rstLabels!ContentLabel)
    If qnumcol Then rstOut!Num = firstQNum(strSurvey(0), rstLabels!TopicLabel, rstLabels!ContentLabel)
    
    For i = 0 To UBound(strSurvey)
        rstOut(strSurvey(i)) = getQ(strSurvey(i), rstLabels!TopicLabel, rstLabels!ContentLabel, (i = 0 And qnumcol), prp, pri, pra, ltq, ro, nr, psi, psp)
    Next
    
    rstLabels.MoveNext
Wend
rstLabels.Close
rstOut.Sort = "SortNum"
rstOut.Save
rstOut.Close

Basically I'm creating TMP_tblOut. After all of the entires have been added with the while loop I want to sort it by SortNum and save it. It seems that rstOut is sorted after .sort is run, but after the code runs TMP_tblOut is not sorted. Can somebody help me? Again, this might be an easy fix I'm overlooking and there might be silly mistakes in the code so I apologize in advance.

[blad3runn69]

maybe something like
Me.OrderBy = "[SortNum] ASC"

[Bob Laubla]

The form does not have a record source, this is a small chunk of the code that I have. I work for a survey firm, and this program generates a comparison between different surveys and it groups them in a very complicated way. All of that is done during the generation of SortNum. At the end of this procedure I have a table. I need this table to be sorted. Immediately after this code I export the table to Word and run a series of Word VB functions to format the text according to various tags that I've inserted into it. I think i've overcomplicated this by giving you my direct example. This is the boiled down scenario:

I create an empty table A
I open it with an adodb recordset
add a few rows
now i want to sort it by one of the fields
and save these changes to A

It seems that I don't have to add any code to make it save the additions, those are saved automatically, however sorting isn't. Again, the recordset is sorted perfectly, I just dont know how to save the exact table within the recordset, The recordset itself contains the sorted table, but I don't know how to save it to my original table... I'm very close to creating another table and saving the records in this recordset into it one by one from the top. There must be a better way...

[Stewart Ross]

Hi. The recordset SORT function does not permanently sort the records - it is a logical sort which does not change the underlying order of the records you added. As you have found, it is not changing anything after the recordset is closed, and really there is no need to do so.

If you want to process the records in sort order you create a query which will sort the records by the sortnum field. You then use this query as the source for your processing, not the table itself.

At its simplest this is just
[code=sql]SELECT * FROM TMP_tblout ORDER BY SortNum;[/code]
and you could use this as the source of another recordset (if processing records in code) or create a query in the query editor for use as the recordsource of a form, say.

-Stewart

[Bob Laubla]

the problem is that the queries dont sort
i orinily had this who code in a single query which i told to orderby sortnum
but sometimes with about 5% chance the table would come out unsorted
its the craziest thing
i fixed it by saving the table in the recordset line by line to another table, its definitely not the best solution but it works 100% so far
i have a quick question, should i have used adodb there? is dao better for somebody like me? i only work with ms access.