Why is my vba recordset not sorted? (1 Viewer)

[HalloweenWeed]

Quick Q: Why is my vba recordset not sorted? I am doing some pretty advanced data validation and error-correction using multiple vba subroutines (let's not get into that as it will take MUCH too long to explain). My subroutine works most efficiently if the recordset data is sorted according to the values in two fields. So I am opening a recordset using an SQL string. But no matter what I put in my sort string, the records are in the order of the source table. I have verified this by using the following code to copy the recordset to a (temporary) table for debugging purposes:

'record the query result in a temp table (tblQry, "TmpPtntLgng_tbl") for troubleshooting purposes
    Set flds = tblCnf.Fields
    Cnt = flds.Count - 1
    Index = 0

    Do While Not (tblCnf.EOF Or tblCnf.BOF)

        tblQry.AddNew

        For Index = 0 To Cnt

            tblQry.Fields(Index).Value = tblCnf.Fields(Index).Value

        Next Index
        
        tblQry.Update
        tblCnf.MoveNext
        If tblCnf.EOF Then Exit Do

    Loop

    tblCnf.MoveFirst

The resulting table is not sorted, but in the exact same order that the source table is in.

This was the original recordset creation string:

Set tblCnf = DB.OpenRecordset(SQLTmp, dbOpenSnapshot)

but since it didn't work I thought perhaps it was because it was snapshot so I tried:

Set tblCnf = DB.OpenRecordset(SQLTmp, dbOpenDynaset, dbReadOnly)

No joy.

First I tried using a simple SQL string:

SELECT * FROM PtntLogging_tbl WHERE (Not ZeroLength(PtntLogging_tbl.PtntLN_FN_MI) And VarType(MR_No) > 1) ORDER BY PtntLogging_tbl.NmCnfdnc DESC, PtntLogging_tbl.MRCnfdnc DESC;

but since that didn't work (sorted) I tried duplicating what I wanted in a Query, and putting that SQL string in:

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

I verified that the resulting query results were sorted. But again the resulting table wasn't.
Now I know there is a way to get around this (run an Access append query without any necessary parameters from vba), but I really want to know what I am doing wrong, is there a way to do this only in a recordset in RAM memory instead?

 

[plog]

Tables have no order, do not expect them to, and don't believe it when you think you see order in them.

Think of fields as a a single link of chain, records as a few connected chain links and tables as a bucket that you throw your connected chain links into. There is no first chain link section, there is no last chain link section, no 43rd chain link section in that bucket. If you grab one chain link section, there is no prior chain link section nor next chain link section. Order does not exist in that bucket. They are just there connected to their sections, but all mixed together.

Now, when you want to apply order to chain link sections that bucket you can, but you must do it explicitly with a query that has an ORDER BY clause. Tables themselves have no order.

 

[theDBguy]

Hi. Viewing a set of records using a table does not mean they are in any order. Instead, to verify your recordset was sorted, you should loop and walk through each record and verify that they are in order.

 

[Pat Hartman]

The only way to order a recordset is to include an Order By clause. Your query had one so perhaps the data isn't what you think it is. Could one of the order by fields be a lookup field? Sorting by a lookup field sorts on the underlying numeric value NOT the string you see. Just one of the dozen or so reasons to NOT use lookups at the table level.

 

[HalloweenWeed]

The only way to order a recordset is to include an Order By clause. Your query had one so perhaps the data isn't what you think it is. Could one of the order by fields be a lookup field? Sorting by a lookup field sorts on the underlying numeric value NOT the string you see. Just one of the dozen or so reasons to NOT use lookups at the table level.

No, they're type Single actually. I have assigned a confidence level with previous subroutines, and am sorting by the numeric result of that. Once sorted, my "master record" would be the one sorted to the top, so-to-speak, so I record that as master, record the work on the other matching records, mark that category index done, and move on to the next category index (next record if it wasn't marked done yet). That's the purpose of the sort, it saves a lot of vba work. Instead, I am currently creating a "maketable query" version, and then opening the (temporary) table for my vba subroutine.

Thank you for your answer theDBguy, your answer is appreciated, but the goal of this thread is learning for me, not just getting the job done. And watching the execution just wasn't doing the trick for me on this one - it wasn't explaining why. I was seeing that it wasn't sorted.

 

