Subform records populated by list box always sorted 1

[TrekBiker]

I'm using a list box lstClients to add people to a subform whose source is an unsorted query qryTempRegular

Its AfterUpdate procedure below appends the ClientID from the list box along with values for Activity, ActivityDate and Hours from fields in the main form.

The subform always sorts itself by ClientName, which qryTempRegular retrieves via the ClientID.

I can't find anything that causes this sorting and need to remove it. Ideas?

Private Sub lstClients_AfterUpdate()

On Error GoTo Err_Q

'Check Date and Duration have been entered
    If IsNull(Me.ActivityDate) Or Me.ActivityDate = "" Or IsNull(Nz(Me.Hours)) Or Me.Hours = "" Then
        MsgBox ("Add Date and Hours")
        Exit Sub
    End If

'Temporarily prevent warnings
    DoCmd.SetWarnings False

'Add new records to tblTempRegular and show in subform
    Dim sqlTxt As String
    Dim item As Variant
       
        sqlTxt = "INSERT INTO tblTempRegular(Activity,ClientID,ActivityDate,Hours,ActivityID) Values(" _
        & Chr(34) & Me.cboActivity.Value & Chr(34) & ", " _
        & Chr(34) & Me.lstClients.Value & Chr(34) & ", " _
        & Chr(34) & Me.ActivityDate & Chr(34) & ", " _
        & Chr(34) & Me.Hours & Chr(34) & ", " _
        & Chr(34) & Me.ActivityID & Chr(34) & ")"
        
        DoCmd.RunSQL (sqlTxt)
    
'Recalculate so the client selected in list box appears in subform
    Me.Recalc

'Turn warnings back on
    DoCmd.SetWarnings True

Exit_Q:
    Exit Sub
Err_Q:
    MsgBox Err.Description
    Resume Exit_Q

End Sub

 

[PHV]

Whats is the value of the OrderBy property of the subform ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TrekBiker]

Order By is blank, Order By On Load is 'Yes'

 

[dhookom]

What order do you want the see the records?

Duane
Hook'D on Access
MS Access MVP

 

[TrekBiker]

When adding a name from the list box I'd like it to appear as the bottom entry in the subform, which is datasheet view.

 

[TrekBiker]

I have just noticed that the ordering of the subform records is the same as that of the Client table. This table feeds into the subform's source query. I also noticed that the subform's Order BY On Load property was set to Yes, but changing to No doesn't change anything.

The big problem with what's happening is that new people added to the subform are often sorted so they appear among previous additions, rather than at the bottom. This makes it more difficult to complete other fields for this person's record.

 

[dhookom]

Add an autonumber field to tblTempRegular and use it for sorting.

Duane
Hook'D on Access
MS Access MVP

 

[TrekBiker]

That's great Duane, does the job perfectly.

Many thanks, as ever.