Solved Sorting records of continuous form - using lookup values from a combo box? (1 Viewer)

XelaIrodavlas

Registered User.
Local time
Today, 00:24
Joined
Oct 26, 2012
Messages
174
Hi All,

This is probably simpler than I realise but hard to explain (please bear with me). I have some code setup which sorts records of a continuous form alphabetically. The user clicks on a header label and it adds to the OrderBy property. See code below, it works fine, with one exception...

The hiccup is I have a number of ComboBox controls which are linked to foreignID fields (integer), which lookup name values from another table. The problem is when I sort by those Fields, it puts them in order of the numerical ID (ie 1, 2, 3), not the name i'm looking up.

Does anyone know a way to lookup the string that's being displayed in a combo box, and sort using those values instead of the underlying ID?

Here's an example to help illustrate the problem.

ID ¦ Name (in foreign Table)
1 ¦ Zebra
2 ¦ Alpacca
3 ¦ Moose

Currently this is sorting in order 1, 2, 3, but I want it to go 2, 3, 1... Ie alphabetically based on the lookup value. Hope that makes sense.

Here's my (simplified) code:
Code:
'Add Sort Order: Animals

Dim vOldOrder As String

'Check if there's an existing OrderBy Clause:
If Len(Me.OrderBy & vbNullString) > 0 Then vOldOrder = ", " & Me.OrderBy

'Add new sort to OrderBy:
Me.OrderBy = "[AnimalID]" & vOldOrder                      
Me.OrderByOn = True

Any help much appreciated! ^^
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 17:24
Joined
Oct 29, 2018
Messages
21,358
Hi. I'll have to check something, but I think you may be able to use the Lookup_ComboName syntax instead of just the combo's name.
 

missinglinq

AWF VIP
Local time
Yesterday, 20:24
Joined
Jun 20, 2003
Messages
6,423
The problem is when I sort by those Fields, it puts them in order of the numerical ID (ie 1, 2, 3), not the name i'm looking up.

Are these actually designated as Lookup Fields in the underlying Table(s)?

Linq ;0)>
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:24
Joined
May 7, 2009
Messages
19,169
based your form on a Query.
create a query Joining the Foreign table and adding the [name] (from foreign table) in your query.
add [name] field to your form (txtForeignName).

to make the above textbox invisible:

set it's Tab Stop property to No.
set the border to None.
set its Width to 0.

amend your code that sort your form:
Code:
'Add Sort Order: Animals

Dim vOldOrder As String

'Check if there's an existing OrderBy Clause:
If Len(Me.OrderBy & vbNullString) > 0 Then vOldOrder = ", " & Me.OrderBy

'Add new sort to OrderBy:
Me.OrderBy = "[name]" & vOldOrder                    
Me.OrderByOn = True

see the attached demo.
 

Attachments

  • sortTest.zip
    30.2 KB · Views: 226
Last edited:

XelaIrodavlas

Registered User.
Local time
Today, 00:24
Joined
Oct 26, 2012
Messages
174
... create a query Joining the Foreign table and adding the [name] (from foreign table) in your query.
add [name] field to your form (txtForeignName). ...

Thanks arnelgp I went with your method in the end. Was trying to avoid it as it required me to lift a complicated query from the combo box and put it into the source for the form (evidently, 'animals' was a simplification) but with some fiddling it works no problem and no noticeable affect on performance.

Thanks!
 

Users who are viewing this thread

Top Bottom