[theDBguy]

Thank you for your answer @theDBguy, your answer is appreciated, but the goal of this thread is learning for me, not just getting the job done. And watching the execution just wasn't doing the trick for me on this one - it wasn't explaining why. I was seeing that it wasn't sorted.

How about now? Do you understand the "why" yet? Just to repeat, you can not verify the sort by using a table. Since you want to know if your recordset was properly sorted, then you have to look at the recordset. For example:

strSQL = "SELECT ID FROM TableName ORDER BY ID"
Set rs = db.OpenRecorset(strSQL, dbOpenSnapshot)
With rs
    Do While Not .EOF
        Debug.Print !ID
        .MoveNext
    Loop
End With

Then, look in the Immediate Window to see if the ID values were in proper sequence. (You can do the same thing with whatever field you want to verify as a sort.)

Hope that makes more sense...

 

[Pat Hartman]

The Order By should have worked if it was by the correct fields and none were Lookups. It is better to solve the mystery rather than moving on because you need to know why what you did didn't work. If you can upload the database with the data and the query, we can look at it. Of course, you would need to obfuscate any sensitive data.

 

[theDBguy]

Hi. I just thought of something where you can use a table to check your recordset sort. Goes something like this:

strSQL = "SELECT FieldName FROM TableName ORDER BY FieldName"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
With rs
    Do While Not .EOF
        x = x + 1
        db.Execute "INSERT INTO NewTable(SortSequence, FieldName) VALUES(" & x & "," & !FieldName & ")", dbFailOnError
        .MoveNext
    Loop
End With

Just a thought...

 

[Pat Hartman]

Access fools people into thinking that tables are in PK sequence because whenever you do a C&R, Access rewrites all tables into PK order. So opening a table in DS view, you see the first 30 or so rows and they always look sorted. However, once you have updated any rows, the updated records could end up at the end of the existing rows. This happens because if you increased the length of the record with the change (all access text fields are variable length), it might no longer fit in the "block" and so Access replaces it with a pointer and puts the updated record at the end of the file.

As the others have mentioned, tables/queries in RDBMS are unordered sets and the ONLY way to have a known sequence is to sort the reocrdset with an Order By clause.

 

[HalloweenWeed]

The Order By should have worked if it was by the correct fields and none were Lookups. It is better to solve the mystery rather than moving on because you need to know why what you did didn't work. If you can upload the database with the data and the query, we can look at it. Of course, you would need to obfuscate any sensitive data.

That's not feasible, data is sensitive Protected Personal Info, source is a linked table with 70 fields and main table made with that source has 122 fields, and these have 4670 records (grows by about 2000 per year). And the present subroutine runs subsequently after 10 subroutines and makes a new table made with the previous subroutines. Quick answer: it's too complicated for that.

Access fools people into thinking that tables are in PK sequence because whenever you do a C&R, Access rewrites all tables into PK order. So opening a table in DS view, you see the first 30 or so rows and they always look sorted. However, once you have updated any rows, the updated records could end up at the end of the existing rows. This happens because if you increased the length of the record with the change (all access text fields are variable length), it might no longer fit in the "block" and so Access replaces it with a pointer and puts the updated record at the end of the file.

As the others have mentioned, tables/queries in RDBMS are unordered sets and the ONLY way to have a known sequence is to sort the reocrdset with an Order By clause.

Not updating/adding any of the rows inside this subroutine, this subroutine merely creates a new table to lookup identification numbers (not the "ID" field shown above) associated with names. Then subsequent subroutines will use the results to process the data with the next step. This subroutine determines which identification number should be used with the associated name, and thus also later subroutines will flag incorrect identification number entries/names. So your concern is appreciated, but invalid.

 

[The_Doc_Man]

Since you were looking for explanations as well as solutions, here it is straight up.

1. Tables are not inherently sorted by a key. If a table is UPDATEd or if an INSERT INTO occurs, that table's record order quickly becomes nearly unpredictable.

Any action that changes a record's contents has a VERY good chance of scrambling the table's record order REGARDLESS of whether or not there is a field size change - because of the possibility of records being in a transaction that could be rolled back or committed. ALSO because if it is a shared DB, if any other person has a recordset open to the same table, SQL has to preserve the records affected by the other person until that session is ended. The records end up jumbled like you tossed them in a blender.

