Why is my vba recordset not sorted?

[HalloweenWeed]

Let me get this straight, the name of your order query is "tblCnf"? Recommend you read the thread on naming conventions. That type of naming convention only adds confusion. qryCnf maybe?
So your printed the Sql string to the immediate window. Did you drop it into the query designger? Did it sort correctly? I do not know what you are doing wrong, but something is wrong with your logic or the sql. I have never seen or heard of an example where you cannot force a recordset to sort.

Although rarely used you can sort a recordset.
dim rs = dao.recordset
set rs = .....
rs.sort = "lastName, FirstName"
dim rsSorted as dao.recordset
set rsSorted = rs.openrecordset

Sorry, I am self-taught, don't know all the naming conventions, I just try my best to follow the examples I have seen.
Hmm, interesting, I was planning on going back in the future to redo this subroutine to use a recordset entirely in memory rather than the hard table. If I get to this I will try it (however I am about to have a second interview for a different job, same employer different agency, and it may not leave me with the time to do this FYI). I didn't know I could do this. Thank you for the tip. This was just the sort of solution that I was looking for with this thread.

 

[HalloweenWeed]

So your printed the Sql string to the immediate window. Did you drop it into the query designger? Did it sort correctly? I do not know what you are doing wrong, but something is wrong with your logic or the sql.

Yes I did (actually in the popup window from MsgBox() from the immediate window). But I found that when I put it into the query designer SQL it didn't like 'SELECT "*"'. So I had to go to the "design view" and drag all the fields into the query manually. So then I took that code, which worked perfectly, and put it into an SQL string in that same subroutine, replacing SQLTmp with:

    SQLTmp = "SELECT PtntLogging_tbl.ID_Key, PtntLogging_tbl.ID,"
    SQLTmp = SQLTmp & " PtntLogging_tbl.MasterPtNameRcrdStatus, PtntLogging_tbl.MasterPtNameRcrdIndx,"
    SQLTmp = SQLTmp & " PtntLogging_tbl.MstrNmCnf, PtntLogging_tbl.MR_No, PtntLogging_tbl.MR_txt,"
    SQLTmp = SQLTmp & " PtntLogging_tbl.MasterMRRcrdStatus, PtntLogging_tbl.MasterMRRcrdIndx,"
    SQLTmp = SQLTmp & " PtntLogging_tbl.MstrMRCnf, PtntLogging_tbl.MRMtchIndx,"
    SQLTmp = SQLTmp & " PtntLogging_tbl.PtntNameInpt, PtntLogging_tbl.PtntLN_FN_MI,"
    SQLTmp = SQLTmp & " PtntLogging_tbl.PtLstName, PtntLogging_tbl.PtFrstName,"
    SQLTmp = SQLTmp & " PtntLogging_tbl.PtMidSfxName, PtntLogging_tbl.PtNmMtchIndx,"
    SQLTmp = SQLTmp & " PtntLogging_tbl.NmCnfdnc, PtntLogging_tbl.MRCnfdnc,"
    SQLTmp = SQLTmp & " PtntLogging_tbl.CntExctPtRcrdMtch, PtntLogging_tbl.CntPtntNameMtch,"
    SQLTmp = SQLTmp & " PtntLogging_tbl.CountMR_LN_FN, PtntLogging_tbl.CntExctNameMtch,"
    SQLTmp = SQLTmp & " PtntLogging_tbl.ErrorFlg, PtntLogging_tbl.MRErrorFlg, PtntLogging_tbl.NmErrorFlg,"
    SQLTmp = SQLTmp & " PtntLogging_tbl.InvrtdPtNames, PtntLogging_tbl.NmInvrsnCnfdnc,"
    SQLTmp = SQLTmp & " PtntLogging_tbl.InvrtdPtNamIndx, PtntLogging_tbl.RcmndPtntLN_FN_MI,"
    SQLTmp = SQLTmp & " PtntLogging_tbl.RcmndPtLstName, PtntLogging_tbl.RcmndPtFrstName,"
    SQLTmp = SQLTmp & " PtntLogging_tbl.RcmndPtMidSfxName, FROM PtntLogging_tbl"
    SQLTmp = SQLTmp & " WHERE (((ZeroLength([PtntLogging_tbl].[PtntLN_FN_MI]))=False)"
    SQLTmp = SQLTmp & " AND ((VarType([MR_No]))>1))"
    SQLTmp = SQLTmp & " ORDER BY PtntLogging_tbl.NmCnfdnc DESC, PtntLogging_tbl.MRCnfdnc DESC;"

    Set tblCnf = DB.OpenRecordset(SQLTmp, dbOpenSnapshot)

And it still didn't sort. FYI.

 

[The_Doc_Man]

Is that a screen capture? Because if so, I am surprised it would have returned anything. There is an extraneous comma in front of the FROM clause, which normally would trigger a syntax error.

 

[MajP]

Where is there no "debug.print SqlTmp" and why did can you not post the result?? I want to see how the string resolves.

 

[HalloweenWeed]

Is that a screen capture? Because if so, I am surprised it would have returned anything. There is an extraneous comma in front of the FROM clause, which normally would trigger a syntax error.

Actually I just pasted from a previous version save, and yes I did troubleshoot said extraneous comma and remove it before it would run. Apparently I used the save previous to that troubleshooting session, sorry.

 

[CJ_London]

Just as an aside. Have you ever copied using windows explorer a large number of files from one folder to another? If so you will no doubt notice that windows does not start with the first file then moves on to the next.

instead, files are copied at random. At least that is my observation based on the destination folder. Unless there is some underlying rule where the order is based on size or creation date or some other metric perhaps to do with level of disk fragmenta?

I believe access will work in much the same way - take the first record and put it here, take the second and squeeze it in someplace else where there is a sufficiently large enough gap in the file continuum, etc.

Or perhaps something to do with efficient recall of records, some will be larger than others based on content so maximising the number of records in a block by grouping the small ones together. But just speculation on my part.

make table probably works better because you are starting with a whole new object

 

[The_Doc_Man]

CJ, this only applies to Compact & Repair, but I believe I have read this somewhere. During a C&R, Access visits the MSysObjects table and copies things in internal object ID order, one object at a time, which has the side effect that every object is internally contiguous. At least until the first time you do anything else.

 

[CJ_London]

It is speculation in my part. I only use tables to store data, not for presentation and a sort order is one of the elements of presentation.

agree with C&R - it is a whole new object

 

[The_Doc_Man]

I've thought about this for a while after you asked. This is SPECULATION on my part and if someone has evidence to the contrary, I would not be totally heart-broken. However, I believe that the method of the copy will make a difference. As noted by the OP, if you use recordset-oriented code or copy something via SQL to make the copied table, you should be able to specify the order of records using ORDER BY syntax. However, if you do a copy/paste through the GUI, I would suggest that you preserve the order of the table at the moment of the copy.