Code Puts Items in Order

[infinitx]

Hi,

I use the following code to add items to a report from a list box:

Private Sub Command20_Click()
Dim rsTempTable As DAO.Recordset
Dim intLoop As Integer
    Dim msg As Integer
        
    msg = MsgBox("Do you want to save the current test for later use?", vbYesNo)
    
        Select Case msg
        Case vbYes
        Text22.Value = 1
        Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "TestName"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    
        Case Else

 Set rsTempTable = CurrentDb.OpenRecordset("TempTest")
 CurrentDb.Execute ("Delete * from TempTest Where TestID=0")
 For intLoop = 0 To Form_TestMaker.List106.ListCount - 1

   If Form_TestMaker.List106.Selected(intLoop) = True Then
     rsTempTable.AddNew
     rsTempTable!TempID = Form_TestMaker.List106.ItemData(intLoop)
     rsTempTable!TestID = 0
     rsTempTable.Update
   End If
 Next intLoop
 
 Set rsTempTable = Nothing
DoCmd.OpenReport "Testing", acViewPreview, , "TestID=0"
DoCmd.OutputTo acReport, "Testing", acFormatRTF, "C:\Program Files\Test.rtf", True
     DoCmd.Close acReport, "Testing"
     DoCmd.Close acForm, "Test"
     
Form_TestMaker.Visible = True
    End Select
End Sub

How can I modify the code so when the items get added to the test by item ID (TempID in the code) from the list box, they appear in the report in the same order that they were selected and not sorted in order of descending item ID?


Thank you

 

[Pat Hartman]

Recordsets must be sorted on a unique identifier to be in a predictable order. If you want to order records in "data entry" order, you must add them to a table that has an autonumber defined. You can then sort on the autonumber to obtain the original data entry order.

 

[infinitx]

Recordsets must be sorted on a unique identifier to be in a predictable order. If you want to order records in "data entry" order, you must add them to a table that has an autonumber defined. You can then sort on the autonumber to obtain the original data entry order.

Could you point me to an example of where I can find how to exactly accomplish what you have mentioned?


Thank you

 

[Pat Hartman]

You don't need any code. Just add an autonumber column to TempTest. You don't need to reset the autonumbers or anything since it makes no difference whatsoever what the actual autonumber values are. It only matters that they are assigned in ascending order when a record is entered and that is controlled by a property setting (the default in fact) when you add the autonumber column. The autonumber values don't even need to be consecutive.

You will not need to add any columns to the .AddNew procedure since you can't supply a value for an autonumber this way in any event.

You will need to add the autonumber column to your sorting and grouping properties in the report.