2. The only way to make record order predictable is to build a query with all the fields you need for your target work (including fields to assert ordering) and then place an ORDER BY clause in the SQL statement of the query.

You appear to want to force the table to be ordered. You can't do that with any guarantees. I'll in fact offer contrary guarantees. If you somehow managed to sort the table and then used it in any way that involved insertion or updating, it will not stay sorted. It is the exact nature of the beast

Technically, having a non-sorted table doesn't violate ANY of the rules applicable to any relational database - Access, SQL Server, ORACLE, Sybase, etc. - because ALL of them are based on Set Theory which does not inherently include the concept of order. It is SQL (Structured Query Language) that can impose order in the PRESENTATION of what is in the table if you ask the right questions. Key word there is PRESENTATION. It is SQL that imposes order out of chaos.

Working like heck to create a new table from something you already have that could be sorted is simply making more work for yourself. If you wanted to search something most efficiently, put an index on it. The table STILL won't be sorted - but the index will. And Access WILL use an index (transparently) in a search on that field.

 

[HalloweenWeed]

Since you were looking for explanations as well as solutions, here it is straight up.

1. Tables are not inherently sorted by a key. If a table is UPDATEd or if an INSERT INTO occurs, that table's record order quickly becomes nearly unpredictable.

Any action that changes a record's contents has a VERY good chance of scrambling the table's record order REGARDLESS of whether or not there is a field size change - because of the possibility of records being in a transaction that could be rolled back or committed. ALSO because if it is a shared DB, if any other person has a recordset open to the same table, SQL has to preserve the records affected by the other person until that session is ended. The records end up jumbled like you tossed them in a blender.

2. The only way to make record order predictable is to build a query with all the fields you need for your target work (including fields to assert ordering) and then place an ORDER BY clause in the SQL statement of the query.

You appear to want to force the table to be ordered. You can't do that with any guarantees. I'll in fact offer contrary guarantees. If you somehow managed to sort the table and then used it in any way that involved insertion or updating, it will not stay sorted. It is the exact nature of the beast

Technically, having a non-sorted table doesn't violate ANY of the rules applicable to any relational database - Access, SQL Server, ORACLE, Sybase, etc. - because ALL of them are based on Set Theory which does not inherently include the concept of order. It is SQL (Structured Query Language) that can impose order in the PRESENTATION of what is in the table if you ask the right questions. Key word there is PRESENTATION. It is SQL that imposes order out of chaos.

Working like heck to create a new table from something you already have that could be sorted is simply making more work for yourself. If you wanted to search something most efficiently, put an index on it. The table STILL won't be sorted - but the index will. And Access WILL use an index (transparently) in a search on that field.

You seem to misunderstand, nothing was done between creating the SOL-sorted recordset and reading it, yet it was not sorted.
I did exactly as you said: "ORDER BY clause in the SQL statement of the query." (recordset). It did not work - simple as that. It just kept starting at the first record in my source table, unsorted - foiling my logic subroutine. I simply would like to know why, and you guys have not come up with any possible explanation for that.

This is the code from this procedure:

    SQLCnfTblWhr = "SELECT * FROM PtntLogging_tbl WHERE (Not ZeroLength(PtntLogging_tbl.PtntLN_FN_MI) And "


'procedure for creating a lookup table for MR# to name
    ProcNmbr = 8    'eighth procedure about to start
    ProcRcrdNo = 0
    ProcDescAncmt = "Creating MR# lookup table"
    ProcDescAncmt.Visible = True

    DoCmd.SetWarnings False
    DB.Execute "DELETE * FROM TmpNumVal_tbl" 'assure temp table is empty
    DB.Execute "DELETE * FROM PtntMRLkp_tbl" 'assure lookup table is empty
    DoCmd.SetWarnings True

    Set tblNmC = DB.OpenRecordset("PtntLogging_tbl", dbOpenDynaset, dbReadOnly)
    tblNmC.MoveLast
    tblNmC.MoveFirst
    Set tblMRC = DB.OpenRecordset("PtntLogging_tbl", dbOpenDynaset, dbReadOnly)
    tblMRC.MoveLast
    tblMRC.MoveFirst
    Set tblLkp = DB.OpenRecordset("PtntMRLkp_tbl", dbOpenTable)
    Set tblTmp = DB.OpenRecordset(SQLNumTbl, dbOpenDynaset, dbInconsistent)

    CritriaStr = "VarType(MR_No) > 1) ORDER BY NmCnfdnc DESC, MRCnfdnc DESC;"
    Set tblCnf = DB.OpenRecordset(SQLCnfTblWhr & CritriaStr, dbOpenSnapshot)


    With tblLkp



        tblCnf.MoveLast                         'Find last record position
        MaxRcrdNo = tblCnf.AbsolutePosition + 1
        tblCnf.MoveFirst                        'Record first record position
        MinRcrdNo = tblCnf.AbsolutePosition
        MaxRcrdNo = MaxRcrdNo - MinRcrdNo

