Solved OrderBy with DLookup Value? (1 Viewer)

dgreen

Member
Local time
Today, 01:45
Joined
Sep 30, 2018
Messages
397
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.

Code:
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

Banishment Pending
Local time
Today, 01:45
Joined
May 11, 2011
Messages
11,613
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.
 

Micron

AWF VIP
Local time
Today, 02:45
Joined
Oct 20, 2018
Messages
3,476
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

Registered User.
Local time
Today, 17:45
Joined
Jul 4, 2013
Messages
2,770
Given the reference to Me.Parent_ID, it should be FilterBy, not OrderBy
 

dgreen

Member
Local time
Today, 01:45
Joined
Sep 30, 2018
Messages
397
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.
1587412553619.png
 

Cronk

Registered User.
Local time
Today, 17:45
Joined
Jul 4, 2013
Messages
2,770
In that case, drop the Dlookup and just order by the field
Code:
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

Member
Local time
Today, 01:45
Joined
Sep 30, 2018
Messages
397
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

Member
Local time
Today, 01:45
Joined
Sep 30, 2018
Messages
397
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.

1587416637391.png


Code:
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
 

Users who are viewing this thread

Top Bottom