Solved OrderBy with DLookup Value?

[dgreen]

I want to sort my form by the value populated from a combo box. Is it possible?

The combo box is storing a primary key # ([Parent_ID]) as the 1st column (hidden) and then showing the selected organization([Organization]).

Here's the current code. It's erroring on a missing operator "[Org_Child_ID]="

Org_Child_ID and Parent_ID as #. Organization is a string.

Private Sub Label4_Click()
    Dim crid As String
    crid = Nz(DLookup("[Organization]", "[t_Organization]", "[Org_Child_ID]=" & Me.Parent_ID), 0)
    
    Me.OrderByOn = True
    If Me.OrderBy = "crid ASC" Then
        Me.OrderBy = "crid DESC"
    Else
        Me.OrderBy = "crid ASC"
    End If
    Me.Requery
End Sub

 

[plog]

I want to sort my form by the value populated from a combo box. Is it possible?

No. My user name is 'plog'. Suppose you had a form with all user names, how would you order by 'plog'? It makes no sense. You order by fields, not by values.

Perhaps you can demonstrate what you want to occur with data.

 

[Deleted member 147267]

Also, if you're going to pass a value from Nz function to a text type variable when Nz returns Null, then you would not pass 0 but "0"

 

[Cronk]

Given the reference to Me.Parent_ID, it should be FilterBy, not OrderBy

 

[dgreen]

Here's a visual. I'm trying to sort A-Z by the Parent field (red arrow).

If I sort by me.Parent_ID I get the below visual. Since they are in the sequence that they were entered into the database.

 

[Cronk]

In that case, drop the Dlookup and just order by the field

Private Sub Label4_Click()

    Me.OrderByOn = True
    If Me.OrderBy = "Parent ASC" Then
        Me.OrderBy = "Parent DESC"
    Else
        Me.OrderBy = "Parent ASC"
    End If
    Me.Requery
End Sub

That is assuming the data in the Parent column in #5 is coming from the same data field name. Although your original post would indicate that it should be Organization.

 

[dgreen]

I'm seeing if it might work w/ ELookup (http://allenbrowne.com/ser-42.html).

Got the idea from (http://www.utteraccess.com/forum/dlookup-sort-order-t1982937.html)

 

[dgreen]

The value from Parent column is coming from the 1st two columns on the left, so nope.

Although your original post would indicate that it should be Organization.

 

[dgreen]

Easy solution. Just add the Value for the Parent_ID to the query that the form is based on. Then you can sort on the value

Sometimes you just need to talk it out, get a back and forth conversation going to get the brain to think.

Thanks.

Private Sub Label4_Click()
'Sorting A-Z on a field that stores a # value.
'https://www.access-programmers.co.uk/forums/threads/orderby-with-dlookup-value.311001/
   
    Me.OrderByOn = True
    If Me.OrderBy = "[Parent_Organization] ASC" Then
        Me.OrderBy = "[Parent_Organization] DESC"
    Else
        Me.OrderBy = "[Parent_Organization] ASC"
    End If
    Me.Requery
End Sub