'Record confidence/error indicators for MR# without a possible name present
        Do While (Not tblCnf.EOF)


'Skip over MR#s that were previously done
            Do While (Not tblCnf.EOF)

                Cnt = DCount("*", "TmpNumVal_tbl", "NumVal = " & CStr(tblCnf![MR_No]))
                If VarType(Cnt) < 2 Then
                    Msg = "Technical error (Null) counting patient Name Index matches in "
                    Msg = Msg & "temporary number table, cannot continue!"
                    MsgBox (Msg)
                    GoTo ExitSub
                End If
                If Cnt < 1 Then Exit Do
                tblCnf.MoveNext
                If tblCnf.EOF Then Exit Do

            Loop

            If tblCnf.EOF Then Exit Do


            ProcRcrdNo = tblCnf.AbsolutePosition - MinRcrdNo + 1
            Me.Form.Refresh
            DoEvents
            DBEngine.Idle dbRefreshCache

'Stop

            tblNmC.MoveFirst          'reset file position
            CritriaStr = "ID = " & CStr(tblCnf!ID)

            tblNmC.FindNext (CritriaStr)
            If tblNmC.NoMatch Then GoTo NoRcrd

            CritriaStr = "MasterMRRcrdStatus And MR_No = " & CStr(tblCnf!MR_No)

            tblMRC.FindNext (CritriaStr)
            If tblMRC.NoMatch Then GoTo NoRcrd

'here we have records for master name and master MR#
            .AddNew
            ![MR_No] = tblMRC![MR_No]
            ![MasterMRRcrdStatus] = tblMRC![MasterMRRcrdStatus]
            ![MasterMRRcrdIndx] = tblMRC![MasterMRRcrdIndx]
            ![MstrMRCnf] = tblMRC![MstrMRCnf]
            ![MRCnfdnc] = tblMRC![MRCnfdnc]
            ![MasterPtNameRcrdIndx] = tblNmC![MasterPtNameRcrdIndx]
            ![MasterPtNameRcrdStatus] = tblNmC![MasterPtNameRcrdStatus]
            ![MstrNmCnf] = tblNmC![MstrNmCnf]
            ![PtntNameInpt] = tblNmC![PtntNameInpt]
            ![PtntLN_FN_MI] = tblNmC![PtntLN_FN_MI]
            ![PtLstName] = tblNmC![PtLstName]
            ![PtFrstName] = tblNmC![PtFrstName]
            ![PtMidSfxName] = tblNmC![PtMidSfxName]
            ![NmCnfdnc] = tblNmC![NmCnfdnc]
            .Update
            'record the MR# as done in the temp table
            tblTmp.AddNew
            tblTmp![NumVal] = tblMRC![MR_No]
            tblTmp.Update


NoRcrd:
'Stop
            If tblCnf.AbsolutePosition > MaxRcrdNo Then Exit Do
            tblCnf.MoveNext                                             'do all records
            If tblCnf.EOF Then Exit Do


        Loop    'tblCnf (index) record loop


    End With


    tblCnf.Close
    Set tblCnf = Nothing
    tblTmp.Close
    Set tblTmp = Nothing
    tblLkp.Close
    Set tblLkp = Nothing
    tblMRC.Close
    Set tblMRC = Nothing
    tblNmC.Close
    Set tblNmC = Nothing

tblCnf is the table I am talking about that remains unsorted. And it is type SnapShot, so any changes to the source table (not possible anyway) would not do anything.

 

[theDBguy]

I simply would like to know why, and you guys have not come up with any possible explanation for that.

