Solved Sorting records of continuous form - using lookup values from a combo box?

[XelaIrodavlas]

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:

'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! ^^

 

[theDBguy]

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]

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]

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:

'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.

 

[XelaIrodavlas]

... 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!