Combo with multiple colums/duplicate data 1

[jmc014]

Hi,

I could do with a little orientation regarding a problem that I have with a combo that holds similar data.
My combo is linked to a table that contains more that 18,000 ports worldwide:

Table: [All_Ports]
[Id] = Auto number
[PortName] = Name of the Port
[CountryCode] = ISO Country Code
[PortCode] = ISO Port Code

The combo that I have shows has three columns: the Port Name, CountryCode and PortCode, but there's a problem:
We all live in a big world, and the are many ports with the same name!!

IE:

Id: 10889 - Newark GB NWK
Id: 10890 - Newark US EWR

And this is where my issue arises from, whenever I select Newark US EWR, I get the results for Newark GB NWK.
This is what I originally dad before the issue came to light:

ComboName = MyPort

CountryCode = DLookup("CountryCode", "All_Ports", "[PortName]=" & "[MyPort]" & "")
PortCode = DLookup("PortCode", "All_Ports", "[PortName]=" & "[MyPort]" & "")
MyPort = CountryCode & PortCode


Without showing the [Id] field in my Combo, what would be the best approach to get the correct data/port after selection?
By the way, I'm using Access 2007

Thanks guys,
JMC

 

[dhookom]

Your combo box bound column must be unique. You can display any column in the combo box by setting the column widths. You can display any other column value in a text box by setting its Control Source to:
=MyPort.Column(x)
Columns are numbered beginning with 0 so the x would be replaced by 1 to display the second column of the Row Source.

Duane
Hook'D on Access
MS Access MVP

 

[jmc014]

Thanks for your valuable help...
Got EXACTLY what I needed with your explanation.

Star's on it way to you.

 

[PWise]

I would change the row source
to
Select Id,PortName & '-' CountryCode ,PortCode
From All_Ports

column count to 3

column widths to to 0";2";1"