Hi. You haven't responded to my last two posts. Just wondering if you saw them.

 

[The_Doc_Man]

Regarding your code from post #12 this thread:

    DoCmd.SetWarnings False
    DB.Execute "DELETE * FROM TmpNumVal_tbl" 'assure temp table is empty
    DB.Execute "DELETE * FROM PtntMRLkp_tbl" 'assure lookup table is empty
    DoCmd.SetWarnings True

Drop the SetWarnings False (for testing, anyway) because I wonder if those tables are being cleared.

Based on your description and these lines of code

            tblTmp.AddNew
            tblTmp![NumVal] = tblMRC![MR_No]
            tblTmp.Update

The only thing that COULD be sorted is whatever is in tblTmp - but that points to

    Set tblTmp = DB.OpenRecordset(SQLNumTbl, dbOpenDynaset, dbInconsistent)

tblCnf is the table I am talking about that remains unsorted.

To which I say, of course it is not sorted. Table SQLNumTbl is the one being rewritten. I can't see where tblCnf is being altered at all.

You might try something a little simpler.

INSERT INTO PtntLogging_tbl (Mr_No, MasterMMRcrdStatus, MasterMRRcrdIndx, ...., NmCnfdnc)
SELECT MR_No, MasterMMRcrdIndx, MstrMRCnf, ...., NMCnfdnc FROM PtntMRLkp_tbl
    WHERE (Not ZeroLength(PtntLN_FN_MI) And VarType(MR_No) > 1) 
    ORDER BY NmCnfdnc DESC, MRCnfdnc DESC ;

The operation would be faster. But faster still would be just the SELECT clause from above taking the place of that ordered table.

 

[HalloweenWeed]

Based on your description and these lines of code

            tblTmp.AddNew
            tblTmp![NumVal] = tblMRC![MR_No]
            tblTmp.Update

The only thing that COULD be sorted is whatever is in tblTmp - but that points to

    Set tblTmp = DB.OpenRecordset(SQLNumTbl, dbOpenDynaset, dbInconsistent)

tblTmp is just a 'scratchpad' of [MR_No]'s that are already done, it is used only to skip the subsequent entries after they have already been processed. As it infers, it is just a temporary table, and is erased on the next procedural use.
You can see this in my code snippet:

'Skip over MR#s that were previously done
            Do While (Not tblCnf.EOF)

                Cnt = DCount("*", "TmpNumVal_tbl", "NumVal = " & CStr(tblCnf![MR_No]))
                If VarType(Cnt) < 2 Then
                    Msg = "Technical error (Null) counting patient Name Index matches in "
                    Msg = Msg & "temporary number table, cannot continue!"
                    MsgBox (Msg)
                    GoTo ExitSub
                End If
                If Cnt < 1 Then Exit Do
                tblCnf.MoveNext
                If tblCnf.EOF Then Exit Do

            Loop

            If tblCnf.EOF Then Exit Do

To which I say, of course it is not sorted. Table SQLNumTbl is the one being rewritten. I can't see where tblCnf is being altered at all.

You might try something a little simpler.

INSERT INTO PtntLogging_tbl (Mr_No, MasterMMRcrdStatus, MasterMRRcrdIndx, ...., NmCnfdnc)
SELECT MR_No, MasterMMRcrdIndx, MstrMRCnf, ...., NMCnfdnc FROM PtntMRLkp_tbl
    WHERE (Not ZeroLength(PtntLN_FN_MI) And VarType(MR_No) > 1)
    ORDER BY NmCnfdnc DESC, MRCnfdnc DESC ;

The operation would be faster. But faster still would be just the SELECT clause from above taking the place of that ordered table.

Yes, that's correct, I am not altering tblCnf at all, and since it was type Snapshot, it is impossible to alter it. It was only a query to find the proper "master record," to be used as the proper lookup for the [MR_No]. That's why it is imperative that the sort work, or the subroutine doesn't achieve its goal.
I don't want to add records in PtntLogging_tbl in this subroutine, only add records (make a new lookup table) in tblLkp. And by lookup table, I don't mean any "lookup"-type fields.

This snippet of code is the heart of the operation:

