Lookup in own column

[McFestoe]

I cant get round this and need a fresh idea, i have a teble with a column that has different company names in it, the same company could appear in many records, what i am trying to do is have a lookup box based on the column and display pervious entered companys, this works but i can a get the list box to ignore duplicates.

Any ideas.

Thanks

 

[lespaul]

Your lookup box should have as it's source the following query:

SELECT DISTINCT COMPANYNAME FROM TABLENAME

once the user has selected a company, you will need to run a query to get the desired information about that company:

SELECT * FROM TABLENAME WHERE COMPANYNAME = frmName!lookupboxname

you can use the expression builder to choose the correct form and component on the form.

HTH

Leslie

 

[McFestoe]

Leslie

Thanks for the quick reply,

That is what i have i think, the listbox still has duplicates in it i just need one entry in the list box for each company.

This is what iam using at the moment

SELECT [Alarm List].[Alarm Co]
FROM [Alarm List];

Richard

 

[lespaul]

you need the DISTINCT keyword in order to eliminate the duplicates:

SELECT DISTINCT [Alarm List].[Alarm Co]
FROM [Alarm List];

now if you have

Joe's Alarm Service, Inc.
Joe's Alarm Service
Joes Alram Service

those are all "distinct" and will all show up.

leslie

 

[willir]

McFestoe

The Filter property works well too.

Create an event procedure for On Change.

If you are using a numeric value, coding could look like...

Private Sub YourComboBox_AfterUpdate()

    Dim intID As Integer
    
    If NZ(Me.YourComboBox) > 0 Then
        intID = Me.YourComboBox
        Me.Filter = "YourNumericField = " & intID
        Me.FilterOn = True
    End If

If you are using a string, coding would need to include quotes around the variable...

Private Sub YourComboBox_AfterUpdate()

    Dim strID As String, strQ as String

    strQ = CHR$(34)
    
    If LEN(NZ(Me.YourComboBox)) > 0 Then
        strID = Me.YourComboBox
        Me.Filter = "YourStringField = " & strQ & strID & strQ
        Me.FilterOn = True
    End If

 

[McFestoe]

Thanks both of you.

I went the simple way with distinct in the query thanks leslie just did not see that one its always the easy ones that do stand out would would never guess how long it was before i posted for help.

Thanks agian......