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

AlexSalvadori

Registered User.
Local time
Today, 11:37
Joined
Oct 26, 2012
Messages
151
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
Local time
Today, 03:37
Joined
Oct 29, 2018
Messages
10,798
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
Today, 06:37
Joined
Jun 20, 2003
Messages
6,370
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

error reading drive A:
Local time
Today, 18:37
Joined
May 7, 2009
Messages
9,615
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

Last edited:

AlexSalvadori

Registered User.
Local time
Today, 11:37
Joined
Oct 26, 2012
Messages
151
... 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 (Users: 0, Guests: 1)

Top Bottom