'here we have records for master name and master MR#
            .AddNew
            ![MR_No] = tblMRC![MR_No]
            ![MasterMRRcrdStatus] = tblMRC![MasterMRRcrdStatus]
            ![MasterMRRcrdIndx] = tblMRC![MasterMRRcrdIndx]
            ![MstrMRCnf] = tblMRC![MstrMRCnf]
            ![MRCnfdnc] = tblMRC![MRCnfdnc]
            ![MasterPtNameRcrdIndx] = tblNmC![MasterPtNameRcrdIndx]
            ![MasterPtNameRcrdStatus] = tblNmC![MasterPtNameRcrdStatus]
            ![MstrNmCnf] = tblNmC![MstrNmCnf]
            ![PtntNameInpt] = tblNmC![PtntNameInpt]
            ![PtntLN_FN_MI] = tblNmC![PtntLN_FN_MI]
            ![PtLstName] = tblNmC![PtLstName]
            ![PtFrstName] = tblNmC![PtFrstName]
            ![PtMidSfxName] = tblNmC![PtMidSfxName]
            ![NmCnfdnc] = tblNmC![NmCnfdnc]
            .Update

My next subroutine will use the lookup table to find mistakes in the linked dataset, and recommend (hopefully the proper) fixes to the user.

 

[HalloweenWeed]

Hi. You haven't responded to my last two posts. Just wondering if you saw them.

By the way, I am now using a maketable query to produce the properly sorted tblCnf, and have it working properly. I have actually moved on to the next subroutine. Sorry theDBguy, but I must keep moving on, no time for that.

 

[HalloweenWeed]

Here is the code (new version) that now replaces the previous code that I couldn't make work:

    SQLNumTbl = "SELECT * FROM TmpNumVal_tbl"


'procedure for creating a lookup table for MR# to name
    ProcNmbr = 8    'eighth procedure about to start
    ProcRcrdNo = 0
    ProcDescAncmt = "Creating MR# lookup table"
    ProcDescAncmt.Visible = True
    TblDelFlg = True

    DoCmd.SetWarnings False
    DB.Execute "DELETE * FROM TmpNumVal_tbl" 'assure temp table is empty
    'DB.Execute "DELETE * FROM TmpPtntLgng_tbl" 'assure temp table is empty
    DB.Execute "DELETE * FROM PtntMRLkp_tbl" 'assure lookup table is empty
    DoCmd.DeleteObject acTable, "TmpPtntLgng_tbl"
    DoCmd.SetWarnings True
    TblDelFlg = False

    Set tblNmC = DB.OpenRecordset("PtntLogging_tbl", dbOpenDynaset, dbReadOnly)
    tblNmC.MoveLast
    Set tblMRC = DB.OpenRecordset("PtntLogging_tbl", dbOpenDynaset, dbReadOnly)
    tblMRC.MoveLast
    Set tblLkp = DB.OpenRecordset("PtntMRLkp_tbl", dbOpenTable)
    Set tblTmp = DB.OpenRecordset(SQLNumTbl, dbOpenDynaset, dbInconsistent)


'Get the PtntLogging_tbl results filtered and sorted for use with this subroutine
    DoCmd.SetWarnings False
    DB.Execute "MakTblPtLogSrtd_q", dbFailOnError
    DoCmd.SetWarnings True

    Set tblQry = DB.OpenRecordset("TmpPtntLgng_tbl", dbOpenDynaset, dbReadOnly)


    With tblLkp


        tblQry.MoveLast                         'Find last record position
        MaxRcrdNo = tblQry.AbsolutePosition + 1
        tblQry.MoveFirst                        'Record first record position
        MinRcrdNo = tblQry.AbsolutePosition
        MaxRcrdNo = MaxRcrdNo - MinRcrdNo

'Record confidence/error indicators for MR# without a possible name present
        Do While (Not tblQry.EOF)


'Skip over MR#s that were previously done
            Do While (Not tblQry.EOF)

                Cnt = DCount("*", "TmpNumVal_tbl", "NumVal = " & CStr(tblQry![MR_No]))
                If VarType(Cnt) < 2 Then
                    Msg = "Technical error (Null) counting patient Name Index matches in "
                    Msg = Msg & "temporary number table, cannot continue!"
                    MsgBox (Msg)
                    GoTo ExitSub
                End If
                If Cnt < 1 Then Exit Do
                tblQry.MoveNext
                If tblQry.EOF Then Exit Do

            Loop

            If tblQry.EOF Then Exit Do


            ProcRcrdNo = tblQry.AbsolutePosition - MinRcrdNo + 1
            Me.Form.Refresh
            DoEvents
            DBEngine.Idle dbRefreshCache

