Duplicate Values In Combo Box

[Kevsim]

I am using one Table with many fields.
For searching I am using 3 combo boxes, the first I select data, then in the second box associated data appears, I select an item, then associated data appears in the third box for selection.

My problem is, in the first box, the same name may appear many times, how do I limit it to only one name for the same item.

I would appreciate your advise.

kevsim

 

[PHV]

Have a look at the DISTINCT predicate in the SQL help.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Kevsim]

PHV,
Thanks for the info, however, I still have a problem as described below.

If I use the key field in the first combo box, even though I am using DISTINCT in the SQL, I still have duplicates.
I remove the key field reference in the first combo box SQL, then I do not have duplicates.
If I then use DISTINCTROW in the SQL, I then again have duplicates.
I remove the ROW from DISTINCT in the first combo box, then I do not have duplicates.

When I select from the second combo box, the listing is correct.

I then select from the third combo box, the listing is correct.
However, when I make the selection I receive an error message,
“The Microsoft jet database engine does not recognise ‘Highspeed” as a valid field name or expression and the second line of code is highlighted.
Highspeed was the selection in the first combo box.

Private Sub Combo72_AfterUpdate()
'Find record that matches control
Me.RecordsetClone.findfirst "[MachineType]=" & Me![Combo72]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

I would appreciate some advise

kevsim

 

[redapples]

try this

Me.RecordsetClone.findfirst "[MachineType]='" & Me![Combo72] & "'"

Want the best answers? See FAQ181-2886

http://www.redapples.v21hosting.co.uk

 

[Kevsim]

redapples,
Thanks for the info.
I tried what you provided, when I use the code as -

Private Sub Combo72_AfterUpdate()
Me.RecordsetClone.findfirst "[MachineType]='" & Me![Combo72] & "'"
End Sub

Nothing happens, I remove the colan after the equals sign and receive the error message "Syntax error in string expression"

I place the colan back and add the line after what you provided -

Private Sub Combo72_AfterUpdate()
Me.RecordsetClone.findfirst "[MachineType]='" & Me![Combo72] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

No matter what I select, I always return to record No1.

I would appreciate some more assistance.

kevsim

 

[Kevsim]

redapples,
Sorry, I had the incorrect field in the "After Update" event.

This is my final code -

Private Sub Combo72_AfterUpdate()
Me.RecordsetClone.findfirst "[MachineManufacturer]='" & Me![Combo72] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

All is now working OK, thank you.

kevsim