'Stop

            tblNmC.MoveFirst          'reset file position
            tblMRC.MoveFirst          'reset file position

            CritriaStr = "ID = " & CStr(tblQry!ID)
            tblNmC.FindNext (CritriaStr)
            If tblNmC.NoMatch Then GoTo NoRcrd

            CritriaStr = "MasterMRRcrdStatus And MR_No = " & CStr(tblQry!MR_No)
            tblMRC.FindNext (CritriaStr)
            If tblMRC.NoMatch Then GoTo NoRcrd

'here we have records for master name and master MR#
            .AddNew
            ![MR_No] = tblMRC![MR_No]
            ![MasterMRRcrdStatus] = tblMRC![MasterMRRcrdStatus]
            ![MasterMRRcrdIndx] = tblMRC![MasterMRRcrdIndx]
            ![MstrMRCnf] = tblMRC![MstrMRCnf]
            ![MRCnfdnc] = tblMRC![MRCnfdnc]
            ![MasterPtNameRcrdIndx] = tblNmC![MasterPtNameRcrdIndx]
            ![MasterPtNameRcrdStatus] = tblNmC![MasterPtNameRcrdStatus]
            ![MstrNmCnf] = tblNmC![MstrNmCnf]
            ![PtntNameInpt] = tblNmC![PtntNameInpt]
            ![PtntLN_FN_MI] = tblNmC![PtntLN_FN_MI]
            ![PtLstName] = tblNmC![PtLstName]
            ![PtFrstName] = tblNmC![PtFrstName]
            ![PtMidSfxName] = tblNmC![PtMidSfxName]
            ![NmCnfdnc] = tblNmC![NmCnfdnc]
            .Update
            'record the MR# as done in the temp table
            tblTmp.AddNew
            tblTmp![NumVal] = tblMRC![MR_No]
            tblTmp.Update


NoRcrd:
'Stop
            If tblQry.AbsolutePosition > MaxRcrdNo Then Exit Do
            tblQry.MoveNext                                             'do all records
            If tblQry.EOF Then Exit Do


        Loop    'tblQry (index) record loop


    End With

This is the SQL of the maketable Query "MakTblPtLogSrtd_q":

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

I'm not telling you this to analyze what's wrong here, it works and I am moving on. I'm telling you so you can compare the difference and perhaps discern what was wrong with doing this using the 'recordset' method, thus answering my original question? Because I still don't know why it didn't work.

 

[theDBguy]

By the way, I am now using a maketable query to produce the properly sorted tblCnf, and have it working properly. I have actually moved on to the next subroutine. Sorry theDBguy, but I must keep moving on, no time for that.

Hi. I understand. Good luck with your project.

 

[MajP]

doing this using the 'recordset' method, thus answering my original question

I think the question is not if the destination table is sorted because there no such thing, but are the records in fact not being inserted in the correct order. My guess is that this is simply a display issue. They are inserted correctly, but the table displays on a index. Even though you may have inserted them in the correct order does not mean when you open up the table display they will be in the desired order to view. If the index matches the source table, they likely will not display in the inserted order. If there is an autonumber in the destination table that would identify the order and you can sort the table on that when you open the table view. Do you have an autonumber field or a timestamp? Just a guess.

 

[HalloweenWeed]

I think the question is not if the destination table is sorted because there no such thing, but are the records in fact not being inserted in the correct order. My guess is that this is simply a display issue. They are inserted correctly, but the table displays on a index. Even though you may have inserted them in the correct order does not mean when you open up the table display they will be in the desired order to view. If the index matches the source table, they likely will not display in the inserted order. If there is an autonumber in the destination table that would identify the order and you can sort the table on that when you open the table view. Do you have an autonumber field or a timestamp? Just a guess.

MajP​

thank you for your input. I watched the results stepping though the code in the debugger. The problem was tblCnf was not sorted (as specified in the ORDER BY statement), so it was doing the wrong records first. I even used MsgBox in the 'immediate' window to read the SQL string (SQLTmp) to verify it was proper.